CODEWITHSUNDEEP
pythonpandas
GK89
GK89

Reputation: 664

Pandas Reshape of Long Data which is grouped

I have a dataframe that looks like this:

   ID  Product Type   Product Setting
   1      AS1              22
   1      AX1              23
   1      AS1              50
   2      AX1              50

I would like something that dynamically creates the number of columns based on whats populated:

ID   Product Type(1)   Product Type(2)  Product Type(3)  Product Setting(1)  Product Setting(2)   Product Setting(3)
1    AS1              AX2             AS1.             22                  23       60   
2   AX1              NA              NA              50                  NA         NA

I have tried to used the pivot() function in pandas but it does not work for my particular case

Upvotes: 0

Views: 54

Answers (2)

Henry Ecker
Henry Ecker

Reputation: 35636

A pivot_table option which uses groupby cumcount to enumerate group rows which will become the column numbers:

# Pivot on ID and position within the group
new_df = df.pivot_table(
    index='ID',
    columns=df.groupby('ID').cumcount() + 1,
    aggfunc='first'
).reindex(['Product Type', 'Product Setting'], level=0, axis=1)
# Collapse MultiIndex
new_df.columns = new_df.columns.map(lambda c: f'{c[0]}({c[1]})')
# Make ID a column instead of index
new_df = new_df.reset_index()

new_df:

   ID Product Type(1) Product Type(2) Product Type(3)  Product Setting(1)  Product Setting(2)  Product Setting(3)
0   1             AS1             AX1             AS1                22.0                23.0                50.0
1   2             AX1             NaN             NaN                50.0                 NaN                 NaN

Upvotes: 1

BENY
BENY

Reputation: 323266

Try assign new column with cumcount then pivot

out = df.assign(key = df.groupby('ID').cumcount().add(1).astype(str)).\
          pivot(index=['ID'],columns=['key'],values=['Product Type','Product Setting'])
out.columns = out.columns.map('_'.join)
out
Out[66]: 
   ProductType_1 ProductType_2  ... ProductSetting_2 ProductSetting_3
ID                              ...                                  
1            AS1           AX1  ...               23               50
2            AX1           NaN  ...              NaN              NaN
[2 rows x 6 columns]

Upvotes: 3

Related Questions