CODEWITHSUNDEEP
pythonpandaspivotpivot-table
Vincent Claes
Vincent Claes

Reputation: 4768

pandas pivot_table keep index

i have a dataframe :

import pandas as pd

data = {'day_bucket': ['2011-01-21', '2011-01-22', '2011-01-23', '2011-01-24'], 'label': ['birds', 'birds', 'birds', 'birds'], 'numeric_value': [4, 0, 7, 3]}

df = pd.DataFrame(data)

   day_bucket  label  numeric_value
0  2011-01-21  birds              4
1  2011-01-22  birds              0
2  2011-01-23  birds              7
3  2011-01-24  birds              3

I want to pivot this dataframe so that i have a column birds with the values below it.

pd.pivot_table(df, values='numeric_value', index='day_bucket',columns='label')

gives:

label       birds
day_bucket             
2011-01-21  4
2011-01-22  0
2011-01-23  7
2011-01-24  3

what should i do the keep the index? The result will look like:

    day_bucket birds    
0   2011-01-21  4
1   2011-01-22  0
2   2011-01-23  7
3   2011-01-24  3

Upvotes: 8

Views: 19025

Answers (3)

piRSquared
piRSquared

Reputation: 294218

set_index with append

df.set_index(['day_bucket', 'label'], append=True) \
  .rename_axis([None, None, None]).squeeze().unstack()


              birds
0 2011-01-21      4
1 2011-01-22      0
2 2011-01-23      7
3 2011-01-24      3

Upvotes: 6

Sean.H
Sean.H

Reputation: 682

just for supplement:

pt2 = pt.rename_axis(None, axis=1).reset_index()
print(pt2)

   day_bucket  birds
0  2011-01-21      4
1  2011-01-22      0
2  2011-01-23      7
3  2011-01-24      3

the dataframe used for it is:

df = pd.DataFrame({'day_bucket': ['2011-01-21', '2011-01-22', '2011-01-23', '2011-01-24'],
                   'label': ['birds', 'birds', 'birds', 'birds'],
                   'num_value': [4, 0, 7, 3]})

pt = pd.pivot_table(df,
                    values='num_value',
                    index='day_bucket',
                    columns='label',
                    aggfunc=np.sum)
print(pt, '\n')

label       birds
day_bucket       
2011-01-21      4
2011-01-22      0
2011-01-23      7
2011-01-24      3

Upvotes: 4

Vincent Claes
Vincent Claes

Reputation: 4768

In the meantime, I also came up with a result

pd.pivot_table(df, values='numeric_value', 
                   index=[df.index.values,'day_bucket'],
                   ,columns='label').reset_index('day_bucket')

label  day_bucket  mortality_birds
0      2011-01-21                4
1      2011-01-22                0
2      2011-01-23                7
3      2011-01-24                3

Upvotes: 7

Related Questions