Repository: IAMconsortium/pyam

Issue:
Proposal to read in multiple sheets (from Excel)
Planning to extend `pyam.IamDataFrame()` such that if the excel file has multiple sheets, they are each read in and concatenated.

Proposal would be to either:

- accept sheets if starting with "data", e.g. data1, data2, etc.. (could be automatically?)
- accept a`str` or `list` of sheetnames, e.g. `sheetname=...`

Any preferences or other suggestions?

Discussion:

Patch:
diff --git a/RELEASE_NOTES.md b/RELEASE_NOTES.md
index 39c8af7..e797332 100644
--- a/RELEASE_NOTES.md
+++ b/RELEASE_NOTES.md
@@ -1,5 +1,14 @@
 # Next Release
 
+## API changes
+
+PR [#488](https://github.com/IAMconsortium/pyam/pull/488) changes the default
+behavior when initializing an IamDataFrame from xlsx: now, all sheets names
+starting with `data` will be parsed for timeseries data.
+
+## Individual updates
+
+- [#488](https://github.com/IAMconsortium/pyam/pull/488) Read all sheets starting with `data` when reading from xlsx
 - [#481](https://github.com/IAMconsortium/pyam/pull/481) Enable custom index columns
 - [#477](https://github.com/IAMconsortium/pyam/pull/477) Add a nightly test suite
 - [#476](https://github.com/IAMconsortium/pyam/pull/476) Add docstrings to plotting functions `df.plot.<kind>()`
diff --git a/pyam/core.py b/pyam/core.py
index 48ecad0..08c88fb 100755
--- a/pyam/core.py
+++ b/pyam/core.py
@@ -98,9 +98,10 @@ class IamDataFrame(object):
     R-style integer column headers (i.e., `X2015`) are acceptable.
 
     When initializing an :class:`IamDataFrame` from an xlsx file,
-    |pyam| will per default look for the sheets 'data' and 'meta' to
-    populate the respective tables. Custom sheet names can be specified with
-    kwargs :code:`sheet_name` ('data') and :code:`meta_sheet_name` ('meta').
+    |pyam| will per default parse all sheets starting with 'data'
+    for timeseries and a sheet 'meta' to populate the respective table.
+    Custom sheet names can be specified with kwargs :code:`sheet_name` ('data')
+    and :code:`meta_sheet_name` ('meta').
     Calling the class with :code:`meta_sheet_name=False` will
     skip the import of the 'meta' table.
 
@@ -1681,9 +1682,9 @@ class IamDataFrame(object):
         sheet_name : str
             name of sheet which will contain dataframe of 'meta' indicators
         """
+        close = False
         if not isinstance(excel_writer, pd.ExcelWriter):
-            close = True
-            excel_writer = pd.ExcelWriter(excel_writer)
+            excel_writer, close = pd.ExcelWriter(excel_writer), True
         write_sheet(excel_writer, sheet_name, self.meta, index=True)
         if close:
             excel_writer.close()
@@ -1721,16 +1722,18 @@ class IamDataFrame(object):
         # return the package (needs to reloaded because `tmp` was deleted)
         return Package(path)
 
-    def load_meta(self, path, *args, **kwargs):
+    def load_meta(self, path, sheet_name='meta', *args, **kwargs):
         """Load 'meta' indicators from file
 
         Parameters
         ----------
         path : str or path object
-            any valid string path or :class:`pathlib.Path`
+            Any valid string path or :class:`pathlib.Path`
+        sheet_name : str, optional
+            Name of the sheet to be parsed
         """
         # load from file
-        df = read_pandas(Path(path), default_sheet='meta', *args, **kwargs)
+        df = read_pandas(Path(path), sheet_name=sheet_name, *args, **kwargs)
 
         # cast model-scenario column headers to lower-case (if necessary)
         df = df.rename(columns=dict([(i.capitalize(), i) for i in META_IDX]))
diff --git a/pyam/utils.py b/pyam/utils.py
index 6972d1a..84ccbf5 100644
--- a/pyam/utils.py
+++ b/pyam/utils.py
@@ -111,15 +111,20 @@ def write_sheet(writer, name, df, index=False):
             pass
 
 
-def read_pandas(path, default_sheet='data', *args, **kwargs):
+def read_pandas(path, sheet_name='data*', *args, **kwargs):
     """Read a file and return a pandas.DataFrame"""
     if isinstance(path, Path) and path.suffix == '.csv':
         df = pd.read_csv(path, *args, **kwargs)
     else:
         xl = pd.ExcelFile(path)
-        if len(xl.sheet_names) > 1 and 'sheet_name' not in kwargs:
-            kwargs['sheet_name'] = default_sheet
-        df = pd.read_excel(path, *args, **kwargs)
+        sheet_names = pd.Series(xl.sheet_names)
+        if len(sheet_names) > 1:
+            sheets = kwargs.pop('sheet_name', sheet_name)
+            # apply pattern-matching for sheet names (use * as wildcard)
+            sheets = sheet_names[pattern_match(sheet_names, values=sheets)]
+            df = pd.concat([xl.parse(s, *args, **kwargs) for s in sheets])
+        else:
+            df = pd.read_excel(path, *args, **kwargs)
 
         # remove unnamed and empty columns
         empty_cols = [c for c in df.columns if str(c).startswith('Unnamed: ')
@@ -414,8 +419,7 @@ def pattern_match(data, values, level=None, regexp=False, has_nan=False):
     for filtering (str, int, bool)
     """
     matches = np.array([False] * len(data))
-    if not isinstance(values, Iterable) or isstr(values):
-        values = [values]
+    values = values if islistable(values) else [values]
 
     # issue (#40) with string-to-nan comparison, replace nan by empty string
     _data = data.copy()


Test patch:
diff --git a/tests/test_io.py b/tests/test_io.py
index ddd33c5..faa974a 100644
--- a/tests/test_io.py
+++ b/tests/test_io.py
@@ -54,9 +54,6 @@ def test_io_csv(test_df, tmpdir):
     [dict(include_meta='foo'), dict(meta_sheet_name='foo')]
 ])
 def test_io_xlsx(test_df, meta_args, tmpdir):
-    # add column to `meta`
-    test_df.set_meta(['a', 'b'], 'string')
-
     # write to xlsx (direct file name and ExcelWriter, see #300)
     file = tmpdir / 'testing_io_write_read.xlsx'
     for f in [file, pd.ExcelWriter(file)]:
@@ -67,10 +64,30 @@ def test_io_xlsx(test_df, meta_args, tmpdir):
         # read from xlsx
         import_df = IamDataFrame(file, **meta_args[1])
 
-        # assert that IamDataFrame instances are equal and delete file
+        # assert that IamDataFrame instances are equal
         assert_iamframe_equal(test_df, import_df)
 
 
+@pytest.mark.parametrize("sheets, sheetname", [
+    [['data1', 'data2'], dict(sheet_name='data*')],
+    [['data1', 'foo'], dict(sheet_name=['data*', 'foo'])]
+])
+def test_io_xlsx_multiple_data_sheets(test_df, sheets, sheetname, tmpdir):
+    # write data to separate sheets in excel file
+    file = tmpdir / 'testing_io_write_read.xlsx'
+    xl = pd.ExcelWriter(file)
+    for i, (model, scenario) in enumerate(test_df.index):
+        test_df.filter(scenario=scenario).to_excel(xl, sheet_name=sheets[i])
+    test_df.export_meta(xl)
+    xl.close()
+
+    # read from xlsx
+    import_df = IamDataFrame(file, **sheetname)
+
+    # assert that IamDataFrame instances are equal
+    assert_iamframe_equal(test_df, import_df)
+
+
 def test_init_df_with_na_unit(test_pd_df, tmpdir):
     # missing values in the unit column are replaced by an empty string
     test_pd_df.loc[1, 'unit'] = np.nan