Filtering pandas dataframe by matching rows in another dataframe

Question

I'm trying to get the rows from my data frame that match a set week year combination

df = pd.DataFrame({'Year':[2020, 2020, 2019, 2019, 2019, 2020],
              'Week':[1, 2, 4, 5, 2, 4],
              'other data':[5, 5, 5, 5, 5, 5]})

wks = pd.DataFrame({'Year':[2020,2020,2019], 'Week':[1, 2, 4]})

I want to be able to use wks to keep the rows in df that match those week/year combinations, so the output would be:

   Year  Week  other data
0  2020     1           5
1  2020     2           5
5  2020     4           5

I tried .loc as follows but this just checks each column individually (in this example returning all of df), instead of only returning where both Year and Week match.

df.loc[df['Week'].isin(wks['Week']) & (df['Year'].isin(wks['Year']))]

Any help greatly appreciated :)

Mayank Porwal · Accepted Answer · 2020-10-16 15:34:25Z

2

Use df.merge:

In [816]: wks.merge(df, on=['Year', 'Week'])
Out[816]: 
   Year  Week  other data
0  2020     1           5
1  2020     2           5
2  2019     4           5

answered Oct 16, 2020 at 15:34

Mayank Porwal

34.2k9 gold badges45 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

Filtering pandas dataframe by matching rows in another dataframe

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