pandas compare two dataframes with criteria

Question

I have two dataframes. df1 and df2. I would like to get whatever values are common from df1 and df2 and the dt value of df2 must be greater than df1's dt value In this case, the expected value is fee

df1 = pd.DataFrame([['2015-01-01 06:00','foo'], ['2015-01-01 07:00','fee'], ['2015-01-01 08:00','fum']], columns=['dt', 'value']) df1.dt=pd.to_datetime(df1.dt) df2=pd.DataFrame([['2015-01-01 06:10','zoo'], ['2015-01-01 07:10','fee'],['2015-01-01 08:10','feu'], ['2015-01-01 09:10','boo']], columns=['dt', 'value']) df2.dt=pd.to_datetime(df2.dt)

If Ed answered your question, it's good to accept it (click the checkmark) so people know the question is answered. (and also any other answers to questions you have asked). It's also 2 points to you, 15 to Ed, and a good way to improve the odds that folks will answer your future questions. ;-) — JohnE
– JohnE, Commented Aug 2, 2015 at 15:52

EdChum · Accepted Answer · 2015-05-22 20:55:01Z

One way would be to merge on 'value' column so this will produce only matching rows, you can then filter the merged df using the 'dt_x', 'dt_y' columns:

In [15]: merged = df2.merge(df1, on='value') merged[merged['dt_x'] > merged['dt_y']] Out[15]: dt_x value dt_y 0 2015-01-01 07:10:00 fee 2015-01-01 07:00:00

You can't do something like the following because the lengths don't match:

df2[ (df2['value'].isin(df1['value'])) & (df2['dt'] > df1['dt']) ]

raises:

ValueError: Series lengths must match to compare

Collectives™ on Stack Overflow

pandas compare two dataframes with criteria

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related