Repository: IAMconsortium/pyam
Pull request #597 (2021-12-23 08:53:15)

Allow IamDataFrame instance with mixed 'year' and 'datetime' domain
# Please confirm that this PR has done the following:

- [x] Tests Added

# Description of PR

This PR is a first step towards implementation of option 3 described in #596: allowing an **IamDataFrame** to have a "time" domain that has both "datetime" and "year" (as integer) values. The scope of this PR is very limited, to only allow initializing an **IamDataFrame** instance that has such a time domain. Filtering or returning the data in wide timeseries format is not tackled here (and fails in any instance that has a mixed index), to keep the PR reviewable (if anyone is interested).

The PR is directed to a feature-branch that will collect further work.

codecov[bot]: # [Codecov](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium) Report
> Merging [#597](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium) (852e374) into [feature/year-and-datetime](https://codecov.io/gh/IAMconsortium/pyam/commit/9f84bcd25f60dce9eb4678fbdb228836b6e5c86b?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium) (9f84bcd) will **decrease** coverage by `0.0%`.
> The diff coverage is `96.3%`.

[![Impacted file tree graph](https://codecov.io/gh/IAMconsortium/pyam/pull/597/graphs/tree.svg?width=650&height=150&src=pr&token=ThxWlNkonJ&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium)](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium)

```diff
@@                     Coverage Diff                     @@
##           feature/year-and-datetime    #597     +/-   ##
===========================================================
- Coverage                       94.2%   94.1%   -0.1%     
===========================================================
  Files                             50      50             
  Lines                           5331    5358     +27     
===========================================================
+ Hits                            5022    5047     +25     
- Misses                           309     311      +2     
```


| [Impacted Files](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium) | Coverage Δ | |
|---|---|---|
| [pyam/utils.py](https://codecov.io/gh/IAMconsortium/pyam/pull/597/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium#diff-cHlhbS91dGlscy5weQ==) | `91.4% <92.8%> (-0.4%)` | :arrow_down: |
| [pyam/core.py](https://codecov.io/gh/IAMconsortium/pyam/pull/597/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium#diff-cHlhbS9jb3JlLnB5) | `94.3% <100.0%> (ø)` | |
| [pyam/index.py](https://codecov.io/gh/IAMconsortium/pyam/pull/597/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium#diff-cHlhbS9pbmRleC5weQ==) | `98.2% <100.0%> (+0.1%)` | :arrow_up: |
| [tests/test\_index.py](https://codecov.io/gh/IAMconsortium/pyam/pull/597/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium#diff-dGVzdHMvdGVzdF9pbmRleC5weQ==) | `100.0% <100.0%> (ø)` | |
| [tests/test\_utils.py](https://codecov.io/gh/IAMconsortium/pyam/pull/597/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium#diff-dGVzdHMvdGVzdF91dGlscy5weQ==) | `100.0% <100.0%> (ø)` | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=footer&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium). Last update [9f84bcd...852e374](https://codecov.io/gh/IAMconsortium/pyam/pull/597?src=pr&el=lastupdated&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=IAMconsortium).

danielhuppmann: I compared performance of this branch against the current main with two large year-based dataset: slightly faster (~10%), but also higher memory use. This is because the consistency-check and casting to int/pd.Timestamp now operates on the index instead of the time column - but this requires creating a full copy of the index rather than a copy of only the time column.

Patch:
diff --git a/pyam/core.py b/pyam/core.py
index 22dc67e..d11b3ff 100755
--- a/pyam/core.py
+++ b/pyam/core.py
@@ -33,7 +33,6 @@ from pyam.utils import (
     read_file,
     read_pandas,
     format_data,
-    format_time_col,
     merge_meta,
     find_depth,
     pattern_match,
@@ -1199,8 +1198,9 @@ class IamDataFrame(object):
         value = kwargs[self.time_col]
         x = df.set_index(IAMC_IDX)
         x["value"] /= x[x[cols] == value]["value"]
+
         x = x.reset_index()
-        ret._data = format_time_col(x, self.time_col).set_index(self.dimensions).value
+        ret._data = x.set_index(self.dimensions).value
 
         if not inplace:
             return ret
@@ -2371,7 +2371,7 @@ class IamDataFrame(object):
             .sort_values(SORT_IDX)
             .reset_index(drop=True)
         )
-        ret._data = format_time_col(df, self.time_col).set_index(self.dimensions).value
+        ret._data = df.set_index(self.dimensions).value
 
         if not inplace:
             return ret
diff --git a/pyam/index.py b/pyam/index.py
index fb131d4..3896c7f 100644
--- a/pyam/index.py
+++ b/pyam/index.py
@@ -5,7 +5,7 @@ from pyam.logging import raise_data_error
 
 
 def get_index_levels(index, level):
-    """Return the category-values for a specific level"""
+    """Return the labels for a specific level"""
 
     if not isinstance(index, pd.Index):
         index = index.index  # assume that the arg `index` is a pd.DataFrame
@@ -21,8 +21,8 @@ def get_index_levels(index, level):
 
 def get_index_levels_codes(df, level):
     """Return the category-values and codes for a specific level"""
-    level = df.index._get_level_number(level)
-    return df.index.levels[level], df.index.codes[level]
+    n = df.index._get_level_number(level)
+    return df.index.levels[n], df.index.codes[n]
 
 
 def get_keep_col(codes, matches):
@@ -40,16 +40,16 @@ def get_keep_col(codes, matches):
     return np.isin(codes, matches)
 
 
-def replace_index_values(df, level, mapping, rows=None):
+def replace_index_values(df, name, mapping, rows=None):
     """Replace one or several category-values at a specific level (for specific rows)"""
     index = df if isinstance(df, pd.Index) else df.index
 
-    n = index._get_level_number(level)
+    n = index._get_level_number(name)
 
     # if replacing level values with a filter (by rows)
     if rows is not None and not all(rows):
         _levels = pd.Series(index.get_level_values(n))
-        renamed_index = replace_index_values(index[rows], level, mapping)
+        renamed_index = replace_index_values(index[rows], name, mapping)
         _levels[rows] = list(renamed_index.get_level_values(n))
         _unique_levels = pd.Index(_levels.unique())
 
@@ -57,7 +57,7 @@ def replace_index_values(df, level, mapping, rows=None):
             index=index.droplevel(n),
             codes=_unique_levels.get_indexer(_levels),
             level=_unique_levels,
-            name=level,
+            name=name,
             order=index.names,
         )
 
@@ -75,6 +75,16 @@ def replace_index_values(df, level, mapping, rows=None):
     return index.set_codes(_codes, level=n).set_levels(_unique_levels, level=n)
 
 
+def replace_index_labels(index, name, labels):
+    """Replace the levels for a specific level"""
+
+    order = index.names
+    n = index._get_level_number(name)
+    codes = index.codes[n]
+
+    return append_index_level(index.droplevel(n), codes, labels, name, order)
+
+
 def append_index_col(index, values, name, order=False):
     """Append a list of `values` as a new column (level) to an `index`"""
     levels = pd.Index(values).unique()
diff --git a/pyam/utils.py b/pyam/utils.py
index 77340c7..3e57c37 100644
--- a/pyam/utils.py
+++ b/pyam/utils.py
@@ -8,6 +8,7 @@ import datetime
 import dateutil
 import time
 
+from pyam.index import get_index_levels, replace_index_labels
 from pyam.logging import raise_data_error
 import numpy as np
 import pandas as pd
@@ -278,12 +279,12 @@ def format_data(df, index, **kwargs):
     # check whether data in wide format (IAMC) or long format (`value` column)
     if "value" in df.columns:
         # check if time column is given as `year` (int) or `time` (datetime)
-        if "year" in df.columns:
+        if "year" in df.columns and "time" not in df.columns:
             time_col = "year"
-        elif "time" in df.columns:
+        elif "time" in df.columns and "year" not in df.columns:
             time_col = "time"
         else:
-            raise ValueError("Invalid time format, must have either `year` or `time`!")
+            raise ValueError("Invalid time domain, must have either `year` or `time`!")
         extra_cols = [
             c
             for c in df.columns
@@ -294,29 +295,36 @@ def format_data(df, index, **kwargs):
         cols = [c for c in df.columns if c not in index + REQUIRED_COLS]
         year_cols, time_cols, extra_cols = [], [], []
         for i in cols:
+            # if the column name can be cast to integer, assume it's a year column
             try:
-                int(i)  # this is a year
+                int(i)
                 year_cols.append(i)
+
+            # otherwise, try casting to datetime
             except (ValueError, TypeError):
                 try:
-                    dateutil.parser.parse(str(i))  # this is datetime
+                    dateutil.parser.parse(str(i))
                     time_cols.append(i)
+
+                # neither year nor datetime, so it is an extra-column
                 except ValueError:
-                    extra_cols.append(i)  # some other string
+                    extra_cols.append(i)
+
         if year_cols and not time_cols:
             time_col = "year"
-            melt_cols = year_cols
-        elif not year_cols and time_cols:
-            time_col = "time"
-            melt_cols = time_cols
+            melt_cols = sorted(year_cols)
         else:
-            raise ValueError("Invalid time format, must be either years or `datetime`!")
-        cols = index + REQUIRED_COLS + extra_cols
+            time_col = "time"
+            melt_cols = sorted(year_cols) + sorted(time_cols)
+        if not melt_cols:
+            raise ValueError("Missing time domain")
+
+        # melt the dataframe
         df = pd.melt(
             df,
-            id_vars=cols,
+            id_vars=index + REQUIRED_COLS + extra_cols,
             var_name=time_col,
-            value_vars=sorted(melt_cols),
+            value_vars=melt_cols,
             value_name="value",
         )
 
@@ -342,13 +350,14 @@ def format_data(df, index, **kwargs):
         raise_data_error("Empty cells in `data`", df.loc[null_rows])
     del null_rows
 
-    # format the time-column
-    df = format_time_col(df, time_col)
-
     # cast to pd.Series, check for duplicates
     idx_cols = index + REQUIRED_COLS + [time_col] + extra_cols
     df = df.set_index(idx_cols).value
 
+    # format the time-column
+    _time = [to_time(i) for i in get_index_levels(df.index, time_col)]
+    df.index = replace_index_labels(df.index, time_col, _time)
+
     rows = df.index.duplicated()
     if any(rows):
         raise_data_error(
@@ -361,15 +370,6 @@ def format_data(df, index, **kwargs):
     return df.sort_index(), index, time_col, extra_cols
 
 
-def format_time_col(data, time_col):
-    """Format time_col to int (year) or datetime"""
-    if time_col == "year":
-        data["year"] = to_int(pd.to_numeric(data["year"]))
-    elif time_col == "time":
-        data["time"] = pd.to_datetime(data["time"])
-    return data
-
-
 def sort_data(data, cols):
     """Sort data rows and order columns by cols"""
     return data.sort_values(cols)[cols + ["value"]].reset_index(drop=True)
@@ -639,6 +639,29 @@ def print_list(x, n):
     return lst + count
 
 
+def to_time(x):
+    """Cast a value to either year (int) or datetime"""
+
+    # if the column name can be cast to integer, assume it's a year column
+    try:
+        j = int(x)
+        is_year = True
+
+    # otherwise, try casting to Timestamp (pandas-equivalent of datetime)
+    except (ValueError, TypeError):
+        try:
+            j = pd.Timestamp(x)
+            is_year = False
+        except ValueError:
+            raise ValueError(f"Invalid time domain: {x}")
+
+    # This is to guard against "years" with decimals (e.g., '2010.5')
+    if is_year and float(x) != j:
+        raise ValueError(f"Invalid time domain: {x}")
+
+    return j
+
+
 def to_int(x, index=False):
     """Formatting series or timeseries columns to int and checking validity
 
@@ -649,7 +672,7 @@ def to_int(x, index=False):
     cols = list(map(int, _x))
     error = _x[cols != _x]
     if not error.empty:
-        raise ValueError("invalid values `{}`".format(list(error)))
+        raise ValueError(f"Invalid values: {error}")
     if index:
         x.index = cols
         return x


Test patch:
diff --git a/tests/test_index.py b/tests/test_index.py
index 6fa2014..83afbd3 100644
--- a/tests/test_index.py
+++ b/tests/test_index.py
@@ -2,7 +2,12 @@ import pytest
 import pandas as pd
 import pandas.testing as pdt
 
-from pyam.index import get_index_levels, replace_index_values, append_index_level
+from pyam.index import (
+    get_index_levels,
+    replace_index_values,
+    replace_index_labels,
+    append_index_level,
+)
 from pyam import IAMC_IDX
 
 
@@ -60,3 +65,22 @@ def test_append_index():
         names=["region", "scenario"],
     )
     pdt.assert_index_equal(obs, exp)
+
+
+def test_replace_index_labels():
+    """Assert that replacing and re-ordering to an index works as expected"""
+
+    index = pd.MultiIndex(
+        codes=[[0, 0], [0, 1], [0, 0]],
+        levels=[["World"], ["scen_a", "scen_b"], ["foo"]],
+        names=["region", "scenario", "variable"],
+    )
+
+    obs = replace_index_labels(index, "scenario", ["A", "B"])
+
+    exp = pd.MultiIndex(
+        codes=[[0, 0], [0, 1], [0, 0]],
+        levels=[["World"], ["A", "B"], ["foo"]],
+        names=["region", "scenario", "variable"],
+    )
+    pdt.assert_index_equal(obs, exp)
diff --git a/tests/test_utils.py b/tests/test_utils.py
index 6cbb30d..fe9c8b9 100644
--- a/tests/test_utils.py
+++ b/tests/test_utils.py
@@ -2,6 +2,8 @@ import pytest
 import pandas as pd
 import numpy as np
 from pandas import testing as pdt
+from pandas import Timestamp
+from datetime import datetime
 
 from pyam import utils, META_IDX
 
@@ -247,3 +249,21 @@ def test_get_variable_components_joinTRUE():
 
 def test_get_variable_components_joinstr():
     assert utils.get_variable_components("foo|bar|baz", [2, 1], join="_") == "baz_bar"
+
+
+@pytest.mark.parametrize(
+    "x, exp",
+    [
+        ("2", 2),
+        ("2010-07-10", Timestamp("2010-07-10 00:00")),
+        (datetime(2010, 7, 10), Timestamp("2010-07-10 00:00")),
+    ],
+)
+def test_to_time(x, exp):
+    assert utils.to_time(x) == exp
+
+
+@pytest.mark.parametrize("x", [2.5, "2010-07-10 foo"])
+def test_to_time_raises(x):
+    with pytest.raises(ValueError, match=f"Invalid time domain: {x}"):
+        utils.to_time(x)