Repository: frictionlessdata/frictionless-py

Issue:
to_pandas should create datetime64 for date columns
# Overview

When converting resources to panda dataframes the `to_pandas` method uses `object` data type for columns that have been defined as `date` in the *Table Schema*. It would be more convenient (and more intuitive) if columns with type `date` would have the correct data type. 

Here is a small example:

```python
from frictionless import Resource
import pandas as pd
r = Resource('https://opendata.zitsh.de/frictionless/pegel_wasserstand.json')
df = r.to_pandas()

df['Zeit [MEZ]'] = pd.to_datetime(df['Zeit [MEZ]']) # this line should not be necessary
df = df.set_index('Zeit [MEZ]')

df.loc['2022'].plot()
```


Discussion:
shashigharti: @roll it is ready for the review!.

Patch:
diff --git a/data/issue-1138.csv b/data/issue-1138.csv
new file mode 100644
index 00000000..dfe532ed
--- /dev/null
+++ b/data/issue-1138.csv
@@ -0,0 +1,8 @@
+Zeit [MEZ];Wasserstand;Status;
+"09.07.2013 12:00:00";"146";nicht qualitätsgesichert;
+"10.07.2013";"145";"qualitätsgesichert";
+"11.07.2013";"145";"qualitätsgesichert";
+"12.07.2013";"144";"qualitätsgesichert";
+"13.07.2013";"143";"qualitätsgesichert";
+"14.07.2013";"143";"qualitätsgesichert";
+"15.07.2013";"142";"qualitätsgesichert";
diff --git a/frictionless/plugins/pandas/parser.py b/frictionless/plugins/pandas/parser.py
index cc3ee7da..a423a15b 100644
--- a/frictionless/plugins/pandas/parser.py
+++ b/frictionless/plugins/pandas/parser.py
@@ -174,6 +174,7 @@ class PandasParser(Parser):
         # If the column is of type float instead of integer, convert it to the type
         # Int64 from pandas that supports NaN.
         # Bug: #1109
+        # Bug: #1138 create datetime64 for date columns
         for field in source.schema.fields:
             if (
                 field.type == "integer"
@@ -182,6 +183,13 @@ class PandasParser(Parser):
             ):
                 dataframe[field.name] = dataframe[field.name].astype("Int64")
 
+            if (
+                field.type == "date"
+                and field.name in dataframe.columns
+                and str(dataframe.dtypes[field.name]) != "date"
+            ):
+                dataframe[field.name] = pd.to_datetime(dataframe[field.name])
+
         target.data = dataframe
 
     def __write_convert_type(self, type=None):


Test patch:
diff --git a/tests/plugins/pandas/test_parser.py b/tests/plugins/pandas/test_parser.py
index 42730f36..a3123a00 100644
--- a/tests/plugins/pandas/test_parser.py
+++ b/tests/plugins/pandas/test_parser.py
@@ -6,7 +6,7 @@ from dateutil.tz import tzoffset
 import pandas as pd
 from decimal import Decimal
 from frictionless import Package, Resource
-
+from pandas.core.dtypes.common import is_datetime64_ns_dtype
 
 # Read
 
@@ -53,7 +53,7 @@ def test_pandas_parser_write_bug_1105():
     }
 
 
-def test_pandas_nan_in_integer_resource_column():
+def test_pandas_parser_nan_in_integer_resource_column():
     # see issue 1109
     res = Resource(
         [
@@ -67,14 +67,14 @@ def test_pandas_nan_in_integer_resource_column():
     assert all(df.dtypes.values == pd.array([pd.Int64Dtype(), float, object]))
 
 
-def test_pandas_nan_in_integer_csv_column():
+def test_pandas_parser_nan_in_integer_csv_column():
     # see issue 1109
     res = Resource("data/issue-1109.csv")
     df = res.to_pandas()
     assert all(df.dtypes.values == pd.array([pd.Int64Dtype(), float, object]))
 
 
-def test_pandas_nan_with_field_type_information_1143():
+def test_pandas_parser_nan_with_field_type_information_1143():
     descriptor = {
         "dialect": {"delimiter": ","},
         "name": "issue-1109",
@@ -92,7 +92,7 @@ def test_pandas_nan_with_field_type_information_1143():
     assert all(df.dtypes.values == pd.array([pd.Int64Dtype(), float, object]))
 
 
-def test_pandas_nan_without_field_type_information_1143():
+def test_pandas_parser_nan_without_field_type_information_1143():
     descriptor = {
         "dialect": {"delimiter": ","},
         "name": "issue-1109",
@@ -121,8 +121,8 @@ def test_pandas_parser_write_types():
                 {"name": "any", "type": "string"},  # type fallback
                 {"name": "array", "type": "array"},
                 {"name": "boolean", "type": "boolean"},
-                {"name": "date", "type": "date"},
-                {"name": "date_year", "type": "date"},  # format removal
+                {"name": "date", "type": "datetime"},
+                {"name": "date_year", "type": "datetime"},  # format removal
                 {"name": "datetime", "type": "datetime"},
                 {"name": "duration", "type": "duration"},
                 {"name": "geojson", "type": "object"},
@@ -260,3 +260,50 @@ def test_pandas_parser_from_dataframe_with_primary_key_having_datetime():
                 "VIXOpen": Decimal("17.66"),
             },
         ]
+
+
+def test_pandas_parser_preserve_datetime_field_type_1138():
+    descriptor = {
+        "name": "article",
+        "schema": {
+            "fields": [
+                {"name": "id", "type": "integer"},
+                {"name": "datetime", "type": "date"},
+            ]
+        },
+        "data": [
+            ["id", "datetime"],
+            ["1", "2020-01-01 15:00:00"],
+            ["2", "2020-01-01 15:00:00"],
+        ],
+    }
+    resource = Resource(descriptor)
+    df = resource.to_pandas()
+    assert is_datetime64_ns_dtype(df.dtypes.values[1])
+
+
+def test_pandas_parser_test_issue_sample_data_1138():
+    descriptor = {
+        "path": "data/issue-1138.csv",
+        "name": "pegeldaten-schleswig-holstein-114515",
+        "profile": "tabular-data-resource",
+        "format": "csv",
+        "encoding": "iso8859-1",
+        "dialect": {"delimiter": ";"},
+        "schema": {
+            "fields": [
+                {"type": "date", "format": "%d.%m.%Y", "name": "Zeit [MEZ]"},
+                {"type": "integer", "name": "Wasserstand"},
+                {
+                    "type": "string",
+                    "name": "Status",
+                    "constraints": {
+                        "enum": ["qualitätsgesichert", "nicht qualitätsgesichert"]
+                    },
+                },
+            ]
+        },
+    }
+    resource = Resource(descriptor)
+    df = resource.to_pandas()
+    assert is_datetime64_ns_dtype(df.dtypes.values[0])