notebooks/Splitting CSV file by rows and combining it back.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Splitting CSV file by rows and combining it back\n",
    "\n",
    "\n",
    "often times, while dealing with big CSV or excel files, \n",
    "we need to split files into smaller chunks by equal number of rows and later combine it back.\n",
    "\n",
    "In this notebook, we will use `ChatGPT` coding skills to create a script to perform these tasks.\n",
    "\n",
    "- we will split CSV by rows\n",
    "    - note down row volume/stats of this CSV file\n",
    "    - split it\n",
    "    - combine it back and then reconcile this total stats to verify data quality.\n",
    "\n",
    "`To split a file by size, please refer to this notebook.`\n",
    "[Splitting big file and combining it back](https://github.com/AmitXShukla/RPA/blob/main/notebooks/Splitting%20big%20file%20and%20combining%20it%20back.ipynb)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "################################\n",
    "# create RPA Virtual environment\n",
    "################################\n",
    "# py -m venv RPA\n",
    "# RPA\\Scripts\\activate.bat\n",
    "# import sys\n",
    "# sys.path\n",
    "# py -m pip --version\n",
    "################################\n",
    "## MAKE SURE these packages\n",
    "## are installed\n",
    "################################\n",
    "# pip install pandas\n",
    "################################"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "tags": []
   },
   "source": [
    "# Test csv file\n",
    "\n",
    "run code to capture stats \n",
    "\n",
    "#### rows, total, mean, median\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Total    123000.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# take a sample csv file\n",
    "# read file to DataFrame\n",
    "# note stats for reconciliation\n",
    "################################\n",
    "\n",
    "import pandas as pd\n",
    "dfBefore=pd.read_csv(\"../SampleData/sampleData.csv\")\n",
    "dfBefore.head(5)\n",
    "dfBefore[\"Total\"].mean(), \n",
    "dfBefore[[\"Total\"]].median()\n",
    "\n",
    "# dfAfter=pd.read_csv(\"../SampleData/combined_output.csv\")\n",
    "# dfAfter.head(5)\n",
    "# dfAfter[\"Total\"].mean(), \n",
    "# dfAfter[[\"Total\"]].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# list directory contents\n",
    "import os\n",
    "os.listdir(\"../SampleData/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### copy paste ChatGPT code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# split the file\n",
    "# run this code in python\n",
    "# terminal\n",
    "\n",
    "## - Python code\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "# Read in the CSV file\n",
    "df = pd.read_csv('../SampleData/sampleData.csv')\n",
    "\n",
    "# Determine the number of rows to split the CSV by\n",
    "chunk_size = 10000\n",
    "num_chunks = int(len(df) / chunk_size) + 1\n",
    "\n",
    "# Split the CSV into chunks of equal size\n",
    "chunks = [df[i*chunk_size:(i+1)*chunk_size] for i in range(num_chunks)]\n",
    "\n",
    "# Write each chunk to its own CSV file\n",
    "for i, chunk in enumerate(chunks):\n",
    "    chunk.to_csv(f'../SampleData/output_{i}.csv', index=False)\n",
    "\n",
    "## run readdir() to make sure files are generated"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# list directory contents\n",
    "# output_xx.csv should be generated\n",
    "import os\n",
    "os.listdir(\"../SampleData/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Combine the chunks back into a single CSV file\n",
    "combined_df = pd.concat(chunks, ignore_index=True)\n",
    "combined_df.to_csv('../SampleData/combined_output.csv', index=False)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "In this code, we first read in the input CSV file using the Pandas library. We then determine the desired chunk size (in this case, 1000 rows), and calculate the number of chunks needed to split the CSV into equal parts. We then use a list comprehension to split the CSV into chunks of the desired size.\n",
    "\n",
    "Next, we write each chunk to its own CSV file using the to_csv method of a Pandas DataFrame. The filename for each output file includes a number to keep track of which chunk it belongs to.\n",
    "\n",
    "Finally, we combine the chunks back into a single CSV file using the concat function from the Pandas library. We pass ignore_index=True to ensure that the row indices of the combined DataFrame are continuous. We then write the combined DataFrame to a CSV file using to_csv.\n",
    "\n",
    "Note that this code assumes that the input CSV file has a header row. If it does not, you may need to modify the code to handle that case."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# read directory to verify newly generate files\n",
    "import os\n",
    "os.listdir(\"../SampleData/\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## compare Before and After Results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfBefore=pd.read_csv(\"../SampleData/sampleData.csv\")\n",
    "dfBefore.head(5)\n",
    "dfBefore[\"Total\"].mean(), dfBefore[[\"Total\"]].median()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfAfter=pd.read_csv(\"../SampleData/combined_output.csv\")\n",
    "dfAfter.head(5)\n",
    "dfAfter[\"Total\"].mean(), dfAfter[[\"Total\"]].median()"
   ]
  }
 ],
 "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.11.2"
  },
  "vscode": {
   "interpreter": {
    "hash": "04b8008dc8de53db04c4b46981e564130c4f658f9828d716cf8a3f9d3479d0f3"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}