pandas unpickling string nan values

[asayeed]

Hi. I'm using Pandas 0.11.0. I have a table with some string None values which I both pickled and appended to an HDFStore (compressed with blosc), and then retrieved with pandas.load and HDFStore.get. In the unpickled table, string values are converted to the string "nan", which does not get detected by isnull. On the other hand, the HDFStore correctly retrieves NaN values.

I'm using natural language data and there is a slim but non-zero chance that "nan" (as a string) is a value representing actual data, and even worse, I didn't know that the unpickled table even had None values until I tested out the HDFStore.

This is my first github bug report, and I don't know if this is a bug or intended behaviour, but it seems bug-like to me.

[jreback]

pls provide an example. what do you mean by 'pickled'? These are stored in HDF5 formt.

None is technically a nan value in object dtypes (meaning strings), but causes really weird things to happen if you tried to store it. You should use np.nan as the value. This might be a bug, OTOH, it maybe what you actually want (meaning you actually do want to store the None), so not sure what do do.

[jreback]

also, here's the docstring for append, you can supply nan_rep to pick any string that you want to represent a Nan, so it won't conflict. I believe this is available in 0.11. This is stored so it will use it on deserialization.

In [1]: pd.HDFStore.append?
Type:       instancemethod
String Form:<unbound method HDFStore.append>
File:       /mnt/home/jreback/pandas/pandas/io/pytables.py
Definition: pd.HDFStore.append(self, key, value, format=None, append=True, columns=None, dropna=None, **kwargs)
Docstring:
Append to Table in file. Node must already exist and be Table
format.

Parameters
----------
key : object
value : {Series, DataFrame, Panel, Panel4D}
format: 'table' is the default
    table(t) : table format
               Write as a PyTables Table structure which may perform worse but
               allow more flexible operations like searching / selecting subsets
               of the data
append   : boolean, default True, append the input data to the existing
data_columns : list of columns to create as data columns, or True to use all columns
min_itemsize : dict of columns that specify minimum string sizes
nan_rep      : string to use as string nan represenation
chunksize    : size to chunk the writing
expectedrows : expected TOTAL row size of this table
encoding     : default None, provide an encoding for strings
dropna       : boolean, default True, do not write an ALL nan row to the store
               settable by the option 'io.hdf.dropna_table'
Notes
-----
Does *not* check if data being appended overlaps with existing
data in the table, so be careful

[asayeed]

Alright, I was hoping this was a stupid question and y'all would automatically have an answer. :)

I have a script that generates a pandas table from string data. I saved it in two formats, one by pandas.save, and one in an HDFStore. I wasn't under the impression that pandas.save used HDF5 format. The docs seem to imply that it uses a standard Python pickle.

Here's what happens when I load the two tables.

In [1]: import pandas as pd

In [2]: A = pd.load("sample.df")

In [3]: A
Out[3]: 
<class 'pandas.core.frame.DataFrame'>
Int64Index: 663106 entries, 0 to 663105
Data columns (total 4 columns):
0      663106  non-null values
1      663106  non-null values
2      663106  non-null values
val    663106  non-null values
dtypes: float64(1), object(3)

In [4]: B = pd.HDFStore("sample.h5", "r")

In [5]: C = B.get("table")

In [6]: C
Out[6]: 
<class 'pandas.core.frame.DataFrame'>
Int64Index: 663106 entries, 0 to 663105
Data columns (total 4 columns):
xxxx0    663101  non-null values
zzzz     663106  non-null values
yyyy1    663101  non-null values
val      663106  non-null values
dtypes: float64(1), object(3)

The columns get renamed before the saving as an HDFStore, but they correspond. And in table C, the one that was retrieved from the HDFStore, you'll see that columns xxxx0 and yyyy1 have smaller non-null counts than the corresponding 0 and 2, which come from the same data.

Now I drill deeper.

In [26]: C[C['xxxx0'].isnull()]
Out[26]: 
       xxxx0     zzzz     yyyy1        val
571093   NaN  nsubj-1  reported   5.061457
586310   NaN  nsubj-1      said  39.931872
622176   NaN  nsubj-1   telling   8.065867
632299   NaN   dobj-1      told   4.634847
653982   NaN  nsubj-1      were   5.200787

This (from the HDFStore) is correct---it has NaN values where xxxx0 is actually empty. So what's going on in table A? (The one loaded by pandas.load.)

In [27]: A.loc[571093]
Out[27]: 
0           nan
1       nsubj-1
2      reported
val    5.061457
Name: 571093, dtype: object

In [28]: A.loc[571093][0]
Out[28]: 'nan'

This string 'nan' is clearly not what I want, because actually I didn't expect there to be ANY null values whatsoever in the table. (It means that there is a problem in the original source text, which I am investigating.) Consequently, I didn't actually replace None values with np.nan, since I didn't really expect to have to. It was only my exploration of HDFStore which alerted me to the discrepancy and the presence of empty values that should not exist.

This seems like a bug in pandas.save to me.

[jreback]

ok. you are mixing too many things here. so this has to do with pickling a frame. pls show how you generate it in the first place. (frame A). It will pickle virtually identically to how it retrieves. So how are you creating A?

[asayeed]

This has to do with the fact that pickling a frame has different behaviour from HDFStoring a frame. Here's the code in my original script that does the saving, after a very long processing pipeline over natural language text files:

print >>sys.stderr, "saving"
os.chdir(origdir)
#store = pd.HDFStore(sys.argv[2], "w", complevel=9, complib="blosc")                                                                                                                                            
#fulldf.columns = ["xxxx0", "zzzz", "yyyy1", "val"]                                                                                                                                                            
#store.append("table", fulldf, data_columns=["xxxx0","zzzz","yyyy1"])                                                                                                                                  
#store.close()                 
pd.save(fulldf, sys.argv[2])

I generated table A and table C by running the same script, and just commenting and uncommenting the above lines to get pickled and HDF5 versions of exactly the same frame.

The text data being used to generate the table is partially processed through an XML parser (in one step of the pipeline before saving) that seems to be generating None values for a few items where it in theory shouldn't. But the behaviour of pd.save turns these None values into the string 'nan' rather than into a proper NaN value. HDFStore.append behaves correctly, so that when I retrieve the store and inspect it, I know that I have NaN values in the table.

[jreback]

@asayeed

save is reproducing the frame exactly as you have it, with some 'nan' strings in your data. HDFStore uses nan as the string rep to indicate strings in the store, and upon deserialization turns that into np.nan.

Your original frame has the string nan in a string column. This is valid. Not sure where None comes in, it doesn't appear anywhere.

[asayeed]

OK, you're right, it does appear that the string NAN (with various capitalizations) appears in the original data.

Then as you say, HDFStore is turning it into np.nan on retrieval, which is also somewhat disturbing and unexpected if you're dealing with text data---I automatically assumed that if it was retrieving a NaN, it meant that there was an empty value there.

Which means that my original complaint was exactly backwards. HDFStore incorrectly assumes that the string "nan" is not literally the string "nan", which causes a nasty surprise.

I don't know if this result is intentional or it is merely that I have an idiosyncratic use case that makes this behaviour seem like a bug. I'd never have guessed that I was losing data this way, especially if I attempted to retrieve the string 'nan' which a script may well do automatically and silently retrieve nothing.

[jreback]

this was my second comment, you can pass nan_rep to be something else, e.g. nan_rep="_nan_" or whatever; this is up to the user to determine this, it is just a convenient default

[asayeed]

OK thanks. :) Just a little taken aback by falling exactly into the corner case where the convenient default is problematic. I might never even have noticed...

[jreback]

np....I think its worth a warning/example in the docs.....which I will add

thanks for the issue!

[jreback]

looks at this after 5pm est today....will have a new section that shows an example.

http://pandas.pydata.org/pandas-docs/dev/io.html#string-columns