CODEWITHSUNDEEP
pythonpandaspivotdataframe
xpt
xpt

Reputation: 22984

Pandas, Pivoting DataFrame By Multiple Hierarchical Columns

I'm following

https://nikolaygrozev.wordpress.com/2015/07/01/reshaping-in-pandas-pivot-pivot-table-stack-and-unstack-explained-with-pictures/

but am facing a different scenario for pivoting DataFrames.

The basic pivot command is like this:

d.pivot(index='Item', columns='CType', values='USD')

Now suppose my 'Item', belongs to two categories, 'Area' and 'Region', in two other data columns. I want the pivoted result contains those three levels (Region, Area, Item). How can I do that?

I had been looking for answers everywhere, and had been trying methods like 'unstack', 'droplevel', 'reset_index', etc, but wasn't able to make them work myself.

Please help.

Thanks

Upvotes: 0

Views: 567

Answers (1)

Alexander
Alexander

Reputation: 109528

First, you probably want to use pd.pivot_table. Second, when you want to have multiple columns along a dimension, you need to pass them as a list (e.g. index=['Item', 'Area', 'Region']).

# Random data.
np.random.seed(0)
df = pd.DataFrame({'Area': ['A', 'A', 'A',  'B', 'B', 'B'], 
                   'Region': ['r', 's', 'r', 's', 'r', 'r'], 
                   'Item': ['car' ,'car', 'car', 'truck', 'bus', 'bus'], 
                   'CType': [3, 4, 3, 3, 5, 5], 
                   'USD': np.random.rand(6) * 100})

>>> df
  Area  CType   Item Region        USD
0    A      3    car      r  54.881350
1    A      4    car      s  71.518937
2    A      3    car      r  60.276338
3    B      3  truck      s  54.488318
4    B      5    bus      r  42.365480
5    B      5    bus      r  64.589411

>>> pd.pivot_table(df, 
                   index=['Item', 'Area', 'Region'], 
                   columns='CType', 
                   values='USD', 
                   aggfunc=sum)

CType                       3          4           5
Item  Area Region                                   
bus   B    r              NaN        NaN  106.954891
car   A    r       115.157688        NaN         NaN
           s              NaN  71.518937         NaN
truck B    s        54.488318        NaN         NaN

Upvotes: 1

Related Questions