Reading Multiple Spreadsheets using Pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pandas read_excel, ExcelWriter, and to_excel Example\n",
    "This is a Jupyter notebook for the [Youtube tutorial explaining how to load multiple Excel sheets to dataframe](https://youtu.be/sspFLy0ju8E). You will learn how to read them to an ordered dictionary and then merge the dataframes into on dataframe. Finally, you will learn how to write multiple dataframes into one Excel file, with multiple sheets."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Reading Multiple Sheets\n",
    "Sometimes we need to read multiple spreadsheets into Pandas dataframe. In this example we are going to read two sheets, 'Session1' and 'Session2'. If you have more sheets, you can add them to the list (parameter *sheet_name*, is used to specify sheets)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "sheets = pd.read_excel('example_sheets2.xlsx', sheet_name=['Session1', 'Session2'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Doing this will give us a dictionary of dataframes: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OrderedDict([('Session1',         Name  ID  Mean  Correct  Session\n",
       "              0       Pete   1    32       15        1\n",
       "              1      Steve   2    31       14        1\n",
       "              2       Dave   3    30       15        1\n",
       "              3    Justine   4    31       13        1\n",
       "              4   Frederic   5    45       14        1\n",
       "              5       John   6    99       99        1\n",
       "              6  Josephine   7    29       11        1\n",
       "              7        Lee   8    35       14        1\n",
       "              8      Roman   9    37       14        1\n",
       "              9   Caroline  10    33       14        1),\n",
       "             ('Session2',         Name  ID  Mean  Correct  Session\n",
       "              0       Pete   1  31.0     14.0        2\n",
       "              1      Steve   2  32.0     15.0        2\n",
       "              2       Dave   3  29.0     14.0        2\n",
       "              3    Justine   4  28.0     12.0        2\n",
       "              4   Frederic   5  43.0      2.0        2\n",
       "              5       John   6   NaN      NaN        2\n",
       "              6  Josephine   7  31.0     11.0        2\n",
       "              7        Lee   8  33.0     13.0        2\n",
       "              8      Roman   9  38.0     15.0        2\n",
       "              9   Caroline  10  33.0     14.0        2)])"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "OrderedDict([('Session1',         Name  ID  Mean  Correct  Session\n",
       "              0       Pete   1    32       15        1\n",
       "              1      Steve   2    31       14        1\n",
       "              2       Dave   3    30       15        1\n",
       "              3    Justine   4    31       13        1\n",
       "              4   Frederic   5    45       14        1\n",
       "              5       John   6    99       99        1\n",
       "              6  Josephine   7    29       11        1\n",
       "              7        Lee   8    35       14        1\n",
       "              8      Roman   9    37       14        1\n",
       "              9   Caroline  10    33       14        1),\n",
       "             ('Session2',         Name  ID  Mean  Correct  Session\n",
       "              0       Pete   1  31.0     14.0        2\n",
       "              1      Steve   2  32.0     15.0        2\n",
       "              2       Dave   3  29.0     14.0        2\n",
       "              3    Justine   4  28.0     12.0        2\n",
       "              4   Frederic   5  43.0      2.0        2\n",
       "              5       John   6   NaN      NaN        2\n",
       "              6  Josephine   7  31.0     11.0        2\n",
       "              7        Lee   8  33.0     13.0        2\n",
       "              8      Roman   9  38.0     15.0        2\n",
       "              9   Caroline  10  33.0     14.0        2)])"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_to_df_map = pd.read_excel('example_sheets2.xlsx', sheet_name=None)\n",
    "sheet_to_df_map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Maybe we want to join the data from all sheets (in this case sessions). Merging Pandas dataframes are quite easy. We just use the concat function and loop over the keys (i.e., sheets):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat(sheet_to_df_map[frame] for frame in sheet_to_df_map.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>ID</th>\n",
       "      <th>Mean</th>\n",
       "      <th>Correct</th>\n",
       "      <th>Session</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pete</td>\n",
       "      <td>1</td>\n",
       "      <td>32</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Steve</td>\n",
       "      <td>2</td>\n",
       "      <td>31</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dave</td>\n",
       "      <td>3</td>\n",
       "      <td>30</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Justine</td>\n",
       "      <td>4</td>\n",
       "      <td>31</td>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Frederic</td>\n",
       "      <td>5</td>\n",
       "      <td>45</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>John</td>\n",
       "      <td>6</td>\n",
       "      <td>31</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Josephine</td>\n",
       "      <td>7</td>\n",
       "      <td>29</td>\n",
       "      <td>11</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Lee</td>\n",
       "      <td>8</td>\n",
       "      <td>35</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Roman</td>\n",
       "      <td>9</td>\n",
       "      <td>37</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Caroline</td>\n",
       "      <td>10</td>\n",
       "      <td>33</td>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Pete</td>\n",
       "      <td>1</td>\n",
       "      <td>31</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Steve</td>\n",
       "      <td>2</td>\n",
       "      <td>32</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dave</td>\n",
       "      <td>3</td>\n",
       "      <td>29</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Justine</td>\n",
       "      <td>4</td>\n",
       "      <td>28</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Frederic</td>\n",
       "      <td>5</td>\n",
       "      <td>43</td>\n",
       "      <td>12</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>John</td>\n",
       "      <td>6</td>\n",
       "      <td>35</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Josephine</td>\n",
       "      <td>7</td>\n",
       "      <td>31</td>\n",
       "      <td>11</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Lee</td>\n",
       "      <td>8</td>\n",
       "      <td>33</td>\n",
       "      <td>13</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Roman</td>\n",
       "      <td>9</td>\n",
       "      <td>38</td>\n",
       "      <td>15</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Caroline</td>\n",
       "      <td>10</td>\n",
       "      <td>33</td>\n",
       "      <td>14</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Name  ID  Mean  Correct  Session\n",
       "0       Pete   1    32       15        1\n",
       "1      Steve   2    31       14        1\n",
       "2       Dave   3    30       15        1\n",
       "3    Justine   4    31       13        1\n",
       "4   Frederic   5    45       14        1\n",
       "5       John   6    31       15        1\n",
       "6  Josephine   7    29       11        1\n",
       "7        Lee   8    35       14        1\n",
       "8      Roman   9    37       14        1\n",
       "9   Caroline  10    33       14        1\n",
       "0       Pete   1    31       14        2\n",
       "1      Steve   2    32       15        2\n",
       "2       Dave   3    29       14        2\n",
       "3    Justine   4    28       12        2\n",
       "4   Frederic   5    43       12        2\n",
       "5       John   6    35       13        2\n",
       "6  Josephine   7    31       11        2\n",
       "7        Lee   8    33       13        2\n",
       "8      Roman   9    38       15        2\n",
       "9   Caroline  10    33       14        2"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet_to_df_map"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Writing Excel Files using Pandas\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In the final example we are going to write 3 dataframes to one Excel file with multple sheets:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame({'Names': ['Andreas', 'George', 'Steve',\n",
    "                           'Sarah', 'Joanna', 'Hanna'],\n",
    "                   'Age':[21, 22, 20, 19, 18, 23]})\n",
    "df2 = pd.DataFrame({'Names': ['Pete', 'Jordan', 'Gustaf',\n",
    "                           'Sophie', 'Sally', 'Simone'],\n",
    "                   'Age':[22, 21, 19, 19, 29, 21]})\n",
    "df3 = pd.DataFrame({'Names': ['Ulrich', 'Donald', 'Jon',\n",
    "                           'Jessica', 'Elisabeth', 'Diana'],\n",
    "                   'Age':[21, 21, 20, 19, 19, 22]})\n",
    "\n",
    "dfs = {'Group1':df1, 'Group2':df2, 'Group3':df3}\n",
    "\n",
    "writer = pd.ExcelWriter('NamesAndAges.xlsx', engine='xlsxwriter')\n",
    "for sheet_name in dfs.keys():\n",
    "    dfs[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)\n",
    "    \n",
    "writer.save()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}