Easy way to make a pivot table

I often work with databases and sometimes I need to transform raw data into pivot table. Many people doesn’t know it might be done by using pivot_table() from pandas library.

Let’s say we have a table where id and key1_N are keys. And we want to transform it into a new form like this:

pivot_tables

How to do it:

import pandas as pd

df = pd.DataFrame({
 'id': [1,1,1,2,2,2],
 'key1_N': [1,2,3,1,2,3],
 'var1': [3,4,8,3,4,8],
 'var2': [5,1,6,6,1,1]
 })
df = df.pivot_table(index='id', columns='key1_N', values=['var1', 'var2'])

After using this operation we get a table with two-level columns. But we can change the value of df.columns:

import itertools 
df.columns = list([x[0] + '_' + str(x[1]) for x in itertools.product(df.columns.levels[0], df.columns.levels[1])])

Leave a Reply

Your email address will not be published. Required fields are marked *