Search code examples
pythonpandaspivottranspose

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

  • 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.