[PYTHON] About MultiIndex of pandas

This article is the 19th day article of Python Part 2 Advent Calendar 2015.

I tried to register for Advent Calendar, but recently I haven't written such Python & even if I write it, I can't write it because I've done too many special things in business, so I'm a little reminded now, but it's a small story of pandas. I will write about pandas, which is familiar in data preprocessing and aggregation, and Multiindex, which I know but is a little difficult to understand.

Environment at the time of writing

• Python 2.7.11 |Anaconda 2.0.1 (x86_64)
• pandas 0.17.0
• Execution environment: jupyter notebook

The code that appears below is

import pandas as pd

It is written on the premise that it is done. Also, the pasted table is a copy of the to_html version, so it is slightly different from the display on the jupyter notebook.

What is MultiIndex

MultiIndex / Advanced Indexing A function to index multiple columns with DataFrame of pandas. This is useful when data can be uniquely identified by using multiple values as keys.

Data preparation and review of ordinary indexes

Ordinary indexes are referred to in this article as Single Index for convenience in contrast to Multi Index. (There is no such type)

Data preparation

Prepare the following csv for explanation

sample.csv

name,year,product,expected,actual
Taro,2013,A,116,119
Taro,2013,B,131,149
Taro,2014,A,151,124
Taro,2014,B,125,107
Taro,2015,A,135,109
Taro,2015,B,142,148
Hanako,2013,A,170,130
Hanako,2013,B,113,190
Hanako,2014,A,102,142
Hanako,2014,B,183,125
Hanako,2015,A,169,120
Hanako,2015,B,134,199

Read this with pandas.

df1 = pd.read_csv('sample.csv')

Which of the row labels in this table, 0,1,2 ..., is data.index and the column labels"name", "year", ...is data.columns? Also has a data type of pandas.Index.

Because pandas.Index has a member called name

df1.index.name = "id"
df1.columns.name = "header"

Then

Data selection

• Select by column

df1["name"]

output

id
0       Taro
1       Taro
2       Taro
3       Taro
4       Taro
5       Taro
6     Hanako
7     Hanako
8     Hanako
9     Hanako
10    Hanako
11    Hanako
Name: name, dtype: object

• Select by line

df1.loc[3]

output

header
name        Taro
year        2014
product        B
expected     125
actual       107
Name: 3, dtype: object

• Select by row index

df1.iloc[3]

This time index is a row index, so the output is the same.

• Select by row and column

df1.ix[3:5,"product":"actual"]

output

• Select by condition

df1[(df1["name"] == "Hanako")&(df1["product"] == "A")]

output

See other articles and books for details.

MultiIndex settings

The main subject is from here. As you can see, the data read earlier

• There are no unique columns in rows like record id,
• If you combine the three columns of name, year, and product, one record will be determined.

In such a case, use MultiIndex to organize the data neatly.

df2 = pd.read_csv("sample.csv",index_col=["name","year","product"])

After reading the data, or when applying MultiIndex to the DataFrame created by various processing 2016/3/18 postscript </ font> If you use the set_index function, the specified column will be indexed. (I didn't know this because it wasn't listed on the MultiIndex page of the official document ...)

df2 = df1.set_index(["name","year","product"], drop=True)

df1 = df2.reset_index()

pd.MultiIndex.from_arrays()
pd.MultiIndex.from_product()
pd.MultiIndex.from_tuples()

pd.MultiIndex.from_product((["Taro","Hanako"],[2013,2014,2015],["A","B"]),\
    names=["name","year","product"])

df2.loc[("Taro",2013,"A")]

expected    116
actual      119
Name: (Taro, 2013, A), dtype: int64

df2.loc[("Taro",2013)]

df2.xs(["Taro","A"],level=[0,2])

df2.loc[("Taro",2013,"A")]

df1[(df1["name"] == "Taro")&(df1["year"] == 2013)&(df1["product"] == "A")].iloc[0]

("Taro",2013,"A") in df2.index

df1[(df1["name"] == "Taro")&(df1["year"] == 2013)&(df1["product"] == "A")].shape[0]>0