Photo by Maxim Tolchinskiy on Unsplash

Greenhouse gas emissions attributable to products—from food to sneakers to appliances—make up more than 75% of global emissions. –The Carbon Catalogue

Our data, which is publicly availably on nature.com, contains product carbon footprints (PCFs) for various companies. PCFs are the greenhouse gas emissions attributable to a given product, measured in CO₂ (carbon dioxide equivalent).

This data is stored in a PostgreSQL database containing one table, prouduct_emissions, which looks at PCFs by product as well as the stage of production these emissions occured in. Here’s a snapshot of what product_emissions contains in each column:

product_emissions

field	data_type
id	VARCHAR
year	INT
product_name	VARCHAR
company	VARCHAR
country	VARCHAR
industry_group	VARCHAR
weight_kg	NUMERIC
carbon_footprint_pcf	NUMERIC
upstream_percent_total_pcf	VARCHAR
operations_percent_total_pcf	VARCHAR
downstream_percent_total_pcf	VARCHAR

You’ll use this data to examine the carbon footprint of each industry in the dataset!

In [5]:

-- Start coding here... 
SELECT *
FROM product_emissions;

Out[5]:

	id	year	product_name	company	country	industry_group	weight_kg	carbon_footprint_pcf	upstream_percent_total_pcf	operations_percent_total_pcf	downstream_percent_total_pcf
0	10056-1-2014	2014	Frosted Flakes(R) Cereal	Kellogg Company	USA	Food, Beverage & Tobacco	0.748500	2.00	57.50%	30.00%	12.50%
1	10056-1-2015	2015	Frosted Flakes, 23 oz, produced in Lancaster, …	Kellogg Company	USA	Food & Beverage Processing	0.748500	2.00	57.50%	30.00%	12.50%
2	10222-1-2013	2013	Office Chair	KNOLL INC	USA	Capital Goods	20.680000	72.54	80.63%	17.36%	2.01%
3	10261-1-2017	2017	Multifunction Printers	Konica Minolta, Inc.	Japan	Technology Hardware & Equipment	110.000000	1488.00	30.65%	5.51%	63.84%
4	10261-2-2017	2017	Multifunction Printers	Konica Minolta, Inc.	Japan	Technology Hardware & Equipment	110.000000	1818.00	25.08%	4.51%	70.41%
…	…	…	…	…	…	…	…	…	…	…	…
861	9298-2-2014	2014	Desktop CPU	Intel Corporation	USA	Semiconductors & Semiconductor Equipment	0.408233	14.00	32.14%	54.29%	13.57%
862	9298-3-2013	2013	Mobile CPU	Intel Corporation	USA	Technology Hardware & Equipment	0.408233	7.00	28.57%	71.43%	0.00%
863	9298-3-2014	2014	Mobile CPU	Intel Corporation	USA	Semiconductors & Semiconductor Equipment	0.408233	7.00	31.43%	54.29%	14.29%
864	9792-1-2017	2017	Complete catalyst system for diesel-powered pa…	Johnson Matthey	United Kingdom	Materials	2.000000	188.00	N/a (product with insufficient stage-level data)	N/a (product with insufficient stage-level data)	N/a (product with insufficient stage-level data)
865	9792-2-2017	2017	Three-way Catalyst for gasoline-powered passen…	Johnson Matthey	United Kingdom	Materials	2.000000	63.00	N/a (product with insufficient stage-level data)	N/a (product with insufficient stage-level data)	N/a (product with insufficient stage-level data)

866 rows × 11 columns

In [17]:

-- industry_group
-- count_industry
-- total_industry_footprint
-- recent years
-- highest emitting industries

SELECT industry_group, COUNT(company) AS count_industry, ROUND(SUM(carbon_footprint_pcf),1) as total_industry_footprint
FROM product_emissions pe
WHERE pe.year IN (SELECT MAX(pe.year) FROM product_emissions pe)
GROUP BY industry_group
ORDER BY  total_industry_footprint DESC;

Out[17]:

	industry_group	count_industry	total_industry_footprint
0	Materials	11	107129.0
1	Capital Goods	4	94942.7
2	Technology Hardware & Equipment	22	21865.1
3	Food, Beverage & Tobacco	22	3161.5
4	Commercial & Professional Services	2	740.6
5	Software & Services	1	690.0