data-analysis/028-pandas-json_normalize/pandas-json_normalize.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Pandas json_normalize()\n",
    "\n",
    "This is a notebook for the medium article [Pandas json_normalize()](https://bindichen.medium.com/all-pandas-json-normalize-you-should-know-for-flattening-json-13eae1dfb7dd)\n",
    "\n",
    "Please check out article for instructions\n",
    "\n",
    "**License**: [BSD 2-Clause](https://opensource.org/licenses/BSD-2-Clause)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Flattening a simple JSON"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_dict = {\n",
    "    'school': 'ABC primary school',\n",
    "    'location': 'London',\n",
    "    'ranking': 2,\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>school</th>\n",
       "      <th>location</th>\n",
       "      <th>ranking</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>London</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               school location  ranking\n",
       "0  ABC primary school   London        2"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.json_normalize(a_dict)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 1 entries, 0 to 0\n",
      "Data columns (total 3 columns):\n",
      " #   Column    Non-Null Count  Dtype \n",
      "---  ------    --------------  ----- \n",
      " 0   school    1 non-null      object\n",
      " 1   location  1 non-null      object\n",
      " 2   ranking   1 non-null      int64 \n",
      "dtypes: int64(1), object(2)\n",
      "memory usage: 152.0+ bytes\n"
     ]
    }
   ],
   "source": [
    "df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A list of dicts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>class</th>\n",
       "      <th>num_of_students</th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>20</td>\n",
       "      <td>Yellow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class  num_of_students    room\n",
       "0  Year 1               20  Yellow\n",
       "1  Year 2               25    Blue"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_list = [\n",
    "    { 'class': 'Year 1', 'num_of_students': 20, 'room': 'Yellow' },\n",
    "    { 'class': 'Year 2', 'num_of_students': 25, 'room': 'Blue' },\n",
    "]\n",
    "pd.json_normalize(json_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>class</th>\n",
       "      <th>num_of_students</th>\n",
       "      <th>room</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>20.0</td>\n",
       "      <td>Yellow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Blue</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class  num_of_students    room\n",
       "0  Year 1             20.0  Yellow\n",
       "1  Year 2              NaN    Blue"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Missing field is represented as NaN\n",
    "json_list = [\n",
    "    { 'class': 'Year 1', 'num_of_students': 20, 'room': 'Yellow' },\n",
    "    { 'class': 'Year 2', 'room': 'Blue' },\n",
    "]\n",
    "pd.json_normalize(json_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Flattening a JSON with Multiple levels"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_obj = {\n",
    "    'school': 'ABC primary school',\n",
    "    'location': 'London',\n",
    "    'ranking': 2,\n",
    "    'info': {\n",
    "        'president': 'John Kasich',\n",
    "        'contacts': {\n",
    "          'email': {\n",
    "              'admission': 'admission@abc.com',\n",
    "              'general': 'info@abc.com'\n",
    "          },\n",
    "          'tel': '123456789',\n",
    "      }\n",
    "    }\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>school</th>\n",
       "      <th>location</th>\n",
       "      <th>ranking</th>\n",
       "      <th>info.president</th>\n",
       "      <th>info.contacts.email.admission</th>\n",
       "      <th>info.contacts.email.general</th>\n",
       "      <th>info.contacts.tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>London</td>\n",
       "      <td>2</td>\n",
       "      <td>John Kasich</td>\n",
       "      <td>admission@abc.com</td>\n",
       "      <td>info@abc.com</td>\n",
       "      <td>123456789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               school location  ranking info.president  \\\n",
       "0  ABC primary school   London        2    John Kasich   \n",
       "\n",
       "  info.contacts.email.admission info.contacts.email.general info.contacts.tel  \n",
       "0             admission@abc.com                info@abc.com         123456789  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(a_obj)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>school</th>\n",
       "      <th>location</th>\n",
       "      <th>ranking</th>\n",
       "      <th>info.president</th>\n",
       "      <th>info.contacts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>London</td>\n",
       "      <td>2</td>\n",
       "      <td>John Kasich</td>\n",
       "      <td>{'email': {'admission': 'admission@abc.com', '...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               school location  ranking info.president  \\\n",
       "0  ABC primary school   London        2    John Kasich   \n",
       "\n",
       "                                       info.contacts  \n",
       "0  {'email': {'admission': 'admission@abc.com', '...  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# If you don’t want to dig all the way down into each sub-object use the max_level argument.\n",
    "pd.json_normalize(a_obj, max_level=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A list of dicts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>class</th>\n",
       "      <th>student count</th>\n",
       "      <th>room</th>\n",
       "      <th>info.teachers.math</th>\n",
       "      <th>info.teachers.physics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>20</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "      <td>Elon Mask</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Alan Turing</td>\n",
       "      <td>Albert Einstein</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class  student count    room info.teachers.math info.teachers.physics\n",
       "0  Year 1             20  Yellow         Rick Scott             Elon Mask\n",
       "1  Year 2             25    Blue        Alan Turing       Albert Einstein"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_list = [\n",
    "    { \n",
    "        'class': 'Year 1', \n",
    "        'student count': 20, \n",
    "        'room': 'Yellow',\n",
    "        'info': {\n",
    "            'teachers': { \n",
    "                'math': 'Rick Scott', \n",
    "                'physics': 'Elon Mask' \n",
    "            }\n",
    "        }\n",
    "    },\n",
    "    { \n",
    "        'class': 'Year 2', \n",
    "        'student count': 25, \n",
    "        'room': 'Blue',\n",
    "        'info': {\n",
    "            'teachers': { \n",
    "                'math': 'Alan Turing', \n",
    "                'physics': 'Albert Einstein' \n",
    "            }\n",
    "        }\n",
    "    },\n",
    "]\n",
    "\n",
    "pd.json_normalize(json_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>class</th>\n",
       "      <th>student count</th>\n",
       "      <th>room</th>\n",
       "      <th>info.teachers</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>20</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>{'math': 'Rick Scott', 'physics': 'Elon Mask'}</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue</td>\n",
       "      <td>{'math': 'Alan Turing', 'physics': 'Albert Ein...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class  student count    room  \\\n",
       "0  Year 1             20  Yellow   \n",
       "1  Year 2             25    Blue   \n",
       "\n",
       "                                       info.teachers  \n",
       "0     {'math': 'Rick Scott', 'physics': 'Elon Mask'}  \n",
       "1  {'math': 'Alan Turing', 'physics': 'Albert Ein...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(json_list, max_level=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "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>class</th>\n",
       "      <th>student count</th>\n",
       "      <th>room</th>\n",
       "      <th>info.teachers.math</th>\n",
       "      <th>info.teachers.physics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>20</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "      <td>Elon Mask</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Albert Einstein</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class  student count    room info.teachers.math info.teachers.physics\n",
       "0  Year 1             20  Yellow         Rick Scott             Elon Mask\n",
       "1  Year 2             25    Blue                NaN       Albert Einstein"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# with missing keys\n",
    "json_list = [\n",
    "    { \n",
    "        'class': 'Year 1', \n",
    "        'student count': 20, \n",
    "        'room': 'Yellow',\n",
    "        'info': {\n",
    "            'teachers': { 'math': 'Rick Scott', 'physics': 'Elon Mask' }\n",
    "        }\n",
    "    },\n",
    "    { \n",
    "        'class': 'Year 2', \n",
    "        'student count': 25, \n",
    "        'room': 'Blue',\n",
    "        'info': {\n",
    "            'teachers': { 'physics': 'Albert Einstein' }\n",
    "        }\n",
    "    },\n",
    "]\n",
    "\n",
    "pd.json_normalize(json_list)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Flattening a JSON with nested list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_dict = {\n",
    "    'school': 'ABC primary school',\n",
    "    'location': 'London',\n",
    "    'ranking': 2,\n",
    "    'info': {\n",
    "        'president': 'John Kasich',\n",
    "        'contacts': {\n",
    "          'email': {\n",
    "              'admission': 'admission@abc.com',\n",
    "              'general': 'info@abc.com'\n",
    "          },\n",
    "          'tel': '123456789',\n",
    "      }\n",
    "    },\n",
    "    'students': [\n",
    "      { 'name': 'Tom' },\n",
    "      { 'name': 'James' },\n",
    "      { 'name': 'Jacqueline' }\n",
    "    ],\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>school</th>\n",
       "      <th>location</th>\n",
       "      <th>ranking</th>\n",
       "      <th>students</th>\n",
       "      <th>info.president</th>\n",
       "      <th>info.contacts.email.admission</th>\n",
       "      <th>info.contacts.email.general</th>\n",
       "      <th>info.contacts.tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>London</td>\n",
       "      <td>2</td>\n",
       "      <td>[{'name': 'Tom'}, {'name': 'James'}, {'name': ...</td>\n",
       "      <td>John Kasich</td>\n",
       "      <td>admission@abc.com</td>\n",
       "      <td>info@abc.com</td>\n",
       "      <td>123456789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               school location  ranking  \\\n",
       "0  ABC primary school   London        2   \n",
       "\n",
       "                                            students info.president  \\\n",
       "0  [{'name': 'Tom'}, {'name': 'James'}, {'name': ...    John Kasich   \n",
       "\n",
       "  info.contacts.email.admission info.contacts.email.general info.contacts.tel  \n",
       "0             admission@abc.com                info@abc.com         123456789  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(a_dict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jacqueline</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name\n",
       "0         Tom\n",
       "1       James\n",
       "2  Jacqueline"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Flatten students\n",
    "pd.json_normalize(a_dict, record_path =['students'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "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>school</th>\n",
       "      <th>info.contacts.tel</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>123456789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>123456789</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Jacqueline</td>\n",
       "      <td>ABC primary school</td>\n",
       "      <td>123456789</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name              school info.contacts.tel\n",
       "0         Tom  ABC primary school         123456789\n",
       "1       James  ABC primary school         123456789\n",
       "2  Jacqueline  ABC primary school         123456789"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Include meta data\n",
    "pd.json_normalize(\n",
    "    a_dict, \n",
    "    record_path =['students'],\n",
    "    meta=['school', ['info', 'contacts', 'tel']],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### A list of dicts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>class</th>\n",
       "      <th>student count</th>\n",
       "      <th>room</th>\n",
       "      <th>students</th>\n",
       "      <th>info.teachers.math</th>\n",
       "      <th>info.teachers.physics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Year 1</td>\n",
       "      <td>20</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>[{'name': 'Tom', 'sex': 'M', 'grades': {'math'...</td>\n",
       "      <td>Rick Scott</td>\n",
       "      <td>Elon Mask</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Year 2</td>\n",
       "      <td>25</td>\n",
       "      <td>Blue</td>\n",
       "      <td>[{'name': 'Tony', 'sex': 'M'}, {'name': 'Jacqu...</td>\n",
       "      <td>Alan Turing</td>\n",
       "      <td>Albert Einstein</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    class  student count    room  \\\n",
       "0  Year 1             20  Yellow   \n",
       "1  Year 2             25    Blue   \n",
       "\n",
       "                                            students info.teachers.math  \\\n",
       "0  [{'name': 'Tom', 'sex': 'M', 'grades': {'math'...         Rick Scott   \n",
       "1  [{'name': 'Tony', 'sex': 'M'}, {'name': 'Jacqu...        Alan Turing   \n",
       "\n",
       "  info.teachers.physics  \n",
       "0             Elon Mask  \n",
       "1       Albert Einstein  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "json_list = [\n",
    "    { \n",
    "        'class': 'Year 1', \n",
    "        'student count': 20, \n",
    "        'room': 'Yellow',\n",
    "        'info': {\n",
    "            'teachers': { \n",
    "                'math': 'Rick Scott', \n",
    "                'physics': 'Elon Mask' \n",
    "            }\n",
    "        },\n",
    "        'students': [\n",
    "            { \n",
    "                'name': 'Tom', \n",
    "                'sex': 'M', \n",
    "                'grades': { 'math': 66, 'physics': 77 } \n",
    "            },\n",
    "            { \n",
    "                'name': 'James', \n",
    "                'sex': 'M', \n",
    "                'grades': { 'math': 80, 'physics': 78 } \n",
    "            },\n",
    "        ]\n",
    "    },\n",
    "    { \n",
    "        'class': 'Year 2', \n",
    "        'student count': 25, \n",
    "        'room': 'Blue',\n",
    "        'info': {\n",
    "            'teachers': { \n",
    "                'math': 'Alan Turing', \n",
    "                'physics': 'Albert Einstein' \n",
    "            }\n",
    "        },\n",
    "        'students': [\n",
    "            { 'name': 'Tony', 'sex': 'M' },\n",
    "            { 'name': 'Jacqueline', 'sex': 'F' },\n",
    "        ]\n",
    "    },\n",
    "]\n",
    "\n",
    "pd.json_normalize(json_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>sex</th>\n",
       "      <th>grades.math</th>\n",
       "      <th>grades.physics</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>M</td>\n",
       "      <td>66.0</td>\n",
       "      <td>77.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jacqueline</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name sex  grades.math  grades.physics\n",
       "0         Tom   M         66.0            77.0\n",
       "1       James   M         80.0            78.0\n",
       "2        Tony   M          NaN             NaN\n",
       "3  Jacqueline   F          NaN             NaN"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(json_list, record_path =['students'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>sex</th>\n",
       "      <th>grades.math</th>\n",
       "      <th>grades.physics</th>\n",
       "      <th>class</th>\n",
       "      <th>room</th>\n",
       "      <th>info.teachers.math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>M</td>\n",
       "      <td>66.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Year 1</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>Year 1</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year 2</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Alan Turing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jacqueline</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year 2</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Alan Turing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name sex  grades.math  grades.physics   class    room  \\\n",
       "0         Tom   M         66.0            77.0  Year 1  Yellow   \n",
       "1       James   M         80.0            78.0  Year 1  Yellow   \n",
       "2        Tony   M          NaN             NaN  Year 2    Blue   \n",
       "3  Jacqueline   F          NaN             NaN  Year 2    Blue   \n",
       "\n",
       "  info.teachers.math  \n",
       "0         Rick Scott  \n",
       "1         Rick Scott  \n",
       "2        Alan Turing  \n",
       "3        Alan Turing  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(\n",
    "    json_list, \n",
    "    record_path =['students'], \n",
    "    meta=['class', 'room', ['info', 'teachers', 'math']]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Ignoring KeyError if keys are not always present "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    { \n",
    "        'class': 'Year 1', \n",
    "        'student count': 20, \n",
    "        'room': 'Yellow',\n",
    "        'info': {\n",
    "            'teachers': { \n",
    "                'math': 'Rick Scott', \n",
    "                'physics': 'Elon Mask',\n",
    "            }\n",
    "        },\n",
    "        'students': [\n",
    "            { 'name': 'Tom', 'sex': 'M' },\n",
    "            { 'name': 'James', 'sex': 'M' },\n",
    "        ]\n",
    "    },\n",
    "    { \n",
    "        'class': 'Year 2', \n",
    "        'student count': 25, \n",
    "        'room': 'Blue',\n",
    "        'info': {\n",
    "            'teachers': { \n",
    "                'physics': 'Albert Einstein'\n",
    "            }\n",
    "        },\n",
    "        'students': [\n",
    "            { 'name': 'Tony', 'sex': 'M' },\n",
    "            { 'name': 'Jacqueline', 'sex': 'F' },\n",
    "        ]\n",
    "    },\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"Try running with errors='ignore' as key 'math' is not always present\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/envs/tf-tutorial/lib/python3.7/site-packages/pandas/io/json/_normalize.py\u001b[0m in \u001b[0;36m_recursive_extract\u001b[0;34m(data, path, seen_meta, level)\u001b[0m\n\u001b[1;32m    323\u001b[0m                         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 324\u001b[0;31m                             \u001b[0mmeta_val\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_pull_field\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobj\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mval\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mlevel\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    325\u001b[0m                         \u001b[0;32mexcept\u001b[0m \u001b[0mKeyError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/tf-tutorial/lib/python3.7/site-packages/pandas/io/json/_normalize.py\u001b[0m in \u001b[0;36m_pull_field\u001b[0;34m(js, spec)\u001b[0m\n\u001b[1;32m    236\u001b[0m             \u001b[0;32mfor\u001b[0m \u001b[0mfield\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mspec\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 237\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mfield\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    238\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'math'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-21-977290635fef>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m     \u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m     \u001b[0mrecord_path\u001b[0m \u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'students'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m     \u001b[0mmeta\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'class'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'room'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'info'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'teachers'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'math'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m )\n",
      "\u001b[0;32m~/anaconda3/envs/tf-tutorial/lib/python3.7/site-packages/pandas/io/json/_normalize.py\u001b[0m in \u001b[0;36m_json_normalize\u001b[0;34m(data, record_path, meta, meta_prefix, record_prefix, errors, sep, max_level)\u001b[0m\n\u001b[1;32m    334\u001b[0m                 \u001b[0mrecords\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrecs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    335\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 336\u001b[0;31m     \u001b[0m_recursive_extract\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrecord_path\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlevel\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    337\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    338\u001b[0m     \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mDataFrame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrecords\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/envs/tf-tutorial/lib/python3.7/site-packages/pandas/io/json/_normalize.py\u001b[0m in \u001b[0;36m_recursive_extract\u001b[0;34m(data, path, seen_meta, level)\u001b[0m\n\u001b[1;32m    330\u001b[0m                                     \u001b[0;34m\"Try running with errors='ignore' as key \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    331\u001b[0m                                     \u001b[0;34mf\"{e} is not always present\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 332\u001b[0;31m                                 ) from e\n\u001b[0m\u001b[1;32m    333\u001b[0m                     \u001b[0mmeta_vals\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mappend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmeta_val\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    334\u001b[0m                 \u001b[0mrecords\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mextend\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrecs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: \"Try running with errors='ignore' as key 'math' is not always present\""
     ]
    }
   ],
   "source": [
    "pd.json_normalize(\n",
    "    data, \n",
    "    record_path =['students'], \n",
    "    meta=['class', 'room', ['info', 'teachers', 'math']],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>sex</th>\n",
       "      <th>class</th>\n",
       "      <th>room</th>\n",
       "      <th>info.teachers.math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>M</td>\n",
       "      <td>Year 1</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "      <td>M</td>\n",
       "      <td>Year 1</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony</td>\n",
       "      <td>M</td>\n",
       "      <td>Year 2</td>\n",
       "      <td>Blue</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jacqueline</td>\n",
       "      <td>F</td>\n",
       "      <td>Year 2</td>\n",
       "      <td>Blue</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name sex   class    room info.teachers.math\n",
       "0         Tom   M  Year 1  Yellow         Rick Scott\n",
       "1       James   M  Year 1  Yellow         Rick Scott\n",
       "2        Tony   M  Year 2    Blue                NaN\n",
       "3  Jacqueline   F  Year 2    Blue                NaN"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(\n",
    "    data, \n",
    "    record_path =['students'], \n",
    "    meta=['class', 'room', ['info', 'teachers', 'math']],\n",
    "    errors='ignore'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 5. Custom separator using `sep`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = [\n",
    "    { \n",
    "        'class': 'Year 1', \n",
    "        'student count': 20, \n",
    "        'room': 'Yellow',\n",
    "        'info': {\n",
    "            'teachers': { 'math': 'Rick Scott', 'physics': 'Elon Mask' }\n",
    "        },\n",
    "        'students': [\n",
    "            { 'name': 'Tom', 'sex': 'M', 'grades': { 'math': 66, 'physics': 77 } },\n",
    "            { 'name': 'James', 'sex': 'M', 'grades': { 'math': 80, 'physics': 78 } },\n",
    "        ]\n",
    "    },\n",
    "    { \n",
    "        'class': 'Year 2', \n",
    "        'student count': 25, \n",
    "        'room': 'Blue',\n",
    "        'info': {\n",
    "            'teachers': { 'math': 'Alan Turing', 'physics': 'Albert Einstein' }\n",
    "        },\n",
    "        'students': [\n",
    "            { 'name': 'Tony', 'sex': 'M' },\n",
    "            { 'name': 'Jacqueline', 'sex': 'F' },\n",
    "        ]\n",
    "    },\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "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>sex</th>\n",
       "      <th>grades-&gt;math</th>\n",
       "      <th>grades-&gt;physics</th>\n",
       "      <th>class</th>\n",
       "      <th>room</th>\n",
       "      <th>info-&gt;teachers-&gt;math</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>M</td>\n",
       "      <td>66.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Year 1</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>Year 1</td>\n",
       "      <td>Yellow</td>\n",
       "      <td>Rick Scott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year 2</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Alan Turing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jacqueline</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year 2</td>\n",
       "      <td>Blue</td>\n",
       "      <td>Alan Turing</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         name sex  grades->math  grades->physics   class    room  \\\n",
       "0         Tom   M          66.0             77.0  Year 1  Yellow   \n",
       "1       James   M          80.0             78.0  Year 1  Yellow   \n",
       "2        Tony   M           NaN              NaN  Year 2    Blue   \n",
       "3  Jacqueline   F           NaN              NaN  Year 2    Blue   \n",
       "\n",
       "  info->teachers->math  \n",
       "0           Rick Scott  \n",
       "1           Rick Scott  \n",
       "2          Alan Turing  \n",
       "3          Alan Turing  "
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(\n",
    "    data, \n",
    "    record_path =['students'], \n",
    "    meta=['class', 'room', ['info', 'teachers', 'math']],\n",
    "    sep='->'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Adding prefix for meta and record data using `meta_prefix` and `record_prefix`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "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>student-name</th>\n",
       "      <th>student-sex</th>\n",
       "      <th>student-grades.math</th>\n",
       "      <th>student-grades.physics</th>\n",
       "      <th>meta-class</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tom</td>\n",
       "      <td>M</td>\n",
       "      <td>66.0</td>\n",
       "      <td>77.0</td>\n",
       "      <td>Year 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>James</td>\n",
       "      <td>M</td>\n",
       "      <td>80.0</td>\n",
       "      <td>78.0</td>\n",
       "      <td>Year 1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Tony</td>\n",
       "      <td>M</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year 2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Jacqueline</td>\n",
       "      <td>F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Year 2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  student-name student-sex  student-grades.math  student-grades.physics  \\\n",
       "0          Tom           M                 66.0                    77.0   \n",
       "1        James           M                 80.0                    78.0   \n",
       "2         Tony           M                  NaN                     NaN   \n",
       "3   Jacqueline           F                  NaN                     NaN   \n",
       "\n",
       "  meta-class  \n",
       "0     Year 1  \n",
       "1     Year 1  \n",
       "2     Year 2  \n",
       "3     Year 2  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.json_normalize(\n",
    "    data, \n",
    "    record_path=['students'], \n",
    "    meta=['class'],\n",
    "    meta_prefix='meta-',\n",
    "    record_prefix='student-'\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Working with a local file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>math</th>\n",
       "      <th>physics</th>\n",
       "      <th>chemistry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A001</td>\n",
       "      <td>Tom</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>James</td>\n",
       "      <td>89</td>\n",
       "      <td>76</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A003</td>\n",
       "      <td>Jenny</td>\n",
       "      <td>79</td>\n",
       "      <td>90</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id   name  math  physics  chemistry\n",
       "0  A001    Tom    60       66         61\n",
       "1  A002  James    89       76         51\n",
       "2  A003  Jenny    79       90         78"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import json\n",
    "# load data using Python JSON module\n",
    "with open('data/simple.json','r') as f:\n",
    "    data = json.loads(f.read())\n",
    "    \n",
    "# Normalizing data\n",
    "pd.json_normalize(data)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Working with a URL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>id</th>\n",
       "      <th>name</th>\n",
       "      <th>math</th>\n",
       "      <th>physics</th>\n",
       "      <th>chemistry</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A001</td>\n",
       "      <td>Tom</td>\n",
       "      <td>60</td>\n",
       "      <td>66</td>\n",
       "      <td>61</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A002</td>\n",
       "      <td>James</td>\n",
       "      <td>89</td>\n",
       "      <td>76</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A003</td>\n",
       "      <td>Jenny</td>\n",
       "      <td>79</td>\n",
       "      <td>90</td>\n",
       "      <td>78</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     id   name  math  physics  chemistry\n",
       "0  A001    Tom    60       66         61\n",
       "1  A002  James    89       76         51\n",
       "2  A003  Jenny    79       90         78"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "URL = 'http://raw.githubusercontent.com/BindiChen/machine-learning/master/data-analysis/027-pandas-convert-json/data/simple.json'\n",
    "\n",
    "data = json.loads(requests.get(URL).text)\n",
    "pd.json_normalize(data) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tf-tutorial",
   "language": "python",
   "name": "tf-tutorial"
  },
  "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}