{ "cells": [ { "cell_type": "markdown", "id": "d8fc6544", "metadata": {}, "source": [ "# pandas: Create Pivot Table" ] }, { "cell_type": "markdown", "id": "97bd0c14", "metadata": {}, "source": [ "## Package Import" ] }, { "cell_type": "code", "execution_count": 1, "id": "d7cc5d66", "metadata": { "execution": { "iopub.execute_input": "2025-09-02T18:59:02.205714Z", "iopub.status.busy": "2025-09-02T18:59:02.205587Z", "iopub.status.idle": "2025-09-02T18:59:02.432372Z", "shell.execute_reply": "2025-09-02T18:59:02.431737Z" }, "tags": [ "hide-output", "scroll-output" ] }, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np" ] }, { "cell_type": "markdown", "id": "4e87e13e", "metadata": {}, "source": [ "## Dataset Import" ] }, { "cell_type": "markdown", "id": "37018a84", "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": "82aa9ab2", "metadata": { "execution": { "iopub.execute_input": "2025-09-02T18:59:02.434127Z", "iopub.status.busy": "2025-09-02T18:59:02.433974Z", "iopub.status.idle": "2025-09-02T18:59:02.438708Z", "shell.execute_reply": "2025-09-02T18:59:02.438238Z" }, "tags": [ "hide-output", "scroll-output" ] }, "outputs": [], "source": [ "data = pd.read_csv('data/Pokemon.csv')" ] }, { "cell_type": "code", "execution_count": 3, "id": "d192adfc", "metadata": { "execution": { "iopub.execute_input": "2025-09-02T18:59:02.440104Z", "iopub.status.busy": "2025-09-02T18:59:02.439983Z", "iopub.status.idle": "2025-09-02T18:59:02.449030Z", "shell.execute_reply": "2025-09-02T18:59:02.448596Z" }, "tags": [ "hide-output", "scroll-output" ] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
#NameType 1Type 2TotalHPAttackDefenseSp. AtkSp. DefSpeedGenerationLegendary
01BulbasaurGrassPoison3184549496565451False
12IvysaurGrassPoison4056062638080601False
23VenusaurGrassPoison525808283100100801False
33VenusaurMega VenusaurGrassPoison62580100123122120801False
44CharmanderFireNaN3093952436050651False
..........................................
795719DiancieRockFairy60050100150100150506True
796719DiancieMega DiancieRockFairy700501601101601101106True
797720HoopaHoopa ConfinedPsychicGhost6008011060150130706True
798720HoopaHoopa UnboundPsychicDark6808016060170130806True
799721VolcanionFireWater6008011012013090706True
\n", "

800 rows × 13 columns

\n", "
" ], "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": "a50511c0", "metadata": {}, "source": [ "## Create a pivot table" ] }, { "cell_type": "code", "execution_count": 4, "id": "31fa86f3", "metadata": { "execution": { "iopub.execute_input": "2025-09-02T18:59:02.450601Z", "iopub.status.busy": "2025-09-02T18:59:02.450470Z", "iopub.status.idle": "2025-09-02T18:59:02.464326Z", "shell.execute_reply": "2025-09-02T18:59:02.463865Z" }, "tags": [ "hide-output", "scroll-output" ] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
LegendaryFalseTrue
Type 1
Bug70.971014NaN
Dark86.862069110.500000
Dragon103.400000126.666667
Electric66.12500098.750000
Fairy57.187500131.000000
Fighting96.777778NaN
Fire82.191489109.000000
Flying50.000000107.500000
Ghost71.366667110.000000
Grass72.11940397.666667
Ground88.000000150.000000
Ice73.22727367.500000
Normal72.083333140.000000
Poison74.678571NaN
Psychic54.953488122.142857
Rock89.925000122.250000
Steel92.08695796.250000
Water72.777778111.250000
\n", "
" ], "text/plain": [ "Legendary False True \n", "Type 1 \n", "Bug 70.971014 NaN\n", "Dark 86.862069 110.500000\n", "Dragon 103.400000 126.666667\n", "Electric 66.125000 98.750000\n", "Fairy 57.187500 131.000000\n", "Fighting 96.777778 NaN\n", "Fire 82.191489 109.000000\n", "Flying 50.000000 107.500000\n", "Ghost 71.366667 110.000000\n", "Grass 72.119403 97.666667\n", "Ground 88.000000 150.000000\n", "Ice 73.227273 67.500000\n", "Normal 72.083333 140.000000\n", "Poison 74.678571 NaN\n", "Psychic 54.953488 122.142857\n", "Rock 89.925000 122.250000\n", "Steel 92.086957 96.250000\n", "Water 72.777778 111.250000" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.pivot_table(index='Type 1', columns='Legendary', values='Attack', aggfunc='mean')" ] }, { "cell_type": "markdown", "id": "67008e47", "metadata": {}, "source": [ "The advantage of pivot table compared to using groupby + unstack is the `margins` parameter, which adds a row/column that gives the totals (or other aggregate functions) for each row/column:" ] }, { "cell_type": "code", "execution_count": 5, "id": "cd24ed55", "metadata": { "execution": { "iopub.execute_input": "2025-09-02T18:59:02.466104Z", "iopub.status.busy": "2025-09-02T18:59:02.465979Z", "iopub.status.idle": "2025-09-02T18:59:02.479536Z", "shell.execute_reply": "2025-09-02T18:59:02.479105Z" }, "tags": [ "hide-output", "scroll-output" ] }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
LegendaryFalseTrueAll
Type 1
Bug70.971014NaN70.971014
Dark86.862069110.50000088.387097
Dragon103.400000126.666667112.125000
Electric66.12500098.75000069.090909
Fairy57.187500131.00000061.529412
Fighting96.777778NaN96.777778
Fire82.191489109.00000084.769231
Flying50.000000107.50000078.750000
Ghost71.366667110.00000073.781250
Grass72.11940397.66666773.214286
Ground88.000000150.00000095.750000
Ice73.22727367.50000072.750000
Normal72.083333140.00000073.469388
Poison74.678571NaN74.678571
Psychic54.953488122.14285771.456140
Rock89.925000122.25000092.863636
Steel92.08695796.25000092.703704
Water72.777778111.25000074.151786
All75.669388116.67692379.001250
\n", "
" ], "text/plain": [ "Legendary False True All\n", "Type 1 \n", "Bug 70.971014 NaN 70.971014\n", "Dark 86.862069 110.500000 88.387097\n", "Dragon 103.400000 126.666667 112.125000\n", "Electric 66.125000 98.750000 69.090909\n", "Fairy 57.187500 131.000000 61.529412\n", "Fighting 96.777778 NaN 96.777778\n", "Fire 82.191489 109.000000 84.769231\n", "Flying 50.000000 107.500000 78.750000\n", "Ghost 71.366667 110.000000 73.781250\n", "Grass 72.119403 97.666667 73.214286\n", "Ground 88.000000 150.000000 95.750000\n", "Ice 73.227273 67.500000 72.750000\n", "Normal 72.083333 140.000000 73.469388\n", "Poison 74.678571 NaN 74.678571\n", "Psychic 54.953488 122.142857 71.456140\n", "Rock 89.925000 122.250000 92.863636\n", "Steel 92.086957 96.250000 92.703704\n", "Water 72.777778 111.250000 74.151786\n", "All 75.669388 116.676923 79.001250" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "data.pivot_table(index='Type 1', columns='Legendary', values='Attack', aggfunc='mean', margins=True)" ] } ], "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 }