Reshaping dataframe with multiple columns to row groups

Question

inp Dataframe

df = pd.DataFrame({'Loc': ['Hyd', 'Hyd','Bang','Bang'],
               'Item': ['A', 'B', 'A', 'B'],
               'Month' : ['May','May','June','June'],
               'Sales': [100, 100, 200, 200],
                'Values': [1000, 1000, 2000, 2000]
               })

My expected output

df = pd.DataFrame({'Loc': ['Hyd', 'Hyd','Hyd','Hyd','Bang','Bang','Bang','Bang'],
               'Item': ['A', 'A', 'B', 'B','A', 'A', 'B', 'B'],
               'VAR' : ['Sales','Values','Sales','Values','Sales','Values','Sales','Values'],
               'May': [100, 1000, 100, 1000, 100, 1000, 100, 1000],
                'June': [200, 2000, 200, 2000, 200, 2000, 200, 2000]
               })

I have tried multiple solutions using melt and pivot but nothing seems to work ? not sure where I am missing ?

Here's my code

    dem.melt(['Part','IBU','Date1']).pivot_table(index=['Part','IBU','variable'],columns=['Date1'])

Any help would be much appreciated

Please don't post [images of data](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-errors-when-asking-a-question) instead add them in text format so that we could be able to copy these while trying to answer your question. Please take some time to read [How to ask good pandas questions?](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — Shubham Sharma, May 24 '21 at 11:35
The data in your input and output dataframe doesn't relate. If you see there is no entry for the month of june for `hyd A` similarly there is no entry for may in `Bang B` — Shubham Sharma, May 24 '21 at 12:08

score 1 · Answer 1 · answered May 24 '21 at 12:23

You can use melt and pivot functions in pandas:

df_melted = pd.melt(df, id_vars=["Loc", "Item", "Month"], value_vars=["Sales", "Values"])

This will result:

And then:

df_pivot = df_melted.pivot_table(index=["Loc", "Item", "variable"], columns="Month")

So, the final output will be:

Reshaping dataframe with multiple columns to row groups

1 Answers1