{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Selecting columns from multi-index dataframe" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "import pandas as pd" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Reference:\n", "\n", "1. [Selecting columns from multi-index dataframe](https://stackoverflow.com/questions/18470323/selecting-columns-from-pandas-multiindex)" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "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", "
Roll no.NameClassMathEnglish
Unnamed: 0_level_1Unnamed: 1_level_1Unnamed: 2_level_11st2nd3rd1st2nd3rd
01A10889391647370
12B9817589679790
23C9909590668176
34D10845981665657
45E9589472609466
\n", "
" ], "text/plain": [ " Roll no. Name Class Math \\\n", " Unnamed: 0_level_1 Unnamed: 1_level_1 Unnamed: 2_level_1 1st 2nd 3rd \n", "0 1 A 10 88 93 91 \n", "1 2 B 9 81 75 89 \n", "2 3 C 9 90 95 90 \n", "3 4 D 10 84 59 81 \n", "4 5 E 9 58 94 72 \n", "\n", " English \n", " 1st 2nd 3rd \n", "0 64 73 70 \n", "1 67 97 90 \n", "2 66 81 76 \n", "3 66 56 57 \n", "4 60 94 66 " ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df = pd.read_excel(\"..//data//sample.xlsx\", header = [3,4])\n", "df.head()" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "MultiIndex([('Roll no.', 'Unnamed: 0_level_1'),\n", " ( 'Name', 'Unnamed: 1_level_1'),\n", " ( 'Class', 'Unnamed: 2_level_1'),\n", " ( 'Math', '1st'),\n", " ( 'Math', '2nd'),\n", " ( 'Math', '3rd'),\n", " ( 'English', '1st'),\n", " ( 'English', '2nd'),\n", " ( 'English', '3rd')],\n", " )" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.columns" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "df = df.set_index([('Roll no.', 'Unnamed: 0_level_1'),\n", " ( 'Name', 'Unnamed: 1_level_1'),\n", " ( 'Class', 'Unnamed: 2_level_1')]).rename_axis(['Roll no.', 'Name', 'Class'])" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "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", "
MathEnglish
1st2nd3rd1st2nd3rd
Roll no.NameClass
1A10889391647370
2B9817589679790
3C9909590668176
4D10845981665657
5E9589472609466
6F10639796766665
7G10977759807159
8H9945591638462
9I9999299788685
10J10738782576288
\n", "
" ], "text/plain": [ " Math English \n", " 1st 2nd 3rd 1st 2nd 3rd\n", "Roll no. Name Class \n", "1 A 10 88 93 91 64 73 70\n", "2 B 9 81 75 89 67 97 90\n", "3 C 9 90 95 90 66 81 76\n", "4 D 10 84 59 81 66 56 57\n", "5 E 9 58 94 72 60 94 66\n", "6 F 10 63 97 96 76 66 65\n", "7 G 10 97 77 59 80 71 59\n", "8 H 9 94 55 91 63 84 62\n", "9 I 9 99 92 99 78 86 85\n", "10 J 10 73 87 82 57 62 88" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Using `.loc[-- , --]`" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "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", "
MathEnglish
1st2nd3rd1st2nd3rd
Roll no.NameClass
1A10889391647370
2B9817589679790
3C9909590668176
4D10845981665657
5E9589472609466
\n", "
" ], "text/plain": [ " Math English \n", " 1st 2nd 3rd 1st 2nd 3rd\n", "Roll no. Name Class \n", "1 A 10 88 93 91 64 73 70\n", "2 B 9 81 75 89 67 97 90\n", "3 C 9 90 95 90 66 81 76\n", "4 D 10 84 59 81 66 56 57\n", "5 E 9 58 94 72 60 94 66" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.loc[:,(['Math', 'English'], ['1st', '2nd' , '3rd'])].head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Remember that `[]` and `()` have special meaning when dealing with a `MultiIndex` object:\n", "\n", "> (...) a tuple is interpreted as one __multi-level__ key\n", "\n", "> [...] a list is used to specify several keys [on the __same level__].\n", "\n", "> (...) a tuple of lists refer to several values within a level\n", "\n", "\n", "When we write `(['one', 'two'], ['a', 'b'])`, the first list inside the tuple specifies all the values we want from the 1st level of the `MultiIndex`. The second list inside the tuple specifies all the values we want from the 2nd level of the `MultiIndex`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "To specify that we want anything from the first level. And then specify which columns from the second level we want, we can use use `pd.IndexSlice`, which helps slicing frames with more elaborate indices." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "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", "
MathEnglishMathEnglish
1st1st3rd3rd
Roll no.NameClass
1A1088649170
2B981678990
3C990669076
4D1084668157
5E958607266
\n", "
" ], "text/plain": [ " Math English Math English\n", " 1st 1st 3rd 3rd\n", "Roll no. Name Class \n", "1 A 10 88 64 91 70\n", "2 B 9 81 67 89 90\n", "3 C 9 90 66 90 76\n", "4 D 10 84 66 81 57\n", "5 E 9 58 60 72 66" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "df.loc[:, pd.IndexSlice[:, ['1st', '3rd']]].head()" ] } ], "metadata": { "interpreter": { "hash": "318a27c7c4238041f5f3eedefa0c1947aff2141229b7b84848eabe630b86fe9c" }, "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.9.7" } }, "nbformat": 4, "nbformat_minor": 2 }