Python convert multiple column values into a single column

Question

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'])

MrNobody33 · Accepted Answer · 2020-08-11 06:26:14Z

2

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.

edited Aug 11, 2020 at 6:26

answered Aug 11, 2020 at 5:58

MrNobody33

6,5039 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jezrael · Accepted Answer · 2020-08-11 06:03:28Z

1

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]

edited Aug 11, 2020 at 6:03

answered Aug 11, 2020 at 5:52

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Python convert multiple column values into a single column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related