168

I'm using the pandas library to read in some CSV data. In my data, certain columns contain strings. The string "nan" is a possible value, as is an empty string. I managed to get pandas to read "nan" as a string, but I can't figure out how to get it not to read an empty value as NaN. Here's sample data and output

One,Two,Three
a,1,one
b,2,two
,3,three
d,4,nan
e,5,five
nan,6,
g,7,seven

>>> pandas.read_csv('test.csv', na_values={'One': [], "Three": []})
    One  Two  Three
0    a    1    one
1    b    2    two
2  NaN    3  three
3    d    4    nan
4    e    5   five
5  nan    6    NaN
6    g    7  seven

It correctly reads "nan" as the string "nan', but still reads the empty cells as NaN. I tried passing in str in the converters argument to read_csv (with converters={'One': str})), but it still reads the empty cells as NaN.

I realize I can fill the values after reading, with fillna, but is there really no way to tell pandas that an empty cell in a particular CSV column should be read as an empty string instead of NaN?

piRSquared
  • 285,575
  • 57
  • 475
  • 624
BrenBarn
  • 242,874
  • 37
  • 412
  • 384
  • 2
    Note the simpler, answer using the more recent option `keep_default_na` below. – nealmcb May 24 '20 at 17:08
  • `pd.read_csv( sourceObj, dtype='string' )` , no additional parameters are needed. Pandas will cast all rows string, and empty values will be set as empty string '' – dank8 Mar 03 '23 at 01:43

6 Answers6

210

I was still confused after reading the other answers and comments. But the answer now seems simpler, so here you go.

Since Pandas version 0.9 (from 2012), you can read your csv with empty cells interpreted as empty strings by simply setting keep_default_na=False:

pd.read_csv('test.csv', keep_default_na=False)

This issue is more clearly explained in

That was fixed on on Aug 19, 2012 for Pandas version 0.9 in

nealmcb
  • 12,479
  • 7
  • 66
  • 91
71

I added a ticket to add an option of some sort here:

https://github.com/pydata/pandas/issues/1450

In the meantime, result.fillna('') should do what you want

EDIT: in the development version (to be 0.8.0 final) if you specify an empty list of na_values, empty strings will stay empty strings in the result

Wes McKinney
  • 101,437
  • 32
  • 142
  • 108
  • 15
    [Documentation for `DataFrame.fillna`.](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.fillna.html) Try `result.fillna('', inplace=True)`. Otherwise it creates a copy of the dataframe. – Sergey Orshanskiy Sep 05 '14 at 22:48
  • 1
    sorry to resurrect such an old answer, but did this ever happen? As far as I can tell from [this GitHub PR](https://github.com/pydata/pandas/pull/1522) it was closed without ever being merged, and I'm not seeing the requested behavior in pandas version 0.14.x – drammock Sep 10 '15 at 20:52
  • 11
    [Documentation](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_csv.html) for read_csv now offers both `na_values` (list or dict indexed by columns) and `keep_default_na` (bool). The `keep_default_na` value indicates whether pandas' default NA values should be replaced or appended to. The OP's code doesn't work currently just because it's missing this flag. For this example, you could use `pandas.read_csv('test.csv',na_values=['nan'], keep_default_na=False)`. – Michael Delgado Sep 30 '15 at 20:17
  • @delgadom Thanks for leading me to `keep_default_na`. But note that he doesn't want 'nan' to be treated as a default either. I've added a more complete explanation as a new answer. – nealmcb May 07 '17 at 14:55
  • 3
    ran into this again. the fix is easy (the best answer is as below to put `keep_default_na=False`) but pandas default behaviour on this is IMO bad. if for some reason pandas read_csv infers a column is not numeric it should not automatically change empty strings to NaN. – pietroppeter Aug 27 '20 at 08:38
  • Those answers altering the result are very common but neglect the fact that you may not know the data structure upfront, eg a software processing custom CSV. I need pandas to be able to detect my column types as smartly as possible as I cannot fix types afterward. – Eric Burel Oct 20 '21 at 11:37
16

We have a simple argument in Pandas read_csv() for this:

Use:

df = pd.read_csv('test.csv', na_filter= False)
buhtz
  • 10,774
  • 18
  • 76
  • 149
Sundeep
  • 169
  • 1
  • 4
  • 2
    It looks like the OP _does_ want to use `na_values` to recognize "nan", but turning `na_filter` off entirely would defeat that. Thus my answer with `keep_default_na=False`. – nealmcb Oct 18 '19 at 14:54
  • 1
    Be careful, the `na_filter=False` can change your columns type to object – Ricardo Mutti Sep 07 '21 at 21:41
  • Per na_filter=False "changing column to type Object": seems to me that Pandas default is to set column as object if the other data elements of the column are strings as opposed to things that are clearly numbers (e.g., column 'One' and 'Three' in the question. – wiseass Feb 04 '23 at 13:59
11

What pandas defines by default as missing value while read_csv() can be found here.

import pandas
default_missing = pandas._libs.parsers.STR_NA_VALUES
print(default_missing)

The output

{'', '<NA>', 'nan', '1.#QNAN', 'NA', 'null', 'n/a', '-nan', '1.#IND', '#N/A N/A', 'N/A', 'NULL', 'NaN', '-1.#IND', '-1.#QNAN', '#NA', '#N/A', '-NaN'}

With that you can do an opt-out.

import pandas
default_missing = pandas._libs.parsers.STR_NA_VALUES
default_missing = default_missing.remove('')
default_missing = default_missing.remove('na')

with open('test.csv', 'r') as csv_file:
    pandas.read_csv(csv_file, na_values=default_missing)
buhtz
  • 10,774
  • 18
  • 76
  • 149
3

If you want to keep the empty strings for just one column, define str as the column converter (dtypes won't work):

pd.read_csv('test.csv', converters={'column_name': str})
ronkov
  • 1,263
  • 9
  • 14
1

pd.read_csv( sourceObj, dtype='string')

no additional parameters needed.

Each column type is python primitive string and empty values become empty string ''.

Version: Pandas v1.5

dank8
  • 361
  • 4
  • 20