Python error : TypeError: Object of type 'Timestamp' is not JSON serializable'

Question

I have a Dataframe that has a time stamp column of type 'datetime64[ns]'. When I try to insert it to Salesforce platform get an error 'TypeError: Object of type 'Timestamp' is not JSON serializable'. How could I change this timestamp column to have it updated properly. Given below is the view of the Dataframe.

Id,Name,Date,Type
1,ProdA,2018-05-18 04:45:08,S
1,ProdB,2018-05-18 02:15:00,S
1,ProdC,2018-05-16 10:20:00,S

Datatype for each of these 4 columns:

Id                                     object
Name                                   object
Date                           datetime64[ns]
Type                                   object
dtype: object

Could anyone assist on this. Thanks.

Answer 1

You can try convert datetime to string:

df['Date'] = df['Date'].astype(str)

Or:

df['Date'] = df['Date'].dt.strftime('%Y-%m-%d %H:%M:%S')

print (df.dtypes)
Id      object
Name    object
Date    object
Type    object
dtype: object

Answer 2

If you get an error TimeStamp has no attribute as "astype(str)", you can try, for example, str(timeseries.index[0]). This will convert the timestamp into a string that can then be serialized.

Answer 3

Had a similar problem and fixed it, as I posted in a similar question. Here is my solution from the link:

Test Data

import numpy as np
import json

data = np.array([0, 1+0j, 3.123, -1, 2, -5, 10], dtype=np.complex128)
data_dict = {'value': data.real[-1], 
             'array': data.real,
             'complex_value': data[-1], 
             'complex_array': data,
             'datetime_value': data.real.astype('datetime64[D]')[0],
             'datetime_array': data.real.astype('datetime64[D]'),
           }

Numpy Encoder

JSON natively supports only strings, integers, and floats but no special (d)types such as complex or datetime. One solution is to convert those special (d)types to an array of strings with the advantage that numpy can read it back easily, as outlined in the decoder section below.

class NpEncoder(json.JSONEncoder):
    def default(self, obj):
        dtypes = (np.datetime64, np.complexfloating)
        if isinstance(obj, dtypes):
            return str(obj)
        elif isinstance(obj, np.integer):
            return int(obj)
        elif isinstance(obj, np.floating):
            return float(obj)
        elif isinstance(obj, np.ndarray):
            if any([np.issubdtype(obj.dtype, i) for i in dtypes]):
                return obj.astype(str).tolist()
            return obj.tolist()
        return super(NpEncoder, self).default(obj)

# example usage
json_str = json.dumps(data_dict, cls=NpEncoder)
# {"value": 10.0, "array": [0.0, 1.0, 3.123, -1.0, 2.0, -5.0, 10.0], "complex_value": "(10+0j)", "complex_array": ["0j", "(1+0j)", "(3.123+0j)", "(-1+0j)", "(2+0j)", "(-5+0j)", "(10+0j)"], "datetime_value": "1970-01-01", "datetime_array": ["1970-01-01", "1970-01-02", "1970-01-04", "1969-12-31", "1970-01-03", "1969-12-27", "1970-01-11"]}

Decoding to numpy

Special (d)types must be converted manually after loading the JSON.

json_data = json.loads(json_str)

# Converting the types manually
json_data['complex_value'] = complex(json_data['complex_value'])
json_data['datetime_value'] = np.datetime64(json_data['datetime_value'])

json_data['array'] = np.array(json_data['array'])
json_data['complex_array'] = np.array(json_data['complex_array']).astype(np.complex128)
json_data['datetime_array'] = np.array(json_data['datetime_array']).astype(np.datetime64)

Answer 4

To apply for all datetime fields of a Dataframe, you could do something like this using Pandas:

for column in df.columns:
    if pandas.api.types.is_datetime64_any_dtype(df[column]):
        df[column] = df[column].dt.strftime('%Y-%m-%d %H:%M:%S')