How to un-pivot a dataframe with pandas

Question

For example, I have a dataframe like this

df = {'name':['Jennifer','Vivian','Trisha'],
     'married':[1,1,0],
     'Mon': [0, 0,1],
     'Tu':[1,0,0],
     'Wed':[0,1,0]}

How can I melt the dummy variables into one column like this:

I tried to use pd.melt() but it just stacks the several columns into one and changes the length of the column. Could someone help me with this? Thank you in advance!

cmaher · Accepted Answer · 2018-04-22T03:00:11.953

3

This should work:

df.replace({'married':{1:'Married', 0: 'Single'}}). \
   melt(id_vars=['married', 'name'], var_name='Workday'). \
   query('value == 1'). \
   drop('value', axis=1)

#    married      name Workday
# 2   Single    Trisha     Mon
# 3  Married  Jennifer      Tu
# 7  Married    Vivian     Wed

edited Apr 22 '18 at 03:00

answered Apr 22 '18 at 02:52

cmaher

5,100
1
22
34

score 0 · Answer 2 · answered Apr 22 '18 at 02:52

This is one way.

res = pd.melt(df, id_vars=['married', 'name'], value_vars=['Mon', 'Tu', 'Wed'],
              var_name='Workday')

res = res[res['value'] == 1].reset_index(drop=True)

d = {0: 'single', 1: 'married'}
res['married'] = res['married'].map(d)

print(res)

#    married      name Workday  value
# 0   single    Trisha     Mon      1
# 1  married  Jennifer      Tu      1
# 2  married    Vivian     Wed      1

How to un-pivot a dataframe with pandas

2 Answers2