Assuming I have the following toy dataset:

count    orders    books  
   1         40       2 
   1         60       4
   1         80       6
   2         10       8
   2         30      10
   2         50      12

I want to create new columns by transforming the dataset such that each column only contains orders and books by the count group

This is the output:

   orders_1   orders_2    books_1    books_2  
       40           10        2           8
       60           30        4          10
       80           50        6          12

I have attempted to use pivot tables, but the code throws me an error for using the same index as well as columns.

CodePudding user response：

Use GroupBy.cumcount for counter, pivot by DataFrame.pivot and last flatten MultiIndex in columns:

df = df.assign(g = df.groupby('count').cumcount()).pivot('g','count')
df.columns = df.columns.map(lambda x: f'{x[0]}_{x[1]}')
print (df)
   orders_1  orders_2  books_1  books_2
g                                      
0        40        10        2        8
1        60        30        4       10
2        80        50        6       12