Repository: IAMconsortium/pyam
Pull request #65 (2018-06-22 08:58:25)

add a `filter_by_meta() ` function
This PR implements a function to join a generic pd.DataFrame (indexed on `['model', 'scenario']`) with specific metadata columns from an IamDataFrame and apply filters on each column.

This is useful in the following workflow:
1. retrieve a pd.DataFrame from an IamDataFrame using `timeseries()`
2. do some computation on the df
3. add relevant metadata like categories from the original IamDataFrame
4. continue working on the df, eg using `groupby()`on the categories

This is sort-of possible with `as_pandas()` and `filter()`, but quite cumbersome, because `as_pandas()` returns value-columns (years)  and metadata columns (Step 1 above). So one would have to make some bypass for step 2 to just operate on the year-columns. I found the workflow described above more flexible and also easier to read (because less bypassing required).

The use of `col=None` for only joining a column without filter is modelled on `slice(None)` of `pd.MultiIndex`

gidden: Ok, conceptually I get it now. Do we want to join the metadata though? Or perhaps that should be optional?
gidden: Ok, I think I see where the implementation is going. It might be simpler to

- subset metadata for values in kwargs first
- extract the model/scenario index from the subset dataframe
- return data subset with subset index

Instead of joining the dataframes first, then doing the subset (also more computationally expensive)
gidden: And can you please also add to release notes! =)
danielhuppmann: Re whether joining is optional: I would make it the default (because in my use cases, I usually want to filter and do something else), but we can make it optional to not have it joined (just perform the filter). What would be a good kwarg-name, `join_meta`?
danielhuppmann: note to myself: if no index is given, check if `model` and `scenario` columns exist and create index from that
gidden: let's talk about this briefly when we meet tomorrow morning.
danielhuppmann: sticklered, rebased, release-noted, error-messaged, and a more efficient workflow (first filter, then join optionally)

and it now also accepts `['model', 'scenario']` as columns instead of index and returns the df in the same way as it was inputted (and yes, there is a test for that, too)

@gidden, good to go once the tests pass

Patch:
diff --git a/RELEASE_NOTES.md b/RELEASE_NOTES.md
index f9bb396..8f79b05 100644
--- a/RELEASE_NOTES.md
+++ b/RELEASE_NOTES.md
@@ -1,4 +1,5 @@
 
 # Next Release
 
-- (#66)[https://github.com/IAMconsortium/pyam/pull/66] Fixes a bug in the `interpolate()` function ( duplication of data points if already defined)
+- (#66)[https://github.com/IAMconsortium/pyam/pull/66] Fixes a bug in the `interpolate()` function (duplication of data points if already defined)
+- (#65)[https://github.com/IAMconsortium/pyam/pull/65] Add a `filter_by_meta()` function to filter/join a pd.DataFrame with an IamDataFrame.meta table
diff --git a/pyam/core.py b/pyam/core.py
index b6404a3..b88aecf 100644
--- a/pyam/core.py
+++ b/pyam/core.py
@@ -958,3 +958,47 @@ def check_aggregate(df, variable, components=None, units=None,
                                   multiplier=multiplier)
         df.meta['exclude'] |= fdf.meta['exclude']  # update if any excluded
         return vdf
+
+
+def filter_by_meta(data, df, join_meta=False, **kwargs):
+    """Filter by and join meta columns from an IamDataFrame to a pd.DataFrame
+
+    Parameters
+    ----------
+    data: pd.DataFrame instance
+        DataFrame to which meta columns are to be joined,
+        index or columns must include `['model', 'scenario']`
+    df: IamDataFrame instance
+        IamDataFrame from which meta columns are filtered and joined (optional)
+    join_meta: bool, default False
+        join selected columns from `df.meta` on `data`
+    kwargs:
+        meta columns to be joined, where `col=...` applies filters
+        by the given arguments (using `utils.pattern_match()`) and `col=None`
+        joins the column without filtering
+    """
+    if not set(META_IDX).issubset(data.index.names + list(data.columns)):
+        raise ValueError('missing required index dimensions or columns!')
+
+    meta = df.meta[list(kwargs)].copy()
+
+    # filter meta by columns
+    keep = np.array([True] * len(meta))
+    for col, values in kwargs.items():
+        if values is not None:
+            keep_col = pattern_match(meta[col], values)
+            keep &= keep_col
+    meta = meta[keep]
+
+    # set the data index to META_IDX and apply filtered meta index
+    data = data.copy()
+    idx = list(data.index.names) if not data.index.names == [None] else None
+    data = data.reset_index().set_index(META_IDX).loc[meta.index]
+
+    # join meta (optional), reset index to format as input arg
+    data = data.join(meta) if join_meta else data
+    data = data.reset_index().set_index(idx or 'index')
+    if idx is None:
+        data.index.name = None
+
+    return data


Test patch:
diff --git a/tests/test_core.py b/tests/test_core.py
index c14980c..28e2aea 100644
--- a/tests/test_core.py
+++ b/tests/test_core.py
@@ -7,7 +7,7 @@ import pandas as pd
 from numpy import testing as npt
 
 from pyam import IamDataFrame, plotting, validate, categorize, \
-    require_variable, check_aggregate
+    require_variable, check_aggregate, filter_by_meta, META_IDX
 from pyam.core import _meta_idx
 
 from conftest import TEST_DATA_DIR
@@ -561,3 +561,46 @@ def test_convert_unit():
     )).data.reset_index(drop=True)
 
     pd.testing.assert_frame_equal(obs, exp, check_index_type=False)
+
+
+def test_pd_filter_by_meta(meta_df):
+    data = pd.DataFrame([
+        ['a_model', 'a_scenario', 'a_region1', 1],
+        ['a_model', 'a_scenario', 'a_region2', 2],
+        ['a_model', 'a_scenario2', 'a_region3', 3],
+    ], columns=['model', 'scenario', 'region', 'col']
+    ).set_index(['model', 'region'])
+
+    meta_df.set_meta([True, False], 'boolean')
+    meta_df.set_meta(0, 'integer')
+
+    obs = filter_by_meta(data, meta_df, join_meta=True,
+                         boolean=True, integer=None)
+    obs = obs.reindex(columns=['scenario', 'col', 'boolean', 'integer'])
+
+    exp = data.iloc[0:2].copy()
+    exp['boolean'] = True
+    exp['integer'] = 0
+
+    pd.testing.assert_frame_equal(obs, exp)
+
+
+def test_pd_filter_by_meta_no_index(meta_df):
+    data = pd.DataFrame([
+        ['a_model', 'a_scenario', 'a_region1', 1],
+        ['a_model', 'a_scenario', 'a_region2', 2],
+        ['a_model', 'a_scenario2', 'a_region3', 3],
+    ], columns=['model', 'scenario', 'region', 'col'])
+
+    meta_df.set_meta([True, False], 'boolean')
+    meta_df.set_meta(0, 'int')
+
+    obs = filter_by_meta(data, meta_df, join_meta=True,
+                         boolean=True, int=None)
+    obs = obs.reindex(columns=META_IDX + ['region', 'col', 'boolean', 'int'])
+
+    exp = data.iloc[0:2].copy()
+    exp['boolean'] = True
+    exp['int'] = 0
+
+    pd.testing.assert_frame_equal(obs, exp)