Errors while converting dataframe to csv

Question

For an automation, I need to convert an excel file to csv along with some modifications in some columns. In one of the column I need to change the time stamp to %Y-%m-%d %H:%M:%S'. Currently I am able to do everything and the change reflects on the dataframe except that when I convert it to csv, the timestamp format changes,

This is the code I have been trying now

df=pd.read_excel(file,headers=0,index=False)
print(df.head())
df['Reported On '] = pd.to_datetime(df['Reported On '])
df['Reported On ']= df['Reported On '].apply(lambda x: dt.datetime.strftime(x, '%Y-%m-%d %H:%M:%S'))
df.to_csv(csv_name,index=False)

The Reported On column in CSV gives the value :8/10/2017 10:50

While if I convert the dataframe to excel, it gives me the required format.

Is it the problem of csv ?

after apply convert that column into str then try to_csv it may work for you.... — Mohamed Thasin ah
– Mohamed Thasin ah, Commented Sep 14, 2018 at 4:43
@MohamedThasinah I doubt that this is the problem. I can not reproduce OPs effect and I have exported to csv while the date column still being of type object... BTW tested with pandas 0.23.4 on Python 3.7 — SpghttCd
– SpghttCd, Commented Sep 14, 2018 at 4:47
@SpghttCd - I didn't get your point. What's problem in this? — Mohamed Thasin ah
– Mohamed Thasin ah, Commented Sep 14, 2018 at 4:51
The result of OPs lambda expression is already a string type. And I tested it but cannot reproduce their error. So converting to string seems to me not the problem here. — SpghttCd
– SpghttCd, Commented Sep 14, 2018 at 4:56
@MohamedThasinah . I tried converting to str - df['Reported On '] = df['Reported On '].astype(str). It did not work for me. — Vincy Mathew
– Vincy Mathew, Commented Sep 14, 2018 at 4:58

SpghttCd · Accepted Answer · 2018-09-14 04:58:44Z

1

Even if I can not reproduce your error, please note that you can define the string representation of dates in a csv-file at the time of writing the file instead of altering your data:

df=pd.read_excel(file,headers=0,index=False)
print(df.head())
df['Reported On '] = pd.to_datetime(df['Reported On '])
df.to_csv(csv_name, date_format='%Y-%m-%d %H:%M:%S', index=False)

This is IMO better anyway, because it leaves your dataframe independent from some output format requirements in a computable state.

answered Sep 14, 2018 at 4:58

SpghttCd

10.9k2 gold badges23 silver badges28 bronze badges

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

7 Comments

Vincy Mathew Over a year ago

Tried this, But the same issue. Has it got something to do with csv?

SpghttCd Over a year ago

You tried this code but in your csv the format is different?? Are you sure you commented your apply/lambda-command away for this test...?

SpghttCd Over a year ago

May I ask you how you are checking the csv-file? Loading in Excel or with a text editor?

Vincy Mathew Over a year ago

Loading in excel

Vincy Mathew Over a year ago

Is it the problem?

|

Collectives™ on Stack Overflow

Errors while converting dataframe to csv

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