Remove object from JSON whose values are NaN using Python?

Question

My final output JSON file is in following format

    [
     {
    "Type": "UPDATE",
    "resource": {
        "site ": "Lakeside mh041",
        "name": "Total Flow",
        "unit": "CubicMeters",
        "device": "2160 LaserFlow Module",
        "data": [
            {
                "timestamp": [
                    "1087009200"
                ],
                "value": [
                    6945.68
                ]
            },
            {
                "timestamp": [
                    "1087095600"
                ],
                "value": [
                    NaN
                ]
            },
            {
                "timestamp": [
                    "1087182000"
                ],
                "value": [
                    7091.62
                ]
            },

I want to remove the whole object if the "value" is NaN.

Expected Output

     [
      {
    "Type": "UPDATE",
    "resource": {
        "site ": "Lakeside mh041",
        "name": "Total Flow",
        "unit": "CubicMeters",
        "device": "2160 LaserFlow Module",
        "data": [
            {
                "timestamp": [
                    "1087009200"
                ],
                "value": [
                    6945.68
                ]
            },
            {
                "timestamp": [
                    "1087182000"
                ],
                "value": [
                    7091.62
                ]
            },

I cannot remove the blank values from my csv file because of the format of the file.

I have tried this:

  with open('Result.json' , 'r') as j:
     json_dict = json.loads(j.read())
     json_dict['data'] = [item for item in json_dict['data'] if 
        len([val for val in item['value'] if isnan(val)]) == 0]

  print(json_dict)

Error - json_dict['data'] = [item for item in json_dict['data'] if len([val for val in item['value'] if isnan(val)]) == 0] TypeError: list indices must be integers or slices, not str

NaN is not part of JSON--this isn't a valid JSON structure. Can you clarify your intent? What have you tried to solve this problem? — ggorlen, Jul 22 '19 at 19:35
@ggorlen Python's standard library json decoder actually does support literal `NaN`, even though it's not part of the standard. https://docs.python.org/3/library/json.html#json.JSONDecoder — Håken Lid, Jul 22 '19 at 20:26
The `TypeError` you describe suggests that your JSON data may not have the structure you expect. Can you double check that what you've shown is representative, and that there aren't any extra layers you've omitted? — Blckknght, Jul 23 '19 at 01:51
@Blckknght yes there are extra layers. I thought that wouldn't be necessary. Let me edit my JSON again. — , Jul 23 '19 at 02:14
So it looks like your top level object type in the JSON is a list, which contains a dict, that has another dictionary under `'resource'`. Only at that level do you have the `'data'` key, and the further contents you were looking for. So maybe you need `json_dict[0]['resource']['data']` rather than just `json_dict['data']`. (You might need to loop on the list, if there might be more than one item in it.) — Blckknght, Jul 23 '19 at 02:38

score 1 · Answer 1 · answered Jul 22 '19 at 19:36

For testing if value is NaN you could use math.isnan() function (doc):

data = '''{"data": [
            {
                "timestamp": [
                    "1058367600"
                ],
                "value": [
                    9.65
                ]
            },
            {
                "timestamp": [
                    "1058368500"
                ],
                "value": [
                    NaN
                ]
            },
            {
                "timestamp": [
                    "1058367600"
                ],
                "value": [
                    4.75
                ]
            }
        ]}'''

import json
from math import isnan

data = json.loads(data)
data['data'] = [i for i in data['data'] if not isnan(i['value'][0])]

print(json.dumps(data, indent=4))

Prints:

{
    "data": [
        {
            "timestamp": [
                "1058367600"
            ],
            "value": [
                9.65
            ]
        },
        {
            "timestamp": [
                "1058367600"
            ],
            "value": [
                4.75
            ]
        }
    ]
}

I am using a JSON file as an input. for that i am getting an error TypeError: list indices must be integers or slices, not str — , Jul 23 '19 at 01:38

Sharan Arumugam · Accepted Answer · 2019-07-23T02:43:42.600

In case you have more than one value for json"value": [...] then,

import json
from math import isnan

json_str = '''
[
    {
        "Type": "UPDATE",
        "resource": {
            "site ": "Lakeside mh041",
            "name": "Total Flow",
            "unit": "CubicMeters",
            "device": "2160 LaserFlow Module",
            "data": [
                {
                     "timestamp": [
                         "1087009200"
                     ],
                    "value": [
                         6945.68
                     ]
                },
                {
                    "timestamp": [
                        "1087095600"
                    ],
                    "value": [
                        NaN
                    ]
                }
            ]
        }
    }
]
'''

json_dict = json.loads(json_str)

for typeObj in json_dict:
    resource_node = typeObj['resource']
    resource_node['data'] = [
        item for item in resource_node['data']
        if len([val for val in item['value'] if isnan(val)]) == 0
    ]

print(json_dict)

I am using a JSON file as an input. for that i am getting an error TypeError: list indices must be integers or slices, not str — , Jul 23 '19 at 02:20
updated for new input. If your JSON starts with ```[``` then its a list... need to loop it once just before ```obj['key']``` — Sharan Arumugam, Jul 23 '19 at 02:46

Remove object from JSON whose values are NaN using Python?

2 Answers2