pandas pivot columns to rows

Question

I have a dataframe such as the one below that I pivoted to apply some operations on.

Original dataframe df:

index   item   value    day     time
0   P472   0.126    2011-12-08  00:00:00
1   P472   0.12     2011-12-08  00:30:00
2   P472   0.119    2011-12-08  01:00:00
3   P472   0.425    2011-12-08  01:30:00
4   P472   0.154    2011-12-08  02:00:00

I pivoted the dataframe with code below to produce the new dataframe below:

df_pivoted = df.pivot_table(index=['item', 'day'], columns='time',
                 values='value', aggfunc='first').reset_index()

df_pivoted:

index   item   day      00:00:00 00:30:00 ... 23:30:00
0   P472   2011-12-08   0.126    0.12     ...   0.18
1   P473   2011-12-08   0.5      0.55     ...   0.30

Now I want to re-pivot df_pivoted to be in the layout of the original dataframe, ie collapse columns 00:00:00 through 23:30:00 to a time column, each 24 hour time within a specific day, and re-introducing the value column (using df_pivoted.stack?) but I cant work out how to do this. Any ideas>?

jpp · Accepted Answer · 2018-10-06 02:33:01Z

9

The reverse of pd.pivot_table can be achieved via pd.melt:

df_melted = df_pivoted.melt(id_vars=['index', 'item', 'day', 'time'],
                            value_vars=['value']).drop('variable', 1)

print(df)

   index  item  value         day      time
0      0  P472  0.126  2011-12-08  00:00:00
1      1  P472  0.120  2011-12-08  00:30:00
2      2  P472  0.119  2011-12-08  01:00:00
3      3  P472  0.425  2011-12-08  01:30:00
4      4  P472  0.154  2011-12-08  02:00:00

It's worth pointing out this works because your aggregation function is 'first' and combinations of ['index', 'item', 'day', 'time'] are unique. If they were not, then the pivot table would be aggregating data and losing information which cannot be put together again.

answered Oct 6, 2018 at 2:33

jpp

166k37 gold badges301 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pandas pivot columns to rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related