Advanced functionality - pandas groupby with pymrio satellite accounts¶
This notebook examplifies how to directly apply Pandas core functions (in this case groupby and aggregation) to the pymrio system.
WIOD material extension aggregation - stressor w/o compartment info¶
Here we use the WIOD MRIO system (see the notebook “Automatic downloading of MRIO databases” for how to automatically retrieve this database) and will aggregate the WIOD material stressor for used and unused materials. We assume, that the WIOD system is available at
[1]:
wiod_folder = "/tmp/mrios/WIOD2013"
To get started we import pymrio
[2]:
import pymrio
For the example here, we use the data from 2009:
[3]:
wiod09 = pymrio.parse_wiod(path=wiod_folder, year=2009)
WIOD includes multiple material accounts, specified for the “Used” and “Unused” category, as well as information on the total. We will use the latter to confirm our calculations:
[4]:
wiod09.mat.F
[4]:
region | AUS | ... | RoW | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sector | AtB | C | 15t16 | 17t18 | 19 | 20 | 21t22 | 23 | 24 | 25 | ... | 63 | 64 | J | 70 | 71t74 | L | M | N | O | P |
stressor | |||||||||||||||||||||
Biomass_animals_Used | 238.487190 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_feed_Used | 314501.775775 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_food_Used | 78736.348430 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_forestry_Used | 21443.712952 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_other_Used | 647.038563 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_coal_Used | 0.000000 | 4.084490e+05 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_gas_Used | 0.000000 | 3.671908e+04 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_oil_Used | 0.000000 | 2.191849e+04 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_other_Used | 0.000000 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Minerals_construction_Used | 0.000000 | 1.098489e+05 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Minerals_industrial_Used | 0.000000 | 2.444270e+04 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Minerals_metals_Used | 0.000000 | 7.019911e+05 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_animals_Unused | 38.094064 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_feed_Unused | 194.597667 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_food_Unused | 17925.841358 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_forestry_Unused | 3216.556943 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Biomass_other_Unused | 128.610253 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_coal_Unused | 0.000000 | 6.430405e+06 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_gas_Unused | 0.000000 | 4.759046e+03 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_oil_Unused | 0.000000 | 4.822068e+03 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Fossil_other_Unused | 0.000000 | 0.000000e+00 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Minerals_construction_Unused | 0.000000 | 3.015773e+03 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Minerals_industrial_Unused | 0.000000 | 3.389710e+04 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Minerals_metals_Unused | 0.000000 | 6.919846e+05 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Total | 437071.063196 | 8.472253e+06 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
25 rows × 1435 columns
To aggregate these with the Pandas groupby function, we need to specify the groups which should be grouped by Pandas. Pymrio contains a helper function which builds such a matching dictionary. The matching can also include regular expressions to simplify the build:
[5]:
groups = wiod09.mat.get_index(
as_dict=True,
grouping_pattern={".*_Used": "Material Used", ".*_Unused": "Material Unused"},
)
groups
[5]:
{'Biomass_animals_Used': 'Material Used',
'Biomass_feed_Used': 'Material Used',
'Biomass_food_Used': 'Material Used',
'Biomass_forestry_Used': 'Material Used',
'Biomass_other_Used': 'Material Used',
'Fossil_coal_Used': 'Material Used',
'Fossil_gas_Used': 'Material Used',
'Fossil_oil_Used': 'Material Used',
'Fossil_other_Used': 'Material Used',
'Minerals_construction_Used': 'Material Used',
'Minerals_industrial_Used': 'Material Used',
'Minerals_metals_Used': 'Material Used',
'Biomass_animals_Unused': 'Material Unused',
'Biomass_feed_Unused': 'Material Unused',
'Biomass_food_Unused': 'Material Unused',
'Biomass_forestry_Unused': 'Material Unused',
'Biomass_other_Unused': 'Material Unused',
'Fossil_coal_Unused': 'Material Unused',
'Fossil_gas_Unused': 'Material Unused',
'Fossil_oil_Unused': 'Material Unused',
'Fossil_other_Unused': 'Material Unused',
'Minerals_construction_Unused': 'Material Unused',
'Minerals_industrial_Unused': 'Material Unused',
'Minerals_metals_Unused': 'Material Unused',
'Total': 'Total'}
Note, that the grouping contains the rows which do not match any of the specified groups. This allows to easily aggregates only parts of a specific stressor set. To actually omit these groups include them in the matching pattern and provide None as value.
To have the aggregated data alongside the original data, we first copy the detailed satellite account:
[6]:
wiod09.mat_agg = wiod09.mat.copy(new_name="Aggregated matrial accounts")
Then, we use the pymrio get_DataFrame iterator together with the pandas groupby and sum functions to aggregate the stressors. For the dataframe containing the unit information, we pass a custom function which concatenate non-unique unit strings.
[7]:
for df_name, df in zip(
wiod09.mat_agg.get_DataFrame(data=False, with_unit=True, with_population=False),
wiod09.mat_agg.get_DataFrame(data=True, with_unit=True, with_population=False),
):
if df_name == "unit":
wiod09.mat_agg.__dict__[df_name] = df.groupby(groups).apply(
lambda x: " & ".join(x.unit.unique())
)
else:
wiod09.mat_agg.__dict__[df_name] = df.groupby(groups).sum()
[8]:
wiod09.mat_agg.F
[8]:
region | AUS | ... | RoW | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sector | AtB | C | 15t16 | 17t18 | 19 | 20 | 21t22 | 23 | 24 | 25 | ... | 63 | 64 | J | 70 | 71t74 | L | M | N | O | P |
Material Unused | 21503.700285 | 7.168884e+06 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Material Used | 415567.362910 | 1.303369e+06 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
Total | 437071.063196 | 8.472253e+06 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | ... | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 |
3 rows × 1435 columns
[9]:
wiod09.mat_agg.unit
[9]:
Material Unused 1000 tonnes
Material Used 1000 tonnes
Total 1000 tonnes
dtype: object
Use with stressors including compartment information:¶
The same regular expression grouping can be used to aggregate stressor data which is given per compartment. To do so, the matching dict needs to consist of tuples corresponding to a valid index value in the DataFrames. Each position in the tuple is interprested as a regular expression. Using the get_index method gives a good indication how a valid grouping dict should look like:
[10]:
tt = pymrio.load_test()
tt.emissions.get_index(as_dict=True)
[10]:
{('emission_type1', 'air'): ('emission_type1', 'air'),
('emission_type2', 'water'): ('emission_type2', 'water')}
With that information, we can now build our own grouping dict, e.g.:
[11]:
agg_groups = {("emis.*", ".*"): "all emissions"}
[12]:
group_dict = tt.emissions.get_index(as_dict=True, grouping_pattern=agg_groups)
group_dict
[12]:
{('emission_type1', 'air'): 'all emissions',
('emission_type2', 'water'): 'all emissions'}
Which can then be used to aggregate the satellite account:
[13]:
for df_name, df in zip(
tt.emissions.get_DataFrame(data=False, with_unit=True, with_population=False),
tt.emissions.get_DataFrame(data=True, with_unit=True, with_population=False),
):
if df_name == "unit":
tt.emissions.__dict__[df_name] = df.groupby(group_dict).apply(
lambda x: " & ".join(x.unit.unique())
)
else:
tt.emissions.__dict__[df_name] = df.groupby(group_dict).sum()
In this case we loose the information on the compartment. To reset the index do:
[14]:
import pandas as pd
tt.emissions.set_index(pd.Index(tt.emissions.get_index(), name="stressor"))
[15]:
tt.emissions.F
[15]:
region | reg1 | reg2 | ... | reg5 | reg6 | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sector | food | mining | manufactoring | electricity | construction | trade | transport | other | food | mining | ... | transport | other | food | mining | manufactoring | electricity | construction | trade | transport | other |
stressor | |||||||||||||||||||||
all emissions | 1987315.27 | 1008791.385 | 24377356.18 | 28413081.55 | 2901538.31 | 5387134.1 | 22779986.1 | 10291268.6 | 1902772.74 | 376842.094 | ... | 46499160 | 17964832.3 | 20604104.1 | 8286580.6 | 125872643.0 | 56775747.7 | 7561126.8 | 32087934 | 55812326.3 | 38415421 |
1 rows × 48 columns