Repository: FlexMeasures/flexmeasures
Pull request #831 (2023-08-24 10:35:42)

Feature/datetime unit conversions with dayfirst and timezone
## Description

This PR is about some special cases of unit conversion, used primarily when importing data from file: event values that themselves represent durations or timestamps that need to be converted to quantitative values. Two features:
1. The first feature deals with naive datetimes, that need to be localized to the timezone of the sensor.
2. The second feature deals with datetimes that are in a dayfirst format.

## Look & Feel

```
import pandas as pd
from flexmeasures.utils.unit_utils import convert_units

data = pd.Series(["01-01-1970", "02-01-1970", "03-01-1970"])
converted_data = convert_units(data, from_unit="dayfirst datetime", to_unit="s")
expected_data = pd.Series([0, 60 * 60 * 24, 60 * 60 * 48])
pd.testing.assert_series_equal(converted_data, expected_data)  # should be True
```

For the issue of localizing to a timezone issue, simply pass a `BeliefsSeries` instance (or give the Series instance a `sensor` atrribute, which itself should have a `timezone` attribute).

## How to test

Additional test cases for unit conversion are added in this PR.

## Further Improvements

I'm just listing ideas here, but none of these require an immediate follow-up.

The `infer_datetime_format` of `pd.to_datetime` isn't smart enough for our purposes. For example:

```
>>> pd.to_datetime(pd.Series(["01-01-2023 00:00", "01-01-2023 12:00", "02-01-2023 00:00", "02-01-2023 12:00", "03-01-2023 00:00", "03-01-2023 12:00",]), infer_datetime_format=True)
0   2023-01-01 00:00:00
1   2023-01-01 12:00:00
2   2023-02-01 00:00:00
3   2023-02-01 12:00:00
4   2023-03-01 00:00:00
5   2023-03-01 12:00:00
dtype: datetime64[ns]
```
This time series can be interpreted in both dayfirst and monthfirst format, but only the dayfirst leads to a steady frequency, whereas the monthfirst is chosen by `pd.to_datetime`.

It would be quite nice if the `dayfirst` parameter passed to `pd.to_datetime` could be derived automatically, also for the case of a `from_unit="datetime"`. This should be possible under the assumption that all of the input data is formatted in the same way, but even then only possible if a date of >12 is present within the data. This is what `infer_datetime_format` already does well. As a fallback, the one leading to a steady frequency should be preferred (at least in the context of FlexMeasures). And if that still leads to a tie, I would pick the one that leads to a chronological order. And if that ties, I'd raise (which for the use case of importing data, means the user could be asked to either update the input data, or use the "dayfirst datetime", which we could keep supporting).


---

- [x] I agree to contribute to the project under Apache 2 License. 
- [x] To the best of my knowledge, the proposed patch is not based on code under GPL or other license that is incompatible with FlexMeasures



Patch:
diff --git a/documentation/changelog.rst b/documentation/changelog.rst
index b2d6ab21..dea42ad7 100644
--- a/documentation/changelog.rst
+++ b/documentation/changelog.rst
@@ -3,7 +3,7 @@
 FlexMeasures Changelog
 **********************
 
-v0.15.1 | August XX, 2023
+v0.16.0 | September XX, 2023
 ============================
 
 New features
@@ -11,6 +11,14 @@ New features
 
 * Introduce new reporter to compute profit/loss due to electricity flows: `ProfitOrLossReporter` [see `PR #808 <https://github.com/FlexMeasures/flexmeasures/pull/808>`_]
 
+Infrastructure / Support
+----------------------
+
+* Allow additional datetime conversions to quantitative time units, specifically, from timezone-naive and/or dayfirst datetimes, which can be useful when importing data [see `PR #831 <https://github.com/FlexMeasures/flexmeasures/pull/831>`_]
+
+
+v0.15.1 | August XX, 2023
+============================
 
 Bugfixes
 -----------
diff --git a/flexmeasures/utils/unit_utils.py b/flexmeasures/utils/unit_utils.py
index 00b4ba1f..806ae38f 100644
--- a/flexmeasures/utils/unit_utils.py
+++ b/flexmeasures/utils/unit_utils.py
@@ -239,10 +239,15 @@ def _convert_time_units(
     if not to_unit[0].isdigit():
         # unit abbreviations passed to pd.Timedelta need a number (so, for example, h becomes 1h)
         to_unit = f"1{to_unit}"
-    if from_unit == "datetime":
-        return (
-            pd.to_datetime(data, utc=True) - pd.Timestamp("1970-01-01", tz="utc")
-        ) // pd.Timedelta(to_unit)
+    if "datetime" in from_unit:
+        dt_data = pd.to_datetime(
+            data, dayfirst=True if "dayfirst" in from_unit else False
+        )
+        # localize timezone naive data to the sensor's timezone, if available
+        if dt_data.dt.tz is None:
+            timezone = data.sensor.timezone if hasattr(data, "sensor") else "utc"
+            dt_data = dt_data.dt.tz_localize(timezone)
+        return (dt_data - pd.Timestamp("1970-01-01", tz="utc")) // pd.Timedelta(to_unit)
     else:
         return data / pd.Timedelta(to_unit)
 
@@ -254,8 +259,14 @@ def convert_units(
     event_resolution: timedelta | None = None,
     capacity: str | None = None,
 ) -> pd.Series | list[int | float] | int | float:
-    """Updates data values to reflect the given unit conversion."""
-    if from_unit in ("datetime", "timedelta"):
+    """Updates data values to reflect the given unit conversion.
+
+    Handles units in short scientific notation (e.g. m³/h, kW, and ºC), as well as three special units to convert from:
+    - from_unit="datetime"          (with data point such as "2023-05-02", "2023-05-02 05:14:49" or "2023-05-02 05:14:49 +02:00")
+    - from_unit="dayfirst datetime" (with data point such as "02-05-2023")
+    - from_unit="timedelta"         (with data point such as "0 days 01:18:25")
+    """
+    if from_unit in ("datetime", "dayfirst datetime", "timedelta"):
         return _convert_time_units(data, from_unit, to_unit)
 
     if from_unit != to_unit:


Test patch:
diff --git a/flexmeasures/utils/tests/test_unit_utils.py b/flexmeasures/utils/tests/test_unit_utils.py
index 9390b55a..2b547cd3 100644
--- a/flexmeasures/utils/tests/test_unit_utils.py
+++ b/flexmeasures/utils/tests/test_unit_utils.py
@@ -4,6 +4,7 @@ import pint.errors
 import pytest
 
 import pandas as pd
+import timely_beliefs as tb
 
 from flexmeasures.utils.unit_utils import (
     convert_units,
@@ -64,6 +65,115 @@ def test_convert_unit(
     pd.testing.assert_series_equal(converted_data, expected_data)
 
 
+@pytest.mark.parametrize(
+    "from_unit, to_unit, timezone, input_values, expected_values",
+    [
+        # datetimes are converted to seconds since UNIX epoch
+        (
+            "datetime",
+            "s",
+            None,
+            ["1970-01-01", "1970-01-02", "1970-01-03"],
+            [0, 60 * 60 * 24, 60 * 60 * 48],
+        ),
+        # nothing overflows for the next 100 years
+        (
+            "datetime",
+            "s",
+            None,
+            ["2123-05-02", "2123-05-03", "2123-05-04"],
+            [4838659200, 4838659200 + 60 * 60 * 24, 4838659200 + 60 * 60 * 48],
+        ),
+        # Same as above, but day precedes month in input
+        (
+            "dayfirst datetime",
+            "s",
+            None,
+            ["02-05-2123", "03-05-2123", "04-05-2123"],
+            [4838659200, 4838659200 + 60 * 60 * 24, 4838659200 + 60 * 60 * 48],
+        ),
+        # Localize timezone-naive datetimes to UTC in case there is no sensor information available
+        (
+            "datetime",
+            "s",
+            None,
+            ["2023-05-02 00:00:01", "2023-05-02 00:00:02", "2023-05-02 00:00:03"],
+            [1682985601, 1682985602, 1682985603],
+        ),
+        # Localize timezone-naive datetimes to sensor's timezone in case that is available
+        (
+            "datetime",
+            "s",
+            "Europe/Amsterdam",
+            ["2023-05-02 00:00:01", "2023-05-02 00:00:02", "2023-05-02 00:00:03"],
+            [
+                1682985601 - 60 * 60 * 2,
+                1682985602 - 60 * 60 * 2,
+                1682985603 - 60 * 60 * 2,
+            ],
+        ),
+        # Timezone-aware datetimes work don't require localization
+        (
+            "datetime",
+            "s",
+            None,
+            [
+                "2023-05-02 00:00:01 +02:00",
+                "2023-05-02 00:00:02 +02:00",
+                "2023-05-02 00:00:03 +02:00",
+            ],
+            [
+                1682985601 - 60 * 60 * 2,
+                1682985602 - 60 * 60 * 2,
+                1682985603 - 60 * 60 * 2,
+            ],
+        ),
+        # Timezone-aware datetimes also means that the sensor timezone is irrelevant
+        (
+            "datetime",
+            "s",
+            "Asia/Seoul",
+            [
+                "2023-05-02 00:00:01 +02:00",
+                "2023-05-02 00:00:02 +02:00",
+                "2023-05-02 00:00:03 +02:00",
+            ],
+            [
+                1682985601 - 60 * 60 * 2,
+                1682985602 - 60 * 60 * 2,
+                1682985603 - 60 * 60 * 2,
+            ],
+        ),
+        # Timedeltas can be converted to units of time
+        ("timedelta", "s", None, ["1 minute", "1 minute 2 seconds"], [60.0, 62.0]),
+        # Convertible timedeltas include absolute days of 24 hours
+        ("timedelta", "d", None, ["1 day", "1 day 12 hours"], [1.0, 1.5]),
+        # Convertible timedeltas exclude nominal durations like month or year, which cannot be represented as a datetime.timedelta object
+        # ("timedelta", "d", None, ["1 month", "1 year"], [30., 365.]),  # fails
+    ],
+)
+def test_convert_special_unit(
+    from_unit,
+    to_unit,
+    timezone,
+    input_values,
+    expected_values,
+):
+    """Check some special unit conversions."""
+    data = pd.Series(input_values)
+    if timezone:
+        data.sensor = tb.Sensor("test", timezone=timezone)
+    converted_data: pd.Series = convert_units(
+        data=data,
+        from_unit=from_unit,
+        to_unit=to_unit,
+    )
+    print(converted_data)
+    expected_data = pd.Series(expected_values)
+    print(expected_data)
+    pd.testing.assert_series_equal(converted_data, expected_data)
+
+
 @pytest.mark.parametrize(
     "unit, time_unit, expected_unit",
     [