Pandas DateTime Format not working - python

Question

I am trying to format some dates with datetime, but for some reason it is ignoring my format call. I want day/month/Year format which is what the CSV file has the format is, but when I try this.

df = pd.read_csv('test.csv', parse_dates=['Date'],
             date_parser=lambda x: pd.to_datetime(x, format='%d/%m/%Y'))

Result:

Why is it what I can only assume "defaulting" to %Y-%m-%d ???

The format specifies what your input data looks like. Once it’s converted to a datetime it will be displayed YYYY-MM-DD HH:mm:SS. You can change the display with strftime, but it’s probably best to leave it as a datetime to perform calculations — ALollz
– ALollz, Commented Oct 14, 2018 at 1:11
Can you specify the format of the Date column in test.csv which you are reading or please add a screenshot of the csv file. The issue is probably because of the month and day parameter interchanged. — nilansh bansal
– nilansh bansal, Commented Oct 14, 2018 at 1:37
Csv here: drive.google.com/open?id=1NyOUVQzNxUl-dwVPuwXiBAIHiGsMnmao — user3170725
– user3170725, Commented Oct 14, 2018 at 1:59

nilansh bansal · Accepted Answer · 2018-10-14 03:58:24Z

2

This should work.

import datetime as dt
import pandas as pd

df = pd.read_csv('test.csv')

formatted_dates =[]

for old_date in df['Date']:

    dt_obj = dt.datetime.strptime(old_date,'%d/%m/%Y')

    new_date = """{}/{}/{}""".format(dt_obj.day,dt_obj.month,dt_obj.year)

    formatted_dates.append(new_date)

df['Date'] = formatted_dates

Output:

18/1/2017
22/1/2017
31/1/2017
...

P.S. There's a bug with the parse_dates,date_parser in pd.read_csv which automatically changes the format to the YYYY-MM-DD.

edited Oct 14, 2018 at 3:58

answered Oct 14, 2018 at 1:18

nilansh bansal

1,4941 gold badge13 silver badges23 bronze badges

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

7 Comments

user3170725 Over a year ago

thanks for replying, I'm getting an error with that code <lambda> date_parser=lambda x: dt.datetime.strptime(x, format='%d/%m/%Y')) TypeError: strptime() takes no keyword arguments

nilansh bansal Over a year ago

@user3170725 can you share the csv through google drive, it would be helpful. Thanks!

user3170725 Over a year ago

No problem, here it is drive.google.com/open?id=1NyOUVQzNxUl-dwVPuwXiBAIHiGsMnmao

nilansh bansal Over a year ago

Strange! Tried lots of different methods still the result is same as yours. Will come back to you.

user3170725 Over a year ago

Thanks! Yes very annoying. I can convert it to a string and it works, but doesn't work well for plotting.

|

Collectives™ on Stack Overflow

Pandas DateTime Format not working - python

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related