pandas: How to convert a string to datetime?

Question

I have a CSV file in which I am trying to convert the date column to dd/mm/yyyy format using the below code but I am still struggling with unconverted data remains.

A few sample data-records from the CSV-file hotel,author,review,date,Overall,Value,Rooms,Location,Cleanliness,Check in/front desk,Service,Business,r9 in,everywhereman2 ,Old seattle getaway This was Old World Excellence at it's best.THIS is the place to stay at when visiting the historical area of Seattle. Your right on the water front near the ferry's and great sea food restrauntsand still with'in walking distance for great blues and jazz music. The staff for this hotel are excellentthey make you feel right at home. The breakfast was great.We did'nt have to travel far to have a good cup of JOE and a light meal to start our adventurous day off into one of the most beautifull city's in america. This hotel is in an area that makes it easy to get to any place you want to go and still find your way back I highly recomend this hotel for your next visit to seattle. ,Jan 6 2009 ,5,5,5,5,5,5,5,5,
in,RW53 ,Location! Location? view from room of nearby freeway ,Dec 26 2008 ,3,4,3,2,4,3,-1,-1,

Can you, please, help me to point out my mistake?

I am reading the file in RSS and then:

import time 
for end_date in rss['date']:
    end_date     = end_date.split(" ")
    end_date[-1] = end_date[-1][:4]
    end_date     = " ".join(end_date)
    conv         = time.strptime( end_date,"%b %d %Y" )
    time.strftime( "%d/%m/%Y", conv )
rss['date']

Thank you in advance.

EdChum · Accepted Answer · 2014-10-12 16:24:16Z

2

I just tried your data and the following worked for me without having to do post-processing:

In [17]:

df =pd.read_csv(r'c:\data\out.csv', parse_dates=['date'])
df.dtypes

Out[17]:
hotel                          object
author                         object
review                         object
date                   datetime64[ns]
Overall                         int64
Value                           int64
Rooms                           int64
Location                        int64
Cleanliness                     int64
Check in/front desk             int64
Service                         int64
Business                        int64
r9                             object
dtype: object

answered Oct 12, 2014 at 16:24

EdChum

397k204 gold badges836 silver badges583 bronze badges

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

5 Comments

miku Over a year ago

thank you.:) how to convert the same in the csv file permanently to dd/mm/yyyy.

EdChum Over a year ago

@miku sorry what do you mean?

b10n Over a year ago

If you mean how do you specify the format when you write to csv then you can specify the the format of dates that are written out by using the date_format argument of the to_csv method. In this case it would be date_format='%d/%m/%Y'

miku Over a year ago

Sorry for vague format of my question: I meant, having data in the CSV already, how can I change the format of the date column via program code.

EdChum Over a year ago

@miku if the data is strings and you want to convert to datetime after loading from csv then pd.to_datetime(df['date'], '%d/%m/%Y') should work, if it is already datetime and you want to change the display format then you convert the datetime to a string using datetime.strftime so df['date_str'] = df['date'].apply(lambda x.strftime('%Y/%m/%d')) should work.

Collectives™ on Stack Overflow

pandas: How to convert a string to datetime?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related