CODEWITHSUNDEEP
pythondatetime
Osca
Osca

Reputation: 1694

Convert string to datetime - python dataframe

Hi I want to convert a dataframe column (string) into date.I found it converted some of the dates correctly and some of them are wrong.

df
  Id       Date     Rev
1605380 1/12/2018   3000.0
2237851 27/11/2018  3000.0
1797180 11/2/2018   2000.0
1156126 9/1/2018    2000.0
1205792 8/4/2017    2000.0

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

The output I got

 Id       Date      Rev
1605380 2018-01-12  3000.0
2237851 2018-11-27  3000.0
1797180 2018-11-02  2000.0
1156126 2018-09-01  2000.0
1205792 2017-08-04  2000.0

It seems that if the "day" is not two digit, datetime converted it into "month" instead of "day". Therefore, 1/12/2018 should be 2018-12-01, not 2018-01-12. How can I fix this issue ?

I actually only need year and month for the output.

Ideal output

  Id       Date     Rev
1605380 2018-12    3000.0
2237851 2018-11    3000.0
1797180 2018-02    2000.0
1156126 2018-01    2000.0
1205792 2017-04    2000.0

Upvotes: 3

Views: 6094

Answers (1)

busybear
busybear

Reputation: 10580

You just need to specify the format parameter to '%d/%m/%Y' to explicitly tell the date format as commented. Or set dayfirst to True. A datetime object actually has information for year, month, day, and time, so to get just month and year displayed, you'll have to convert back to string:

df['Date'] = pd.to_datetime(df['Date'], dayfirst=True).dt.strftime('%Y-%m')

Upvotes: 3

Related Questions