Home > database >  Unpivot in pandas using a column that have mutiple value columns
Unpivot in pandas using a column that have mutiple value columns

Time:08-11

So i have a dataframe frame like this

index type_of_product dt_of_product value_of_product size_of_product
1 A 01/02/22 23.1 1
1 B 01/03/22 23.2 2
1 C 01/04/22 23.3 2

And i need to unpivot the colum type_of_product with the values of dt_of_product, value_of_product and size_of_product

I tryed to use pd.pivot(df, index = "index", column = "type_of_product", values = ["dt_of_product","value_of_product","size_of_product"]

and want to get this desire output

index A_dt_of_product B_dt_of_product C_dt_of_product A_value_of_product B_value_of_product C_value_of_product A_size_of_product B_size_of_product C_size_of_product
1 01/02/22 01/03/22 01/04/22 23.1 23.2 23.3 1 2 2

Is there a way to do this in pandas with one pivot or do i have to do 3 pivots and merges all on them?

CodePudding user response:

You can do:

df = df.pivot(index='index', 
              values=['dt_of_product', 'value_of_product', 'size_of_product'], 
              columns = ['type_of_product'])
df.columns = df.columns.swaplevel(0).map('_'.join)

CodePudding user response:

Try:

df = (
    df.set_index(["index", "type_of_product"])
    .unstack(level=1)
    .swaplevel(axis=1)
)
df.columns = map("_".join, df.columns)
print(df.reset_index().to_markdown(index=False))

Prints:

index A_dt_of_product B_dt_of_product C_dt_of_product A_value_of_product B_value_of_product C_value_of_product A_size_of_product B_size_of_product C_size_of_product
1 01/02/22 01/03/22 01/04/22 23.1 23.2 23.3 1 2 2

CodePudding user response:

You could try set_index with unstack

s = df.set_index(['index','type_of_product']).unstack().sort_index(level=1,axis=1)
s.columns = s.columns.map('{0[1]}_{0[0]}'.format)
s
Out[30]: 
      A_dt_of_product  A_size_of_product  ...  C_size_of_product C_value_of_product
index                                     ...                                      
1            01/02/22                  1  ...                  2               23.3
[1 rows x 9 columns]
  • Related