Repository: dirty-cat/dirty_cat (python)

Issue:
TableVectorizer test failing with latest dependencies
<!--Provide a brief description of the bug.-->
We have an error when testing the TableVectorizer with the latest dependencies. See here:
https://github.com/dirty-cat/dirty_cat/actions/runs/4599113088/jobs/8124079078

Error:
```python
AssertionError: assert 'datetime64[ns]' == dtype('O')
```


Pull request:
Improved datetime format inference
Fix #540 
The bug in #540 was related to [pandas 2.0 update](https://pandas.pydata.org/pdeps/0004-consistent-to-datetime-parsing.html): as datetime format inference became stricter, a bug which was silent (inconsistent date format inferred for one column in the test) became loud.

This PR fixes this and improves date format detection (and thus datetime column detection in `TableVectorizer`). This replaces using `pd.datetime` directly for datetime format inference.

To do this, we try pandas's `guess_datetime_format` (on a subset) both with `dayfirst` being `True` and `False`, and see if one of these options finds a single format for all rows. If both work, we return the monthfirst format and raise a warning. If both return multiple formats, we give up.

This finds the right %d-%m-%Y  format for the failing test example, instead of failing or returning mixed format (see below).
```
  "11-12-2029",
  "02-12-2012",
  "11-09-2012",
  "13-02-2000",
  "10-11-2001"
```


### `pd.to_datetime` format inference
If I understand correctly, pandas datetime format inference (in `pd.to_datetime`) works like this:
#### Versions < 2.0
If not `infer_datetime_format`, the format of each row is inferred independently.
If `infer_datetime_format`, the format is inferred from the first non-missing example, and pandas *tries* to apply it to the other example (but can use another format otherwise).
This can easily create issues, for instance in our tests:
```
    "11-12-2029",
    "02-12-2012",
    "11-09-2012",
    "13-02-2000",
    "10-11-2001"
```
For all rows, the inferred format was %m-%d-%Y expect 13-02-2000, for which the format was %d-%m-%Y.
#### Version >= 2.0
The format is inferred from the first non-missing example, and pandas *tries* to apply it to the other example (and raise an error otherwise).




Patch:
diff --git a/CHANGES.rst b/CHANGES.rst
index d033de9..1f13963 100644
--- a/CHANGES.rst
+++ b/CHANGES.rst
@@ -10,6 +10,13 @@ Major changes
 * :func:`fuzzy_join` and :class:`FeatureAugmenter` now perform joins on missing values as in `pandas.merge`
 but raises a warning. :pr:`522` and :pr:`529` by :user:`Jovan Stojanovic <jovan-stojanovic>`
 
+Minor changes
+-------------
+* Improvement of date column detection and date format inference in :class:`TableVectorizer`. The
+format inference now finds a format which works for all non-missing values of the column, instead
+of relying on pandas behavior. If no such format exists, the column is not casted to a date column.
+:pr:`543` by :user:`Leo Grinsztajn <LeoGrin>`
+
 Release 0.4.0
 =============
 
diff --git a/dirty_cat/_table_vectorizer.py b/dirty_cat/_table_vectorizer.py
index 1d7ea33..de9971a 100644
--- a/dirty_cat/_table_vectorizer.py
+++ b/dirty_cat/_table_vectorizer.py
@@ -3,13 +3,14 @@ Implements the TableVectorizer: a preprocessor to automatically apply
 transformers/encoders to different types of data, without the need to
 manually categorize them beforehand, or construct complex Pipelines.
 """
-
+import warnings
 from typing import Dict, List, Literal, Optional, Tuple, Union
 from warnings import warn
 
 import numpy as np
 import pandas as pd
 import sklearn
+from pandas._libs.tslibs.parsing import guess_datetime_format
 from pandas.core.dtypes.base import ExtensionDtype
 from sklearn import __version__ as sklearn_version
 from sklearn.base import TransformerMixin, clone
@@ -25,6 +26,70 @@ from dirty_cat._utils import parse_version
 # flake8: noqa: E501
 
 
+def _infer_date_format(date_column: pd.Series, n_trials: int = 100) -> Optional[str]:
+    """Infer the date format of a date column,
+    by finding a format which should work for all dates in the column.
+
+    Parameters
+    ----------
+    date_column : :class:`~pandas.Series`
+        A column of dates, as strings.
+    n_trials : int, default=100
+        Number of rows to use to infer the date format.
+
+    Returns
+    -------
+    Optional[str]
+        The date format inferred from the column.
+        If no format could be inferred, returns None.
+    """
+    if len(date_column) == 0:
+        return
+    date_column_sample = date_column.dropna().sample(
+        frac=min(n_trials / len(date_column), 1), random_state=42
+    )
+    # try to infer the date format
+    # see if either dayfirst or monthfirst works for all the rows
+    with warnings.catch_warnings():
+        # pandas warns when dayfirst is not strictly applied
+        warnings.simplefilter("ignore")
+        date_format_monthfirst = date_column_sample.apply(
+            lambda x: guess_datetime_format(x)
+        )
+        date_format_dayfirst = date_column_sample.apply(
+            lambda x: guess_datetime_format(x, dayfirst=True),
+        )
+    # if one row could not be parsed, return None
+    if date_format_monthfirst.isnull().any() or date_format_dayfirst.isnull().any():
+        return
+    # even with dayfirst=True, monthfirst format can be inferred
+    # so we need to check if the format is the same for all the rows
+    elif date_format_monthfirst.nunique() == 1:
+        # one monthfirst format works for all the rows
+        # check if another format works for all the rows
+        # if so, raise a warning
+        if date_format_dayfirst.nunique() == 1:
+            # check if monthfirst and dayfirst haven't found the same format
+            if date_format_monthfirst[0] != date_format_dayfirst[0]:
+                warnings.warn(
+                    f"""
+                    Both {date_format_monthfirst[0]} and {date_format_dayfirst[0]} are valid
+                    formats for the dates in column {date_column.name}.
+                    Format {date_format_monthfirst[0]} will be used.
+                    """,
+                    UserWarning,
+                    stacklevel=2,
+                )
+        return date_format_monthfirst[0]
+    elif date_format_dayfirst.nunique() == 1:
+        # only this format works for all the rows
+        return date_format_dayfirst[0]
+    else:
+        # more than two different formats were found
+        # TODO: maybe we could deal with this case
+        return
+
+
 def _has_missing_values(df: Union[pd.DataFrame, pd.Series]) -> bool:
     """
     Returns True if `array` contains missing values, False otherwise.
@@ -425,12 +490,9 @@ class TableVectorizer(ColumnTransformer):
                 except (ValueError, TypeError):
                     # Only try to convert to datetime
                     # if the variable isn't numeric.
-                    try:
-                        X[col] = pd.to_datetime(
-                            X[col], errors="raise", infer_datetime_format=True
-                        )
-                    except (ValueError, TypeError):
-                        pass
+                    format = _infer_date_format(X[col])
+                    if format is not None:
+                        X[col] = pd.to_datetime(X[col], errors="raise", format=format)
             # Cast pandas dtypes to numpy dtypes
             # for earlier versions of sklearn. FIXME: which ?
             if issubclass(X[col].dtype.__class__, ExtensionDtype):
diff --git a/examples/04_fuzzy_joining_and_FeatureAugmenter.py b/examples/04_fuzzy_joining_and_FeatureAugmenter.py
index d7eefad..6d04e1b 100644
--- a/examples/04_fuzzy_joining_and_FeatureAugmenter.py
+++ b/examples/04_fuzzy_joining_and_FeatureAugmenter.py
@@ -159,28 +159,11 @@ df1.tail(20)
 #
 # Let's do some more inspection of the merging done.
 
-###############################################################################
-# The best way to inspect the matches is to use the following function:
-import numpy as np
-
-
-def print_worst_matches(joined_table, n=5):
-    """Prints n worst matches for inspection."""
-    max_ind = np.argsort(joined_table["matching_score"], axis=0)[:n]
-    max_dist = pd.Series(
-        joined_table["matching_score"][max_ind.ravel()].ravel(),
-        index=max_ind.ravel(),
-    )
-    worst_matches = joined_table.iloc[list(max_ind.ravel())]
-    worst_matches = worst_matches.assign(matching_score=max_dist)
-    return worst_matches
-
-
 ###############################################################################
 # Let's print the four worst matches, which will give
 # us an overview of the situation:
 
-print_worst_matches(df1, n=4)
+df1.sort_values("matching_score").head(4)
 
 ###############################################################################
 # We see that some matches were unsuccesful
@@ -199,7 +182,7 @@ df1 = fuzzy_join(
     match_score=0.35,
     return_score=True,
 )
-print_worst_matches(df1, n=4)
+df1.sort_values("matching_score").head(4)
 
 ###############################################################################
 # Matches that are not available (or precise enough) are marked as `NaN`.
@@ -428,7 +411,7 @@ encoder = make_column_transformer(
 pipeline = make_pipeline(fa, encoder, HistGradientBoostingRegressor())
 
 ##########################################################################
-# And the best part is that we are now able to evaluate the paramaters of the |fj|.
+# And the best part is that we are now able to evaluate the parameters of the |fj|.
 # For instance, the ``match_score`` was manually picked and can now be
 # introduced into a grid search:
 


Test patch:
diff --git a/dirty_cat/tests/test_table_vectorizer.py b/dirty_cat/tests/test_table_vectorizer.py
index 1add210..408d808 100644
--- a/dirty_cat/tests/test_table_vectorizer.py
+++ b/dirty_cat/tests/test_table_vectorizer.py
@@ -9,6 +9,7 @@ from sklearn.preprocessing import StandardScaler
 from sklearn.utils.validation import check_is_fitted
 
 from dirty_cat import GapEncoder, SuperVectorizer, TableVectorizer
+from dirty_cat._table_vectorizer import _infer_date_format
 from dirty_cat._utils import parse_version
 
 
@@ -521,3 +522,82 @@ def test_handle_unknown():
         with pytest.raises(ValueError, match="Found unknown categories"):
             table_vec.transform(x_unknown)
         table_vec.transform(x_known)
+
+
+def test__infer_date_format():
+    # Test with an ambiguous date format
+    # but with a single format that works for all rows
+    date_column = pd.Series(["01-01-2022", "13-01-2022", "01-03-2022"])
+    assert _infer_date_format(date_column) == "%d-%m-%Y"
+
+    date_column = pd.Series(["01-01-2022", "01-13-2022", "01-03-2022"])
+    assert _infer_date_format(date_column) == "%m-%d-%Y"
+
+    # Test with an ambiguous date format
+    # but several formats that work for all rows
+    date_column = pd.Series(["01-01-2022", "01-02-2019", "01-03-2019"])
+    # check that a warning is raised
+    with pytest.warns(UserWarning):
+        assert _infer_date_format(date_column) == "%m-%d-%Y"
+
+    # Test with irreconcilable date formats
+    date_column = pd.Series(["01-01-2022", "13-01-2019", "01-03-2022", "01-13-2019"])
+    assert _infer_date_format(date_column) is None
+
+    # Test previous cases with missing values
+
+    date_column = pd.Series(["01-01-2022", "13-01-2022", "01-03-2022", pd.NA])
+    assert _infer_date_format(date_column) == "%d-%m-%Y"
+
+    date_column = pd.Series(["01-01-2022", "01-13-2022", "01-03-2022", pd.NA])
+    assert _infer_date_format(date_column) == "%m-%d-%Y"
+
+    date_column = pd.Series(["01-01-2022", "01-02-2019", "01-03-2019", pd.NA])
+    # check that a warning is raised
+    with pytest.warns(UserWarning):
+        assert _infer_date_format(date_column) == "%m-%d-%Y"
+
+    date_column = pd.Series(
+        ["01-01-2022", "13-01-2019", "01-03-2022", "01-13-2019", pd.NA]
+    )
+    assert _infer_date_format(date_column) is None
+
+    # Test previous cases with hours and minutes
+
+    date_column = pd.Series(
+        ["01-01-2022 12:00", "13-01-2022 12:00", "01-03-2022 12:00"]
+    )
+    assert _infer_date_format(date_column) == "%d-%m-%Y %H:%M"
+
+    date_column = pd.Series(
+        ["01-01-2022 12:00", "01-13-2022 12:00", "01-03-2022 12:00"]
+    )
+    assert _infer_date_format(date_column) == "%m-%d-%Y %H:%M"
+
+    date_column = pd.Series(
+        ["01-01-2022 12:00", "01-02-2019 12:00", "01-03-2019 12:00"]
+    )
+    # check that a warning is raised
+    with pytest.warns(UserWarning):
+        assert _infer_date_format(date_column) == "%m-%d-%Y %H:%M"
+
+    date_column = pd.Series(
+        ["01-01-2022 12:00", "13-01-2019 12:00", "01-03-2022 12:00", "01-13-2019 12:00"]
+    )
+    assert _infer_date_format(date_column) is None
+
+    # Test with an empty column
+    date_column = pd.Series([], dtype="object")
+    assert _infer_date_format(date_column) is None
+
+    # Test with a column containing only NaN values
+    date_column = pd.Series([pd.NA, pd.NA, pd.NA])
+    assert _infer_date_format(date_column) is None
+
+    # Test with a column containing both dates and non-dates
+    date_column = pd.Series(["2022-01-01", "2022-01-02", "not a date"])
+    assert _infer_date_format(date_column) is None
+
+    # Test with a column containing more than two date formats
+    date_column = pd.Series(["2022-01-01", "01/02/2022", "20220103", "2022-Jan-04"])
+    assert _infer_date_format(date_column) is None