DataFrame.to_dict(orient='records') datetime conversion inconsistency

[depristo]

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')]}

[jreback]

I would guess this is some older code, yep these should be Timestamps

[jreback]

pull-requests are welcome.

[depristo]

Normally I'd love to fix but I looked at the code and it's not at all
obvious to me what the underlying problem is. There's no explicit
conversion code happening in to_dict, so I think the conversion is being
triggered by some low-level system.

On Mon, Oct 5, 2015 at 3:26 PM, Jeff Reback [email protected]
wrote:

pull-requests are welcome.

—
Reply to this email directly or view it on GitHub
#11247 (comment).

Mark A. DePristo
[email protected]

[jreback]

set an example up (yours above), then step thru code until you see what's happening

[udiboy1209]

Same thing happens for orient='series' and orient='split'!

>>> print(df.to_dict(orient='series'))
{'c2': 0   2015-02-02
1   2015-02-03
2   2015-02-04
Name: c2, dtype: datetime64[ns], 'c1': 0   2015-01-01
1   2015-01-02
2   2015-01-03
Name: c1, dtype: datetime64[ns]}

>>> print(df.to_dict(orient='split'))
{'index': [0, 1, 2], 'data': [[1420070400000000000L, 1422835200000000000L], [1420156800000000000L, 1422921600000000000L], [1420243200000000000L, 1423008000000000000L]], 'columns': ['c1', 'c2']}

I agree with @depristo , this is a pretty non-trivial bug. The code for to_dict() gives no clues.

[ethanluoyc]

I did some preliminary investigation. It appears that to_list() invoked using parameter "list" will invoke __iter__ in series.py which checks for the type of the element in it and if it is np.datetime64 it will return the Timestamp object using lib.Timestamp(x). Apparently the checking of the dtype seems to be lacking when using "record", may this be the problem?

[ethanluoyc]

I think I find the problem. When working with dict and list, the value is a Series object and some conversion is implemented. When working with record, the returned is directly from the NDArray so there is no correct conversion of the datetime objects.
From frame.py:

    def to_dict(self, orient='dict'):
        """Convert DataFrame to dictionary.

        Parameters
        ----------
        orient : str {'dict', 'list', 'series', 'split', 'records'}
            Determines the type of the values of the dictionary.

            - dict (default) : dict like {column -> {index -> value}}
            - list : dict like {column -> [values]}
            - series : dict like {column -> Series(values)}
            - split : dict like
              {index -> [index], columns -> [columns], data -> [values]}
            - records : list like
              [{column -> value}, ... , {column -> value}]

            Abbreviations are allowed. `s` indicates `series` and `sp`
            indicates `split`.

        Returns
        -------
        result : dict like {column -> {index -> value}}
        """
        if not self.columns.is_unique:
            warnings.warn("DataFrame columns are not unique, some "
                          "columns will be omitted.", UserWarning)
        if orient.lower().startswith('d'):
            return dict((k, v.to_dict()) for k, v in compat.iteritems(self))
        elif orient.lower().startswith('l'):
            return dict((k, v.tolist()) for k, v in compat.iteritems(self))
        elif orient.lower().startswith('sp'):
            return {'index': self.index.tolist(),
                    'columns': self.columns.tolist(),
                    'data': self.values.tolist()}
        elif orient.lower().startswith('s'):
            return dict((k, v) for k, v in compat.iteritems(self))
        elif orient.lower().startswith('r'):
            return [dict((k, v) for k, v in zip(self.columns, row))
                    for row in self.values]
        else:
            raise ValueError("orient '%s' not understood" % orient)

@jreback is this so?

[jreback]

so using .values on a DataFrame causes a numpy array container to be created that will hold all of the elements. If the frame is mixed type (e.g. say object and datetime64[ns]) then it will be object dtype and all will work, e.g.

In [5]: df = DataFrame({'A' : 'foo', 'B' : pd.date_range('20130101',periods=3), 'C' : 1})

In [6]: df.to_dict(orient='records')
Out[6]: 
[{'A': 'foo', 'B': Timestamp('2013-01-01 00:00:00'), 'C': 1},
 {'A': 'foo', 'B': Timestamp('2013-01-02 00:00:00'), 'C': 1},
 {'A': 'foo', 'B': Timestamp('2013-01-03 00:00:00'), 'C': 1}]

but in the case above .values creates a datetime64[ns] container so the values are represented as np.datetime64, so this is all correct.

What you would need to do is box things, you can use: https://github.com/pydata/pandas/blob/master/pandas/core/common.py#L1848 (_maybe_box_datetimelike)

[jreback]

should fix as I indicated above

On Oct 14, 2015, at 9:57 AM, Ethan Luo [email protected] wrote:

@jreback so do we consider this as an intended effect or we need to fix this?

—
Reply to this email directly or view it on GitHub.

[jreback]

closed by #11327