Python_Excel_Combo_Laurino_Updated2022.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<b>Melissa Laurino</b> <br>\n",
    "July 2022 <br>\n",
    "Stockton University"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# <b>Using Python 3 and Pandas to compile excel data sheets:</b>\n",
    "Model used: https://stackoverflow.com/questions/15793349/how-to-concatenate-three-excels-files-xlsx-using-python"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Import necessary libraries:\n",
    "import pandas as pd\n",
    "import os\n",
    "from os import listdir"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ensure the Jupyter notebook (.ipynb) document and excel sheets (.xlsx) are located in the same directory. If not, you will have to locate the directory first."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['.ipynb_checkpoints',\n",
       " '2022_02_20.xlsx',\n",
       " '2022_03_18.xlsx',\n",
       " '2022_03_20.xlsx',\n",
       " '2022_03_26.xlsx',\n",
       " '2022_03_27.xlsx',\n",
       " '2022_04_11.xlsx',\n",
       " '2022_04_12.xlsx',\n",
       " '2022_04_13.xlsx',\n",
       " '2022_04_15.xlsx',\n",
       " '2022_04_16.xlsx',\n",
       " '2022_04_20.xlsx',\n",
       " '2022_04_23.xlsx',\n",
       " '2022_04_26.xlsx',\n",
       " '2022_04_28.xlsx',\n",
       " '2022_04_30.xlsx',\n",
       " '2022_05_01.xlsx',\n",
       " '2022_05_03.xlsx',\n",
       " '2022_05_13.xlsx',\n",
       " '2022_05_14.xlsx',\n",
       " '2022_05_15.xlsx',\n",
       " '2022_05_17.xlsx',\n",
       " '2022_05_18.xlsx',\n",
       " '2022_05_19.xlsx',\n",
       " '2022_05_20.xlsx',\n",
       " '2022_05_21.xlsx',\n",
       " '2022_05_22.xlsx',\n",
       " '2022_05_23.xlsx',\n",
       " '2022_05_26.xlsx',\n",
       " '2022_05_28.xlsx',\n",
       " '2022_05_29.xlsx',\n",
       " '2022_05_30.xlsx',\n",
       " '2022_05_31.xlsx',\n",
       " '2022_06_01.xlsx',\n",
       " '2022_06_03.xlsx',\n",
       " '2022_06_04.xlsx',\n",
       " '2022_06_05.xlsx',\n",
       " '2022_06_06.xlsx',\n",
       " '2022_06_07.xlsx',\n",
       " '2022_06_09.xlsx',\n",
       " '2022_06_10.xlsx',\n",
       " '2022_06_11.xlsx',\n",
       " '2022_06_12.xlsx',\n",
       " '2022_06_13.xlsx',\n",
       " '2022_06_14.xlsx',\n",
       " '2022_06_15.xlsx',\n",
       " '2022_06_16.xlsx',\n",
       " '2022_06_17.xlsx',\n",
       " '2022_06_18.xlsx',\n",
       " '2022_06_19.xlsx',\n",
       " '2022_06_20.xlsx',\n",
       " '2022_06_21.xlsx',\n",
       " '2022_06_22.xlsx',\n",
       " '2022_06_23.xlsx',\n",
       " '2022_06_24.xlsx',\n",
       " '2022_06_25.xlsx',\n",
       " '2022_06_26.xlsx',\n",
       " '2022_06_28.xlsx',\n",
       " '2022_06_29.xlsx',\n",
       " '2022_06_30.xlsx',\n",
       " '2022_07_01.xlsx',\n",
       " '2022_07_02.xlsx',\n",
       " '2022_07_03.xlsx',\n",
       " '2022_07_04.xlsx',\n",
       " '2022_07_05.xlsx',\n",
       " '2022_07_06.xlsx',\n",
       " '2022_07_07.xlsx',\n",
       " '2022_07_08.xlsx',\n",
       " '2022_07_09.xlsx',\n",
       " '2022_07_10.xlsx',\n",
       " '2022_07_11.xlsx',\n",
       " '2022_07_12.xlsx',\n",
       " '2022_07_13.xlsx',\n",
       " '2022_07_14.xlsx',\n",
       " '2022_07_15.xlsx',\n",
       " '2022_07_16.xlsx',\n",
       " '2022_07_17.xlsx',\n",
       " '2022_07_18.xlsx',\n",
       " '2022_07_19.xlsx',\n",
       " '2022_07_20.xlsx',\n",
       " '2022_07_21.xlsx',\n",
       " '2022_07_22.xlsx',\n",
       " '2022_07_23.xlsx',\n",
       " '2022_07_24.xlsx',\n",
       " '2022_07_25.xlsx',\n",
       " '2022_07_26.xlsx',\n",
       " '2022_07_27.xlsx',\n",
       " '2022_07_28.xlsx',\n",
       " 'Combined Files',\n",
       " 'Python_Excel_Combo_Laurino.ipynb']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#List all the files in the folder you would like to combine:\n",
    "#Example: #os.listdir(\"DataCombo\")\n",
    "os.listdir()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use the list to define your excel sheets (I )\n",
    "excel_sheets = [\n",
    "'2022_02_20.xlsx',\n",
    " '2022_03_18.xlsx',\n",
    " '2022_03_20.xlsx',\n",
    " '2022_03_26.xlsx',\n",
    " '2022_03_27.xlsx',\n",
    " '2022_04_11.xlsx',\n",
    " '2022_04_12.xlsx',\n",
    " '2022_04_13.xlsx',\n",
    " '2022_04_15.xlsx',\n",
    " '2022_04_16.xlsx',\n",
    " '2022_04_20.xlsx',\n",
    " '2022_04_23.xlsx',\n",
    " '2022_04_26.xlsx',\n",
    " '2022_04_28.xlsx',\n",
    " '2022_04_30.xlsx',\n",
    " '2022_05_01.xlsx',\n",
    " '2022_05_03.xlsx',\n",
    " '2022_05_13.xlsx',\n",
    " '2022_05_14.xlsx',\n",
    " '2022_05_15.xlsx',\n",
    " '2022_05_17.xlsx',\n",
    " '2022_05_18.xlsx',\n",
    " '2022_05_19.xlsx',\n",
    " '2022_05_20.xlsx',\n",
    " '2022_05_21.xlsx',\n",
    " '2022_05_22.xlsx',\n",
    " '2022_05_23.xlsx',\n",
    " '2022_05_26.xlsx',\n",
    " '2022_05_28.xlsx',\n",
    " '2022_05_29.xlsx',\n",
    " '2022_05_30.xlsx',\n",
    " '2022_05_31.xlsx',\n",
    " '2022_06_01.xlsx',\n",
    " '2022_06_03.xlsx',\n",
    " '2022_06_04.xlsx',\n",
    " '2022_06_05.xlsx',\n",
    " '2022_06_06.xlsx',\n",
    " '2022_06_07.xlsx',\n",
    " '2022_06_09.xlsx',\n",
    " '2022_06_10.xlsx',\n",
    " '2022_06_11.xlsx',\n",
    " '2022_06_12.xlsx',\n",
    " '2022_06_13.xlsx',\n",
    " '2022_06_14.xlsx',\n",
    " '2022_06_15.xlsx',\n",
    " '2022_06_16.xlsx',\n",
    " '2022_06_17.xlsx',\n",
    " '2022_06_18.xlsx',\n",
    " '2022_06_19.xlsx',\n",
    " '2022_06_20.xlsx',\n",
    " '2022_06_21.xlsx',\n",
    " '2022_06_22.xlsx',\n",
    " '2022_06_23.xlsx',\n",
    " '2022_06_24.xlsx',\n",
    " '2022_06_25.xlsx',\n",
    " '2022_06_26.xlsx',\n",
    " '2022_06_28.xlsx',\n",
    " '2022_06_29.xlsx',\n",
    " '2022_06_30.xlsx',\n",
    " '2022_07_01.xlsx',\n",
    " '2022_07_02.xlsx',\n",
    " '2022_07_03.xlsx',\n",
    " '2022_07_04.xlsx',\n",
    " '2022_07_05.xlsx',\n",
    " '2022_07_06.xlsx',\n",
    " '2022_07_07.xlsx',\n",
    " '2022_07_08.xlsx',\n",
    " '2022_07_09.xlsx',\n",
    " '2022_07_10.xlsx',\n",
    " '2022_07_11.xlsx',\n",
    " '2022_07_12.xlsx',\n",
    " '2022_07_13.xlsx',\n",
    " '2022_07_14.xlsx',\n",
    " '2022_07_15.xlsx',\n",
    " '2022_07_16.xlsx',\n",
    " '2022_07_17.xlsx',\n",
    " '2022_07_18.xlsx',\n",
    " '2022_07_19.xlsx',\n",
    " '2022_07_20.xlsx',\n",
    " '2022_07_21.xlsx',\n",
    " '2022_07_22.xlsx',\n",
    " '2022_07_23.xlsx',\n",
    " '2022_07_24.xlsx',\n",
    " '2022_07_25.xlsx',\n",
    " '2022_07_26.xlsx',\n",
    " '2022_07_27.xlsx',\n",
    " '2022_07_28.xlsx',\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Read them into Pandas\n",
    "excels = [pd.ExcelFile(name) for name in excel_sheets]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a data frame using Pandas\n",
    "frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#For each data frame, remove the header row on all excel sheets except the first one in the list.\n",
    "#This step may or may not be necessary based on your data.\n",
    "frames[1:] = [df[1:] for df in frames[1:]]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Use pandas command \"concatenate\" to combine the data sheets:\n",
    "combined = pd.concat(frames)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Save the new combined document to the directory:\n",
    "combined.to_excel(\"2022Data_SMM.xlsx\", header=False, index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you are combining different version of Excel sheets composed on Mac/PC, 2007/2003 etc., you may encounter a calculation discrepancy with Dates or other variables due to the 1904 date system. With my data, sometimes the dates in excel may shift four years due to this system.\n",
    "<br>Follow this tutorial to fix your discrepancy:<br>\n",
    "https://www.accountingweb.com/technology/excel/when-excel-dates-mysteriously-shift-by-4-years<br>\n",
    "IF the tutorial does not fix your discrepancy, you may be able to quickly copy and paste a column into Microsoft Notepad to fix the format issue.<br>"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}