Repository: calliope-project/calliope (language: python)

Issue:
Multiple rows and columns introduce false index items
### What happened?

Loading a file with multiple rows and multiple columns adds fake indexes sometimes.

See the file:
```csv
vintagesteps,2020,2030,2040,2050,2030,2040,2050,2040,2050,2050
investsteps,2020,2020,2020,2020,2030,2030,2030,2040,2040,2050
techs,,,,,,,,,,
geothermal,1,1,1,1,1,1,1,1,1,1
hydropower,1,1,1,1,1,1,1,1,1,1
waste,1,1,0.0,0,1,1,0.0,1,1,1
bioenergy,1,1,0.8,0,1,1,0.8,1,1,1
oil,1,1,1,1,1,1,1,1,1,1
coal,1,1,1,1,1,1,1,1,1,1
ccgt,1,1,0.0,0,1,1,0.0,1,1,1
wind,1,1,0.0,0,1,1,0.0,1,1,1
pv,1,1,1,0.2,1,1,1,1,1,1
battery_li,1,1,0.5,0,1,1,0.5,1,1,1
battery_phs,1,1,1,1,1,1,1,1,1,1
```

Loaded via:
```yaml
  vintage_availability_techs:
    source: data_sources/investstep_series/available_vintages_techs.csv
    rows: techs
    columns: [vintagesteps, investsteps]
    add_dimensions:
      parameters: available_vintages
```

In this case, a fake index called `techs` will be added.

### Which operating systems have you used?

- [ ] macOS
- [ ] Windows
- [X] Linux

### Version

v0.7

### Relevant log output

_No response_
Multiple rows and columns introduce false index items
### What happened?

Loading a file with multiple rows and multiple columns adds fake indexes sometimes.

See the file:
```csv
vintagesteps,2020,2030,2040,2050,2030,2040,2050,2040,2050,2050
investsteps,2020,2020,2020,2020,2030,2030,2030,2040,2040,2050
techs,,,,,,,,,,
geothermal,1,1,1,1,1,1,1,1,1,1
hydropower,1,1,1,1,1,1,1,1,1,1
waste,1,1,0.0,0,1,1,0.0,1,1,1
bioenergy,1,1,0.8,0,1,1,0.8,1,1,1
oil,1,1,1,1,1,1,1,1,1,1
coal,1,1,1,1,1,1,1,1,1,1
ccgt,1,1,0.0,0,1,1,0.0,1,1,1
wind,1,1,0.0,0,1,1,0.0,1,1,1
pv,1,1,1,0.2,1,1,1,1,1,1
battery_li,1,1,0.5,0,1,1,0.5,1,1,1
battery_phs,1,1,1,1,1,1,1,1,1,1
```

Loaded via:
```yaml
  vintage_availability_techs:
    source: data_sources/investstep_series/available_vintages_techs.csv
    rows: techs
    columns: [vintagesteps, investsteps]
    add_dimensions:
      parameters: available_vintages
```

In this case, a fake index called `techs` will be added.

### Which operating systems have you used?

- [ ] macOS
- [ ] Windows
- [X] Linux

### Version

v0.7

### Relevant log output

_No response_


Discussion / hints:
Another example:

This one fails. Removing the header fixes the issue. Interestingly, the behavior changes depending on whether or not you are using a debugger.

```csv
nodes,techs,parameters,values
NORD,ccgt,initial_flow_cap,20000000
NORD,hydropower,initial_flow_cap,11191600
NORD,wind,initial_flow_cap,115600
NORD,pv,initial_flow_cap,8319100
NORD,battery_phs,initial_flow_cap,5064300
NORD,battery_phs,initial_storage_cap,469050200
NORD,waste,initial_flow_cap,384700
NORD,bioenergy,initial_flow_cap,2159900
CNOR,ccgt,initial_flow_cap,2000000
CNOR,hydropower,initial_flow_cap,1100900
CNOR,wind,initial_flow_cap,133600
CNOR,pv,initial_flow_cap,2270800
CNOR,waste,initial_flow_cap,23000
```

```yaml
data_sources:
  # Initial setup
  initial_tech_capacity_params:
    source: data_sources/initial_capacity_techs_kw.csv
    rows: [nodes, techs, parameters]
```
OK, so this is a limitation of what we can ask of pandas.

A workaround:

```yaml
data_sources:
  # Initial setup
  initial_tech_capacity_params:
    source: data_sources/initial_capacity_techs_kw.csv
    rows: [nodes, techs, parameters]
    columns: [values]
    drop: values
```
I can only reproduce this issue with your second example. The first one loads just fine.
> I can only reproduce this issue with your second example. The first one loads just fine.

Odd, that's the one I saw as most problematic. I'll give an update if I can reproduce it...

For the second: I would like to propose that this type of "dropping" should be the standard, to ensure the files given to the model are "stand alone". Otherwise, you'd need to always consult two files. This way, you have good data practices "baked in".

What do you think?
It's impossible to make it standard as we have to know whether the top row
is data or not. As soon as you say it isn't data (columns: [...]) then it
becomes an index / column and you wouldn't want that data deleted
automatically if you actually _wanted_ it to be part of you model. So the
only way to handle it consistently is to force the user to be explicit or
to tell them that there must _always_ be a header and that if they miss it
then their top row of data may be silently(?) lost...

On Wed, 21 Feb 2024, 19:35 Ivan Ruiz Manuel, ***@***.***>
wrote:

> I can only reproduce this issue with your second example. The first one
> loads just fine.
>
> Odd, that's the one I saw as most problematic. I'll give an update if I
> can reproduce it...
>
> For the second: I would like to propose that this type of "dropping"
> should be the standard, to ensure the files given to the model are "stand
> alone". Otherwise, you'd need to always consult two files. This way, you
> have good data practices "baked in".
>
> What do you think?
>
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/calliope-project/calliope/issues/573#issuecomment-1957765430>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AEDB63XQKJEQUYE5ON7NDI3YUZEBNAVCNFSM6AAAAABDTMI46KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNJXG43DKNBTGA>
> .
> You are receiving this because you commented.Message ID:
> ***@***.***>
>

Hmmm, that is true...
The only way to make it possible would be to force one type of table (i.e. rows only), which would make the input very inflexible).
Plan: enforce a header to always exist in a CSV, even if it is just one row. We will set `header=0` as the bare minimum internally. 
Another example:

This one fails. Removing the header fixes the issue. Interestingly, the behavior changes depending on whether or not you are using a debugger.

```csv
nodes,techs,parameters,values
NORD,ccgt,initial_flow_cap,20000000
NORD,hydropower,initial_flow_cap,11191600
NORD,wind,initial_flow_cap,115600
NORD,pv,initial_flow_cap,8319100
NORD,battery_phs,initial_flow_cap,5064300
NORD,battery_phs,initial_storage_cap,469050200
NORD,waste,initial_flow_cap,384700
NORD,bioenergy,initial_flow_cap,2159900
CNOR,ccgt,initial_flow_cap,2000000
CNOR,hydropower,initial_flow_cap,1100900
CNOR,wind,initial_flow_cap,133600
CNOR,pv,initial_flow_cap,2270800
CNOR,waste,initial_flow_cap,23000
```

```yaml
data_sources:
  # Initial setup
  initial_tech_capacity_params:
    source: data_sources/initial_capacity_techs_kw.csv
    rows: [nodes, techs, parameters]
```
OK, so this is a limitation of what we can ask of pandas.

A workaround:

```yaml
data_sources:
  # Initial setup
  initial_tech_capacity_params:
    source: data_sources/initial_capacity_techs_kw.csv
    rows: [nodes, techs, parameters]
    columns: [values]
    drop: values
```
I can only reproduce this issue with your second example. The first one loads just fine.
> I can only reproduce this issue with your second example. The first one loads just fine.

Odd, that's the one I saw as most problematic. I'll give an update if I can reproduce it...

For the second: I would like to propose that this type of "dropping" should be the standard, to ensure the files given to the model are "stand alone". Otherwise, you'd need to always consult two files. This way, you have good data practices "baked in".

What do you think?
It's impossible to make it standard as we have to know whether the top row
is data or not. As soon as you say it isn't data (columns: [...]) then it
becomes an index / column and you wouldn't want that data deleted
automatically if you actually _wanted_ it to be part of you model. So the
only way to handle it consistently is to force the user to be explicit or
to tell them that there must _always_ be a header and that if they miss it
then their top row of data may be silently(?) lost...

On Wed, 21 Feb 2024, 19:35 Ivan Ruiz Manuel, ***@***.***>
wrote:

> I can only reproduce this issue with your second example. The first one
> loads just fine.
>
> Odd, that's the one I saw as most problematic. I'll give an update if I
> can reproduce it...
>
> For the second: I would like to propose that this type of "dropping"
> should be the standard, to ensure the files given to the model are "stand
> alone". Otherwise, you'd need to always consult two files. This way, you
> have good data practices "baked in".
>
> What do you think?
>
> —
> Reply to this email directly, view it on GitHub
> <https://github.com/calliope-project/calliope/issues/573#issuecomment-1957765430>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AEDB63XQKJEQUYE5ON7NDI3YUZEBNAVCNFSM6AAAAABDTMI46KVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSNJXG43DKNBTGA>
> .
> You are receiving this because you commented.Message ID:
> ***@***.***>
>

Hmmm, that is true...
The only way to make it possible would be to force one type of table (i.e. rows only), which would make the input very inflexible).
Plan: enforce a header to always exist in a CSV, even if it is just one row. We will set `header=0` as the bare minimum internally. 

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 8e8e9987..4cf6e1b2 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -2,6 +2,9 @@
 
 ### User-facing changes
 
+|fixed| Force a header row in tabular data loaded from CSV to (#596).
+Fixes issue where unexpected index levels can end up in the loaded data (#573).
+
 |fixed| Single element lists/sets in the model Dataset attribute dictionary are restored to lists/sets on loading from NetCDF (#614).
 
 |new| Decision variables and global expressions can have a `title` defined, which will be available in the model results as attributes of those components and can be used for e.g. visualisation (#582).
diff --git a/docs/creating/data_sources.md b/docs/creating/data_sources.md
index 9dcf27f9..b6926268 100644
--- a/docs/creating/data_sources.md
+++ b/docs/creating/data_sources.md
@@ -23,6 +23,91 @@ In addition, when loading from file, there is the _required_ dimension `paramete
 This is a placeholder to point Calliope to the parameter name(s) that your tabular data is referring to.
 The values assigned as parameters will become the array names in your loaded model dataset ([`model.inputs`][calliope.Model.inputs]).
 
+## Structuring your CSV files
+
+### Include a header row
+
+CSV files in which tabular data is stored _must_ have at least one header row.
+If you do not have this header row, then the first row of data will still be taken to be dimension names,
+which will cause an error in Calliope as those names will not match what you have defined in YAML as `rows` and/or `columns` dimensions.
+
+=== "Valid CSV (row names only)"
+
+    | timesteps           |    |
+    | ------------------: | :- |
+    | 2005-01-01 12:00:00 | 15 |
+    | 2005-01-01 13:00:00 | 5  |
+
+=== "Valid CSV (column names only)"
+
+    |                     | vals |
+    | ------------------: | :--- |
+    | 2005-01-01 12:00:00 | 15   |
+    | 2005-01-01 13:00:00 | 5    |
+
+=== "Valid CSV (multi-level)"
+
+    | <br><br>timesteps   | <br><br>carriers | node1<br>tech1 |
+    | ------------------: | ---------------: | :------------- |
+    | 2005-01-01 12:00:00 | electricity      | 15             |
+    | 2005-01-01 13:00:00 | electricity      | 5              |
+
+=== "Invalid CSV"
+
+    | 2005-01-01 12:00:00 | 15 |
+    | 2005-01-01 13:00:00 | 5  |
+
+### Use multiple index levels per row or per column
+
+Since you will likely define data that is indexed over two or more dimensions, you can leverage index "levels" in your CSV file.
+Data in dimension rows/columns will be loaded in as the dimension data; everything else will be parameter data.
+
+=== "Two index levels, defined per row"
+
+    | nodes | techs |      |
+    | ----: | ----: | :--- |
+    | node1 | tech1 | 15   |
+    | node2 | tech2 | 5    |
+
+    This will be loaded in as `(node1, tech1): 15`, `(node2, tech2): 5`.
+
+=== "Two index levels, defined per column"
+
+    | node1<br>tech1 | node2<br>tech2 |
+    | :------------- | :------------- |
+    | 15             | 5              |
+
+    This will be loaded in as `(node1, tech1): 15`, `(node2, tech2): 5`.
+
+=== "Four index levels, 2 per row and 2 per column"
+
+    | nodes | techs | cost1<br>carrier1 | cost1<br>carrier2 |
+    | ----: | ----: | :---------------- | :---------------- |
+    | node1 | tech1 | 15                | 150               |
+    | node2 | tech2 | 5                 | 50                |
+
+
+    This will be loaded in as `(node1, tech1, cost1, carrier1): 15`, `(node1, tech1, cost1, carrier2): 150`,
+    `(node2, tech2, cost1, carrier1): 5`, `(node2, tech2, cost1, carrier2): 50`.
+
+### Go for long and thin if it is a sparse array
+
+Although not a strict requirement, if many combinations of dimension items are empty of data, then use a "dense" CSV structure.
+
+=== "Dense CSV (long and thin)"
+
+    | nodes | techs |      |
+    | ----: | ----: | :--- |
+    | node1 | tech1 | 15   |
+    | node2 | tech2 | 5    |
+
+=== "Sparse CSV (square)"
+
+    |       | tech1 | tech2 |
+    | ----: | :---- | :---- |
+    | node1 | 15    |       |
+    | node2 |       | 5     |
+
 ## YAML vs tabular definitions
 
 In this section we will show some examples of loading data and provide the equivalent YAML definition that it would replace.
@@ -76,9 +161,9 @@ In this section we will show some examples of loading data and provide the equiv
 
     <div class="annotate" markdown>
 
-    |       |                       |             |
+    | techs | parameters            | values (1)  |
     | ----: | --------------------: | :---------- |
-    | tech1 | base_tech             | supply  (1) |
+    | tech1 | base_tech             | supply      |
     | tech1 | flow_cap_max          | 100         |
     | tech1 | flow_out_eff          | 0.1         |
     | tech1 | area_use_max          | 500         |
@@ -90,9 +175,8 @@ In this section we will show some examples of loading data and provide the equiv
 
     </div>
 
-    1. Unlike the previous example, we do not have a "header" row with column names in this file.
-    We start directly with defining data.
-    Our dimensions are _only_ defined per row, not per column.
+    1. Unlike the previous example, we have a column name in this file.
+    It doesn't matter what name we give it since it will be ["squeezed"][pandas.DataFrame.squeeze] out of the dataframe.
 
     YAML definition to load data:
 
@@ -127,7 +211,7 @@ In this section we will show some examples of loading data and provide the equiv
 
     Data in file:
 
-    |       |                    |      |
+    | techs | costs              |      |
     | ----: | -----------------: | :--- |
     | tech1 | cost_flow_cap      | 100  |
     | tech1 | cost_area_use      | 50   |
@@ -197,7 +281,7 @@ For instance:
 
 Data in file:
 
-|       |            | node1 | node2 | node3 |
+| techs | parameters | node1 | node2 | node3 |
 | ----: | ---------: | :---- | :---- | :---- |
 | tech1 | parameter1 | 100   | 200   | 300   |
 | tech2 | parameter1 | 0.1   | 0.3   | 0.5   |
@@ -219,7 +303,7 @@ You may also want to store scenarios in your file.
 When you load in the data, you can select your scenario.
 You will also need to `drop` the dimension so that it doesn't appear in the final calliope model dataset:
 
-|       |            | scenario1 | scenario2 |
+| techs | parameters | scenario1 | scenario2 |
 | ----: | ---------: | :-------- | :-------- |
 | tech1 | parameter1 | 100       | 200       |
 | tech2 | parameter1 | 0.1       | 0.3       |
diff --git a/docs/hooks/dummy_model/costs.csv b/docs/hooks/dummy_model/costs.csv
index d56303e9..c48a4f45 100644
--- a/docs/hooks/dummy_model/costs.csv
+++ b/docs/hooks/dummy_model/costs.csv
@@ -1,4 +1,5 @@
-﻿supply_tech,cost_area_use,1
+﻿techs,parameters,
+supply_tech,cost_area_use,1
 supply_tech,cost_depreciation_rate,1
 supply_tech,cost_export,1
 supply_tech,cost_flow_cap,1
diff --git a/docs/hooks/dummy_model/nodes.csv b/docs/hooks/dummy_model/nodes.csv
index e20a21ec..c8988e2e 100644
--- a/docs/hooks/dummy_model/nodes.csv
+++ b/docs/hooks/dummy_model/nodes.csv
@@ -1,1 +1,2 @@
-﻿available_area,1
\ No newline at end of file
+﻿parameters,
+available_area,1
\ No newline at end of file
diff --git a/docs/hooks/dummy_model/tech_carrier.csv b/docs/hooks/dummy_model/tech_carrier.csv
index c627e979..2bf803e1 100644
--- a/docs/hooks/dummy_model/tech_carrier.csv
+++ b/docs/hooks/dummy_model/tech_carrier.csv
@@ -1,3 +1,4 @@
+techs,carriers,parameters,
 demand_tech,foo,carrier_in,1.0
 conversion_tech,foo,carrier_out,1.0
 conversion_tech,bar,carrier_in,1.0
diff --git a/docs/hooks/dummy_model/techs.csv b/docs/hooks/dummy_model/techs.csv
index 27309b3a..653a3d46 100644
--- a/docs/hooks/dummy_model/techs.csv
+++ b/docs/hooks/dummy_model/techs.csv
@@ -1,3 +1,4 @@
+techs,parameters,
 demand_tech,lifetime,25
 demand_tech,sink_use_min,0.2
 conversion_tech,include_storage,1.0
diff --git a/docs/hooks/dummy_model/time_varying.csv b/docs/hooks/dummy_model/time_varying.csv
index eae9c3f2..f8abbfa1 100644
--- a/docs/hooks/dummy_model/time_varying.csv
+++ b/docs/hooks/dummy_model/time_varying.csv
@@ -1,4 +1,5 @@
-﻿2005-01-01 00:00:00,1
+﻿timesteps,
+2005-01-01 00:00:00,1
 2005-01-01 12:00:00,1
 2005-01-02 00:00:00,1
 2005-01-02 12:00:00,1
diff --git a/src/calliope/preprocess/data_sources.py b/src/calliope/preprocess/data_sources.py
index a30f862f..129d5e45 100644
--- a/src/calliope/preprocess/data_sources.py
+++ b/src/calliope/preprocess/data_sources.py
@@ -245,13 +245,18 @@ def _read_csv(self) -> pd.DataFrame:
             pd.DataFrame: Loaded data without any processing.
         """
         filename = self.input["source"]
-        csv_reader_kwargs = {"header": self.columns, "index_col": self.index}
-        for axis, names in csv_reader_kwargs.items():
-            if names is not None:
-                csv_reader_kwargs[axis] = [i for i, _ in enumerate(names)]
+
+        if self.columns is None:
+            self._log(
+                "No column dimensions have been defined. "
+                "We will still assume the first line of the CSV file to contain a header row with index dimensions",
+                level="debug",
+            )
+        header = [0] if self.columns is None else list(range(len(self.columns)))
+        index_col = None if self.index is None else list(range(len(self.index)))
 
         filepath = relative_path(self.model_definition_path, filename)
-        df = pd.read_csv(filepath, encoding="utf-8", **csv_reader_kwargs)
+        df = pd.read_csv(filepath, encoding="utf-8", header=header, index_col=index_col)
         return df
 
     def _df_to_ds(self, df: pd.DataFrame) -> xr.Dataset:
@@ -375,6 +380,7 @@ def _listify_if_defined(self, key: str) -> Optional[list]:
 
         Args:
             key (str): Definition dictionary key whose value are to be returned as a list.
+            default (Literal[None, 0]): Either zero or None
 
         Returns:
             Optional[list]: If `key` not defined in data source, return None, else return values as a list.


Test patch:
diff --git a/tests/common/national_scale_from_data_sources/data_sources/dimensionless_params.csv b/tests/common/national_scale_from_data_sources/data_sources/dimensionless_params.csv
index 70d8cc5b..28b907d1 100644
--- a/tests/common/national_scale_from_data_sources/data_sources/dimensionless_params.csv
+++ b/tests/common/national_scale_from_data_sources/data_sources/dimensionless_params.csv
@@ -1,1 +1,2 @@
+parameters,
 bigM,1000000
\ No newline at end of file
diff --git a/tests/test_preprocess_data_sources.py b/tests/test_preprocess_data_sources.py
index 6b0c338d..af0817e9 100644
--- a/tests/test_preprocess_data_sources.py
+++ b/tests/test_preprocess_data_sources.py
@@ -22,9 +22,9 @@ def data_dir(tmp_path_factory):
 
 @pytest.fixture(scope="class")
 def generate_data_source_dict(data_dir):
-    def _generate_data_source_dict(filename, df, rows, columns, **to_csv_kwargs):
+    def _generate_data_source_dict(filename, df, rows, columns):
         filepath = data_dir / filename
-        df.to_csv(filepath, **to_csv_kwargs)
+        df.rename_axis(index=rows).to_csv(filepath)
         return {
             "source": filepath.as_posix(),
             "rows": rows,
@@ -40,7 +40,7 @@ class TestDataSourceUtils:
     def source_obj(self, init_config, generate_data_source_dict):
         df = pd.Series({"bar": 0, "baz": 1})
         source_dict = generate_data_source_dict(
-            "foo.csv", df, rows="test_row", columns=None, header=None
+            "foo.csv", df, rows="test_row", columns=None
         )
         ds = data_sources.DataSource(init_config, "ds_name", source_dict)
         ds.input["foo"] = ["foobar"]
@@ -102,7 +102,7 @@ class TestDataSourceInitOneLevel:
     def multi_row_no_col_data(self, generate_data_source_dict):
         df = pd.Series({"bar": 0, "baz": 1})
         return df, generate_data_source_dict(
-            "multi_row_no_col_file.csv", df, rows="test_row", columns=None, header=None
+            "multi_row_no_col_file.csv", df, rows="test_row", columns=None
         )
 
     @pytest.fixture(scope="class")
@@ -201,7 +201,6 @@ def multi_row_no_col_data(self, generate_data_source_dict):
             df,
             rows=["test_row1", "test_row2"],
             columns=None,
-            header=None,
         )
 
     @pytest.fixture(scope="class")