Remove blank key entries in Pandas pd.to_dict

user_155406 0
Viewed 402

Pandas has a very nice feature to export our dataframes to a list of dicts via pd.to_dict('records').

For example:

d = pd.DataFrame({'a':[1,2,3], 'b':['a', 'b', None]}) 
d.to_dict('records')

returns

[{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3, 'b': None}]

For my use case, I would prefer the following entry:

[{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3}]

where you can see the key of b is removed from the third entry. This is default behavior in R when using jsonlite, and am wondering how I would remove keys with missing values from each entry.

pandas dictionary
4 Answers

We can do stack 

l=d.stack().groupby(level=0).agg(lambda x : x.reset_index(level=0,drop=True).to_dict()).tolist()
Out[142]: [{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3}]
user_79645270

Update: using list comprehension and itertuples with nested dict comprehension. It is the fastest

l = [{k: v for k, v in tup._asdict().items() if v is not None} 
                                       for tup in d.itertuples(index=False)]

Out[74]: [{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3}]

Timing:

d1 = pd.concat([d]*5000, ignore_index=True)

In [76]: %timeit [{k: v for k, v in tup._asdict().items() if v is not None} for
    ...:  tup in d1.itertuples(index=False)]
442 ms ± 28 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Another way is using list comprehension and iterrows

l = [row.dropna().to_dict() for k, row in d.iterrows()]

Out[33]: [{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3}]

iterrows has reputation of slow performance. I tested on sample of 15000 rows to compare against stack

In [49]: d1 = pd.concat([d]*5000, ignore_index=True)

In [50]: %timeit d1.stack().groupby(level=0).agg(lambda x : x.reset_index(level
    ...: =0,drop=True).to_dict()).tolist()
7.52 s ± 370 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [51]: %timeit [row.dropna().to_dict() for k, row in d1.iterrows()]
6.45 s ± 60.4 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

Interesting result. However, I think if the data is bigger, it will be slower than stack

user_101892140

Dump everything into a dictionary, it should be faster, and easier with a list comprehension : 

[{key:value
 for key,value in entry.items() 
if value is not None}
 for entry in d.to_dict('records')]


[{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3}]
user_71757130

Here is another approach (if performance is not an issue) with apply and series.dropna()

d.apply(lambda x: x.dropna().to_dict(),axis=1).tolist()

[{'a': 1, 'b': 'a'}, {'a': 2, 'b': 'b'}, {'a': 3}]
user_98406370
Related