Pandas filtering rows in one dataframe based on values in another data frame

Question

New to pandas and trying to figure out filtering results from a dataframe based on data in another dataframe.

df1                   df2
v1  v2                v1   v2
1    2                1    3
1    3                2    3
1    4                3    4
2    2                
2    4
3    2
3    3   
3    4

I have two dataframes: df1 and df2. I'm trying to create df3 which filters off results where if df1.v1 = df2.v1 and df1.v2 < df2.v2. This would yield df3 here:

I'm not sure how to go about doing this. I'm thinking of maybe converting df2 to a dictionary (df.v1 are all unique).

mozway · Accepted Answer · 2022-01-26 08:48:03Z

3

merge on "v1" and use a boolean mask to filter:

merged_df = df1.merge(df2, on='v1', suffixes=('_df1', ''))
merged_df.loc[merged_df['v2_df1'] < merged_df['v2'], ['v1', 'v2']]

Output:

edited Jan 26, 2022 at 8:48

mozway

267k13 gold badges56 silver badges106 bronze badges

answered Jan 26, 2022 at 8:39

user7864386

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

1 Comment

mozway Over a year ago

I simplified a bit your code ;)

mozway · Accepted Answer · 2022-01-26 08:40:11Z

2

You can merge on v1, then filter on the v2 values and cleanup the intermediate column:

(df1.merge(df2, on='v1', suffixes=('_', ''))
    .query('v2 <= v2_')
    .drop(columns='v2_')
)

output:

answered Jan 26, 2022 at 8:40

mozway

267k13 gold badges56 silver badges106 bronze badges

Comments

Corralien · Accepted Answer · 2022-01-26 08:53:24Z

2

You can merge your 2 dataframes on v1 and filter out:

df3 = df1.merge(df2, on='v1', suffixes=('_', '')) \
         .query("v2 <= v2_").drop(columns='v2_')
print(df3)

# Output
   v1  v2
1   1   3
2   1   3
4   2   3
7   3   4

Update

In case you don't have a strict equality between v1 columns, you can merge with how='cross' to create all combinations:

df3 = df1.merge(df2, how='cross', suffixes=('_', '')) \
         .query("(v1 == v1_) & (v2 <= v2_)").drop(columns=['v1_', 'v2_'])
print(df3)

# Output
    v1  v2
3    1   3
6    1   3
13   2   3
23   3   4

edited Jan 26, 2022 at 8:53

answered Jan 26, 2022 at 8:39

Corralien

121k8 gold badges43 silver badges68 bronze badges

3 Comments

mozway Over a year ago

:D great minds…

Corralien Over a year ago

Except I made a mistake :)

mozway Over a year ago

I had not seen it, I just read the global logic :p

Collectives™ on Stack Overflow

Pandas filtering rows in one dataframe based on values in another data frame

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related