Pandas - Pivot table.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "dbb5ddbd",
   "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>date</th>\n",
       "      <th>city</th>\n",
       "      <th>temperature</th>\n",
       "      <th>humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>66</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>68</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>75</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>78</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>82</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>beijing</td>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>beijing</td>\n",
       "      <td>77</td>\n",
       "      <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>beijing</td>\n",
       "      <td>79</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date      city  temperature  humidity\n",
       "0  5/1/2017  new york           65        56\n",
       "1  5/2/2017  new york           66        58\n",
       "2  5/3/2017  new york           68        60\n",
       "3  5/1/2017    mumbai           75        80\n",
       "4  5/2/2017    mumbai           78        83\n",
       "5  5/3/2017    mumbai           82        85\n",
       "6  5/1/2017   beijing           80        26\n",
       "7  5/2/2017   beijing           77        30\n",
       "8  5/3/2017   beijing           79        35"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "df = pd.read_csv(\"data/weather1.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "053d9257",
   "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=\"3\" halign=\"left\">temperature</th>\n",
       "      <th colspan=\"3\" halign=\"left\">humidity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>80</td>\n",
       "      <td>77</td>\n",
       "      <td>79</td>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>75</td>\n",
       "      <td>78</td>\n",
       "      <td>82</td>\n",
       "      <td>80</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>65</td>\n",
       "      <td>66</td>\n",
       "      <td>68</td>\n",
       "      <td>56</td>\n",
       "      <td>58</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         temperature                   humidity                  \n",
       "date        5/1/2017 5/2/2017 5/3/2017 5/1/2017 5/2/2017 5/3/2017\n",
       "city                                                             \n",
       "beijing           80       77       79       26       30       35\n",
       "mumbai            75       78       82       80       83       85\n",
       "new york          65       66       68       56       58       60"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='city',columns='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "65720f02",
   "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>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/3/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>beijing</th>\n",
       "      <td>26</td>\n",
       "      <td>30</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>80</td>\n",
       "      <td>83</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>56</td>\n",
       "      <td>58</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "date      5/1/2017  5/2/2017  5/3/2017\n",
       "city                                  \n",
       "beijing         26        30        35\n",
       "mumbai          80        83        85\n",
       "new york        56        58        60"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='city',columns='date',values=\"humidity\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "74370676",
   "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=\"3\" halign=\"left\">date</th>\n",
       "      <th colspan=\"3\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>beijing</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>new york</th>\n",
       "      <th>beijing</th>\n",
       "      <th>mumbai</th>\n",
       "      <th>new york</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>humidity</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>79.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>58</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>68.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>80</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>85</th>\n",
       "      <td>NaN</td>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>82.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date                     temperature                \n",
       "city       beijing    mumbai  new york     beijing mumbai new york\n",
       "humidity                                                          \n",
       "26        5/1/2017       NaN       NaN        80.0    NaN      NaN\n",
       "30        5/2/2017       NaN       NaN        77.0    NaN      NaN\n",
       "35        5/3/2017       NaN       NaN        79.0    NaN      NaN\n",
       "56             NaN       NaN  5/1/2017         NaN    NaN     65.0\n",
       "58             NaN       NaN  5/2/2017         NaN    NaN     66.0\n",
       "60             NaN       NaN  5/3/2017         NaN    NaN     68.0\n",
       "80             NaN  5/1/2017       NaN         NaN   75.0      NaN\n",
       "83             NaN  5/2/2017       NaN         NaN   78.0      NaN\n",
       "85             NaN  5/3/2017       NaN         NaN   82.0      NaN"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot(index='humidity',columns='city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f3feb1e5",
   "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>date</th>\n",
       "      <th>city</th>\n",
       "      <th>temperature</th>\n",
       "      <th>humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>61</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>72</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>75</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>78</td>\n",
       "      <td>83</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>82</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>mumbai</td>\n",
       "      <td>80</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       date      city  temperature  humidity\n",
       "0  5/1/2017  new york           65        56\n",
       "1  5/1/2017  new york           61        54\n",
       "2  5/2/2017  new york           70        60\n",
       "3  5/2/2017  new york           72        62\n",
       "4  5/1/2017    mumbai           75        80\n",
       "5  5/1/2017    mumbai           78        83\n",
       "6  5/2/2017    mumbai           82        85\n",
       "7  5/2/2017    mumbai           80        26"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/weather2.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "9bdd8b52",
   "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\">humidity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>81.5</td>\n",
       "      <td>55.5</td>\n",
       "      <td>76.5</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>55.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>71.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         humidity          temperature         \n",
       "date     5/1/2017 5/2/2017    5/1/2017 5/2/2017\n",
       "city                                           \n",
       "mumbai       81.5     55.5        76.5     81.0\n",
       "new york     55.0     61.0        63.0     71.0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index='city', columns='date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "b67022ea",
   "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\">humidity</th>\n",
       "      <th colspan=\"2\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>163</td>\n",
       "      <td>111</td>\n",
       "      <td>153</td>\n",
       "      <td>162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>110</td>\n",
       "      <td>122</td>\n",
       "      <td>126</td>\n",
       "      <td>142</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         humidity          temperature         \n",
       "date     5/1/2017 5/2/2017    5/1/2017 5/2/2017\n",
       "city                                           \n",
       "mumbai        163      111         153      162\n",
       "new york      110      122         126      142"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=\"city\",columns=\"date\",aggfunc='sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8f348b6d",
   "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=\"3\" halign=\"left\">humidity</th>\n",
       "      <th colspan=\"3\" halign=\"left\">temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>All</th>\n",
       "      <th>5/1/2017</th>\n",
       "      <th>5/2/2017</th>\n",
       "      <th>All</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>mumbai</th>\n",
       "      <td>163</td>\n",
       "      <td>111</td>\n",
       "      <td>274</td>\n",
       "      <td>153</td>\n",
       "      <td>162</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>new york</th>\n",
       "      <td>110</td>\n",
       "      <td>122</td>\n",
       "      <td>232</td>\n",
       "      <td>126</td>\n",
       "      <td>142</td>\n",
       "      <td>268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All</th>\n",
       "      <td>273</td>\n",
       "      <td>233</td>\n",
       "      <td>506</td>\n",
       "      <td>279</td>\n",
       "      <td>304</td>\n",
       "      <td>583</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         humidity               temperature              \n",
       "date     5/1/2017 5/2/2017  All    5/1/2017 5/2/2017  All\n",
       "city                                                     \n",
       "mumbai        163      111  274         153      162  315\n",
       "new york      110      122  232         126      142  268\n",
       "All           273      233  506         279      304  583"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=\"city\",columns=\"date\", margins=True,aggfunc=np.sum)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "bd64e8fc",
   "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>date</th>\n",
       "      <th>city</th>\n",
       "      <th>temperature</th>\n",
       "      <th>humidity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>5/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>65</td>\n",
       "      <td>56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>61</td>\n",
       "      <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5/3/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>70</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12/1/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>30</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12/2/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>28</td>\n",
       "      <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>12/3/2017</td>\n",
       "      <td>new york</td>\n",
       "      <td>25</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date      city  temperature  humidity\n",
       "0   5/1/2017  new york           65        56\n",
       "1   5/2/2017  new york           61        54\n",
       "2   5/3/2017  new york           70        60\n",
       "3  12/1/2017  new york           30        50\n",
       "4  12/2/2017  new york           28        52\n",
       "5  12/3/2017  new york           25        51"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"data/weather3.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "719fd5ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "df['date'] = pd.to_datetime(df['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4f7dfc51",
   "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>humidity</th>\n",
       "      <th>temperature</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>new york</th>\n",
       "      <th>new york</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2017-05-31</th>\n",
       "      <td>56.666667</td>\n",
       "      <td>65.333333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017-12-31</th>\n",
       "      <td>51.000000</td>\n",
       "      <td>27.666667</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             humidity temperature\n",
       "city         new york    new york\n",
       "date                             \n",
       "2017-05-31  56.666667   65.333333\n",
       "2017-12-31  51.000000   27.666667"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.pivot_table(index=pd.Grouper(freq='M', key='date'), columns='city')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12fb46ca",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}