sr_reader.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "30db2e7e-2ffb-48b8-964f-eba9c3a95bf9",
   "metadata": {},
   "source": [
    "## Import needed library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b847201b-e671-4709-94b0-f2cd040b4342",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "# file Dialog\n",
    "import tkinter as tk\n",
    "from tkinter import filedialog\n",
    "# Path unix style\n",
    "import glob"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "194ccf78-67bc-42cf-ae5d-6de965f751c2",
   "metadata": {},
   "source": [
    "## Function to Read Excels in folder combine them as one Dataframe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "937fb9af-f5e4-4489-969e-f21a72c0ff9f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def baca_fileSR():\n",
    "    # Path Folder with Dialog Window\n",
    "    root = tk.Tk()\n",
    "    file_path = filedialog.askdirectory(title=\"Silahkan Pilih Folder SR\")\n",
    "    root.destroy()\n",
    "    # Read All Excel File in selected folder\n",
    "    all_files = glob.glob(file_path + \"/*.xlsx\")\n",
    "    df_sr = pd.DataFrame()\n",
    "    for f in all_files:\n",
    "        df = pd.read_excel(f,dtype=object)\n",
    "        df_sr = df_sr.append(df,ignore_index=True)\n",
    "    # Convert String to Datetime type\n",
    "    df_sr['Created Date'] = pd.to_datetime(df_sr['Created Date'], format=\"%d %b %Y %H:%M:%S\")\n",
    "    df_sr['Closed Date'] = pd.to_datetime(df_sr['Closed Date'], format=\"%d %b %Y %H:%M:%S\")\n",
    "    df_sr['Complaint Due Date time'] = pd.to_datetime(df_sr['Complaint Due Date time'], format=\"%d %b %Y %H:%M:%S\")\n",
    "    df_sr['Due Date'] = pd.to_datetime(df_sr['Due Date'], format=\"%d %b %Y %H:%M:%S\")\n",
    "    df_sr['Last Updated Date'] = pd.to_datetime(df_sr['Last Updated Date'], format=\"%d %b %Y %H:%M:%S\")\n",
    "    # Throw the dataframe\n",
    "    return df_sr\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "20109292-08cd-4eaf-b402-87d951cccd0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "sr = baca_fileSR()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "71194013-3339-49d7-8aaa-b4a5305426fb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ServiceRequest No</th>\n",
       "      <th>CIF ID</th>\n",
       "      <th>Created Date</th>\n",
       "      <th>Closed Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ID-SR-20210410-ICRM-861001</td>\n",
       "      <td>2192273</td>\n",
       "      <td>2021-04-10 23:43:49</td>\n",
       "      <td>2021-04-13 10:05:21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ID-SR-20210410-ICRM-860559</td>\n",
       "      <td>2117433</td>\n",
       "      <td>2021-04-10 22:55:54</td>\n",
       "      <td>2021-04-10 23:33:22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ID-SR-20210410-ICRM-860558</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-04-10 22:55:07</td>\n",
       "      <td>2021-04-12 00:57:05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ID-SR-20210410-ICRM-860557</td>\n",
       "      <td>2117433</td>\n",
       "      <td>2021-04-10 22:54:26</td>\n",
       "      <td>2021-04-10 22:54:27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>ID-SR-20210410-ICRM-860556</td>\n",
       "      <td>4862791</td>\n",
       "      <td>2021-04-10 22:30:51</td>\n",
       "      <td>2021-04-15 11:12:57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30903</th>\n",
       "      <td>ID-SR-20210411-ICRM-862005</td>\n",
       "      <td>4696878</td>\n",
       "      <td>2021-04-11 07:25:07</td>\n",
       "      <td>2021-04-12 09:31:49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30904</th>\n",
       "      <td>ID-SR-20210411-ICRM-862004</td>\n",
       "      <td>3551910</td>\n",
       "      <td>2021-04-11 07:19:07</td>\n",
       "      <td>2021-04-14 10:09:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30905</th>\n",
       "      <td>ID-SR-20210411-ICRM-862003</td>\n",
       "      <td>3179577</td>\n",
       "      <td>2021-04-11 06:30:37</td>\n",
       "      <td>2021-04-13 10:05:17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30906</th>\n",
       "      <td>ID-SR-20210411-ICRM-862002</td>\n",
       "      <td>3411131</td>\n",
       "      <td>2021-04-11 06:25:24</td>\n",
       "      <td>2021-04-13 10:05:20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30907</th>\n",
       "      <td>ID-SR-20210411-ICRM-862001</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-04-11 01:07:08</td>\n",
       "      <td>2021-04-12 00:57:49</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30908 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                ServiceRequest No   CIF ID        Created Date  \\\n",
       "0      ID-SR-20210410-ICRM-861001  2192273 2021-04-10 23:43:49   \n",
       "1      ID-SR-20210410-ICRM-860559  2117433 2021-04-10 22:55:54   \n",
       "2      ID-SR-20210410-ICRM-860558      NaN 2021-04-10 22:55:07   \n",
       "3      ID-SR-20210410-ICRM-860557  2117433 2021-04-10 22:54:26   \n",
       "4      ID-SR-20210410-ICRM-860556  4862791 2021-04-10 22:30:51   \n",
       "...                           ...      ...                 ...   \n",
       "30903  ID-SR-20210411-ICRM-862005  4696878 2021-04-11 07:25:07   \n",
       "30904  ID-SR-20210411-ICRM-862004  3551910 2021-04-11 07:19:07   \n",
       "30905  ID-SR-20210411-ICRM-862003  3179577 2021-04-11 06:30:37   \n",
       "30906  ID-SR-20210411-ICRM-862002  3411131 2021-04-11 06:25:24   \n",
       "30907  ID-SR-20210411-ICRM-862001      NaN 2021-04-11 01:07:08   \n",
       "\n",
       "              Closed Date  \n",
       "0     2021-04-13 10:05:21  \n",
       "1     2021-04-10 23:33:22  \n",
       "2     2021-04-12 00:57:05  \n",
       "3     2021-04-10 22:54:27  \n",
       "4     2021-04-15 11:12:57  \n",
       "...                   ...  \n",
       "30903 2021-04-12 09:31:49  \n",
       "30904 2021-04-14 10:09:17  \n",
       "30905 2021-04-13 10:05:17  \n",
       "30906 2021-04-13 10:05:20  \n",
       "30907 2021-04-12 00:57:49  \n",
       "\n",
       "[30908 rows x 4 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sr[['ServiceRequest No', 'CIF ID','Created Date','Closed Date']]"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}