pandas dataframe merge expression using a less-than operator?

Question

I was trying to merge two dataframes using a less-than operator. But I ended up using pandasql.

Is it possible to do the same query below using pandas functions? (Records may be duplicated, but that is fine as I'm looking for something similar to cumulative total later)

sql = '''select A.Name,A.Code,B.edate from df1 A
        inner join df2 B on A.Name = B.Name
        and A.Code=B.Code
        where A.edate < B.edate '''

df4 = sqldf(sql)

The suggested answer seems similar but couldn't get the result expected. Also the answer below looks very crisp.

Does this answer your question? How to do a conditional join in python Pandas? — smci
– smci, Commented Jun 22, 2020 at 6:15

jezrael · Accepted Answer · 2020-06-22 06:15:59Z

2

Use:

df = df1.merge(df2, on=['Name','Code']).query('edate_x < edate_y')[['Name','Code','edate_y']]

answered Jun 22, 2020 at 6:15

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

7 Comments

smci Over a year ago

jezrael, could you add this method to How to do/workaround a conditional join in python Pandas?

jezrael Over a year ago

@smci - I think it is something else, so not dupe, only similar.

Ch3steR Over a year ago

df.query is very useful but under-used. +1

smci Over a year ago

rael, perhaps, but that other question could sorely use your mention of df.query(), so I recommend you add it there too.

jezrael Over a year ago

@sjd - Unfortunately pandas working not so nice like sql libs :(

|

Collectives™ on Stack Overflow

pandas dataframe merge expression using a less-than operator?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related