Replacing datetime conditionally - python

Question

I have a 'Posting Date' column in the dataframe in the format of '2017-03-01'. The type is <datetime64>[ns]. And I want to change the value if it is after '2017-03-31' to '2017-03-31', and all others remain unchanged.

When I type df['Posting Date']>'2017-03-31',it can correctly show me all the rows where the condition is met. So I guess the date filtering function works.

However, when I used numpy.where to write the condition as this:

df['Posting Date'] = np.where(df['Posting Date']>'2017-03-31','2017-03-31,'df['Posting Date'])

it incurrs an invalid type promotion error.

I also tried df.loc and the same error occers.

df.loc[df['Posting Date']>'2017-03-31','Posting Date']='2017-03-31'

ValueError: invalid literal for int() with base 10: '2017-03-31'

I'm wondering why the error occurs. How can I replace date correctly? Whatever method which works is fine.

I think you've got a cut-and-paste error. '2017-03-31,'df['Posting Date'] is a syntax error. (Presumably the comma should be outside the quotes.) If this is actually correct numpy syntax, my apologies. — John Gordon
– John Gordon, Commented Oct 15, 2017 at 2:19
I haven’t tried it but you can try. df[‘Posting Date’].clip(upper=pd.Timestamp(‘2017-03-31’)) — piRSquared
– piRSquared, Commented Oct 15, 2017 at 2:35

Bharath M Shetty · Accepted Answer · 2017-10-15 05:43:47Z

1

Its because of are trying to replace datetime with string in datetime dtype column so pass a datetime in np.where i.e

df['Posting Date'] = np.where(df['Posting Date']>'2017-03-31',pd.to_datetime(['2017-03-31']),df['Posting Date'])

Example output :

df = pd.DataFrame({'Posting Date': pd.to_datetime(['20-4-2017','20-4-2017','20-4-2017','20-3-2017','20-2-2017'])})
df['Posting Date'] = np.where(df['Posting Date']>'2017-03-31',pd.to_datetime(['2017-03-31']),df['Posting Date'])

Output :

Posting Date
0   2017-03-31
1   2017-03-31
2   2017-03-31
3   2017-03-20
4   2017-02-20

Better one posted by @pirSquared in comment using clip i.e

df['Posting Date'] = df['Posting Date'].clip(upper=pd.Timestamp('2017-03-31'))

answered Oct 15, 2017 at 5:43

Bharath M Shetty

30.6k6 gold badges65 silver badges111 bronze badges

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

2 Comments

Bharath M Shetty Over a year ago

Glad to help @LavenderPan

mrbTT Over a year ago

For someone that might land here having the same problem as I did. Was doing the where almost like what Dark said but all values that were not supposed to be replaced were being set as integer numbers instead of Datetime as they were before. What I have missed was the [ ] nesting the string date inside the pd.to_datetime(). The why I don't know.

Collectives™ on Stack Overflow

Replacing datetime conditionally - python

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related