Adding columns based on values in same group

Question

I had a question that is borne out of data that is formatted in a way that isn't optimal to do "versus analysis". For example, we have one record per name per game, and we also have data for the player they played against (always 2 records per game; think center vs. center or pg vs. pg in basketball etc).

Current data:

    uid   name  field1  field2  field3
    1     bob       35      0       49
    1     evan       4      24      29
    2     bob       39      47      26
    2     mike       6      40      49
    3     bob       48      7       7
    3     evan      18      20      11
    4     bob        3      49      41
    4     evan      25      35      23

Desired Output:

uid     name    versus  field1  field2  field3
1        bob    evan    same as above 
1        evan   bob
2         bob   mike
2        mike   bob
3         bob   evan
3        evan   bob 
4         bob   evan    
4        evan   bob

The goal of this re-arrangement/addition being that now I can do group-by analysis on one player vs. another over multiple games to find how they do on average in that matchup.

Rohith · Accepted Answer · 2020-02-25 01:05:47Z

2

Since it is just 2 entries for each game, you could try grouping and reversing the names.

df['versus'] = df.groupby('uid')['name'].transform(lambda x: x[::-1])

answered Feb 25, 2020 at 1:05

Rohith

1,0483 gold badges8 silver badges19 bronze badges

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

Comments

Quang Hoang · Accepted Answer · 2020-02-25 01:34:43Z

1

If you only need the other player's name, you should go for Rohith's solution. You can also use merge and query to get other players' statistics as well:

(df.merge(df, on='uid')
  .query('name_x != name_y')
)

Output

      uid  name_x      field1_x    field2_x    field3_x  name_y      field1_y    field2_y    field3_y
--  -----  --------  ----------  ----------  ----------  --------  ----------  ----------  ----------
 1      1  bob               35           0          49  evan               4          24          29
 2      1  evan               4          24          29  bob               35           0          49
 5      2  bob               39          47          26  mike               6          40          49
 6      2  mike               6          40          49  bob               39          47          26
 9      3  bob               48           7           7  evan              18          20          11
10      3  evan              18          20          11  bob               48           7           7
13      4  bob                3          49          41  evan              25          35          23
14      4  evan              25          35          23  bob                3          49          41

answered Feb 25, 2020 at 1:34

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

1 Comment

zeff Over a year ago

Thanks for this response as well. I did just need the player name but this is a great way to concatenate multiple row information into one.

Collectives™ on Stack Overflow

Adding columns based on values in same group

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related