pd.read_csv automatically casts strings into int/float

[rusiano]

Code Sample, a copy-pastable example if possible

import pandas as pd
import csv

d = pd.DataFrame([
    ['1', '2', 3], 
    ['3', 4, 5]])

d.to_csv('test_d', quoting=csv.QUOTE_NONNUMERIC, index=False)

dr = pd.read_csv('test_d')
dr.dtypes

0 int64
1 int64
2 int64
dtype: object

Problem description

I have string columns that I write as strings, but when I read them I find out that they have been converted to numeric even though the csv has all the necessary quoting characters. I do not understand why they are converted to numeric if they are strings, the fact that the characters reperesent numbers should make no difference.

The option of using the parameter dtype=... is not an option for me because I do not necessarily know the names of all the columns.

Expected Output

The expected output is finding string where there are strings and numbers where there are numbers.

Output of pd.show_versions()

[paste the output of pd.show_versions() here below this line]

INSTALLED VERSIONS

commit : None
python : 3.7.4.final.0
python-bits : 64
OS : Windows
OS-release : 10
machine : AMD64
processor : Intel64 Family 6 Model 142 Stepping 12, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.None

pandas : 0.25.1
numpy : 1.16.5
pytz : 2019.3
dateutil : 2.8.0
pip : 19.2.3
setuptools : 41.4.0
Cython : 0.29.13
pytest : 5.2.1
hypothesis : None
sphinx : 2.2.0
blosc : None
feather : None
xlsxwriter : 1.2.1
lxml.etree : 4.4.1
html5lib : 1.0.1
pymysql : None
psycopg2 : None
jinja2 : 2.10.3
IPython : 7.8.0
pandas_datareader: None
bs4 : 4.8.0
bottleneck : 1.2.1
fastparquet : None
gcsfs : None
lxml.etree : 4.4.1
matplotlib : 3.1.1
numexpr : 2.7.0
odfpy : None
openpyxl : 3.0.0
pandas_gbq : None
pyarrow : None
pytables : None
s3fs : 0.2.2
scipy : 1.3.1
sqlalchemy : 1.3.9
tables : 3.5.2
xarray : None
xlrd : 1.2.0
xlwt : 1.3.0
xlsxwriter : 1.2.1

[asishm]

related xref: #35713

[TomMagerman]

Is there any news on this issue?

I also regard this as not expected behavior: if a dataframe column is defined as string or object, and quoting is set to csv.QUOTE_NONNUMERIC, then to_csv() should quote all values in that column, and not convert them to integer of float.

For me this looks clearly as a bug, although I understand that some people might look at it from a different perspective. I expect the quoting behavior to be defined at the column level, hence all values of a given column get the same quoting. One could also expect the quoting behavior to be defined at the individual value level: every individual value is evaluated and can be quoted differently according to the value (a string value within a string/object column will be quoted, an integer value within a string/object column will not be quoted).

I clearly expect quoting behavior at the column level: if a dataframe is a string/object column, and quoting is set to csv.QUOTE_NONNUMERIC, I expect all values of that column to be quoted. CSV is a complex format, and a lot of things can go wrong. To check if a CSV is correctly loaded into a dataframe, I export the dataframe back to CSV and check if both files are identical. The current behavior of to_csv() does not allow me to do so because it makes it impossible to regenerate the original CSV file.

But I understand that the other behavior can also be desired in some circumstances.

So I think Pandas should support both behavoirs, meaning that additional quoting options should be added to differentiate between both behaviors.

[m-movahhedinia]

I have the same issue. I save the dataframe as csv using QUOTE_NONNUMERIC and when I read it back it gets converted to int. I confirmed that it gets saved correctly. The problem is with pd.read_csv() I think anything quoted should be a string.

[heetbeet]

Pandas version 1.3.5

I have to process some .csv files that inconsistently trigger this bug. The culprit seems to be an id column that contains a mixture of alphanumeric strings (e.g. "P1234") and numeric strings (e.g. "12345"). Pandas would sometimes read everything as strings (the expected behaviour), but sometimes it would read some of the numeric strings as ints (not all of them, just some). This is not only unexpected but also completely inconsistent. This also seems to be dependent on either the size of the data frame or the number of columns.

I have compiled two example csvs to illustrate the inconsistency:
- string-parsing-expected.csv
- string-parsing-incoherent.csv

Code:

import pandas as pd
import numpy as np

def is_numeric_string(x):
    if isinstance(x, str):
        try:
            int(x)
            return True
        except:
            return False
    return False

for fname in ["string-parsing-expected.csv", "string-parsing-incoherent.csv"]:
    df = pd.read_csv(fname)
    ints = [i for i in df['example'] if isinstance(i, int)]
    numeric_string = [i for i in df['example'] if is_numeric_string(i)]
    print(f"****** {fname} ******")
    print("  has ints:", bool(ints))
    if ints:
        print("    example: ", repr(ints[0]))

    print("  has numeric strings:", bool(numeric_string))
    if numeric_string:
        print("    example: ", repr(numeric_string[0]))

    print()

Output:

****** string-parsing-expected.csv ******
  has ints: False
  has numeric strings: True
    example:  '12345'

****** string-parsing-incoherent.csv ******
  has ints: True
    example:  12345
  has numeric strings: True
    example:  '12345'

[DeepSkyWonder]

Hi All, Is there any update on this? I have quoted strings, that are not numeric (serial numbers) being converted to stings (i.e. '001005' --> 1005.0) in pandas.read_csv. Is there any workaround, until a fix is available? Cameron :-)

df = pd.read_csv('serial_numbers.csv', sep=',', quotechar="'")

version: 1.4.4

[thomas-vincent]

You could parse the first row and then for every quoted field, you cast the colmun to string using astype. Le dim. 2 avr. 2023 18 h 06, DeepSkyWonder ***@***.***> a écrit :

…

Hi All, Is there any update on this? I have quoted strings, that are not numeric (serial numbers) being converted to stings (i.e. '001005' --> 1005.0) in pandas.read_csv. Is there any workaround, until a fix is available? Cameron :-) df = pd.read_csv('serial_numbers.csv', sep=',', quotechar="'") — Reply to this email directly, view it on GitHub <#31821 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAE2GDXYBPN7FQNGTJ67NX3W7HZ7TANCNFSM4KSBGELA> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[DeepSkyWonder]

Thanks Thomas, that's an excellent idea.

[blem14]

@thomas-vincent That doesn't solve the issue in some cases though. You may introduce unwanted data changes when casting. Below's some notes in case it'll be useful to someone.

Consider you just want to reformat the CSV and you use following code:

import pandas as pd
from csv import QUOTE_NONNUMERIC

from io import BytesIO
csv = BytesIO(b'a,b,c\n"1",,3\n"4","5","6"')
df = pd.read_csv(csv)

# casts as suggested
df["a"] = df["a"].astype(str)
df["b"] = df["b"].astype(str)
df["c"] = df["c"].astype(str)

x = BytesIO()
df.to_csv(x, quoting=QUOTE_NONNUMERIC, index=False)
x.seek(0)
x.read()

it gives you

b'"a","b","c"\n"1","nan","3"\n"4","5.0","6"\n'

The empty value from B column are now "nan" which is entirely different and incorrect data.
On top of that "5" becomes "5.0", because the column data type is implied based on the first value, which is float for empty ones. I've checked it with this code

for v in df[column_name]:
    print(type(v))

Better solution may be to not use casts, but set dtype on read. A single line read could be performed to get the header and then build a dict for dtype. However, this has some downsides too, as adding dtype={"a": str,"b": str,"c": str} to read_csv above code, you will get you this result

b'"a","b","c"\n"1","","3"\n"4","5","6"\n'

That still makes "" out of empty columns which still is incorect data.
Also, if I do the type check from above, then it turns out that all of the values are str, apart from the empty one - it is still float, so...

---

I actually ended up here because of similar problem when writing to parquet files.
I had "123" along with "A123" in one column and even with them being qouted the to_parquet failed. I figured out the problem is the size of the data - I can't tell you exact size treshold, but it had problems with 7MB file and ~2.5k rows in it. It didn't care for the dtype, casted integer-looking values to int and logged DtypeWarning. I've switched to low_memory=False, but looking at the docs - I shouldn't have to. It clearly says in low_memory doc: To ensure no mixed types either set False, OR specify the type with the dtype parameter. In the end it doesn't seem to work like that...
I'll check if there is an issue for that, will create one if not.

[blamatt]

I am experiencing a similar issue but instead of a conversion of a strings to floating point, my floating point is getting converted to a string. Mine starts with only 16 rows and 199 columns. My CSV has a mix of data types.