Conditional merging of pandas dataframes

Question

My head is hurting after read post after post on this, and i cannot get the gist of how to solve this.

I have 2 pandas dataframes containing sports matches (simplified here):

A: Date, HomeTeam, AwayTeam
B: Date, HomeTeam, AwayTeam, HomeScore, AwayScore

A and B must merge into A.

Acontains more matches than B, and thus, Ais larger than BThe size of Amust be preserved (consider Aour "master").

Bmust fill in the HomeScore and AwayScore for each row where Date, HomeTeam and AwayTeam matches.

How can i merge these two properly?

I have considered using iterrows() or panda conditions pd[(a == b)], but I can not see how to solve it.

can you share your create statement for pandas dataframe?

Vikrant Singh Rana
– Vikrant Singh Rana

2019-07-30 08:29:26 +00:00
Commented Jul 30, 2019 at 8:29 — Vikrant Singh Rana
– Vikrant Singh Rana, Commented Jul 30, 2019 at 8:29

vlemaistre · Accepted Answer · 2019-07-30 09:19:47Z

2

You can use the merge() using the option how = 'left' to specify that you want to do a left join keeping the rows of A.

Here is what it could look like :

A = pd.DataFrame({'Date' : ['2019-06-12', '2019-08-06', '2019-08-06'],
                  'HomeTeam' : ['Team A', 'Team B', 'Team C'],
                  'AwayTeam' : ['Team D', 'Team E', 'Team F']})
B = pd.DataFrame({'Date' : ['2019-06-12', '2019-08-06'],
                  'HomeTeam' : ['Team A', 'Team B'],
                  'AwayTeam' : ['Team D', 'Team E'],
                  'HomeScore' : [54, 64], 'AwayScore' : [12, 16]})

A.merge(B, on = ['Date', 'HomeTeam', 'AwayTeam'], how = 'left')

Output :

         Date HomeTeam AwayTeam  HomeScore  AwayScore
0  2019-06-12   Team A   Team D       54.0       12.0
1  2019-08-06   Team B   Team E       64.0       16.0
2  2019-08-06   Team C   Team F        NaN        NaN

edited Jul 30, 2019 at 9:19

answered Jul 30, 2019 at 8:45

vlemaistre

3,34115 silver badges32 bronze badges

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

Comments

josoler · Accepted Answer · 2019-07-30 08:42:55Z

2

You can use pd.DataFrame.join

idxs = ['Date', 'HomeTeam', 'AwayTeam']
joined = A.set_index(idxs).join(B.set_index(idxs), how='left').reset_index()

This will produce a dataframe with as many rows as in A but with extra columns which will get the value from B or be NaN if the corresponding combination of values of idx doesn't appear there. You are performing a left join, using SQL terms.

edited Jul 30, 2019 at 8:42

answered Jul 30, 2019 at 8:31

josoler

1,44310 silver badges15 bronze badges

5 Comments

skywalkerdk Over a year ago

It seems to be working. Is there a way to specify that only the "HomeScore" and "AwayScore" should be joined? (The dataset contains more columns)

josoler Over a year ago

Use B[['HomeScore', 'AwayScore']] instead of plain B

skywalkerdk Over a year ago

When i do, i get this: AttributeError: 'Series' object has no attribute 'set_index'

josoler Over a year ago

I had a typo in the comment, now it's fixed. Are you sure you are indexing B with a list of column names? This shouldn't output a pd.Series but a new pd.DataFrame instead. Anyway, if the problem persists, consider posting a new question where you can fully explain the details

skywalkerdk Over a year ago

I solved it by doing: b_df = b_df[["Date", "HomeTeam", "AwayTeam", "HomeScore", "AwayScore"]]

Collectives™ on Stack Overflow

Conditional merging of pandas dataframes

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related