Search code examples
pythonnumpytranspose

Transpose in Python Pandas

I have the below table. 

Item Id Desc    Values
M1  Color   Red
M1  Weight  100GR
M1  Type    Mouse
M1  Company Lenovo
M1  Wireless    Yes
M1  Battery Yes
M2  Color   Green
M2  Weight  150GR
M2  Type    KB
M2  Company Lenovo
M2  Wireless    No
M2  Battery No
M3  Color   Yellow
M3  Weight  100GR
M3  Type    Headphone
M3  Company MS
M3  Wireless    Yes
M3  Battery No

Expected Output: 

Item Id Color  Weight   Type    Company Wireless    Battery
M1      Red    100GR    Mouse    Lenovo  Yes        Yes
M2     Green   150GR    KB       Lenovo   No        No
M3   Yellow    100GR    Headphone MS      Yes   No

The original file contains millions of records. The attached file is just an example.

Please help me get the solution. I tried .T and also tried through loop, but not able to set it correct. Looping hangs by PC when I work on the original file.

Sorry I am not able to find the option to attach the excel file.

Solution

  • You could try something like pivot_tables.

    import pandas as pd

    df = pd.read_csv('data.csv')

    df2 = pd.pivot_table(df, values='Values', index = ['ItemId'], columns=['Desc'], aggfunc=lambda x: ' '.join(x))

    print(df2.head())

    Play with the indexes and columns for a different result.

    https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.pivot_table.html