CODEWITHSUNDEEP
pythonpandas
8-Bit Borges
8-Bit Borges

Reputation: 10033

Pandas - get rid of repeated pair of column values in a row and move unique row value to new column

I have a dataframe of football matches, like so:

team_id  adversary_id round_id xG
262      263          1        0.45
263      262          1        0.34
245      254          1        0.67
254      245          1        0.15
...

How do I get rid of repeated fixtures and change dataframe into this wider format:

team_id  adversary_id  round_id  xG_team  xG_adversary
262      263           1         0.45     0.34
245      254           1         0.67     0.15
...

Upvotes: 1

Views: 33

Answers (1)

BENY
BENY

Reputation: 323316

Try with numpy sort then merge

df[['team_id','adversary_id']] = np.sort(df[['team_id','adversary_id']].values,axis=1)
out = df.iloc[0::2].merge(df.iloc[1::2],on = ['team_id','adversary_id','round_id'], suffixes = ('_team','_adversary'))
Out[403]: 
   team_id  adversary_id  round_id  xG_team  xG_adversary
0      262           263         1     0.45          0.34
1      245           254         1     0.67          0.15

Upvotes: 3

Related Questions