Python/Python_Consolidate_Excel_files.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3eaf9cd0-87cf-471f-bf7c-07f81e163206",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-07T18:08:42.284676Z",
     "iopub.status.busy": "2021-10-07T18:08:42.284311Z",
     "iopub.status.idle": "2021-10-07T18:08:42.296032Z",
     "shell.execute_reply": "2021-10-07T18:08:42.295101Z",
     "shell.execute_reply.started": "2021-10-07T18:08:42.284600Z"
    },
    "papermill": {},
    "tags": []
   },
   "source": [
    "<img width=\"8%\" alt=\"Python.png\" src=\"https://raw.githubusercontent.com/jupyter-naas/awesome-notebooks/master/.github/assets/logos/Python.png\" style=\"border-radius: 15%\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "23cc1f57-01c6-462b-8da4-af5ddd5dd815",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "# Python - Consolidate Excel files\n",
    "<a href=\"https://bit.ly/3JyWIk6\">Give Feedback</a> | <a href=\"https://github.com/jupyter-naas/awesome-notebooks/issues/new?assignees=&labels=bug&template=bug_report.md&title=Python+-+Consolidate+Excel+files:+Error+short+description\">Bug report</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "74a154f7-1396-4b5d-bec7-cf9be1669b50",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-10-07T17:02:10.532600Z",
     "iopub.status.busy": "2021-10-07T17:02:10.532368Z",
     "iopub.status.idle": "2021-10-07T17:02:10.540305Z",
     "shell.execute_reply": "2021-10-07T17:02:10.539440Z",
     "shell.execute_reply.started": "2021-10-07T17:02:10.532576Z"
    },
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Tags:** #python #consolidate #files #productivity #snippet #operations #excel"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b89da2b-d7df-4257-8788-89394e0c9786",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Author:** [Florent Ravenel](https://www.linkedin.com/in/ACoAABCNSioBW3YZHc2lBHVG0E_TXYWitQkmwog/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d5c5dfb-7088-4eff-8287-02344ef6d958",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Last update:** 2023-04-12 (Created: 2021-10-07)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9cadf93e-36f8-44e5-855b-7808a7070ece",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "The objective of this notebook is to consolidate multiple Excel files (.xlsx) into one. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8c69c1c-f7e7-443c-b6ab-dd5a4430a351",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Input "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "58458713-c53b-489c-b929-37b1f95583e4",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Import library\n",
    "Import the necessary libraries: os and pandas "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a227aa86-256d-4e9a-b473-343260c16b38",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0c34bf31-eea1-4e0b-8be5-a7863b538534",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Variables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e60725c8-1384-416d-9b8a-3f80cada2a5c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Output\n",
    "excel_output = \"concatenate.xlsx\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "73b5c16a-5c78-43cb-8072-bb597d29c6fb",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Model\n",
    "Use a for loop to \n",
    "- List all the files in the current directory with os.listdir().\n",
    "- Filter files with the .endswith(‘.xlsx’) method.\n",
    "- Make sure the files will be stored into a list called my_list and then combined with pd.concat()\n",
    "\n",
    "Then\n",
    "- Return a dataframe and name it df_concat. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f175e3a4-7584-48e2-a281-00ca2a06649c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "files = os.listdir()\n",
    "my_list = []\n",
    "for file in files:\n",
    "    if file.endswith(\".xlsx\"):\n",
    "        df = pd.read_excel(file)\n",
    "        my_list.append(df)\n",
    "\n",
    "df_concat = pd.concat(my_list, axis=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fbf22da0-4d20-43e7-ab5b-bfa4e5d57b3e",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Output\n",
    "Export your dataframe to an Excel file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "819d4adc-4cd5-455a-87ba-87cf43255024",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_concat.to_excel(excel_output, index=False)"
   ]
  }
 ],
 "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.8.8"
  },
  "naas": {
   "notebook_id": "ccf490cb6569994548b9c95421373ae1e94bb10fa7fb61dadd3576a073277f1d",
   "notebook_path": "Python/Python_Consolidate_Excel_files.ipynb"
  },
  "papermill": {
   "default_parameters": {},
   "environment_variables": {},
   "parameters": {},
   "version": "2.3.3"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}