pandas astype python bool instead of numpy.bool_

Question

I need to convert a pandas dataframe to a JSON object.

However

json.dumps(df.to_dict(orient='records'))

fails as the boolean columns are not JSON serializable since they are of type numpy.bool_. Now I've tried df['boolCol'] = df['boolCol'].astype(bool) but that still leaves the type of the fields as numpy.bool_ rather than the pyhton bool which serializes to JSON no problem.

Any suggestions on how to convert the columns without looping through every record and converting it?

Thanks

EDIT:

This is part of a whole sanitization of dataframes of varying content so they can be used as the JSON payload for an API. Hence we currently have something like this:

for cols in df.columns:
    if type(df[cols][0]) == pd._libs.tslibs.timestamps.Timestamp:
        df[cols] = df[cols].astype(str)
    elif type(df[cols]) == numpy.bool_:
        df[cols] = df[cols].astype(bool) #still numnpy bool afterwards!

Answer 1

Just tested it out, and the problem seems to be caused by the orient='records' parameter. Seems you have to set it to a option (e.g. list) and convert the results to your preferred format.

import numpy as np
import pandas as pd
column_name = 'bool_col'

bool_df = pd.DataFrame(np.array([True, False, True]), columns=[column_name])

list_repres = bool_df.to_dict('list')
record_repres = [{column_name: values} for values in list_repres[column_name]]

json.dumps(record_repres)

Answer 2

You need to use .astype and set its field dtype to object

See example below:

df = pd.DataFrame({
   "time": ['0hr', '128hr', '72hr', '48hr', '96hr'],
   "value": [10, 20, 30, 40, None]
})
df['revoked'] = False
df.revoked = df.revoked.astype(bool)
print 'setting astype as bool:', type(df.iloc[0]['revoked'])


df.revoked = df.revoked.astype(object)
print 'setting astype as object:', type(df.iloc[0]['revoked'])


>>> setting astype as bool: <type 'numpy.bool_'>
>>> setting astype as object: <type 'bool'>