[Python] Support converting to non-nano datetime64 for pandas >= 2.0

[asfimport]

Pandas is adding capabilities to store non-nanosecond datetime64 data. At the moment, we however always do convert to nanosecond, regardless of the timestamp resolution of the arrow table (and regardless of the pandas metadata).

Using the development version of pandas:

In [1]: df = pd.DataFrame({"col": np.arange("2012-01-01", 10, dtype="datetime64[s]")})

In [2]: df.dtypes
Out[2]: 
col    datetime64[s]
dtype: object

In [3]: table = pa.table(df)

In [4]: table.schema
Out[4]: 
col: timestamp[s]
-- schema metadata --
pandas: '{"index_columns": [{"kind": "range", "name": null, "start": 0, "' + 423

In [6]: table.to_pandas().dtypes
Out[6]: 
col    datetime64[ns]
dtype: object

This is because we have a coerce_temporal_nanoseconds conversion option which we hardcode to True (for top-level columns, we hardcode it to False for nested data).

When users have pandas >= 2, we should support converting with preserving the resolution. We should certainly do so if the pandas metadata indicates which resolution was originally used (to ensure correct roundtrip).
We could (and at some point also should) also do that by default if there is no pandas metadata (but maybe only later depending on how stable this new feature is in pandas, as it is potentially a breaking change for our users if you use eg pyarrow to read a parquet file).

Reporter: Joris Van den Bossche / @jorisvandenbossche

Related issues:

• [Python][CI] Build with pandas master/nightly failure related to timedelta64 resolution (is related to)

_{Note: This issue was originally created as ARROW-18124. Please see the migration documentation for further details.}

[danepitkin]

When users have pandas >= 2, we should support converting with preserving the resolution. We should certainly do so if the pandas metadata indicates which resolution was originally used (to ensure correct roundtrip).
We could (and at some point also should) also do that by default if there is no pandas metadata (but maybe only later depending on how stable this new feature is in pandas, as it is potentially a breaking change for our users if you use eg pyarrow to read a parquet file).

Can you provide some more detail about this paragraph? Does pandas metadata mean the pyarrow/array.pxi::PandasOptions class? When would pandas metadata not be present and why would that break parquet reading?

[jorisvandenbossche]

The "pandas metadata" is custom metadata that we store in the pyarrow schema whenever the data is created from a pandas.DataFrame:

>>> df = pd.DataFrame({"col": pd.date_range("2012-01-01", periods=3, freq="D")})
>>> df
         col
0 2012-01-01
1 2012-01-02
2 2012-01-03
>>> table = pa.table(df)
>>> table.schema
col: timestamp[ns]
-- schema metadata --
pandas: '{"index_columns": [{"kind": "range", "name": null, "start": 0, "' + 413
# easier way to access this (and converted to a dict)
>>> table.schema.pandas_metadata
{'index_columns': [{'kind': 'range',
   'name': None,
   'start': 0,
   'stop': 3,
   'step': 1}],
 'column_indexes': [{'name': None,
   'field_name': None,
   'pandas_type': 'unicode',
   'numpy_type': 'object',
   'metadata': {'encoding': 'UTF-8'}}],
 'columns': [{'name': 'col',
   'field_name': 'col',
   'pandas_type': 'datetime',
   'numpy_type': 'datetime64[ns]',
   'metadata': None}],
 'creator': {'library': 'pyarrow', 'version': '13.0.0.dev106+gfbe5f641d'},
 'pandas_version': '2.1.0.dev0+484.g7187e67500'}

So this indicates that the original data in the pandas.DataFrame had "datetime64[ns]" type. In this case that matches the arrow type, but for example after a roundtrip through Parquet, this might no longer be the case:

>>> pq.write_table(table, "test.parquet")
>>> table2 = pq.read_table("test.parquet")
>>> table2.schema
Out[33]: 
col: timestamp[us]                 # <--- now us instead of ns
-- schema metadata --
pandas: '{"index_columns": [{"kind": "range", "name": null, "start": 0, "' + 413
>>>  table2.schema.pandas_metadata
{ ...
 'columns': [{'name': 'col',
   'field_name': 'col',
   'pandas_type': 'datetime',
   'numpy_type': 'datetime64[ns]',   # <--- but this still indicates ns
   'metadata': None}],
...

So the question is here what table2.to_pandas() should do? Use the microsecond resolution of the data, of the nanosecond resolution of the metadata?

(note that this is also a consequence of the default parquet version we write not yet supporting nanoseconds, while we should probably bump that default version we write, and then the nanoseconds would be preserved in the Parquet roundtrip)

Now, I am not sure if it would be easy to use the information of the pandas metadata to influence the conversion, as we typically only use the metadata after converting the actual data to finalize the resulting pandas DataFrame (eg set the index, cast the column names, ..).
And I am also not fully sure if it would actually be desirable to follow the pandas metadata, since that would involve an extra conversion step (and effectively all existing pandas metadata (eg in already written parquet files) will always say that it is nanoseconds, since until recently that was the only supported resolution by pandas).

[danepitkin]

Excellent write-up and example! I'd prefer supporting nanosecond in parquet and not converting based off of metadata since it seems like its not guaranteed to be reliable. Filed #35746 to track Parquet support for ns.