CODEWITHSUNDEEP
pythonpandas
TheStoryTeller
TheStoryTeller

Reputation: 35

Reshaping a Dataframe in pandas(With Date)

I have a dataframe and i am looking to mould it into the way i need. Here is an exact replica of the dataframe i am working on...

stack_overflow = pd.DataFrame({'id': ['a1','a1','a1','a2','a2','a2','a3','a3','a3'],
                              'value': [10,20,0,33,64,21,87,55,4],
                               'schedule': [22,11,44,55,7,5,3,2,4],
                               'Date':['2020-09-13','2020-09-13','2020-09-13','2020-09-14','2020-09-14','2020-09-14',
                                       '2020-09-15','2020-09-15','2020-09-15']})

I am looking to get it into this format

          2020-09-13     2020-09-14     2020-09-15    
     Schedule Value  Schedule Value  Schedule Value 
a1      22      10      55      33       3       87
a2      11      20       7      64       2       55
a3      44       0       5      21       4        4

I am not a data person, so this is how far i got to.

pivot_table = pd.pivot_table(stack_overflow,values = ['schedule','value'],index = ['id'],columns = ['Date'])

As you can see the output is not very readable at all. Any suggestions to get the output into the above mentioned format?

Upvotes: 2

Views: 54

Answers (1)

jezrael
jezrael

Reputation: 863531

Use DataFrame.swaplevel with DataFrame.sort_index:

pivot_table = (pd.pivot_table(stack_overflow,values = ['schedule','value'],
                             index = ['id'],
                             columns = ['Date'])
                 .swaplevel(1,0, axis=1)
                 .sort_index(axis=1))
print (pivot_table)
Date 2020-09-13       2020-09-14            2020-09-15           
       schedule value   schedule      value   schedule      value
id                                                               
a1    25.666667  10.0        NaN        NaN        NaN        NaN
a2          NaN   NaN  22.333333  39.333333        NaN        NaN
a3          NaN   NaN        NaN        NaN        3.0  48.666667

Upvotes: 1

Related Questions