Group rows and add columns (delete repeated lines)

Question

How to group rows and add new columns.

See the example:

import pandas as pd

df = pd.DataFrame({
    'name': ['Andy', 'Bob', 'Chad', 'Andy', 'Chad', 'Bob', 'George', 'Hank'],
    'col_1': ['A1', 'A2', 'A3', 'A4', 'B1', 'B2', 'B3', 'B4'],
    'col_2': [1, 1, 2, 2, 1, 1, 2, 2]
    })

df.groupby(by="name")
df

This generates the following result:

        name    col_1   col_2
   0    Andy    A1      1
   1    Bob     A2      1
   2    Chad    A3      2
   3    Andy    A4      2
   4    Chad    B1      1
   5    Bob     B2      1
   6    George  B3      2
   7    Hank    B4      2

But I need it to look like this:

  name    col_1   col_2    col_1   col_2
0 Andy    A1      1        A4      2
1 Bob     A2      1        B2      1
2 Chad    A3      2        B1      1 
3 George  B3      2        
4 Hank    B4      2

Thanks

score 1 · Accepted Answer · answered Jun 15 '23 at 22:41

Try:

df['col'] = df.groupby('name').cumcount()
out = df.pivot(index='name', columns='col').swaplevel(axis=1).sort_index(axis=1).fillna('')
out.columns = (f'{b}_{a}' for a, b in out.columns)

print(out)

Prints:

       col_1_0  col_2_0 col_1_1 col_2_1
name                                   
Andy        A1      1.0      A4     2.0
Bob         A2      1.0      B2     1.0
Chad        A3      2.0      B1     1.0
George      B3      2.0                
Hank        B4      2.0

Perfect. It worked. Thank you very much! – marceloamadeu Jun 16 '23 at 00:20 — marceloamadeu, Jun 16 '23 at 00:20

Group rows and add columns (delete repeated lines)

1 Answers1