SusFin_Assignment 6

Cleaning Scope 1 Data

  1. Domestic Emissions (UNFCCC) Including LULUCF Downloaded from climatewatchdata.org, from year 1995-2018
library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.4     ✔ readr     2.1.5
✔ forcats   1.0.0     ✔ stringr   1.5.1
✔ ggplot2   3.4.4     ✔ tibble    3.2.1
✔ lubridate 1.9.3     ✔ tidyr     1.3.1
✔ purrr     1.0.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(countrycode)
library(readxl)
library(dplyr)

#Read excel file
domestic <- read_excel(
  "data-raw/domestic_ghg.xlsx",
  skip = 1)

#Pivot to long
domestic_longer <- domestic |> 
  slice(1:(n() - 2)) |> 
  pivot_longer(
    cols = `1995`:`2018`,
    names_to = "year",
    values_to = "scp1_domestic_emissions"
  ) |> 
  mutate(
    year = as.numeric(year)
  )

#Write excel to data folder
library(openxlsx)
write.xlsx(domestic_longer, "data/scp1_domestic_emissions.xlsx")
  1. Emissions of Exports of Goods and Services Downloaded from OECD, from year 1995-2018 Indicator: EXGR_DCO2: Domestic CO2 emissions embodied in gross exports Industry: DTOTAL: TOTAL
#Read excel file
export <- read_excel(
  "data-raw/oecd_ex.xlsx",
  skip = 6)
New names:
• `` -> `...2`
export_clean <- export |> 
  separate(col = Time, into = c("iso", "country"), sep = ": ") |> 
  slice(-1:-2) |> 
  slice(1:(n() - 16)) |> 
  select(-`...2`)
Warning: Expected 2 pieces. Missing pieces filled with `NA` in 2 rows [1, 85].
#Pivot to long
export_longer <- export_clean |> 
  pivot_longer(
    cols = `1995`:`2018`,
    names_to = "year",
    values_to = "scp1_export_emissions"
  ) |> 
  mutate(
    year = as.numeric(year)
  )

write.xlsx(export_longer, "data/scp1_export_emissions.xlsx")
  1. Combine the two sheets and calculate total Scope 1 Emissions
domestic_longer <- read_excel("data/scp1_domestic_emissions.xlsx") |> 
  rename(country = `Country/Region`)

export_longer <- read_excel("data/scp1_export_emissions.xlsx")

combined_scp1 <- inner_join(domestic_longer, export_longer, by = c("country" = "country", "year" = "year"))

combined_scp1 <- combined_scp1 |> 
  select(-`iso.y`) |> 
  mutate(scp1_total_emissions = scp1_domestic_emissions + scp1_export_emissions)

write.xlsx(combined_scp1, "data/combined_scp1.xlsx")

Cleaning Scope 2 Data

  1. Grid-supplied electricity, steam and cooling imports Downloaded from OECD, from year 1995-2018 Indicator: IMGR_DCO2: Domestic CO2 emissions embodied in gross imports Industry: D35: Electricity, gas, steam and air conditioning supply
#Read excel file
import_d35 <- read_excel(
  "data-raw/oecd_im_d35.xlsx",
  skip = 6)
New names:
• `` -> `...2`
import_d35_clean <- import_d35 |> 
  separate(col = Time, into = c("iso", "country"), sep = ": ") |> 
  slice(-1:-2) |> 
  slice(1:(n() - 16)) |> 
  select(-`...2`)
Warning: Expected 2 pieces. Missing pieces filled with `NA` in 2 rows [1, 85].
#Pivot to long
import_d35_longer <- import_d35_clean |> 
  pivot_longer(
    cols = `1995`:`2018`,
    names_to = "year",
    values_to = "scp2_emissions"
  ) |> 
  mutate(
    year = as.numeric(year)
  )

write.xlsx(import_d35_longer, "data/scp2_emissions.xlsx")
  1. Combine Scope 2 and Scope 1 Emissions into one sheet
combined_scp1 <- read_excel("data/combined_scp1.xlsx") 
import_d35_longer <- read_excel("data/scp2_emissions.xlsx")

combined_scp1_scp2 <- inner_join(combined_scp1, import_d35_longer, by = c("country" = "country", "year" = "year")) |> 
  select(-`iso`)

combined_scp1_scp2
# A tibble: 768 × 8
   iso.x country       unit    year scp1_domestic_emissi…¹ scp1_export_emissions
   <chr> <chr>         <chr>  <dbl>                  <dbl>                 <dbl>
 1 USA   United States MtCO2e  1995                  5762.                  355.
 2 USA   United States MtCO2e  1996                  5933.                  365.
 3 USA   United States MtCO2e  1997                  6193.                  396.
 4 USA   United States MtCO2e  1998                  6241.                  380.
 5 USA   United States MtCO2e  1999                  6243.                  389.
 6 USA   United States MtCO2e  2000                  6406.                  410.
 7 USA   United States MtCO2e  2001                  6368.                  377.
 8 USA   United States MtCO2e  2002                  6216.                  320.
 9 USA   United States MtCO2e  2003                  6280.                  305.
10 USA   United States MtCO2e  2004                  6363.                  329.
# ℹ 758 more rows
# ℹ abbreviated name: ¹​scp1_domestic_emissions
# ℹ 2 more variables: scp1_total_emissions <dbl>, scp2_emissions <dbl>
write.xlsx(combined_scp1_scp2, "data/combined_scp1_scp2.xlsx")

Cleaning Scope 3 Data

  1. Import and Clean Total Import Emissions Downloaded from OECD, from year 1995-2018 Indicator: IMGR_DCO2: Domestic CO2 emissions embodied in gross imports Industry: DTOTAL: TOTAL
#Read excel file
import_total <- read_excel(
  "data-raw/oecd_im_total.xlsx",
  skip = 6)
New names:
• `` -> `...2`
import_total_clean <- import_total |> 
  separate(col = Time, into = c("iso", "country"), sep = ": ") |> 
  slice(-1:-2) |> 
  slice(1:(n() - 16)) |> 
  select(-`...2`)
Warning: Expected 2 pieces. Missing pieces filled with `NA` in 2 rows [1, 85].
#Pivot to long
import_total_longer <- import_total_clean |> 
  pivot_longer(
    cols = `1995`:`2018`,
    names_to = "year",
    values_to = "import_total_emissions"
  ) |> 
  mutate(
    year = as.numeric(year)
  )

write.xlsx(import_total_longer, "data/import_total_emissions.xlsx")
  1. Combine total import emissions with scope 1 and scope 2
combined_scp1_scp2 <- read_excel("data/combined_scp1_scp2.xlsx") 
import_total_longer <- read_excel("data/import_total_emissions.xlsx")

combined_scp1_scp2_scp3 <- inner_join(combined_scp1_scp2, import_total_longer, by = c("country" = "country", "year" = "year")) |> 
  select(-`iso`) |> 
  mutate(scp3_emissions = import_total_emissions - scp2_emissions)

write.xlsx(combined_scp1_scp2_scp3, "data/combined_scp1_scp2_scp3.xlsx")

glimpse(combined_scp1_scp2_scp3)
Rows: 768
Columns: 10
$ iso.x                   <chr> "USA", "USA", "USA", "USA", "USA", "USA", "USA…
$ country                 <chr> "United States", "United States", "United Stat…
$ unit                    <chr> "MtCO2e", "MtCO2e", "MtCO2e", "MtCO2e", "MtCO2…
$ year                    <dbl> 1995, 1996, 1997, 1998, 1999, 2000, 2001, 2002…
$ scp1_domestic_emissions <dbl> 5761.90, 5933.25, 6193.38, 6241.11, 6242.51, 6…
$ scp1_export_emissions   <dbl> 355.468, 364.979, 396.255, 380.327, 388.570, 4…
$ scp1_total_emissions    <dbl> 6117.368, 6298.229, 6589.635, 6621.437, 6631.0…
$ scp2_emissions          <dbl> 0.050, 0.050, 0.058, 0.069, 0.065, 0.148, 0.09…
$ import_total_emissions  <dbl> 36.007, 40.165, 47.759, 50.373, 55.524, 60.565…
$ scp3_emissions          <dbl> 35.957, 40.115, 47.701, 50.304, 55.459, 60.417…