How to get values from other rows based on multiple conditions in Pandas?

Question

I have the following df -

    +--------+--------+--------------------+------------+--------------------+----------+----------+
    | GameID | TeamID | Team               | OpponentID | Opponent           | Location | score    |
    +--------+--------+--------------------+------------+--------------------+----------+----------+
    | 1      | 1      | Alabama            | 2          | Jacksonville State | H        | 1.098633 |
    +--------+--------+--------------------+------------+--------------------+----------+----------+
    | 1      | 2      | Jacksonville State | 1          | Alabama            | V        | 0.756562 |
    +--------+--------+--------------------+------------+--------------------+----------+----------+
    | 2      | 3      | UAB                | 4          | Alcorn State       | H        | 1.270638 |
    +--------+--------+--------------------+------------+--------------------+----------+----------+
    | 2      | 4      | Alcorn State       | 3          | UAB                | V        | 0.682791 |
    +--------+--------+--------------------+------------+--------------------+----------+----------+

Each row represnts one of two teams results from a distinct GameID. My goal is to have a final df that looks like this

+--------+--------+--------------------+------------+--------------------+----------+----------+-----------------+
| GameID | TeamID | Team               | OpponentID | Opponent           | Location | score    | opponents score |
+--------+--------+--------------------+------------+--------------------+----------+----------+-----------------+
| 1      | 1      | Alabama            | 2          | Jacksonville State | H        | 1.098633 | 0.756562        |
+--------+--------+--------------------+------------+--------------------+----------+----------+-----------------+
| 1      | 2      | Jacksonville State | 1          | Alabama            | V        | 0.756562 | 1.098633        |
+--------+--------+--------------------+------------+--------------------+----------+----------+-----------------+
| 2      | 3      | UAB                | 4          | Alcorn State       | H        | 1.270638 | 0.682791        |
+--------+--------+--------------------+------------+--------------------+----------+----------+-----------------+
| 2      | 4      | Alcorn State       | 3          | UAB                | V        | 0.682791 | 1.270638        |
+--------+--------+--------------------+------------+--------------------+----------+----------+-----------------+

I am stuck on how to look up values that match criteria with different column names. Thanks!

df.merge(df, left_on=['GameID','TeamID'], right_on=['GameID', 'OpponentID'], how'left') and rename accordingly. — Quang Hoang
– Quang Hoang, Commented Apr 6, 2021 at 2:39

Anurag Dabas · Accepted Answer · 2021-04-06 02:48:50Z

1

You can make use of merge() method:

resultdf=df.merge(df[['GameID','OpponentID','score']], left_on=['GameID','TeamID'], right_on=['GameID','OpponentID'], how='left')

Now make use of drop() method:

result.drop(columns=['OpponentID_y'])

Finally make use of rename() method:

result=result.rename(columns={'OpponentID_x':'OpponentID','score_x':'score','score_y':'opponents score'})

Now if you print result you will get your desired output

answered Apr 6, 2021 at 2:48

Anurag Dabas

24.3k9 gold badges25 silver badges41 bronze badges

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

Comments

iamarchisha · Accepted Answer · 2021-04-06 03:47:32Z

0

Using Pandas merge solves your query very easily. You can achieve the desired result using the following code:

df_merge = df.merge(df, on=['GameID','TeamID'], how='outer', suffixes=(None, '_y'))
#print("Merged Dataset")
#display(df_merge)
df_merge.drop(['Team_y', 'OpponentID_y', 'Opponent_y', 'Location_y'], axis=1, inplace=True)
df_merge.rename( columns={'Score_y':'Opponent Score'}, inplace=True)
#print("Final Dataset")
#display(df_merge)

Oputput:

answered Apr 6, 2021 at 3:47

iamarchisha

3051 silver badge8 bronze badges

Collectives™ on Stack Overflow

How to get values from other rows based on multiple conditions in Pandas?

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