python - Pandas remove null values when to_json

Question

i have actually a pandas dataframe and i want to save it to json format. From the pandas docs it says:

Note NaN‘s, NaT‘s and None will be converted to null and datetime objects will be converted based on the date_format and date_unit parameters

Then using the orient option records i have something like this

[{"A":1,"B":4,"C":7},{"A":null,"B":5,"C":null},{"A":3,"B":null,"C":null}]

Is it possible to have this instead:

[{"A":1,"B":4,"C":7},{"B":5},{"A":3}]'

Thank you

Answer 1

The solution above doesn't actually produce results in the 'records' format. This solution also uses the json package, but produces exactly the result asked for in the original question.

import pandas as pd
import json

json.dumps([row.dropna().to_dict() for index,row in df.iterrows()])

Additionally, if you want to include the index (and you are on Python 3.5+) you can do:

json.dumps([{'index':index, **row.dropna().to_dict()} for index,row in df.iterrows()])