I am currently trying to return a dataframe to its' original state after performing some operations on the pivoted dataframe.

I basically have a dataframe which looks like:

After transforming the dataframe using pivot and performing some operations on it, the dataframe looks like this where every row represented by a date, every column is unique combination of appkey cc and the value is the target.

besides that, I have also added an aggregation of the sum of target under total which sums up daily target and appkey_total which sums up daily target but only for the appkey.

The idea is to return the pivoted table to it's original state the total and appkey_total as added columns.

My problem is that I don't keep appkey and cc as columns in the pivot table and I concatenate the appkey and cc, so I'm not sure how to return it back? I can't melt it because I don't have the original columns names.

Any help will be appreciated, thanks!

Edit After trying what @jezrael suggested, I got the following output: As can be seen, the appkey was added as index, while the 3 unique appkey are stayed as column names.

CodePudding user response：

Add total to index for MultiIndex, then split columns and reshape by DataFrame.stack:

df1 = df.set_index('total', append=True)
df1.columns = df1.columns.str.split('_', expand=True)
df1 = df1.rename_axis(['cc','appkey'], axis=1).stack([0, 1]).reset_index()