Converting only selected columns to transpose using melt and pivot?

user_5037278 0
Viewed 35

I have dataframe like this: 

id  Gender        TV        Radio
0   M            Daily      Daily
1   F            Monthly    Weekly
2   M            Weekly     Weekly
3   F            Daily      Daily

I need to change the columns into values and values into column, This is my desired output. 

id  Gender             Daily    Monthly      Weekly
0   M                   TV       NaN          NaN
0   M                   Radio    NaN          NaN
1   F                   NaN      TV           Weekly
2   M                   NaN      NaN          TV
2   M                   NaN      NaN          Radio
3   F                   TV       NaN          NaN
3   F                   Radio    NaN          NaN

I am using melt+pivot but i lose information. Here is what i am using.

idx = ['id', 'Gender']
m = df.melt(idx).pivot_table(index=idx,columns='value',values='variable',aggfunc='first')
out = m.reset_index(idx).rename_axis(index=None,columns=None)

But i am getting only the first value. For example if the daily occur for tv and radio then i am geting only the first value.. I know i am aggregating only first but dont know which function i can use to get all the values. 

 id    Gender               Daily   Monthly      Weekly
    0   M                   TV       NaN          NaN
    1   F                   NaN      TV           Weekly
    2   M                   NaN      NaN          TV
    3   F                   TV       NaN          NaN
melt pandas dataframe pivot python
1 Answers

Use DataFrame.melt with GroupBy.cumcount for counter by duplicated values and then Series.unstack for reshape:

idx = ['id', 'Gender']
m = df.melt(idx)
m['g'] = m.groupby(idx).cumcount()
m = m.set_index(idx + ['g', 'value'])['variable'].unstack()
out = m.reset_index(idx).rename_axis(index=None,columns=None)
print (out)
   id Gender  Daily Monthly Weekly
0   0      M     TV     NaN    NaN
1   0      M  Radio     NaN    NaN
0   1      F    NaN      TV    NaN
1   1      F    NaN     NaN  Radio
0   2      M    NaN     NaN     TV
1   2      M    NaN     NaN  Radio
0   3      F     TV     NaN    NaN
1   3      F  Radio     NaN    NaN
user_29010020
Related