CODEWITHSUNDEEP
pythonpandaspivottranspose
shockwave
shockwave

Reputation: 3272

Python convert multiple column values into a single column

In Python how to convert the following table structure into rows

ID   Manager1    Manager2    Manager3
1    S394[1]       G490[2]     3004[3]
2    3905[1]       4003[2]     5003[3]

Expected Output

    ID   Manager
    1    S394[1]
    1    G490[2]
    1    3004[3]
    2    3905[1] 
    2    4003[2] 
    2    5003[3]

I tried using transpose() and pivot(). Both options did not work

df.pivot(index='SOEID',columns=['Manager1','Manager2','Manager3'])

I also tried pd.melt() I get an error saying index has only 1 level not 2.

df.melt(id_vars='SOEID', var_name=['Manager1','Manager2','Manager3'])

Upvotes: 0

Views: 48

Answers (2)

MrNobody33
MrNobody33

Reputation: 6483

You can use lreshape too:

pd.lreshape(df, {'Manager': ['Manager1', 'Manager2','Manager3']}).sort_values('ID')

Ouput:

   ID  Manager
0   1  S394[1]
2   1  G490[2]
4   1  3004[3]
1   2  3905[1]
3   2  4003[2]
5   2  5003[3]

Note: lreshape is currently undocumented, and it's possible it might be removed.

Upvotes: 2

jezrael
jezrael

Reputation: 862731

Problem is you need value_vars for list of columns names in DataFrame.melt:

df1 = (df.melt(id_vars='ID', 
             value_vars=['Manager1','Manager2','Manager3'], 
             value_name='Manager', 
             var_name='tmp').drop('tmp', axis=1).sort_values('ID'))
 

print (df1)
   ID  Manager
0   1  S394[1]
2   1  G490[2]
4   1  3004[3]
1   2  3905[1]
3   2  4003[2]
5   2  5003[3]

Alternative solution with DataFrame.stack:

df2 = (df.set_index('ID')[['Manager1','Manager2','Manager3']]
         .stack()
         .reset_index(level=1, drop=True)
         .reset_index(name='Manager'))

print (df2)
   ID  Manager
0   1  S394[1]
1   1  G490[2]
2   1  3004[3]
3   2  3905[1]
4   2  4003[2]
5   2  5003[3]

Upvotes: 1

Related Questions