Pandas: how to unpivot crosstable

Many data provided by government are not vertically stacked table like database. They are usually summed up by raws and columns, and sometimes nested.
I don’t like such pivot table because it’s weary to make data visualization with it. You would need to parse different attribute along with two dimensions –rows and columns.

The snippet below unpivots the pivot table. The final product has only columns. And its rows are the possible combination of nominal values of the new columns.

 

1.Reading xls file which is a simple pivot table. The data is dataframe of Pandas:

import pandas as pd
data = pd.read_excel("pivot_data.xls")
data
A B C
a 3 10 11
b 2 9 3
c 1 4 5
d 7 6 1

 

2.Converting the columns into values of a column. Each row is possible combination of ABC and abcd:

unstack_data = data.unstack().reset_index(name="value")
unstack_data
level_0 level_1 value
0 A a 3
1 A b 2
2 A c 1
3 A d 7
4 B a 10
5 B b 9
6 B c 4
7 B d 6
8 C a 11
9 C b 3
10 C c 5
11 C d 1

 

3.Change column names:

unstack_data.columns=["ABC","abcd","value"]
unstack_data
ABC abcd value
0 A a 3
1 A b 2
2 A c 1
3 A d 7
4 B a 10
5 B b 9
6 B c 4
7 B d 6
8 C a 11
9 C b 3
10 C c 5
11 C d 1

Leave a Reply