CODEWITHSUNDEEP
pythonpandascsvnumpydata-science
Prabhat Sharma
Prabhat Sharma

Reputation: 76

Pandas to_csv now not writing values correctly

I'm using to csv to save a datframe which looks like this:

    PredictionIdx   CustomerInterest
0   fe789a06f3  0.654059
1   6238f6b829  0.654269
2   b0e1883ce5  0.666289
3   85e07cdd04  0.664172

in which I've a value '0e15826235' in first column.I'm writing this dataframe to csv using pandas to_csv() . But when I open this csv in google excel or libreoffice it shows 0E in excel and 0 in libreoffice. It is giving me problem during submission in kaggle. But one point to note here is that when I'm reading the same csv using pandas read_csv it shows the above value correctly in dataframe.

Upvotes: 0

Views: 3595

Answers (2)

Savage Henry
Savage Henry

Reputation: 2069

As noted in the first comment, the error is resulting from your choice of editor. Many editors will use some version of scientific notation that reads an e (in specific places like the second character) as an indicator of an exponent. Excel, for instance, will read it as a "base X raised to the power Y" where X are the numbers before the e and Y are the numbers after the e. This is a brief description of Excel's scientific notation.

This does not happen in the other cell entries because there appear to be other string-like characters. Excel, Libre, and possibly Google attempt to interpret what the entry is, rather than taking it literally.

In your question you write '0e15826235' with single quotes, indicating that it might be a string, but this might be something to make sure of when writing out the values to a file -- Excel and the rest might not know this is meant to be a string literal.

In general, check for the format of the value and consider what your eventual editor might "think" it is when it opens. For Excel specifically, a single quote character at the start of the string will force Excel to read it as a string. See this answer.

Upvotes: 1

user2564741
user2564741

Reputation: 76

For me code below works correctly with google spreadsheets:

import pandas as pd
df = pd.DataFrame({'PredictionIdx': ['fe789a06f3',
                                     '6238f6b829',
                                     'b0e1883ce5',
                                     '85e07cdd04'],
                   'CustomerInterest': [0.654059,
                                        0.654269,
                                        0.666289,
                                        0.664172]})

df.to_csv('./test.csv', index = None)

Also csv is very simple text format, it doesn't hold any information about data types. So you could use df.to_excel() as Nihal suggested, or adjust column type settings in your favourite spreadsheets viewer.

Upvotes: 0

Related Questions