CODEWITHSUNDEEP
pythondjangopandas
Bill Armstrong
Bill Armstrong

Reputation: 1777

Pandas Privot Table from a Django-Pandas queryset

I have the following dataframe that was created from a Django queryset using Django-Pandas. I think this is important because I can't find a way to set the column index on conversion.

df1 = pd.DataFrame({
    'J_col': ['us', 'us', 'us', 'ca', 'az', 'az'],
    'def_desc': ['item1', 'item2', 'item3', 'item4', 'item5', 'item3'],
    'desc_bal': [45000, -120000, 53000, 100, 1000, 2000],
    'valid': [False, True, True, False, True, True]})


In [12]: df1
Out[12]: 
  J_col def_desc  desc_bal  valid
0    us    item1     45000  False
1    us    item2   -120000   True
2    us    item3     53000   True
3    ca    item4       100  False
4    az    item5      1000   True
5    az    item3      2000   True

I would like to have a multi-index for the columns that looks like this.

   def_desc  us               ca               az
             desc_bal  valid  desc_bal  valid  desc_bal  valid
0  item 1       45000  False
1  item 2     -120000   True
2  item 3       53000   True                       1000   True
3  item 4                          100  False
4  item 5                                          2000   True
5

Note that there will be rows with common def_desc that need to be grouped. I've tried using a .pivot_table() like this. But it throws KeyError: 'desc_bal'

In [20]: pd.pivot_table(df, values=['desc_bal', 'values'], index='def_desc', columns='J_col')

Question - how do I pivot this df into this shape?

Upvotes: 1

Views: 470

Answers (1)

cs95
cs95

Reputation: 402854

You'll need pivot_table (or pivot) and then a little housekeeping to reorder the axes and levels.

(df1.pivot_table(index='def_desc', columns='J_col')
    .swaplevel(0, 1, axis=1)
    .sort_index(axis=1, level=[0,1], ascending=[False,True])
    .fillna('')
)

J_col          us              ca              az      
         desc_bal  valid desc_bal  valid desc_bal valid
def_desc                                               
item1       45000  False                               
item2     -120000   True                               
item3       53000   True                     2000  True
item4                         100  False               
item5                                        1000  True

Upvotes: 1

Related Questions