{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "3659a239",
   "metadata": {},
   "source": [
    "# pandas: Reshape MultiIndexed Series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "97a2da35",
   "metadata": {},
   "source": [
    "## Package Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6b0694c8",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-09-02T18:58:34.360724Z",
     "iopub.status.busy": "2025-09-02T18:58:34.360522Z",
     "iopub.status.idle": "2025-09-02T18:58:34.588519Z",
     "shell.execute_reply": "2025-09-02T18:58:34.587980Z"
    },
    "tags": [
     "hide-output",
     "scroll-output"
    ]
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31872b7d",
   "metadata": {},
   "source": [
    "## Dataset Import"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "69cde461",
   "metadata": {},
   "source": [
    "The dataset used in this notebook is from [Kaggle - Pokemon](https://www.kaggle.com/datasets/abcsds/pokemon)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f7869ffd",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-09-02T18:58:34.590262Z",
     "iopub.status.busy": "2025-09-02T18:58:34.590106Z",
     "iopub.status.idle": "2025-09-02T18:58:34.594719Z",
     "shell.execute_reply": "2025-09-02T18:58:34.594180Z"
    },
    "tags": [
     "hide-output",
     "scroll-output"
    ]
   },
   "outputs": [],
   "source": [
    "data = pd.read_csv('data/Pokemon.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "92dd4708",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-09-02T18:58:34.596022Z",
     "iopub.status.busy": "2025-09-02T18:58:34.595905Z",
     "iopub.status.idle": "2025-09-02T18:58:34.604173Z",
     "shell.execute_reply": "2025-09-02T18:58:34.603770Z"
    },
    "tags": [
     "hide-output",
     "scroll-output"
    ]
   },
   "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>#</th>\n",
       "      <th>Name</th>\n",
       "      <th>Type 1</th>\n",
       "      <th>Type 2</th>\n",
       "      <th>Total</th>\n",
       "      <th>HP</th>\n",
       "      <th>Attack</th>\n",
       "      <th>Defense</th>\n",
       "      <th>Sp. Atk</th>\n",
       "      <th>Sp. Def</th>\n",
       "      <th>Speed</th>\n",
       "      <th>Generation</th>\n",
       "      <th>Legendary</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Bulbasaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>318</td>\n",
       "      <td>45</td>\n",
       "      <td>49</td>\n",
       "      <td>49</td>\n",
       "      <td>65</td>\n",
       "      <td>65</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Ivysaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>405</td>\n",
       "      <td>60</td>\n",
       "      <td>62</td>\n",
       "      <td>63</td>\n",
       "      <td>80</td>\n",
       "      <td>80</td>\n",
       "      <td>60</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>525</td>\n",
       "      <td>80</td>\n",
       "      <td>82</td>\n",
       "      <td>83</td>\n",
       "      <td>100</td>\n",
       "      <td>100</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>VenusaurMega Venusaur</td>\n",
       "      <td>Grass</td>\n",
       "      <td>Poison</td>\n",
       "      <td>625</td>\n",
       "      <td>80</td>\n",
       "      <td>100</td>\n",
       "      <td>123</td>\n",
       "      <td>122</td>\n",
       "      <td>120</td>\n",
       "      <td>80</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>Charmander</td>\n",
       "      <td>Fire</td>\n",
       "      <td>NaN</td>\n",
       "      <td>309</td>\n",
       "      <td>39</td>\n",
       "      <td>52</td>\n",
       "      <td>43</td>\n",
       "      <td>60</td>\n",
       "      <td>50</td>\n",
       "      <td>65</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>795</th>\n",
       "      <td>719</td>\n",
       "      <td>Diancie</td>\n",
       "      <td>Rock</td>\n",
       "      <td>Fairy</td>\n",
       "      <td>600</td>\n",
       "      <td>50</td>\n",
       "      <td>100</td>\n",
       "      <td>150</td>\n",
       "      <td>100</td>\n",
       "      <td>150</td>\n",
       "      <td>50</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>796</th>\n",
       "      <td>719</td>\n",
       "      <td>DiancieMega Diancie</td>\n",
       "      <td>Rock</td>\n",
       "      <td>Fairy</td>\n",
       "      <td>700</td>\n",
       "      <td>50</td>\n",
       "      <td>160</td>\n",
       "      <td>110</td>\n",
       "      <td>160</td>\n",
       "      <td>110</td>\n",
       "      <td>110</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>797</th>\n",
       "      <td>720</td>\n",
       "      <td>HoopaHoopa Confined</td>\n",
       "      <td>Psychic</td>\n",
       "      <td>Ghost</td>\n",
       "      <td>600</td>\n",
       "      <td>80</td>\n",
       "      <td>110</td>\n",
       "      <td>60</td>\n",
       "      <td>150</td>\n",
       "      <td>130</td>\n",
       "      <td>70</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>798</th>\n",
       "      <td>720</td>\n",
       "      <td>HoopaHoopa Unbound</td>\n",
       "      <td>Psychic</td>\n",
       "      <td>Dark</td>\n",
       "      <td>680</td>\n",
       "      <td>80</td>\n",
       "      <td>160</td>\n",
       "      <td>60</td>\n",
       "      <td>170</td>\n",
       "      <td>130</td>\n",
       "      <td>80</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>799</th>\n",
       "      <td>721</td>\n",
       "      <td>Volcanion</td>\n",
       "      <td>Fire</td>\n",
       "      <td>Water</td>\n",
       "      <td>600</td>\n",
       "      <td>80</td>\n",
       "      <td>110</td>\n",
       "      <td>120</td>\n",
       "      <td>130</td>\n",
       "      <td>90</td>\n",
       "      <td>70</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>800 rows × 13 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       #                   Name   Type 1  Type 2  Total  HP  Attack  Defense  \\\n",
       "0      1              Bulbasaur    Grass  Poison    318  45      49       49   \n",
       "1      2                Ivysaur    Grass  Poison    405  60      62       63   \n",
       "2      3               Venusaur    Grass  Poison    525  80      82       83   \n",
       "3      3  VenusaurMega Venusaur    Grass  Poison    625  80     100      123   \n",
       "4      4             Charmander     Fire     NaN    309  39      52       43   \n",
       "..   ...                    ...      ...     ...    ...  ..     ...      ...   \n",
       "795  719                Diancie     Rock   Fairy    600  50     100      150   \n",
       "796  719    DiancieMega Diancie     Rock   Fairy    700  50     160      110   \n",
       "797  720    HoopaHoopa Confined  Psychic   Ghost    600  80     110       60   \n",
       "798  720     HoopaHoopa Unbound  Psychic    Dark    680  80     160       60   \n",
       "799  721              Volcanion     Fire   Water    600  80     110      120   \n",
       "\n",
       "     Sp. Atk  Sp. Def  Speed  Generation  Legendary  \n",
       "0         65       65     45           1      False  \n",
       "1         80       80     60           1      False  \n",
       "2        100      100     80           1      False  \n",
       "3        122      120     80           1      False  \n",
       "4         60       50     65           1      False  \n",
       "..       ...      ...    ...         ...        ...  \n",
       "795      100      150     50           6       True  \n",
       "796      160      110    110           6       True  \n",
       "797      150      130     70           6       True  \n",
       "798      170      130     80           6       True  \n",
       "799      130       90     70           6       True  \n",
       "\n",
       "[800 rows x 13 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87a981cf",
   "metadata": {},
   "source": [
    "## Reshape a MultiIndexed Series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "820b496b",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-09-02T18:58:34.605623Z",
     "iopub.status.busy": "2025-09-02T18:58:34.605506Z",
     "iopub.status.idle": "2025-09-02T18:58:34.613599Z",
     "shell.execute_reply": "2025-09-02T18:58:34.613187Z"
    },
    "tags": [
     "hide-output",
     "scroll-output"
    ]
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Type 1    Legendary\n",
       "Bug       False         69\n",
       "Dark      False         29\n",
       "          True           2\n",
       "Dragon    False         20\n",
       "          True          12\n",
       "Electric  False         40\n",
       "          True           4\n",
       "Fairy     False         16\n",
       "          True           1\n",
       "Fighting  False         27\n",
       "Fire      False         47\n",
       "          True           5\n",
       "Flying    False          2\n",
       "          True           2\n",
       "Ghost     False         30\n",
       "          True           2\n",
       "Grass     False         67\n",
       "          True           3\n",
       "Ground    False         28\n",
       "          True           4\n",
       "Ice       False         22\n",
       "          True           2\n",
       "Normal    False         96\n",
       "          True           2\n",
       "Poison    False         28\n",
       "Psychic   False         43\n",
       "          True          14\n",
       "Rock      False         40\n",
       "          True           4\n",
       "Steel     False         23\n",
       "          True           4\n",
       "Water     False        108\n",
       "          True           4\n",
       "Name: #, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(['Type 1', 'Legendary'])['#'].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93d81aea",
   "metadata": {},
   "source": [
    "What if we want to convert the above Series into a DataFrame, with `Type 1` as rows, `Legendary` as columns, and the counts as values?\n",
    "- Use `unstack()` after `groupby()` and `count()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "889be731",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2025-09-02T18:58:34.614883Z",
     "iopub.status.busy": "2025-09-02T18:58:34.614774Z",
     "iopub.status.idle": "2025-09-02T18:58:34.622180Z",
     "shell.execute_reply": "2025-09-02T18:58:34.621817Z"
    },
    "tags": [
     "hide-output",
     "scroll-output"
    ]
   },
   "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>Legendary</th>\n",
       "      <th>False</th>\n",
       "      <th>True</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Type 1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Bug</th>\n",
       "      <td>69.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dark</th>\n",
       "      <td>29.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dragon</th>\n",
       "      <td>20.0</td>\n",
       "      <td>12.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Electric</th>\n",
       "      <td>40.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fairy</th>\n",
       "      <td>16.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fighting</th>\n",
       "      <td>27.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Fire</th>\n",
       "      <td>47.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Flying</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ghost</th>\n",
       "      <td>30.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Grass</th>\n",
       "      <td>67.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ground</th>\n",
       "      <td>28.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ice</th>\n",
       "      <td>22.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Normal</th>\n",
       "      <td>96.0</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Poison</th>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Psychic</th>\n",
       "      <td>43.0</td>\n",
       "      <td>14.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Rock</th>\n",
       "      <td>40.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Steel</th>\n",
       "      <td>23.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Water</th>\n",
       "      <td>108.0</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Legendary  False  True \n",
       "Type 1                 \n",
       "Bug         69.0    NaN\n",
       "Dark        29.0    2.0\n",
       "Dragon      20.0   12.0\n",
       "Electric    40.0    4.0\n",
       "Fairy       16.0    1.0\n",
       "Fighting    27.0    NaN\n",
       "Fire        47.0    5.0\n",
       "Flying       2.0    2.0\n",
       "Ghost       30.0    2.0\n",
       "Grass       67.0    3.0\n",
       "Ground      28.0    4.0\n",
       "Ice         22.0    2.0\n",
       "Normal      96.0    2.0\n",
       "Poison      28.0    NaN\n",
       "Psychic     43.0   14.0\n",
       "Rock        40.0    4.0\n",
       "Steel       23.0    4.0\n",
       "Water      108.0    4.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(['Type 1', 'Legendary'])['#'].count().unstack()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34dffc27",
   "metadata": {},
   "source": [
    "But there's a better way to do this by using `pivot_table()`, which can do more things like aggregation, filling missing values, and `margins`, which `unstack()` cannot do. See [this article](https://cornel05.github.io/cornel.ai/notebooks/snippets/pandas/pandas_create_pivot_table.html) for more details."
   ]
  }
 ],
 "metadata": {
  "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.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}