TypeError: Object of type NAType is not JSON serializable

user_1933875 0
Viewed 4277

Thank you in advance for your help.

My python code reads json input file and loads the data into a data frame, masks or changes on the data frame column specified by configuration and in the last stage, creates json output file.

read json into data frame --> mask/change the df column ---> generate json

Input json:

[
    {
        "BinLogFilename": "mysql.log",
        "Type": "UPDATE",
        "Table": "users",
        "ServerId": 1,
        "BinLogPosition": 2111
    },        {
    {   "BinLogFilename": "mysql.log",
        "Type": "UPDATE",
        "Table": "users",
        "ServerId": null,
        "BinLogPosition": 2111
    },
  ...
]

when I load the above json into data frame, the data frame column "ServerId" has float values because it has null in few blocks of json input.

The main central logic converts/fakes "ServerId" into another number, however the output contains float numbers.

Output json:

[
      {
            "BinLogFilename": "mysql.log",
            "Type": "UPDATE",
            "Table": "users",
            "ServerId": 5627.0,
            "BinLogPosition": 2111
        }, 
        {
            "BinLogFilename": "mysql.log",
            "Type": "UPDATE",
            "Table": "users",
            "ServerId": null,
            "BinLogPosition": 2111
        },
     ....
]

masking logic

df['ServerId'] = [fake.pyint() if not(pd.isna(df['ServerId'][index])) else np.nan for index in range(len(df['ServerId']))]

The challenge is, the output "ServerId" should contain only integers but unfortunately it contains floats.

df['ServerId']
0     9590.0
1        NaN
2     1779.0
3     1303.0
4        NaN

I found a answer to this problem, to use 'Int64'

df['ServerId'] = df['ServerId'].astype('Int64')
0     8920
1     <NA>
2     9148
3     2434
4     <NA>

However using 'Int64', it converts NaN to NA and while writing back to json, i get an error as,

TypeError: Object of type NAType is not JSON serializable

with gzip.open(outputFile, 'w') as outfile:
    outfile.write(json.dumps(json_objects_list).encode('utf-8'))

Is it possible to keep NaN after converting to 'Int64' data type? If this is not possible, how can i fix the error?

python-3.8 pandas dataframe numpy python
3 Answers

Indeed, Pandas NA and NaT are not JSON serialisable by the built-in Python json library.

But the Pandas DataFrame to_json() method will handle those values for you and convert them to JSON null.

from pandas import DataFrame, Series, NA, NaT

df = DataFrame({"ServerId" : Series([8920, NA, 9148, 2434, NA], dtype="Int64") })
s = df.to_json()

# -> {"ServerId":{"0":8920,"1":null,"2":9148,"3":2434,"4":null}}
user_146412820

The error is related to the fact that some of the values in your pandas DataFrame are pd.NaT and will then cause troubles when calling json.dumps().

One possible solution is to replace all missing values (including None, pd.NaT, numpy.nan and really any other missing value-realted type) first with np.nan and then replace the latter with None:

import numpy as np

df = df.fillna(np.nan).replace([np.nan], [None])
user_71317570

Just add your custom json encoder like this:

from decimal import Decimal
from pandas._libs.missing import NAType
    
class MyEncoder(json.JSONEncoder):
    def default(self, obj):
        if isinstance(obj, Decimal):
            return str(obj)
        if isinstance(obj, NAType):
            return ""
        # ️ otherwise use the default behavior
        return json.JSONEncoder.default(self, obj)

And then you can pass it to json.dumps(), for example:

print(json.dumps(list(df.T.to_dict().values()), indent=4, cls=MyEncoder))
user_70390
Related