I would like to reshape my data frame from long format to wide one.
Here is the example of what I want to achieve:
Input:
ID Group Month Stage Value
1 X July A 5
1 X July B 6
2 Y July B 7
Output:
ID Group Month A B
1 X July 5 0
1 X July 0 6
2 Y July 0 7
I tried the following code:
df.pivot(index='ID', columns='Stage', values='Value')
It obviously won't work because there are duplicates in ID variable and also the ID is not the only index that should be used to pivot (it should also be pivoted by Group and Month).
One idea that I have is to create a new variable by concatenating ID, Group and Month, then pivot the table and then try to recreate the original variables. But maybe there is something easier.
I'd be super grateful for help.
