use_arrow=True vs False: different handling of date columns from shapefiles

[theroggy]

There are different ways a date column is handled now:

• If use_arrow=False, (the default), a date column is returned as dtype "datetime64".
• If use_arrow=True, a date column is returned as dtype "object" with datetime.date objects as data values.
• When fiona is used, a date column is returned as dtype "object" with datetime.date objects as data values, like when arrow=True.

Not sure what is the way to go, but probably it would be better if the behaviour is the same.

Additional complication: columns of type "datetime64" are written by pyogrio to a column of Date type (tested with shapefile), while "object" columns with datetime.date values seem to be written as a "String" column.

[theroggy]

Script to reproduce both aspects + how fiona handles this case: fiona formats dates as string.

from pathlib import Path
import tempfile

import geopandas as gpd
import pyogrio

tmp_dir = Path(tempfile.gettempdir())
url = "https://github.com/theroggy/pysnippets/raw/main/pysnippets/pyogrio/polygon-parcel_31370.zip"
for use_arrow in [True, False]:
    gdf = pyogrio.read_dataframe(url, use_arrow=use_arrow)
    print(
        f"input_file, use_arrow: {use_arrow}, column dtype: {gdf['DATUM'].dtype}, "
        f"value: {gdf['DATUM'][0]}, value type: {type(gdf['DATUM'][0])}"
    )
    written_path = tmp_dir / f"output_{use_arrow}.shp"
    pyogrio.write_dataframe(gdf, written_path)
    gdf = pyogrio.read_dataframe(written_path, use_arrow=False)
    print(
        f"written_file, use_arrow: {use_arrow}, column dtype: {gdf['DATUM'].dtype}, "
        f"value: {gdf['DATUM'][0]}, value type: {type(gdf['DATUM'][0])}"
    )

# Do a round-trip using fiona for comparison
gdf = gpd.read_file(url, engine="fiona")
print(
    f"input_file, fiona, column dtype: {gdf['DATUM'].dtype}, "
    f"value: {gdf['DATUM'][0]}, value type: {type(gdf['DATUM'][0])}"
)
written_path = tmp_dir / "output_fiona.shp"
gdf.to_file(written_path)
gdf = gpd.read_file(written_path, engine="fiona")
print(
    f"written_file, fiona, column dtype: {gdf['DATUM'].dtype}, "
    f"value: {gdf['DATUM'][0]}, value type: {type(gdf['DATUM'][0])}"
)

(relevant) output:

input_file, use_arrow: True, column dtype: object, value: 2020-05-01, value type: <class 'datetime.date'>
written_file, use_arrow: True, column dtype: object, value: 2020-05-01, value type: <class 'str'>
input_file, use_arrow: False, column dtype: datetime64[s], value: 2020-05-01 00:00:00, value type: <class 'pandas._libs.tslibs.timestamps.Timestamp'>
written_file, use_arrow: False, column dtype: datetime64[s], value: 2020-05-01 00:00:00, value type: <class 'pandas._libs.tslibs.timestamps.Timestamp'>
input_file, fiona, column dtype: object, value: 2020-05-01, value type: <class 'str'>
written_file, fiona, column dtype: object, value: 2020-05-01, value type: <class 'str'>

[theroggy]

In geofileops I'm thinking towards dealing with it as follows, so convert these columns to datetime64:

    # Cast columns that are of object type, but contain datetime.date or datetime.date
    # to proper datetime64 columns.
    if len(result_gdf) > 0:
        for column in result_gdf.select_dtypes(include=["object"]):
            if isinstance(result_gdf[column].iloc[0], (datetime.date, datetime.datetime)):
                result_gdf[column] = pd.to_datetime(result_gdf[column])

[kylebarron]

My personal view in all of these data type issues is that in the long term, it makes sense to adopt use_arrow=True as the standard, and deprecate use_arrow=False. For one, there's lots of data type handling that is currently handled manually in use_arrow=False that gets handled automatically by GDAL when using its RFC 86 bindings. The Arrow bindings should be potentially faster, have much lower maintenance requirements, and have zero-copy interoperability with a larger ecosystem.

[jorisvandenbossche]

If use_arrow=True, a date column is returned as dtype "object" with datetime.date objects as data values.

What I suppose is happening here, is that we get an arrow table with an actual date column (arrow has a date32 and date64 data types). And then in read_dataframe we convert this pyarrow table to pandas using table.to_pandas().
It is this arrow->pandas conversion where pyarrow chooses to create datetime.date objects by default. But this can actually be controlled by a keyword, so if we want to have the same behaviour, we can specify date_as_object=False in the to_pandas call (and then there is no need to do pd.to_datetime afterwards)

I don't think I have a strong opinion on what option is best (datetime.date vs datetime64[ns]). Ideally in the near future, pandas will actually natively support a "date" data type, and then that would also solve this question.

(I do agree that longer term, we hopefully can just start relying on the ArrowStream-based interface of GDAL)

[kylebarron]

But this can actually be controlled by a keyword

Ah right. That reminded me of #241 (comment), so in theory you could use types_mapper here for the pyarrow -> pandas conversion to get the date type you want?

[jorisvandenbossche]

You could indeed use types_mapper to preserve the arrow date in an Arrow-backed pandas extension dtype. But you can't use this keyword to choose between object and datetime64, because that are no extension dtypes (and the types_mapper keyword only works with extension dtypes ..)