pandas.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "00e728dc",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5cc75be7",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pd.read_csv('assignment/northwind-categories.csv').to_csv('assignment/processed-northwind-categories.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "002cf0ee",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "customer_df = pd.read_csv('assignment/northwind-customers.csv', on_bad_lines='warn', header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "267847cc",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "customer_df.to_csv('assignment/processed-northwind-customers.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "df1a4ba8",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "employee_df = pd.read_csv('assignment/northwind-employees.csv', on_bad_lines='warn', header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e090b076",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "employee_df.to_csv('assignment/processed-northwind-employees.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65214ffb",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "orders_df = pd.read_csv('assignment/northwind-orders.csv',on_bad_lines='warn', header=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "270165bf",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "orders_df.to_csv('assignment/processed-northwind-orders.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31b305a6",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pd.read_csv('assignment/northwind-products.csv').to_csv('assignment/processed-northwind-products.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f2186315",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pd.read_csv('assignment/northwind-suppliers.csv',on_bad_lines='warn').to_csv('assignment/processed-northwind-suppliers.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0a6e8325",
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "pd.read_csv('assignment/northwond-order-details.csv').to_csv('assignment/processed-northwond-order-details.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1092614",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process file - 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49c22784",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.read_table('files/northwind-customers.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd196779",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.rename(columns={df1.columns.values[0]: 'columns'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1826f28d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = (df1['columns']).str.split(pat=',', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "accda05f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.rename(columns={0:'customerID',1:'companyName',2:'contactName',3:'contactTitle',4:'address',5:'city',6:'region',7:'postalCode',8:'country',9:'phone',10:'fax',11:'other'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca528d46",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1['postalCode'] = pd.to_numeric(df1['postalCode'], errors='coerce').fillna(0).astype('int64')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff510566",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.convert_dtypes()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7722e0e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.drop(columns=['other'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f9893982",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df1.dropna()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1c9eb0b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6535e31",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Process file - 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bcc94ac4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = pd.read_table('files/northwind-suppliers.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "014cf3a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "cols =  df2.columns.values[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b9ef537d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.rename(columns={df2.columns.values[0]: 'columns'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ea31b518",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (df2['columns']).str.split(pat=',', expand=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cc2b4dc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df2.dropna(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1762ab10",
   "metadata": {},
   "outputs": [],
   "source": [
    "col_dict = {i:j for i,j in zip(list(range(18)),pd.Series(cols).str.split(',')[0])}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dd647ca1",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.rename(columns=col_dict, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "157ea011",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce91f43b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0684c9d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_list = os.listdir('landing-folder/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2abc2a57",
   "metadata": {},
   "outputs": [],
   "source": [
    "file_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "86b0d9be",
   "metadata": {},
   "outputs": [],
   "source": [
    "def process_files(file_name, landing_folder, processed_folder):    \n",
    "    df = pd.read_table(str(landing_folder) + str(file_name))\n",
    "    cols =  df.columns.values[0]\n",
    "    df.rename(columns={df.columns.values[0]: 'columns'}, inplace=True)\n",
    "    df = (df['columns']).str.split(pat=',', expand=True)\n",
    "    df = df.dropna(axis=1)\n",
    "    col_dict = {i:j for i,j in zip(list(range(18)),pd.Series(cols).str.split(',')[0])}\n",
    "    df.rename(columns=col_dict, inplace=True)\n",
    "    df.head(2)\n",
    "    df.to_csv(str(processed_folder) + str(file_name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7530634",
   "metadata": {},
   "outputs": [],
   "source": [
    "process_files(file_name='northwond-order-details.csv', landing_folder='landing-folder/', processed_folder='processed-folder/')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc8e0e29",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}