Tutorials/Python_Data_Engineering/5. Python for Data Engineering/5.2 Working with file formats.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "be858957",
   "metadata": {},
   "source": [
    "### <div id=\"py\"> Working with different file formats </div>\n",
    "\n",
    "\n",
    "\n",
    "- JSON (java script object notation)\n",
    "- CSV (Command Seperated Values)\n",
    "- Excel\n",
    "- Avro\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db1ec6af",
   "metadata": {},
   "source": [
    "### Data comes in various forms\n",
    "\n",
    "<img src=\"images/data_gen.jpeg\">\n",
    "\n",
    "As a data person you will deal with various type of data and it's important to learn how to handle these file formats "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "96639378",
   "metadata": {},
   "source": [
    "\n",
    "\n",
    "## Working in JSON files\n",
    "\n",
    "***\n",
    "Since its inception, JSON has quickly become the de facto standard for information exchange. \n",
    "\n",
    "Chances are you’re here because you need to transport some data from here to there. Perhaps you’re gathering information through an API or storing your data in a document database. \n",
    "\n",
    "One way or another, you’re up to your neck in JSON, and you’ve got to Python your way out.\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4945d9c7",
   "metadata": {},
   "source": [
    "## A (Very) Brief History of JSON\n",
    "\n",
    "JSON stangs for JavaScript Object Notation was inspired by a subset of the JavaScript programming language dealing with object literal syntax. \n",
    "\n",
    "Ultimately, the community at large adopted JSON because it’s easy for both humans and machines to create and understand."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fa222af9",
   "metadata": {},
   "source": [
    "### Look, it’s JSON!\n",
    "```\n",
    "{\n",
    "    \"firstName\": \"Jane\",\n",
    "    \"lastName\": \"Doe\",\n",
    "    \"hobbies\": [\"running\", \"sky diving\", \"singing\"],\n",
    "    \"age\": 35,\n",
    "    \"children\": [\n",
    "        {\n",
    "            \"firstName\": \"Alice\",\n",
    "            \"age\": 6\n",
    "        },\n",
    "        {\n",
    "            \"firstName\": \"Bob\",\n",
    "            \"age\": 8\n",
    "        }\n",
    "    ]\n",
    "}\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceafc60f",
   "metadata": {},
   "source": [
    "### Does this look similar to something?\n",
    "\n",
    "YES! Python **dictionary!**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ff0b925",
   "metadata": {},
   "source": [
    "### Writing JSON files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "19cba507",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "481ec6f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = {\n",
    "    \"president\": {\n",
    "        \"name\": \"Zaphod Beeblebrox\",\n",
    "        \"species\": \"Betelgeusian\"\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "8cd43843",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data_file.json\", \"w\") as write_file:\n",
    "    json.dump(data, write_file)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "040a25a4",
   "metadata": {},
   "source": [
    " Note that `dump()` takes two positional arguments:\n",
    " 1. the data object to be serialized, and\n",
    " 2. the file-like object to which the bytes will be written."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ae9f99b",
   "metadata": {},
   "source": [
    "### Reading JSON files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "60eade2e",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"data_file.json\", \"r\") as read_file:\n",
    "    data = json.load(read_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "cbb6e862",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "3a6c5f06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'president': {'name': 'Zaphod Beeblebrox', 'species': 'Betelgeusian'}}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4880e5c1",
   "metadata": {},
   "source": [
    "### You can also read JSON as DataFrame in Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "3448afbd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       Courses  Discount\n",
      "Index0  Pandas      1200\n",
      "Index1  Hadoop      1500\n",
      "Index2   Spark      1800\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "jsonStr = '''{\"Index0\":{\"Courses\": \"Pandas\",\"Discount\": \"1200\"},\n",
    "           \"Index1\":{\"Courses\": \"Hadoop\",\"Discount\": \"1500\"},\n",
    "           \"Index2\":{\"Courses\": \"Spark\",\"Discount\": \"1800\"}\n",
    "          }'''\n",
    "\n",
    "# Convert JSON to DataFrame Using read_json()\n",
    "df2 = pd.read_json(jsonStr, orient ='index')\n",
    "print(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0097b560",
   "metadata": {},
   "source": [
    "### Convert Dict To DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "06bceead",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'name': 'Zaphod Beeblebrox', 'species': 'Betelgeusian'}"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['president']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "84371c6d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df3 = pd.DataFrame.from_dict(data, orient ='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "9522c07e",
   "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>name</th>\n",
       "      <th>species</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>president</th>\n",
       "      <td>Zaphod Beeblebrox</td>\n",
       "      <td>Betelgeusian</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        name       species\n",
       "president  Zaphod Beeblebrox  Betelgeusian"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "140b0503",
   "metadata": {},
   "source": [
    "## Working with CSV files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ee495711",
   "metadata": {},
   "source": [
    "A CSV file (Comma Separated Values file) is a type of plain text file that uses specific structuring to arrange tabular data. \n",
    "\n",
    "It’s a plain text file that has data separated by commas!\n",
    "\n",
    "```\n",
    "column 1 name,column 2 name, column 3 name\n",
    "first row data 1,first row data 2,first row data 3\n",
    "second row data 1,second row data 2,second row data 3\n",
    "...\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "40170091",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/hrdata.csv', index_col='Name')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "e4c610f8",
   "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>Hire Date</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Sick Days remaining</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Graham Chapman</th>\n",
       "      <td>03/15/14</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Cleese</th>\n",
       "      <td>06/01/15</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Eric Idle</th>\n",
       "      <td>05/12/14</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Terry Jones</th>\n",
       "      <td>11/01/13</td>\n",
       "      <td>70000.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Terry Gilliam</th>\n",
       "      <td>08/12/14</td>\n",
       "      <td>48000.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               Hire Date   Salary  Sick Days remaining\n",
       "Name                                                  \n",
       "Graham Chapman  03/15/14  50000.0                   10\n",
       "John Cleese     06/01/15  65000.0                    8\n",
       "Eric Idle       05/12/14  45000.0                   10\n",
       "Terry Jones     11/01/13  70000.0                    3\n",
       "Terry Gilliam   08/12/14  48000.0                    7"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "35cb4247",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/hrdata.csv', index_col='Name', parse_dates=['Hire Date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "4997f3a3",
   "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>Hire Date</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Sick Days remaining</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Name</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Graham Chapman</th>\n",
       "      <td>2014-03-15</td>\n",
       "      <td>50000.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>John Cleese</th>\n",
       "      <td>2015-06-01</td>\n",
       "      <td>65000.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Eric Idle</th>\n",
       "      <td>2014-05-12</td>\n",
       "      <td>45000.0</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Terry Jones</th>\n",
       "      <td>2013-11-01</td>\n",
       "      <td>70000.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Terry Gilliam</th>\n",
       "      <td>2014-08-12</td>\n",
       "      <td>48000.0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Michael Palin</th>\n",
       "      <td>2013-05-23</td>\n",
       "      <td>66000.0</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Hire Date   Salary  Sick Days remaining\n",
       "Name                                                   \n",
       "Graham Chapman 2014-03-15  50000.0                   10\n",
       "John Cleese    2015-06-01  65000.0                    8\n",
       "Eric Idle      2014-05-12  45000.0                   10\n",
       "Terry Jones    2013-11-01  70000.0                    3\n",
       "Terry Gilliam  2014-08-12  48000.0                    7\n",
       "Michael Palin  2013-05-23  66000.0                    8"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ceed2cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('data/hrdata_modified.csv')`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86e220c9",
   "metadata": {},
   "source": [
    "## Working with Excel Files"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "160f022f",
   "metadata": {},
   "source": [
    "Excel spreadsheets are one of those things you might have to deal with at some point. Either it’s because your boss loves them or because marketing needs them, and you might have to learn how to work with spreadsheets.\n",
    "\n",
    "Many companies still prefer using Excel files for their data storage and analysis, as a data expert you should know how to handle these files programatically!\n",
    "\n",
    "To work with Excel files we have package in python `openpyxl`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "d8954655",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: openpyxl in /opt/homebrew/lib/python3.10/site-packages (3.0.10)\n",
      "Requirement already satisfied: et-xmlfile in /opt/homebrew/lib/python3.10/site-packages (from openpyxl) (1.1.0)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip available: \u001b[0m\u001b[31;49m22.3.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.0\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpython3.10 -m pip install --upgrade pip\u001b[0m\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install openpyxl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5007e215",
   "metadata": {},
   "source": [
    "### Basics of Excel\n",
    "\n",
    "<img src=\"images/excel.png\" width=550>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "37ea8610",
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "\n",
    "workbook = Workbook()\n",
    "sheet = workbook.active\n",
    "\n",
    "sheet[\"A1\"] = \"hello\"\n",
    "sheet[\"B1\"] = \"world!\"\n",
    "\n",
    "workbook.save(filename=\"hello_world.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "f417c6ce",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Sheet 1']"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Reading excel file\n",
    "\n",
    "from openpyxl import load_workbook\n",
    "workbook = load_workbook(filename=\"data/sample-xlsx-file.xlsx\")\n",
    "workbook.sheetnames\n",
    "['Sheet 1']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "6576d18c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sheet = workbook.active"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "e897bf90",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Worksheet \"Employee\">"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "0c837c19",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Employee'"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.title"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "3bc2d417",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Cell 'Employee'.A1>"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet[\"A1\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "c029da1d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Rajeev Singh'"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet[\"A2\"].value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "5f785b8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Cell 'Employee'.F10>"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.cell(row=10, column=6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "1cd98ca8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "datetime.datetime(1965, 1, 13, 0, 0)"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.cell(row=3, column=3).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "eb1e0b38",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((<Cell 'Employee'.A1>, <Cell 'Employee'.B1>, <Cell 'Employee'.C1>),\n",
       " (<Cell 'Employee'.A2>, <Cell 'Employee'.B2>, <Cell 'Employee'.C2>))"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet[\"A1:C2\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "192ba25b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('Name', 'Email', 'Date Of Birth', 'Salary', 'Department')\n",
      "('Rajeev Singh', 'rajeev@example.com', datetime.datetime(1992, 7, 21, 0, 0), 1500000.0, 'Software Engineering')\n",
      "('John Doe', 'john@example.com', datetime.datetime(1965, 1, 13, 0, 0), 1300000.0, 'Sales')\n",
      "('Jack Sparrow', 'jack@example.com', datetime.datetime(1986, 12, 19, 0, 0), 1000000.0, 'HR')\n",
      "('Steven Cook', 'steven@example.com', datetime.datetime(1994, 5, 4, 0, 0), 1200000.0, 'Marketing')\n"
     ]
    }
   ],
   "source": [
    "for row in sheet.iter_rows(values_only=True):\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71ce2761",
   "metadata": {},
   "source": [
    "### You can read Excel file as DataFrame using Pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "96673c05",
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_df = pd.read_excel('data/sample-xlsx-file.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "f4129976",
   "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>Name</th>\n",
       "      <th>Email</th>\n",
       "      <th>Date Of Birth</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Department</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rajeev Singh</td>\n",
       "      <td>rajeev@example.com</td>\n",
       "      <td>1992-07-21</td>\n",
       "      <td>1500000</td>\n",
       "      <td>Software Engineering</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>John Doe</td>\n",
       "      <td>john@example.com</td>\n",
       "      <td>1965-01-13</td>\n",
       "      <td>1300000</td>\n",
       "      <td>Sales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jack Sparrow</td>\n",
       "      <td>jack@example.com</td>\n",
       "      <td>1986-12-19</td>\n",
       "      <td>1000000</td>\n",
       "      <td>HR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Steven Cook</td>\n",
       "      <td>steven@example.com</td>\n",
       "      <td>1994-05-04</td>\n",
       "      <td>1200000</td>\n",
       "      <td>Marketing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Name               Email Date Of Birth   Salary  \\\n",
       "0  Rajeev Singh  rajeev@example.com    1992-07-21  1500000   \n",
       "1      John Doe    john@example.com    1965-01-13  1300000   \n",
       "2  Jack Sparrow    jack@example.com    1986-12-19  1000000   \n",
       "3   Steven Cook  steven@example.com    1994-05-04  1200000   \n",
       "\n",
       "             Department  \n",
       "0  Software Engineering  \n",
       "1                 Sales  \n",
       "2                    HR  \n",
       "3             Marketing  "
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "excel_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "1042994b",
   "metadata": {},
   "outputs": [],
   "source": [
    "excel_df.to_excel('data/sample-xlsx-file-modifeid.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aad8c3c2",
   "metadata": {},
   "source": [
    "## Working with AVRO"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e58db039",
   "metadata": {},
   "source": [
    "Apache Avro is a data serialization format. We can store data as `.avro` files on disk. \n",
    "\n",
    "Avro files are typically used with Spark but Spark is completely independent of Avro.\n",
    "\n",
    "Avro is a row-based format that is suitable for evolving data schemas. One benefit of using Avro is that schema and metadata travels with the data.\n",
    "\n",
    "If you have an .avro file, you have the schema of the data as well. \n",
    "\n",
    "The Apache Avro Specification provides easy-to-read yet detailed information."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "78034bbc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: avro-python3 in /opt/homebrew/lib/python3.10/site-packages (1.10.2)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip available: \u001b[0m\u001b[31;49m22.3.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.0\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpython3.10 -m pip install --upgrade pip\u001b[0m\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install avro-python3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8fefc748",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Python 3 with `avro-python3` package available\n",
    "import copy\n",
    "import json\n",
    "import avro\n",
    "from avro.datafile import DataFileWriter, DataFileReader\n",
    "from avro.io import DatumWriter, DatumReader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "f0b82b8f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Note that we combined namespace and name to get \"full name\"\n",
    "schema = {\n",
    "    'name': 'avro.example.User',\n",
    "    'type': 'record',\n",
    "    'fields': [\n",
    "        {'name': 'name', 'type': 'string'},\n",
    "        {'name': 'age', 'type': 'int'}\n",
    "    ]\n",
    "}\n",
    "\n",
    "# Parse the schema so we can use it to write the data\n",
    "schema_parsed = avro.schema.Parse(json.dumps(schema))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "cd70674d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<avro.schema.RecordSchema at 0x12af06830>"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "schema_parsed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "11cbe87f",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# Write data to an avro file\n",
    "with open('users.avro', 'wb') as f:\n",
    "    writer = DataFileWriter(f, DatumWriter(), schema_parsed)\n",
    "    writer.append({'name': 'Pierre-Simon Laplace', 'age': 77})\n",
    "    writer.append({'name': 'John von Neumann', 'age': 53})\n",
    "    writer.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "814999d1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Schema that we specified:\n",
      " {'name': 'avro.example.User', 'type': 'record', 'fields': [{'name': 'name', 'type': 'string'}, {'name': 'age', 'type': 'int'}]}\n",
      "Schema that we parsed:\n",
      " {\"type\": \"record\", \"name\": \"User\", \"namespace\": \"avro.example\", \"fields\": [{\"type\": \"string\", \"name\": \"name\"}, {\"type\": \"int\", \"name\": \"age\"}]}\n",
      "Schema from users.avro file:\n",
      " {'type': 'record', 'name': 'User', 'namespace': 'avro.example', 'fields': [{'type': 'string', 'name': 'name'}, {'type': 'int', 'name': 'age'}]}\n",
      "Users:\n",
      " [{'name': 'Pierre-Simon Laplace', 'age': 77}, {'name': 'John von Neumann', 'age': 53}]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# Read data from an avro file\n",
    "with open('users.avro', 'rb') as f:\n",
    "    reader = DataFileReader(f, DatumReader())\n",
    "    metadata = copy.deepcopy(reader.meta)\n",
    "    schema_from_file = json.loads(metadata['avro.schema'])\n",
    "    users = [user for user in reader]\n",
    "    reader.close()\n",
    "\n",
    "print(f'Schema that we specified:\\n {schema}')\n",
    "print(f'Schema that we parsed:\\n {schema_parsed}')\n",
    "print(f'Schema from users.avro file:\\n {schema_from_file}')\n",
    "print(f'Users:\\n {users}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1ad7a488",
   "metadata": {},
   "source": [
    "### Reading Avro Using Pandas\n",
    "\n",
    "Avro format simply requires a schema and a list of records. We don’t need a dataframe to handle Avro files. \n",
    "\n",
    "However, we can write a `pandas` dataframe into an Avro file or read an Avro file into a `pandas` dataframe. \n",
    "\n",
    "To begin with, we can always represent a dataframe as a list of records and vice-versa"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "e92ddfd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting pandavro\n",
      "  Downloading pandavro-1.7.1.tar.gz (8.1 kB)\n",
      "  Preparing metadata (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25hCollecting fastavro==1.5.1\n",
      "  Downloading fastavro-1.5.1.tar.gz (761 kB)\n",
      "\u001b[2K     \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m761.6/761.6 kB\u001b[0m \u001b[31m15.5 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0ma \u001b[36m0:00:01\u001b[0m\n",
      "\u001b[?25h  Installing build dependencies ... \u001b[?25ldone\n",
      "\u001b[?25h  Getting requirements to build wheel ... \u001b[?25ldone\n",
      "\u001b[?25h  Preparing metadata (pyproject.toml) ... \u001b[?25ldone\n",
      "\u001b[?25hRequirement already satisfied: pandas>=1.1 in /opt/homebrew/lib/python3.10/site-packages (from pandavro) (1.4.4)\n",
      "Requirement already satisfied: numpy>=1.15.4 in /opt/homebrew/lib/python3.10/site-packages (from pandavro) (1.23.3)\n",
      "Requirement already satisfied: pytz>=2020.1 in /opt/homebrew/lib/python3.10/site-packages (from pandas>=1.1->pandavro) (2022.2.1)\n",
      "Requirement already satisfied: python-dateutil>=2.8.1 in /opt/homebrew/lib/python3.10/site-packages (from pandas>=1.1->pandavro) (2.8.2)\n",
      "Requirement already satisfied: six>=1.5 in /opt/homebrew/lib/python3.10/site-packages (from python-dateutil>=2.8.1->pandas>=1.1->pandavro) (1.16.0)\n",
      "Building wheels for collected packages: pandavro, fastavro\n",
      "  Building wheel for pandavro (setup.py) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for pandavro: filename=pandavro-1.7.1-py3-none-any.whl size=5670 sha256=c41fae16385fc0bef1471091596f5f864c0155a760454a2a17ea5c1052fe4e22\n",
      "  Stored in directory: /Users/darshil/Library/Caches/pip/wheels/e2/1d/01/65058b6e307541a4b8738cbc2caf6726a39d02d0e5425a1486\n",
      "  Building wheel for fastavro (pyproject.toml) ... \u001b[?25ldone\n",
      "\u001b[?25h  Created wheel for fastavro: filename=fastavro-1.5.1-cp310-cp310-macosx_12_0_arm64.whl size=437402 sha256=41557f15010a3c7b14ec43c09e6845e61d0270b0191487e91b46f6e23fb31695\n",
      "  Stored in directory: /Users/darshil/Library/Caches/pip/wheels/6b/3a/87/aa70ea616d31ec0f1dbbea02b7366c621b0982e67c84c1335b\n",
      "Successfully built pandavro fastavro\n",
      "Installing collected packages: fastavro, pandavro\n",
      "Successfully installed fastavro-1.5.1 pandavro-1.7.1\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "pip install pandavro"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "e8767509",
   "metadata": {},
   "outputs": [],
   "source": [
    "import copy\n",
    "import json\n",
    "import pandas as pd\n",
    "import pandavro as pdx\n",
    "from avro.datafile import DataFileReader\n",
    "from avro.io import DatumReader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "61a7ea6b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                   name  age\n",
      "0  Pierre-Simon Laplace   77\n",
      "1      John von Neumann   53\n"
     ]
    }
   ],
   "source": [
    "# Data to be saved\n",
    "users = [{'name': 'Pierre-Simon Laplace', 'age': 77},\n",
    "         {'name': 'John von Neumann', 'age': 53}]\n",
    "users_df = pd.DataFrame.from_records(users)\n",
    "print(users_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "cf12af26",
   "metadata": {},
   "outputs": [],
   "source": [
    "pdx.to_avro('data/users_test.avro', users_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "a0237ff5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "# Read the data back\n",
    "users_df_redux = pdx.from_avro('data/users_test.avro')\n",
    "print(type(users_df_redux))\n",
    "# <class 'pandas.core.frame.DataFrame'>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "db7256cc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'type': 'record', 'name': 'User', 'namespace': 'avro.example', 'fields': [{'type': 'string', 'name': 'name'}, {'type': 'int', 'name': 'age'}]}\n"
     ]
    }
   ],
   "source": [
    "# Check the schema for \"users.avro\"\n",
    "with open('users.avro', 'rb') as f:\n",
    "    reader = DataFileReader(f, DatumReader())\n",
    "    metadata = copy.deepcopy(reader.meta)\n",
    "    schema_from_file = json.loads(metadata['avro.schema'])\n",
    "    reader.close()\n",
    "print(schema_from_file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c91be078",
   "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.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}