Pandas - replace all NaN values in DataFrame with empty python dict objects

Question

I have a pandas DataFrame where each cell contains a python dict.

>>> data = {'Q':{'X':{2:2010}, 'Y':{2:2011, 3:2009}},'R':{'X':{1:2013}}}
>>> frame = DataFrame(data)
>>> frame
                    Q          R
X           {2: 2010}  {1: 2013}
Y  {2: 2011, 3: 2009}        NaN

I'd like to replace the NaN with an empty dict, to get this result:

                    Q          R
X           {2: 2010}  {1: 2013}
Y  {2: 2011, 3: 2009}        {}

However, because the fillna function interprets empty dict not as a scalar value but as a mapping of column --> value, it does NOTHING if I simply do this (i.e. it doesn't work):

>>> frame.fillna(inplace=True, value={})
                    Q          R
X           {2: 2010}  {1: 2013}
Y  {2: 2011, 3: 2009}        NaN

Is there any way to use fillna to accomplish what I want? Do I have to iterate through the entire DataFrame or construct a silly dict with all my columns mapped to empty dict?

Answer 1

I was able to use DataFrame.applymap in this way:

>>> from pandas import isnull
>>> frame=frame.applymap(lambda x: {} if isnull(x) else x)
>>> frame
                    Q          R
X           {2: 2010}  {1: 2013}
Y  {2: 2011, 3: 2009}         {}

This solution avoids the pitfalls in both EdChum's solution (where all NaN cells wind up pointing at same underlying dict object in memory, preventing them from being updated independently from one another) and Shashank's (where a potentially large data structure needs to be constructed with nested dicts, just to specify a single empty dict value).

Answer 2

The problem is that when a dict is passed to fillna, it tries to fill the values based on the columns in the frame. So the first solution I tried was -

frame.fillna({column: {} for column in frame.columns})

But if a dictionary is provided at the second level like this, it tries to match the keys against the index, so the solution that worked was -

frame.fillna({column: {ind: {} for ind in frame.index} for column in frame.columns})

Which gives -

                    Q          R
X           {2: 2010}  {1: 2013}
Y  {2: 2011, 3: 2009}         {}

EdChum's answer is probably better for your needs, but this can be used when you don't want to make changes in place.

EDIT: The solution above works well for smaller frames, but can be a problem for larger frames. Using replace can solve that.

frame.replace(np.nan, {column: {} for column in frame.columns})