Pandas Datetime column Filtering Issue when using variables

Question

I'm trying to filter a pandas column based on the date in one of my dataframe columns. So for example I have one column called 'Date', that has been converted to datetime using

df['Date'] = pd.to_datetime(df['Date'])

Placing it in the format 2019-06-01 for example. Now I can perform a filter on the column, so if I wanted to get dates only in June I could do

df[(df['Date'] >= '2019-06-01') & (df['Date'] <= '2019-06-30')]

And this works just fine, comparing the datetime to a string, which I assume pandas converts to a datetime automatically to perform the comparison.

However, this stops working as soon as I assign the comparison string to a variable, so if I do this

start = '2019-06-01'
end = '2019-06-30'
df[(df['Date'] >= start) & (df['Date'] <= end)]

I get an error: TypeError: Invalid comparison between dtype=datetime64[ns] and str

Any ideas on why this may be occurring?

The error suggests that of the two columns being compared, one has type string while the other has type datetime. So there's probably an entry in one of the columns that makes it cast to string. — MAK7
– MAK7, Commented Nov 26, 2019 at 15:22

Valdi_Bo · Accepted Answer · 2019-11-26 17:21:49Z

1

I use Pandas version 0.25 and Python version 3.7.0.

I checked your code:

start = '2019-06-01'
end = '2019-06-30'
df[(df['Date'] >= start) & (df['Date'] <= end)]

getting proper result (no error).

If you use some older version of either Python or Pandas, consider upgrading them.

I checked also other variants of code:

Conversion of "border" values to datetime:

d1 = pd.to_datetime('2019-06-01')
d2 = pd.to_datetime('2019-06-30')
df[df.Date.between(d1, d2)]

Usage of between with both arguments as strings:

df[df.Date.between('2019-06-01', '2019-06-30')]

getting also proper result. Check them on your installation as it is now and after upgrade (if you decide to do it).

answered Nov 26, 2019 at 17:21

Valdi_Bo

31.1k4 gold badges29 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Pandas Datetime column Filtering Issue when using variables

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