Selecting rows with dates before a given date using pandasql

Question

I have this data frame, where id is an int64, and date is datetime64[ns]:

         id                        date
0   1811302  2017-03-08 00:00:00.000000
1     16095  2013-03-12 00:00:00.000000
2     16095  2017-09-15 00:00:00.000000
..      ...                         ...
74  1111806  2017-09-15 00:00:00.000000
75   482254  2013-01-01 00:00:00.000000
76   482254  2015-12-16 00:00:00.000000

In reality, my data frame has more than a million number of rows.

Now, using pandasql, how do I extract rows before a given date, say before 2017-09-11?

I tried:

sqlcode = 'SELECT id, date from df where strftime("%Y-%m-%d", date) < {}'.format("2017-09-11")
ps.sqldf(sqlcode,locals())

Which returns nothing:

Empty DataFrame
Columns: [id, date]
Index: []

Any suggestions?

Martien Lubberink · Accepted Answer · 2022-01-20 23:22:01Z

1

Okay, it turns out the quotes around the curly brackets are important:

sqlcode = 'SELECT id, date from df where strftime("%Y-%m-%d", date) < "{}"'.format("2017-09-11")
ps.sqldf(sqlcode,locals())

answered Jan 20, 2022 at 23:22

Martien Lubberink

2,7671 gold badge23 silver badges35 bronze badges

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

Collectives™ on Stack Overflow

Selecting rows with dates before a given date using pandasql

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