Exporting Pandas DataFrame cells directly to excel/csv (python)

Question

I have a Pandas DataFrame that has sports records in it. All of them look like this: "1-2-0", "17-12-1", etc., for wins, losses and ties. When I export this the records come up in different date formats within Excel. Some will come up as "12-May", others as "9/5/2001", and others will come up as I want them to.

The DataFrame that I want to export is named 'x' and this is the command I'm currently using. I tried it without the date_format part and it gave the same response in Excel.

x.to_csv(r'C:\Users\B\Desktop\nba.csv', date_format = '%s')

Also tried using to_excel and I kept getting errors while trying to export. Any ideas? I was thinking I am doing the date_format part wrong, but don't know to transfer the string of text directly instead of it getting automatically switched to a string.

Thanks!

convert your date columns to a proper datetime, with pd.to_datetime() excel will try to infer datetimes, just click on format and change it to string/number. — Umar.H
– Umar.H, Commented Dec 27, 2019 at 22:01

merit_2 · Accepted Answer · 2019-12-27 22:21:03Z

1

I don't think its a python issue, but Excel auto detecting dates in your data. But, see below to convert your scores to strings.

Try this, import pandas as pd

df = pd.DataFrame({"lakers" : ["10-0-1"],"celtics" : ["11-1-3"]})

print(df.head())

here is the dataframe with made up data.

lakers celtics
0  10-0-1  11-1-3

Convert to dataframe to string

df = df.astype(str)

and save the csv:

df.to_csv('nba.csv')

Opening in LibreOffice gives me to columns with scores (made up)

You might have a use Excel issue going on here. Inline with my comment below, you can change any column in Excel to lots of different formats. In this case I believe Excel is auto detecting date formatting, incorrectly. Select your columns of data, right click, select format and change to anything else, like 'General'.

edited Dec 27, 2019 at 22:21

answered Dec 27, 2019 at 22:11

merit_2

4715 silver badges17 bronze badges

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

4 Comments

merit_2 Over a year ago

Also, in excel you can change the cell formatting to be many things. In this case Excel is auto detecting dates. Select your columns in Excel, format and pick general or just about anything besides date.

kalg1241 Over a year ago

Thanks! I downloaded LibreOffice and it formats correctly in there. However, it still gets automatically formatted to a date in Excel. I try to format the cells in there but it comes back even more messed up when I do it each time. Guess I'll stick to LibreOffice for now, thank you!

kalg1241 Over a year ago

Update: I opened it in LibreOffice then saved as 'Excel 2007-2019' and it seems to be formatted correctly now in here. Thanks again!

merit_2 Over a year ago

In Excel change the formatting on your columns to not be dates. In the data import wizard you can also tell Excel what data types it should import the data.

Collectives™ on Stack Overflow

Exporting Pandas DataFrame cells directly to excel/csv (python)

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related