How to merge rows within the same dataframe in python?

Question

I would like to combine rows of the same dataframe, more precisely, to take rows that have the same values in a specific column and create only one row. Here is an example:

I have the following dataframe:

te= {'TEAM': ['HC','TC','HC','BC','TC','BC'],
        'A1': [22,25,27,35,31,41],
        'A2': [20,50,70,11,14,12]
        }

df = pd.DataFrame(te,columns= ['TEAM', 'A1', "A2"])

print (df)

 TEAM  A1  A2
0   HC  22  20
1   TC  25  50
2   HC  27  70
3   BC  35  11
4   TC  31  14
5   BC  41  12

and I would like to form a row for the three possibles values of the column TEAM such as the expected output look like this:

 TEAM  A1  A2  A1(1)  A2(1)
0   HC  22  20     27     70
1   TC  25  50     31     14
2   BC  35  11     41     12

How can I do that ?

the last row of your output isnt correct. it should be 35 11 41 12 — gbruenjes
– gbruenjes, Commented Nov 13, 2019 at 6:18

Andy L. · Accepted Answer · 2019-11-13 06:32:24Z

1

It is pivot table with pre-process for columns

s = df.groupby('TEAM').cumcount()
m = s.astype(bool) * ('('+s.astype(str)+')')
df_out = df.set_index(['TEAM', m]).unstack().sort_index(level=1, axis=1).reset_index()
df_out.columns = df_out.columns.map(lambda x: f'{x[0]}{x[1]}')

Out[268]:
  TEAM  A1  A2  A1(1)  A2(1)
0   BC  35  11     41     12
1   HC  22  20     27     70
2   TC  25  50     31     14

answered Nov 13, 2019 at 6:32

Andy L.

25.3k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DYZ · Accepted Answer · 2019-11-13 06:41:23Z

0

There may be a better way, but this solution scales to an arbitrary number of lines.

df['order'] = df.groupby('TEAM').cumcount() + 1
df.set_index(['TEAM','order']).unstack()
#       A1      A2         
#order   1   2   1   2  
#TEAM                       
#BC     35  41  11  12  
#HC     22  27  20  70  
#TC     25  31  50  14

answered Nov 13, 2019 at 6:41

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Collectives™ on Stack Overflow

How to merge rows within the same dataframe in python?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related