{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import itertools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Generate random dataframe, then convert to numpy array"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>column 0</th>\n",
       "      <th>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-2.514103</td>\n",
       "      <td>-11.355411</td>\n",
       "      <td>-7.881233</td>\n",
       "      <td>2.462776</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-8.846382</td>\n",
       "      <td>-3.083167</td>\n",
       "      <td>-14.698746</td>\n",
       "      <td>-2.668292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-1.551953</td>\n",
       "      <td>0.827107</td>\n",
       "      <td>-4.770237</td>\n",
       "      <td>-17.009419</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   column 0   column 1   column 2   column 3\n",
       "0 -2.514103 -11.355411  -7.881233   2.462776\n",
       "1 -8.846382  -3.083167 -14.698746  -2.668292\n",
       "2 -1.551953   0.827107  -4.770237 -17.009419"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.randn(3, 4) * 10, columns=[f\"column {i}\" for i in range(4)])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([[ -2.51410318, -11.35541057,  -7.8812329 ,   2.46277592],\n",
       "       [ -8.84638153,  -3.08316687, -14.69874602,  -2.66829197],\n",
       "       [ -1.55195347,   0.82710666,  -4.77023707, -17.00941874]])"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.values  # this is numpy array"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Create a dataframe with multiindex names. And show how you gonna use loc on this dataframe?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "group_list = [3 * [\"portfolio_1\"], 3 * [\"portfolio_2\"], 3 * [\"portfolio_3\"]]\n",
    "flat_group = list(itertools.chain(*group_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "subgroup_list = [[f\"strategy_{i}\" for i in range(1, 4)] for _ in range(3)]\n",
    "flat_subgroup = list(itertools.chain(*subgroup_list))"
   ]
  },
  {
   "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></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>portfolio</th>\n",
       "      <th>strategy</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_1</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>-1.666313</td>\n",
       "      <td>-1.535478</td>\n",
       "      <td>1.192467</td>\n",
       "      <td>-0.221651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>-0.173095</td>\n",
       "      <td>-0.213803</td>\n",
       "      <td>0.235330</td>\n",
       "      <td>0.861566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>0.374859</td>\n",
       "      <td>1.990981</td>\n",
       "      <td>0.107066</td>\n",
       "      <td>-1.670760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_2</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>-0.084183</td>\n",
       "      <td>0.288425</td>\n",
       "      <td>1.052083</td>\n",
       "      <td>-0.146290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>0.326947</td>\n",
       "      <td>0.027266</td>\n",
       "      <td>0.222703</td>\n",
       "      <td>0.308883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>1.563305</td>\n",
       "      <td>1.280715</td>\n",
       "      <td>0.685003</td>\n",
       "      <td>2.202861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_3</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>0.464870</td>\n",
       "      <td>-1.077632</td>\n",
       "      <td>0.000620</td>\n",
       "      <td>1.331755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>-1.627832</td>\n",
       "      <td>-0.361344</td>\n",
       "      <td>1.080016</td>\n",
       "      <td>-0.599627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>0.348844</td>\n",
       "      <td>0.780083</td>\n",
       "      <td>-0.679279</td>\n",
       "      <td>-0.716045</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               0         1         2         3\n",
       "portfolio   strategy                                          \n",
       "portfolio_1 strategy_1 -1.666313 -1.535478  1.192467 -0.221651\n",
       "            strategy_2 -0.173095 -0.213803  0.235330  0.861566\n",
       "            strategy_3  0.374859  1.990981  0.107066 -1.670760\n",
       "portfolio_2 strategy_1 -0.084183  0.288425  1.052083 -0.146290\n",
       "            strategy_2  0.326947  0.027266  0.222703  0.308883\n",
       "            strategy_3  1.563305  1.280715  0.685003  2.202861\n",
       "portfolio_3 strategy_1  0.464870 -1.077632  0.000620  1.331755\n",
       "            strategy_2 -1.627832 -0.361344  1.080016 -0.599627\n",
       "            strategy_3  0.348844  0.780083 -0.679279 -0.716045"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(np.random.randn(9, 4))\n",
    "df.index = pd.MultiIndex.from_arrays(\n",
    "    [flat_group, flat_subgroup], names=(\"portfolio\", \"strategy\")\n",
    ")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>portfolio</th>\n",
       "      <th>strategy</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>portfolio_2</th>\n",
       "      <th>strategy_3</th>\n",
       "      <td>-1.44679</td>\n",
       "      <td>-1.263631</td>\n",
       "      <td>0.45624</td>\n",
       "      <td>-1.349913</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              0         1        2         3\n",
       "portfolio   strategy                                        \n",
       "portfolio_2 strategy_3 -1.44679 -1.263631  0.45624 -1.349913"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[[(\"portfolio_2\", \"strategy_3\")]]  # select with a tuple"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.How to use Multicolumn? "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "top_columns = [f\"PM {i}\" for i in range(1, 3) for _ in range(2)]\n",
    "bottom_columns = 2 * [\"Opportunistic\", \"Niches\"]"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">PM 1</th>\n",
       "      <th colspan=\"2\" halign=\"left\">PM 2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Opportunistic</th>\n",
       "      <th>Niches</th>\n",
       "      <th>Opportunistic</th>\n",
       "      <th>Niches</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>portfolio</th>\n",
       "      <th>strategy</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_1</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>-1.666313</td>\n",
       "      <td>-1.535478</td>\n",
       "      <td>1.192467</td>\n",
       "      <td>-0.221651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>-0.173095</td>\n",
       "      <td>-0.213803</td>\n",
       "      <td>0.235330</td>\n",
       "      <td>0.861566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>0.374859</td>\n",
       "      <td>1.990981</td>\n",
       "      <td>0.107066</td>\n",
       "      <td>-1.670760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_2</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>-0.084183</td>\n",
       "      <td>0.288425</td>\n",
       "      <td>1.052083</td>\n",
       "      <td>-0.146290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>0.326947</td>\n",
       "      <td>0.027266</td>\n",
       "      <td>0.222703</td>\n",
       "      <td>0.308883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>1.563305</td>\n",
       "      <td>1.280715</td>\n",
       "      <td>0.685003</td>\n",
       "      <td>2.202861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_3</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>0.464870</td>\n",
       "      <td>-1.077632</td>\n",
       "      <td>0.000620</td>\n",
       "      <td>1.331755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>-1.627832</td>\n",
       "      <td>-0.361344</td>\n",
       "      <td>1.080016</td>\n",
       "      <td>-0.599627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>0.348844</td>\n",
       "      <td>0.780083</td>\n",
       "      <td>-0.679279</td>\n",
       "      <td>-0.716045</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                PM 1                    PM 2          \n",
       "                       Opportunistic    Niches Opportunistic    Niches\n",
       "portfolio   strategy                                                  \n",
       "portfolio_1 strategy_1     -1.666313 -1.535478      1.192467 -0.221651\n",
       "            strategy_2     -0.173095 -0.213803      0.235330  0.861566\n",
       "            strategy_3      0.374859  1.990981      0.107066 -1.670760\n",
       "portfolio_2 strategy_1     -0.084183  0.288425      1.052083 -0.146290\n",
       "            strategy_2      0.326947  0.027266      0.222703  0.308883\n",
       "            strategy_3      1.563305  1.280715      0.685003  2.202861\n",
       "portfolio_3 strategy_1      0.464870 -1.077632      0.000620  1.331755\n",
       "            strategy_2     -1.627832 -0.361344      1.080016 -0.599627\n",
       "            strategy_3      0.348844  0.780083     -0.679279 -0.716045"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns = pd.MultiIndex.from_arrays([top_columns, bottom_columns])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "portfolio    strategy  \n",
       "portfolio_1  strategy_1   -0.221651\n",
       "             strategy_2    0.861566\n",
       "             strategy_3   -1.670760\n",
       "portfolio_2  strategy_1   -0.146290\n",
       "             strategy_2    0.308883\n",
       "             strategy_3    2.202861\n",
       "portfolio_3  strategy_1    1.331755\n",
       "             strategy_2   -0.599627\n",
       "             strategy_3   -0.716045\n",
       "Name: (PM 2, Niches), dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# select PM 2 and Niche, return a series\n",
    "df.loc[:, (\"PM 2\", \"Niches\")]"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>PM 2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>Niches</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>portfolio</th>\n",
       "      <th>strategy</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_1</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>-0.221651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>0.861566</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>-1.670760</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_2</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>-0.146290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>0.308883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>2.202861</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">portfolio_3</th>\n",
       "      <th>strategy_1</th>\n",
       "      <td>1.331755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_2</th>\n",
       "      <td>-0.599627</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>strategy_3</th>\n",
       "      <td>-0.716045</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            PM 2\n",
       "                          Niches\n",
       "portfolio   strategy            \n",
       "portfolio_1 strategy_1 -0.221651\n",
       "            strategy_2  0.861566\n",
       "            strategy_3 -1.670760\n",
       "portfolio_2 strategy_1 -0.146290\n",
       "            strategy_2  0.308883\n",
       "            strategy_3  2.202861\n",
       "portfolio_3 strategy_1  1.331755\n",
       "            strategy_2 -0.599627\n",
       "            strategy_3 -0.716045"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[:, (\"PM 2\", \"Niches\")].to_frame()  # return a dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. How to use reindex to change the order of index or columns?"
   ]
  },
  {
   "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>column 0</th>\n",
       "      <th>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2024-01-01</th>\n",
       "      <td>1.604649</td>\n",
       "      <td>0.923969</td>\n",
       "      <td>-0.627469</td>\n",
       "      <td>0.625340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-02</th>\n",
       "      <td>0.587526</td>\n",
       "      <td>1.938590</td>\n",
       "      <td>-1.275731</td>\n",
       "      <td>0.112374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-03</th>\n",
       "      <td>-0.082560</td>\n",
       "      <td>0.964331</td>\n",
       "      <td>1.170139</td>\n",
       "      <td>0.474678</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            column 0  column 1  column 2  column 3\n",
       "2024-01-01  1.604649  0.923969 -0.627469  0.625340\n",
       "2024-01-02  0.587526  1.938590 -1.275731  0.112374\n",
       "2024-01-03 -0.082560  0.964331  1.170139  0.474678"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# An example of reindexing\n",
    "df = pd.DataFrame(\n",
    "    np.random.randn(3, 4),\n",
    "    columns=[f\"column {i}\" for i in range(4)],\n",
    "    index=pd.date_range(\"20240101\", periods=3, freq=\"B\"),\n",
    ")\n",
    "df"
   ]
  },
  {
   "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>column 0</th>\n",
       "      <th>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023-12-29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-01</th>\n",
       "      <td>1.604649</td>\n",
       "      <td>0.923969</td>\n",
       "      <td>-0.627469</td>\n",
       "      <td>0.625340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-02</th>\n",
       "      <td>0.587526</td>\n",
       "      <td>1.938590</td>\n",
       "      <td>-1.275731</td>\n",
       "      <td>0.112374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-03</th>\n",
       "      <td>-0.082560</td>\n",
       "      <td>0.964331</td>\n",
       "      <td>1.170139</td>\n",
       "      <td>0.474678</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            column 0  column 1  column 2  column 3\n",
       "2023-12-29       NaN       NaN       NaN       NaN\n",
       "2023-12-30       NaN       NaN       NaN       NaN\n",
       "2023-12-31       NaN       NaN       NaN       NaN\n",
       "2024-01-01  1.604649  0.923969 -0.627469  0.625340\n",
       "2024-01-02  0.587526  1.938590 -1.275731  0.112374\n",
       "2024-01-03 -0.082560  0.964331  1.170139  0.474678\n",
       "2024-01-04       NaN       NaN       NaN       NaN\n",
       "2024-01-05       NaN       NaN       NaN       NaN"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index_2 = pd.date_range(\"20231229\", periods=8)\n",
    "df = df.reindex(index_2)\n",
    "df"
   ]
  },
  {
   "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>column -2</th>\n",
       "      <th>column -1</th>\n",
       "      <th>column 0</th>\n",
       "      <th>column 1</th>\n",
       "      <th>column 2</th>\n",
       "      <th>column 3</th>\n",
       "      <th>column 4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2023-12-29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2023-12-31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-01</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.604649</td>\n",
       "      <td>0.923969</td>\n",
       "      <td>-0.627469</td>\n",
       "      <td>0.625340</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.587526</td>\n",
       "      <td>1.938590</td>\n",
       "      <td>-1.275731</td>\n",
       "      <td>0.112374</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.082560</td>\n",
       "      <td>0.964331</td>\n",
       "      <td>1.170139</td>\n",
       "      <td>0.474678</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2024-01-05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            column -2  column -1  column 0  column 1  column 2  column 3  \\\n",
       "2023-12-29        NaN        NaN       NaN       NaN       NaN       NaN   \n",
       "2023-12-30        NaN        NaN       NaN       NaN       NaN       NaN   \n",
       "2023-12-31        NaN        NaN       NaN       NaN       NaN       NaN   \n",
       "2024-01-01        NaN        NaN  1.604649  0.923969 -0.627469  0.625340   \n",
       "2024-01-02        NaN        NaN  0.587526  1.938590 -1.275731  0.112374   \n",
       "2024-01-03        NaN        NaN -0.082560  0.964331  1.170139  0.474678   \n",
       "2024-01-04        NaN        NaN       NaN       NaN       NaN       NaN   \n",
       "2024-01-05        NaN        NaN       NaN       NaN       NaN       NaN   \n",
       "\n",
       "            column 4  \n",
       "2023-12-29       NaN  \n",
       "2023-12-30       NaN  \n",
       "2023-12-31       NaN  \n",
       "2024-01-01       NaN  \n",
       "2024-01-02       NaN  \n",
       "2024-01-03       NaN  \n",
       "2024-01-04       NaN  \n",
       "2024-01-05       NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "new_columns = [f\"column {i}\" for i in range(-2, 5)]\n",
    "df = df.reindex(columns=new_columns)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Show how you gonna use .pivot_table method? And what does arg column and index mean in this method? After pivoting, what are the index and columns type?"
   ]
  },
  {
   "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>A</th>\n",
       "      <th>B</th>\n",
       "      <th>C</th>\n",
       "      <th>D</th>\n",
       "      <th>E</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>apple</td>\n",
       "      <td>one</td>\n",
       "      <td>small</td>\n",
       "      <td>-0.596805</td>\n",
       "      <td>1.722010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>apple</td>\n",
       "      <td>two</td>\n",
       "      <td>large</td>\n",
       "      <td>0.009945</td>\n",
       "      <td>0.832135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>pear</td>\n",
       "      <td>one</td>\n",
       "      <td>large</td>\n",
       "      <td>0.387427</td>\n",
       "      <td>-0.118612</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>banana</td>\n",
       "      <td>two</td>\n",
       "      <td>small</td>\n",
       "      <td>-0.361440</td>\n",
       "      <td>0.431291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>apple</td>\n",
       "      <td>one</td>\n",
       "      <td>small</td>\n",
       "      <td>-3.021230</td>\n",
       "      <td>0.905683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>apple</td>\n",
       "      <td>two</td>\n",
       "      <td>large</td>\n",
       "      <td>-1.515054</td>\n",
       "      <td>2.379202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>pear</td>\n",
       "      <td>one</td>\n",
       "      <td>large</td>\n",
       "      <td>-0.536045</td>\n",
       "      <td>-1.021149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>banana</td>\n",
       "      <td>two</td>\n",
       "      <td>small</td>\n",
       "      <td>1.498246</td>\n",
       "      <td>0.444942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>apple</td>\n",
       "      <td>one</td>\n",
       "      <td>small</td>\n",
       "      <td>-1.286598</td>\n",
       "      <td>1.124035</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>apple</td>\n",
       "      <td>two</td>\n",
       "      <td>large</td>\n",
       "      <td>-0.481828</td>\n",
       "      <td>-0.273580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>pear</td>\n",
       "      <td>one</td>\n",
       "      <td>large</td>\n",
       "      <td>-0.967426</td>\n",
       "      <td>-0.463726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>banana</td>\n",
       "      <td>two</td>\n",
       "      <td>small</td>\n",
       "      <td>-0.400804</td>\n",
       "      <td>0.282053</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         A    B      C         D         E\n",
       "0    apple  one  small -0.596805  1.722010\n",
       "1    apple  two  large  0.009945  0.832135\n",
       "2     pear  one  large  0.387427 -0.118612\n",
       "3   banana  two  small -0.361440  0.431291\n",
       "4    apple  one  small -3.021230  0.905683\n",
       "5    apple  two  large -1.515054  2.379202\n",
       "6     pear  one  large -0.536045 -1.021149\n",
       "7   banana  two  small  1.498246  0.444942\n",
       "8    apple  one  small -1.286598  1.124035\n",
       "9    apple  two  large -0.481828 -0.273580\n",
       "10    pear  one  large -0.967426 -0.463726\n",
       "11  banana  two  small -0.400804  0.282053"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# demonstrate how to use .pivot() to create a dataframe\n",
    "\n",
    "df = pd.DataFrame(\n",
    "    {\n",
    "        \"A\": [\"apple\", \"apple\", \"pear\", \"banana\"] * 3,\n",
    "        \"B\": [\"one\", \"two\", \"one\", \"two\"] * 3,\n",
    "        \"C\": [\"small\", \"large\", \"large\", \"small\"] * 3,\n",
    "        \"D\": np.random.randn(12),\n",
    "        \"E\": np.random.randn(12),\n",
    "    }\n",
    "); df"
   ]
  },
  {
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>apple</th>\n",
       "      <td>-0.475195</td>\n",
       "      <td>0.837590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>banana</th>\n",
       "      <td>NaN</td>\n",
       "      <td>-0.678793</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pear</th>\n",
       "      <td>-0.693141</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               D          \n",
       "B            one       two\n",
       "A                         \n",
       "apple  -0.475195  0.837590\n",
       "banana       NaN -0.678793\n",
       "pear   -0.693141       NaN"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=\"A\", columns=\"B\", values=[\"D\"]) # default aggregation is mean"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">D</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>apple</th>\n",
       "      <td>-4.904634</td>\n",
       "      <td>-1.986937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>banana</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0.736002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pear</th>\n",
       "      <td>-1.116043</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               D          \n",
       "B            one       two\n",
       "A                         \n",
       "apple  -4.904634 -1.986937\n",
       "banana       NaN  0.736002\n",
       "pear   -1.116043       NaN"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=\"A\", columns=\"B\", values=[\"D\"], aggfunc=\"sum\") # sum aggregation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "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 tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">D</th>\n",
       "      <th colspan=\"2\" halign=\"left\">E</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>B</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "      <th>one</th>\n",
       "      <th>two</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>A</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>apple</th>\n",
       "      <td>-0.596805</td>\n",
       "      <td>0.009945</td>\n",
       "      <td>1.722010</td>\n",
       "      <td>2.379202</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>banana</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.498246</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.444942</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pear</th>\n",
       "      <td>0.387427</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-0.118612</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               D                   E          \n",
       "B            one       two       one       two\n",
       "A                                             \n",
       "apple  -0.596805  0.009945  1.722010  2.379202\n",
       "banana  0.000000  1.498246  0.000000  0.444942\n",
       "pear    0.387427  0.000000 -0.118612  0.000000"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pivoted = df.pivot_table(index=\"A\", columns=\"B\", values=[\"D\", \"E\"], aggfunc=\"max\", fill_value=0); df_pivoted"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex([('D', 'one'),\n",
       "            ('D', 'two'),\n",
       "            ('E', 'one'),\n",
       "            ('E', 'two')],\n",
       "           names=[None, 'B'])"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_pivoted.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "weijie-chen-Vyy5-xRM-py3.12",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}