Odd behavior from read_csv with na_values set to non-string values

[rhstanton]

read_csv behaves oddly when na_values is set to non-string values. Sometimes
it correctly replaces the assigned number with NaN, and sometimes it doesn't. Here are some examples. Note in particular the different behavior of the last two statements:

Create file

df = DataFrame({'A' : [-999, 2, 3], 'B' : [1.2, -999, 4.5]})
df.to_csv('test2.csv', sep=' ', index=False)

print read_csv('test2.csv', sep= ' ', header=0, na_values=[-999])

---

A B
0 NaN 1.2
1 2 -999.0

2 3 4.5

print read_csv('test2.csv', sep= ' ', header=0, na_values=[-999.0])

---

A B
0 -999 1.2
1 2 NaN

2 3 4.5

print read_csv('test2.csv', sep= ' ', header=0, na_values=[-999.0,-999])

---

A B
0 -999 1.2
1 2 NaN

2 3 4.5

print read_csv('test2.csv', sep= ' ', header=0, na_values=[-999,-999.0])

---

A B
0 NaN 1.2
1 2 -999.0

2 3 4.5

[jreback]

@rhstanton give a try with master, this should be fixed (via #3615)

[rhstanton]

Thanks! The behavior in the last two cases is now consistent.

However, using 999 does not register as na when the input value is (float) 999.0, and vice versa. I’m not really sure what should happen here, or even if this should work at all instead of flagging an error (should this insist on string values?). If it is supposed to work with numeric instead of string values (which I’d be quite happy with), wouldn’t it then make sense for (say) 999 to work with both integer and float inputs equal to 999? After all, if I set i = 999, then both

i == 999

and

i == 999.0

return True.

From: jreback [mailto:notifications@github.com]
Sent: Wednesday, May 15, 2013 6:02 PM
To: pydata/pandas
Cc: Richard Stanton
Subject: Re: [pandas] Odd behavior from read_csv with na_values set to non-string values (#3611)

@rhstantonhttps://github.com/rhstanton give a try with master, this should be fixed (via #3615#3615)

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/3611#issuecomment-17975374.

[jreback]

999 and 999.0 are different strings which is how this matched

u can different values per column as well (pass a dict)

[rhstanton]

‘999’ and ‘999.0’ are different strings, but in these examples we’re passing numbers, not strings, and

999 == 999.0

returns True. If it’s going to work at all passing numbers, I think my expectation would be for the results to be based on a numerical comparison, not a string comparison.

Thanks for the dict tip. That could be useful.

From: jreback [mailto:notifications@github.com]
Sent: Wednesday, May 15, 2013 9:01 PM
To: pydata/pandas
Cc: Richard Stanton
Subject: Re: [pandas] Odd behavior from read_csv with na_values set to non-string values (#3611)

999 and 999.0 are different strings which is how this matched

u can different values per column as well (pass a dict)

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/3611#issuecomment-17980206.

[jreback]

keep in mind that this is an exact string match, these don't have dtypes at this point. that said, you have a valid point, so you are proposing adding the float versions of ints and vice versa if specified in na_vaues, pls open an enhancement issue for this

note you can do this:

read in with no na match for 999,999.0

df[df==999] == np.nan

will work for ints and floats (you can of course column restrict)

[fonnesbeck]

This is a problem for me as well, despite having a very recent build of Pandas. In my dataset, 88 is used as a missing value. Hence, I used the following:

dataset = data_file.parse("Sheet1", index_col=0, na_values=[88])

however, this value was not picked up as being NaN:

dataset.diarrhea.value_counts()

0     282
1     197
88      8
dtype: int64

I have tried using a float and an integer, but neither worked. Also, specifying the columns for replacement with a dict does not work.

Here is the dataset that I am using.

Also, the solution you suggest above does not work if even a single column is of a different type:

dataset[dataset==88] = np.nan

ValueError: Cannot do boolean setting on mixed-type frame

So, it appears that the only solution is to manually check which columns contain the value, and replace them in a loop.

[wesm]

If you pass na_values=['88'] does it work?

[fonnesbeck]

Unfortunately not.

[jreback]

This was a bug (introduced when I fixed this originally)

also, slight API change, na_values will now take a value like 88 (as a int), and match '88',88,88.0 (string/numeric dtypes) in whatever the resulting dtype of the column

@fonnesbeck it seems natural to read this dataset with index_col=None

@fonnesbeck pls try this out if you can (via the PR)

In [5]: pd.read_excel('test.xlsx',"Sheet1", index_col=0, na_values=['88'])
Out[5]: 
<class 'pandas.core.frame.DataFrame'>
Index: 490 entries, 1.0 to 1.0
Data columns (total 36 columns):
race         490  non-null values
ethn         484  non-null values
stcol        484  non-null values
rota         393  non-null values
blood        487  non-null values
saliva       484  non-null values
diarrhea     479  non-null values
qod          160  non-null values
abdpain      333  non-null values
vomit        483  non-null values
dos          478  non-null values
travel       16  non-null values
sickcon      231  non-null values
noc          486  non-null values
type         490  non-null values
abx          28  non-null values
dtp          337  non-null values
fever        472  non-null values
feverval     112  non-null values
pathogens    160  non-null values
futuse       488  non-null values
ecoli        486  non-null values
amc          490  non-null values
am           490  non-null values
cz           490  non-null values
fep          490  non-null values
caz          490  non-null values
cro          490  non-null values
cxm          490  non-null values
cip          490  non-null values
gm           490  non-null values
mem          490  non-null values
tzp          490  non-null values
stx          490  non-null values
case         490  non-null values
month        490  non-null values
dtypes: float64(36)

In [6]: pd.read_excel('test.xlsx',"Sheet1", index_col=0, na_values=['88.0']).head()
Out[6]: 
     race  ethn  stcol  rota  blood  saliva  diarrhea  qod  abdpain  vomit  dos  travel  sickcon  noc  type  abx  dtp  fever  feverval  pathogens  futuse  ecoli  amc  am  cz  fep  caz  cro  cxm  cip  gm  mem  tzp  stx  case  month
sex                                                                                                                                                                                                                                   
1       1     1      1     0      0       1         1  NaN        0      0    4     NaN      NaN    1     0  NaN    8      0       NaN        NaN       1      1    1   2   1    1    1    1    1    1   1    1    1    1     1     12
0       2     2      1     0      0       1         0  NaN      NaN      1    3     NaN        1    0     0  NaN  NaN      1       NaN        NaN       0      0    0   0   0    0    0    0    0    0   0    0    0    0     1     12
0       2     2      0   NaN      0       2         0  NaN        1      1    4     NaN      NaN    0     0  NaN  NaN      0       NaN        NaN       1      0    0   0   0    0    0    0    0    0   0    0    0    0     1      0
1       3     2      1     0      0       1         1    0      NaN      1    4     NaN        1    0     0  NaN  NaN      0       NaN        NaN       1      0    0   0   0    0    0    0    0    0   0    0    0    0     1     12
1       1     1      1     0      0       1         0  NaN        1      1    2     NaN        0    0     0  NaN    3      1       NaN        NaN       0      0    0   0   0    0    0    0    0    0   0    0    0    0     1     12

[fonnesbeck]

Sorry, the original file included a patient ID in column 0, which I had to remove before sharing. I will try the fix.

[fonnesbeck]

It is still failing for me, unfortunately:

In[11]: pd.read_excel("Ecoli1.xlsx", "Sheet1", na_values=[88]).travel.value_counts()

Out[11]:
88    471
0      14
1       2
dtype: int64