How do I create a new column based on matching values in two different dataframes?

Question

I have two dataframes:

df1 (a row for every event that happens in the game)

Date	Game	Event Type	Player	Time
02/28/10	USA vs Canada	Faceoff	Sidney Crosby	20:00
02/28/10	USA vs Canada	Pass	Drew Doughty	19:59
02/28/10	USA vs Canada	Pass	Scott Niedermayer	19:42
02/28/10	USA vs Canada	Shot	Sidney Crosby	18:57
02/28/10	USA vs Canada	Takeaway	Dany Heatley	18:49
02/28/10	USA vs Canada	Shot	Dany Heatley	18:02
02/28/10	USA vs Canada	Shot	Sidney Crosby	17:37

df2

Player
Sidney Crosby
Dany Heatley
Scott Niedermayer
Drew Doughty

How do I create a new column in df2 that matches the Player column in each dataframe and counts each row where the Event Type in df1 is "Shot"?

This is the output I would look for in this example:

Player	Shots
Sidney Crosby	2
Dany Heatley	1
Scott Niedermayer	0
Drew Doughty	0

I'm new to Python, so I apologize if there's an easy answer that I'm missing. Thank you!

What have you tried this far? A code example is always good. Ask others to help to find a bug in your code, not writing code for yoyu — ex4
– ex4, Commented Feb 10, 2021 at 16:00
@ex4 My apologies! I'm new to the community. I did try some various things that didn't get what I wanted. I'll be sure to post what code I've tried whenever my next question comes about! — Nayan Patel
– Nayan Patel, Commented Feb 11, 2021 at 3:39
No worries and welcome to Stack Overflow. There is system in SO where more experienced users review first posts of new users and help them follow guidelines. Your question came up when I was reviewing them. That why the comment. Great to have new active users here. — ex4
– ex4, Commented Feb 11, 2021 at 7:35

Quang Hoang · Accepted Answer · 2021-02-10 15:54:00Z

1

You can filter the df1 for shot, then do a value count:

shots = df1.loc[df1['Event Type']=='shot', 'Player'].value_counts()

df2['shots'] = df2['Player'].map(shots)
# or using reindex with `fill_value` option
# shots.reindex(df2['Player'], fill_value=0).values

Bonus: Use crosstab and merge to get all statistics at once:

df2.merge(pd.crosstab(df1['Player'], df1['Event Type']),
          on='Player', how='left')

answered Feb 10, 2021 at 15:54

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

How do I create a new column based on matching values in two different dataframes?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related