CODEWITHSUNDEEP
pythonpandas
Matt H
Matt H

Reputation: 427

Filtering one dataframe by two fields in another dataframes

I have a dataframe that contains columns with day_of_year, year, and the value 

               Year   Day      Value 
  Object A     2011   2         12
  Object B     2011   3         12
  Object C     2012   4          3
  Object D     2012   5         23
   ...

I have another table which has a list of target dates

  Year    Day       
  2011     2
  2012     4
   ...     ...

I'd like to filter the first dataframe with the conditions in the second. (in a given year, the day selected is different than other years. But there is only one day per year)

The returned dataset would look like: 

               Year   Day      Value 
  Object A     2011   2         12
  Object C     2012   4          3
   ...

I've tried using various lambdas without luck, and believe there must be an easier way to do this.

Upvotes: 0

Views: 55

Answers (2)

DailRowe
DailRowe

Reputation: 31

You can also do it with 'isin':

>>> df1
         Year  Day  Value
Object A  2011    2     12
Object B  2011    3     12
Object C  2012    4      3
Object D  2012    5     23

>>> df2
   Year  Day
0  2011    2
1  2012    4

>>> df1[ df1.Year.isin( df2.Year ) & df1.Day.isin( df2.Day ) ]

          Year  Day  Value
Object A  2011    2     12
Object C  2012    4      3

Upvotes: 0

DSM
DSM

Reputation: 353019

IIUC, you could use merge:

>>> df1
          Year  Day  Value
Object A  2011    2     12
Object B  2011    3     12
Object C  2012    4      3
Object D  2012    5     23
>>> df2
   Year  Day
0  2011    2
1  2012    4
>>> df1.reset_index().merge(df2).set_index("index")
          Year  Day  Value
index                     
Object A  2011    2     12
Object C  2012    4      3

where I've used reset/set_index to preserve the object index.

Upvotes: 2

Related Questions