CODEWITHSUNDEEP
pythonpandas
bobbyccie
bobbyccie

Reputation: 55

Exclude NaNs when using pandas to_dict

I am working with a pandas DataFrame that contains some NaNs, for example:

import pandas as pd
import numpy as np

raw_data={'hostname':{1:'server1',2:'server2',3:'server3',4:'server4'},'nic':{1:'eth1',2:'eth1',3:'eth1',4:'eth1'},'vlan':{1:'100',2:np.nan,3:'200',4:np.nan}}

df=pd.DataFrame(raw_data)

df
  hostname   nic vlan
1  server1  eth1  100
2  server2  eth1  NaN
3  server3  eth1  200
4  server4  eth1  NaN

I then apply some filtering and create a dictionary:

my_dict = df.loc[df['hostname'] == 'server2'].drop('hostname', axis=1).to_dict(orient='records')

my_dict
[{'nic': 'eth1', 'vlan': nan}]

The problem is I want to exclude any keys with a NaN value in the output dictionary, so the output for server2 would be:

my_dict
[{'nic': 'eth1']

I found a possible solution here: make pandas DataFrame to a dict and dropna

from pandas import compat

def to_dict_dropna(data):
  return dict((k, v.dropna().to_dict()) for k, v in compat.iteritems(data))

my_dict=to_dict_dropna(df)

my_dict
{'nic': {1: 'eth1', 2: 'eth1', 3: 'eth1', 4: 'eth1'}, 'hostname': {1: 'server1', 2: 'server2', 3: 'server3', 4: 'server4'}, 'vlan': {1: '100', 3: '200'}}

But I don't know how to combine this solution with my other requirements of filtering and using the orient='records' option.

Basically I need to include the above to_dict_dropna function with my existing string of pandas options. Can anyone suggest a solution? Thanks

Upvotes: 4

Views: 3133

Answers (1)

jezrael
jezrael

Reputation: 863226

Use list comprehension after your solution:

my_dict = (df.loc[df['hostname'] == 'server2']
             .drop('hostname', axis=1)
             .to_dict(orient='records'))

my_dict = [{k:v for k, v in x.items() if v == v } for x in my_dict]
print (my_dict)
[{'nic': 'eth1'}]

Upvotes: 3

Related Questions