DataFrame.to_dict(orient='records') datetime conversion inconsistency · Issue #11247 · pandas-dev/pandas (original) (raw)

My original code used:

data = df.to_dict(orient='records')

but I had to replace with a different to_dict conversion because to_dict(orient='records') doesn't properly convert numpy.datetime64 to Timestamps but rather returns numpy.datetime64 values which are hard to work with downstream. If you include any other non-datetime fields in the dataframe the conversion to a Timestamp happens with 'records' mode, so I think this is some kind of edge-case where converters aren't being triggered when everything is a datetime.

import pandas as pd
df = pd.DataFrame({
        'c1': pd.to_datetime(['1-1-2015', '1-2-2015', '1-3-2015']),
        'c2': pd.to_datetime(['2-2-2015', '2-3-2015', '2-4-2015']),
    })
print('raw dataframe')
print(df)
print('\nconverted to records')
print(df.to_dict(orient='records'))
print('\nconverted to dict')
print(df.to_dict(orient='dict'))
print('\nconverted to list')
print(df.to_dict(orient='list'))

produces

raw dataframe
          c1         c2
0 2015-01-01 2015-02-02
1 2015-01-02 2015-02-03
2 2015-01-03 2015-02-04

converted to records
[{'c2': numpy.datetime64('2015-02-01T16:00:00.000000000-0800'), 'c1': numpy.datetime64('2014-12-31T16:00:00.000000000-0800')}, {'c2': numpy.datetime64('2015-02-02T16:00:00.000000000-0800'), 'c1': numpy.datetime64('2015-01-01T16:00:00.000000000-0800')}, {'c2': numpy.datetime64('2015-02-03T16:00:00.000000000-0800'), 'c1': numpy.datetime64('2015-01-02T16:00:00.000000000-0800')}]

converted to dict
{'c2': {0: Timestamp('2015-02-02 00:00:00'), 1: Timestamp('2015-02-03 00:00:00'), 2: Timestamp('2015-02-04 00:00:00')}, 'c1': {0: Timestamp('2015-01-01 00:00:00'), 1: Timestamp('2015-01-02 00:00:00'), 2: Timestamp('2015-01-03 00:00:00')}}

converted to list
{'c2': [Timestamp('2015-02-02 00:00:00'), Timestamp('2015-02-03 00:00:00'), Timestamp('2015-02-04 00:00:00')], 'c1': [Timestamp('2015-01-01 00:00:00'), Timestamp('2015-01-02 00:00:00'), Timestamp('2015-01-03 00:00:00')]}