how to eliminate null values from json-CodePudding

I have to convert csv to json and eliminate null values.

Csv

ID   Col1   Col2  Col3  Col4
1    123    Null  ABC   Null
2    Null   456   ABC   Null
3    123    345   ABC   Null

I would like to eliminate all the null values and then create a json file or create json and eliminate all null values.

My json file should like :

{"ID":"1","Col1":"123","Col3":"ABC"}
{"ID":"2","Col2":"456","Col3":"ABC"}
{"ID":"2","Col1":"123","Col2":"345","Col3":"ABC"}

I tried with dropna(axis=0/1,how=all/any) in csv

Also, after writing to json, I read the json file again to a dataframe and use below code

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)

Both are not working.

Can anyone help me eliminate null values?

CodePudding user response：

You can try export row by row

df = df.replace('Null', pd.NA)

with open('data.json', mode='a', newline='\n') as f:
    for idx, row in df.iterrows():
        row.dropna().to_json(f)
        f.write('\n')

data.json

{"ID":1,"Col1":"123","Col3":"ABC"}
{"ID":2,"Col2":"456","Col3":"ABC"}
{"ID":3,"Col1":"123","Col2":"345","Col3":"ABC"}

CodePudding user response：

You can built a dictionnary key value from that json , then use this function below

def remove_null_values(d: dict) -> dict:
    return {
        key: remove_null_values(value) if isinstance(value, dict) else value
        for key, value in d.items()
        if value != None
    }

CodePudding user response：

Try this:

def remove_empty_elements(d):
"""recursively remove empty lists, empty dicts, or None elements from a dictionary"""

def empty(x):
    return x is None or x == {} or x == []

if not isinstance(d, (dict, list)):
    return d
elif isinstance(d, list):
    return [v for v in (remove_empty_elements(v) for v in d) if not empty(v)]
else:
    return {k: v for k, v in ((k, remove_empty_elements(v)) for k, v in d.items()) if not empty(v)}

Source: https://gist.github.com/nlohmann/c899442d8126917946580e7f84bf7ee7

CodePudding user response：

try this piece of code. It should work

df = pd.read_csv(name_of_the_file)

print(df.dropna(axis=1).to_dict("records"))