Get rid of columns with null value in json output

Question

I'm writing data from dataframe to json file using pandas function .to_json like this:

xx.to_json('file_path',orient='records',lines=True)

The output of this looks like this:

{"speed":null,"state":4.0,"stop_trigger":null,"t":1527237121263,"target_speed":null}
{"speed":null,"state":null,"stop_trigger":null,"t":1527237121264,"target_speed":400.0}

How is it possible to get rid of columns in the output if there are null values?:

{"state":4.0,"t":1527237121263}
{"t":1527237121264,"target_speed":400.0}

Blinxen · Accepted Answer · 2018-05-25 15:19:03Z

2

The best way is to check every element in the dict and remove the keys which have the null value. It would look something like this.

with open('data.json') as f:
    json_dict = json.load(f)
for key in json_dict:
    if json_dict[key] is Null:
         json_dict.pop(key)

With json_dict[key] you get the value of the key and with the pop() function you delete the element from the dictionary. The pop() function also returns the value of the key that is going to be deleted.

edited May 25, 2018 at 15:19

answered May 25, 2018 at 15:07

Blinxen

8642 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alex Over a year ago

This does not work in Python 3: "dictionary changed size during iteration"

jpp · Accepted Answer · 2018-05-25 14:10:41Z

1

You can iterate your dataframe to remove null columns and create a list of dictionaries.

Then use the json module to write the list to a file.

import pandas as pd
import json

df = pd.DataFrame([[np.nan, 4.0, np.nan, 1527237121263, np.nan],
                   [np.nan, np.nan, np.nan, 1527237121264, 400.0]],
                  columns=['speed', 'state', 'stop_trigger', 't', 'target_speed'])

d = [dict(row.dropna()) for idx, row in df.iterrows()]

with open('file.json', 'w') as fp:
    json.dump(d, fp)

answered May 25, 2018 at 14:10

jpp

166k37 gold badges301 silver badges362 bronze badges

Comments

Micks Ketches · Accepted Answer · 2018-05-25 14:58:34Z

1

singledispatch is your friend, you can use this to handle null/None cases in different data types with one function (you can do this on a dataframe, dict, file, or json string).

import os
import json
from functools import singledispatch


@singledispatch
def remove_null_bool(ob):
    return ob

@remove_null_bool.register(list)
def _process_list(ob):
    return [remove_null_bool(v) for v in ob if v is not None]

@remove_null_bool.register(dict)
def _process_list(ob):
    return {k: remove_null_bool(v) for k, v in ob.items() if v is not None}



def cleanse(in_file):
    with open(in_file, 'r') as source:
        source_json = json.load(source)
    with open(in_file, 'w') as source:
        json.dump(remove_null_bool(source_json), source)

edited May 25, 2018 at 14:58

answered May 25, 2018 at 14:52

Micks Ketches

4861 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Get rid of columns with null value in json output

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related