CODEWITHSUNDEEP
pythonpandas
proximacentauri
proximacentauri

Reputation: 1879

pandas pivot columns to rows

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>?

Upvotes: 9

Views: 14773

Answers (1)

jpp
jpp

Reputation: 164663

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.

Upvotes: 9

Related Questions