how to read data from csv as date format in python pandas

Question

I have data in following format

Month        Country BA Total
11/1/2018     CN     3   10

after reading Month comes as object though I want in date format, I tried to convert it in date time format using

hs = pd.read_csv('history.csv',parse_dates=['Month'])  #this is not solving the issue either

hs['Month'] = pd.to_datetime(hs['Month']) #this throws error

Please suggest me how to read it as date or convert it to date format

could you please add the error output (traceback)? also, "11/1/2018" means mm/dd/yyyy? or is it day first? — FObersteiner
– FObersteiner, Commented Aug 23, 2021 at 14:09
error: OutOfBoundsDatetime: Out of bounds nanosecond timestamp: 1-04-19 00:00:00 it is mm/dd/yyyy format — Ashish
– Ashish, Commented Aug 23, 2021 at 14:12
hm "1-04-19" looks like a different format; does your "Month" column contain date/time strings with mixed formats? In principle, pd.to_datetime has no issues with parsing mm/dd/yyyy to datetime correctly. You can set errors='coerce' to skip the errors, but that will leave "NaT" for those elements in the datetime column. — FObersteiner
– FObersteiner, Commented Aug 23, 2021 at 14:18
yes I noticed that last few rows had different format, I have updated it and ran again — Ashish
– Ashish, Commented Aug 23, 2021 at 14:32

Mahdi F. · Accepted Answer · 2021-08-23 14:19:13Z

2

try one of this two line, maybe don't get error, your error maybe base on day is first or month is first:

df['sale_date'] = pd.to_datetime(df['sale_date'], format='%m/%d/%y')
# or
df['sale_date'] = pd.to_datetime(df['sale_date'], dayfirst=False)

OR

df['sale_date'] = pd.to_datetime(df['sale_date'], format='%d/%m/%y')
# or
df['sale_date'] = pd.to_datetime(df['sale_date'], dayfirst=True)

answered Aug 23, 2021 at 14:19

Mahdi F.

24.1k5 gold badges25 silver badges32 bronze badges

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

Comments

Akas Antony · Accepted Answer · 2021-08-23 14:19:07Z

1

Try this

from datetime import datetime
dateparse = lambda x: datetime.strptime(x, '%m/%d/%Y')

df = pd.read_csv('history.csv', parse_dates=['Month'], date_parser=dateparse)

answered Aug 23, 2021 at 14:19

Akas Antony

7491 gold badge10 silver badges19 bronze badges

1 Comment

Ashish Over a year ago

from datetime import datetime dateparse = lambda x: datetime.strptime(x, '%m/%d/%Y') #hs = pd.read_csv(r'C:\Users\kumaashi\Documents\Treasury\prophet\history.csv',parse_dates=['Month']) hs = pd.read_csv('history.csv',parse_dates=['Month'], date_parser=dateparse)

Mark Domino · Accepted Answer · 2021-08-23 14:20:36Z

1

Since pandas represents timestamps in nanosecond resolution, the timespan that can be represented using a 64-bit integer is limited to approximately 584 years Workaround:

This will force the dates which are outside the bounds to NaT

pd.to_datetime(date_col_to_force, errors = 'coerce')

answered Aug 23, 2021 at 14:20

Mark Domino

257 bronze badges

Comments

Pierre D · Accepted Answer · 2021-08-23 14:20:00Z

0

If the dates in the file are less than pristine, it is often a good idea to load the file without parse_dates, then use pd.to_datetime() which has better control, including format and how to deal with errors ('raise', 'coerce', or 'ignore').

answered Aug 23, 2021 at 14:20

Pierre D

26.6k8 gold badges71 silver badges108 bronze badges

Comments

Ashish · Accepted Answer · 2021-08-23 14:32:11Z

-1

thanks a lot to all of the suggestions, I tried last one and it worked, as I am running short of time, could not try other suggestions, will definitely try once I get time.

answered Aug 23, 2021 at 14:32

Ashish

1253 silver badges18 bronze badges

Collectives™ on Stack Overflow

how to read data from csv as date format in python pandas

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related