── 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(stringr)options(scipen =999)
Scope 1 data: UNFCC
# UNFCC (scope 1)ghg_emissions <-read_excel("ghg-emissions.xlsx", range ="A1:AF196")
Warning: Coercing boolean to numeric in D122 / R122C4
# Convert the 'country' column values to snake caseghg_emissions$country <-to_snake_case(ghg_emissions$country)# Pivot the data to long formatghg_emissions_tidy <- ghg_emissions |>pivot_longer(cols =-country, # Selects all columns except 'country' to pivotnames_to ="year",values_to ="ghg_emissions" ) |># Optionally, clean the column namesclean_names()# View the first few rows to verify the changeshead(ghg_emissions_tidy)
#Saving the dataset as csvwrite_csv(ghg_emissions, here("ghg_emissions_tidy.csv"))
Scope 1 data: EDGAR
# Load the datasetedgar_wide <-read_excel("EDGAR_CH4_1970_2022.xlsx", sheet ="TOTALS BY COUNTRY", range ="A10:BF233")# Rename and clean column names, and convert country names to snake caseedgar_tidy <- edgar_wide |>rename_with(~str_to_lower(gsub("[^[:alnum:]_]+", "_", .)), everything()) |># Clean all column namesrename(country = name) |>mutate(country =to_snake_case(country)) |>pivot_longer(cols =!all_of(c("ipcc_annex", "c_group_im24_sh", "country_code_a3", "country", "substance")), # Exclude the specified columns by their namesnames_to ="year",values_to ="emissions",names_prefix ="y_"# Remove 'y_' prefix from year columns ) |>mutate(year =as.numeric(str_replace(year, "x", "")), # Convert 'year' column to numeric, removing any 'x' prefixesipcc_annex =to_snake_case(str_to_lower(ipcc_annex)), # Clean and convert ipcc_annex to snake casec_group_im24_sh =to_snake_case(str_to_lower(c_group_im24_sh)) # Clean and convert c_group_im24_sh to snake case )# View the first few rows to verify the changeshead(edgar_tidy)
#Saving the dataset as csvwrite_csv(edgar_tidy, here("edgar_tidy.csv"))
Scope 1 data: total exports of goods and services
# Co2 in total exports of goods and services (scope 2)CO2_totexports <-read_excel("co2 exports goods and services.xls", range ="A7:Z91")
New names:
• `` -> `...2`
head(CO2_totexports)
# A tibble: 6 × 26
Time ...2 `1995` `1996` `1997` `1998` `1999` `2000` `2001` `2002` `2003`
<chr> <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Country NA NA NA NA NA NA NA NA NA NA
2 OECD: OE… NA 604. 655. 715. 687. 659. 734. 747. 730. 759.
3 AUS: Aus… NA 76.7 86.7 87.4 92.2 96.2 101. 107. 94.6 95.1
4 AUT: Aus… NA 30.3 31.5 34.1 34.8 35.2 38.6 40.9 42.0 44.5
5 BEL: Bel… NA 83.4 83.8 84.3 87.3 87.3 97.8 93.3 85.5 87.1
6 CAN: Can… NA 206. 217. 237. 252. 264. 287. 279. 267. 263.
# ℹ 15 more variables: `2004` <dbl>, `2005` <dbl>, `2006` <dbl>, `2007` <dbl>,
# `2008` <dbl>, `2009` <dbl>, `2010` <dbl>, `2011` <dbl>, `2012` <dbl>,
# `2013` <dbl>, `2014` <dbl>, `2015` <dbl>, `2016` <dbl>, `2017` <dbl>,
# `2018` <dbl>
# Remove the first row which seems to be a redundant header and columnCO2_totexports <- CO2_totexports[-1, -2]# Rename the 'Time' column to 'country'CO2_totexports <- CO2_totexports |>rename(country = Time)# This will remove any prefix that ends with a colon and a spaceCO2_totexports$country <-sub(".*: ", "", CO2_totexports$country)# Pivot the data to long formatCO2_totexports_long <- CO2_totexports |>pivot_longer(cols =-country, # This will select all columns except 'country' to pivotnames_to ="year",values_to ="CO2_totexports" )# Pivot the data back to wide formatCO2_totexports_wide <- CO2_totexports_long |>pivot_wider(names_from = year,values_from = CO2_totexports ) |># Use snakecase to clean the country namesmutate(country =to_snake_case(country))# View the first few rows of the wide format datahead(CO2_totexports_wide)
# Pivot back to long format as tidy dataCO2_totexports_tidy <- CO2_totexports_wide |>pivot_longer(cols =-country,names_to ="year",values_to ="CO2_totexports" )# View the first few rows of the tidy datahead(CO2_totexports_tidy)
#Saving the dataset as csvwrite_csv(CO2_totexports_tidy, here("CO2_totexports_tidy.csv"))
Scope 2 data
# Co2 in total imports of electricity, steam (scope 2)#Load datasetCO2_energyimports <-read_excel("Co2 imports electricity.xls", sheet ="OECD.Stat export", range ="A7:Z91")
New names:
• `` -> `...2`
head(CO2_energyimports)
# A tibble: 6 × 26
Time ...2 `1995` `1996` `1997` `1998` `1999` `2000` `2001` `2002` `2003`
<chr> <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Country NA NA NA NA NA NA NA NA NA NA
2 OECD: OE… NA 0.03 0.033 0.039 0.042 0.04 0.039 0.045 0.051 0.06
3 AUS: Aus… NA 0.001 0.001 0.001 0.001 0.001 0.001 0.001 0.001 0.001
4 AUT: Aus… NA 0.001 0.002 0.002 0.002 0.002 0.005 0.012 0.022 0.027
5 BEL: Bel… NA 0.002 0.003 0.003 0.002 0.002 0.005 0.009 0.014 0.019
6 CAN: Can… NA 0.023 0.026 0.042 0.053 0.042 0.05 0.081 0.05 0.069
# ℹ 15 more variables: `2004` <dbl>, `2005` <dbl>, `2006` <dbl>, `2007` <dbl>,
# `2008` <dbl>, `2009` <dbl>, `2010` <dbl>, `2011` <dbl>, `2012` <dbl>,
# `2013` <dbl>, `2014` <dbl>, `2015` <dbl>, `2016` <dbl>, `2017` <dbl>,
# `2018` <dbl>
# Remove the first row which seems to be a redundant headerCO2_energyimports <- CO2_energyimports[-1, -2]CO2_energyimports
# This will remove any prefix that ends with a colon and a spaceCO2_energyimports$country <-sub(".*: ", "", CO2_energyimports$country)CO2_energyimports
# Now pivot the data to long formatCO2_energyimports_long <- CO2_energyimports |>pivot_longer(cols =-country, # This will select all columns except 'country' to pivotnames_to ="year",values_to ="CO2_energyimports" ) CO2_energyimports_long
# A tibble: 1,992 × 3
country year CO2_energyimports
<chr> <chr> <dbl>
1 OECD member countries 1995 0.03
2 OECD member countries 1996 0.033
3 OECD member countries 1997 0.039
4 OECD member countries 1998 0.042
5 OECD member countries 1999 0.04
6 OECD member countries 2000 0.039
7 OECD member countries 2001 0.045
8 OECD member countries 2002 0.051
9 OECD member countries 2003 0.06
10 OECD member countries 2004 0.06
# ℹ 1,982 more rows
# Pivot the data from long back to wide formatCO2_energyimports_wide <- CO2_energyimports_long |>pivot_wider(names_from = year,values_from = CO2_energyimports ) |># Use snakecase to clean the country namesmutate(country =to_snake_case(country)) head (CO2_energyimports_wide)
# Load datasetCO2_totalimports <-read_excel("co2 total imports.xlsx", sheet ="OECD.Stat export", range ="A7:Z91")
New names:
• `` -> `...2`
head(CO2_totalimports)
# A tibble: 6 × 26
Time ...2 `1995` `1996` `1997` `1998` `1999` `2000` `2001` `2002` `2003`
<chr> <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 Coun… NA NA NA NA NA NA NA NA NA NA
2 OECD… NA 72.8 80.9 87.0 89.1 93.3 111. 108. 105. 112.
3 AUS:… NA 0.492 0.593 0.636 0.669 0.746 0.75 0.799 0.694 0.687
4 AUT:… NA 0.184 0.201 0.212 0.219 0.218 0.256 0.277 0.286 0.307
5 BEL:… NA 0.66 0.666 0.63 0.645 0.647 0.739 0.719 0.65 0.678
6 CAN:… NA 2.62 2.71 3.18 3.31 3.36 3.67 3.35 3.23 3.17
# ℹ 15 more variables: `2004` <dbl>, `2005` <dbl>, `2006` <dbl>, `2007` <dbl>,
# `2008` <dbl>, `2009` <dbl>, `2010` <dbl>, `2011` <dbl>, `2012` <dbl>,
# `2013` <dbl>, `2014` <dbl>, `2015` <dbl>, `2016` <dbl>, `2017` <dbl>,
# `2018` <dbl>
#CO2 non-energy = total imports - energy imports. Need to tidy total imports data first# Remove the first row which seems to be a redundant header and columnCO2_totalimports<- CO2_totalimports[-1, -2]CO2_totalimports
# Now pivot the data to long formatCO2_totalimports_long <- CO2_totalimports |>pivot_longer(cols =-country, # This will select all columns except 'country' to pivotnames_to ="year",values_to ="CO2_totalimports" ) CO2_totalimports_long
# A tibble: 1,992 × 3
country year CO2_totalimports
<chr> <chr> <dbl>
1 OECD member countries 1995 72.8
2 OECD member countries 1996 80.9
3 OECD member countries 1997 87.0
4 OECD member countries 1998 89.1
5 OECD member countries 1999 93.3
6 OECD member countries 2000 111.
7 OECD member countries 2001 108.
8 OECD member countries 2002 105.
9 OECD member countries 2003 112.
10 OECD member countries 2004 128.
# ℹ 1,982 more rows
# Pivot the data from long back to wide formatCO2_totalimports_wide <- CO2_totalimports_long |>pivot_wider(names_from = year,values_from = CO2_totalimports ) |># Use snakecase to clean the country namesmutate(country =to_snake_case(country)) head (CO2_totalimports_wide)
# Check column names for both dataframescolnames(CO2_totalimports_tidy)
[1] "country" "year" "CO2_totalimports"
colnames(CO2_energyimports_tidy)
[1] "country" "year" "CO2_energyimports"
# Join the CO2 total imports and energy imports data frames using the correct column namesCO2_totalimports_joined <- CO2_totalimports_tidy |>left_join(CO2_energyimports_tidy, by =c("country", "year"))# View the first few rows to verify the changeshead(CO2_totalimports_joined)
#PPP-adjusted GDP# Read in the data, clean column names, and convert country names to snake casePPP_adjusted_gdp <-read_excel("API_NY.GDP.MKTP.PP.CD_DS2_en_excel_v2_205.xls", sheet ="Data", range ="A4:BO270") |>clean_names() |>mutate(country_name =to_snake_case(country_name))# Pivot data to long formatPPP_adjusted_gdp_long <- PPP_adjusted_gdp |>pivot_longer(cols =!c(country_name, country_code, indicator_name, indicator_code),names_to ="year", values_to ="gdp" )# Remove the 'x' from the year column and convert it to integerPPP_adjusted_gdp_long <- PPP_adjusted_gdp_long |>mutate(year =as.integer(str_remove(year, "x")))# View the first few rows to verify the changeshead(PPP_adjusted_gdp_long)
# A tibble: 6 × 6
country_name country_code indicator_name indicator_code year gdp
<chr> <chr> <chr> <chr> <int> <dbl>
1 aruba ABW GDP, PPP (current intern… NY.GDP.MKTP.P… 1960 NA
2 aruba ABW GDP, PPP (current intern… NY.GDP.MKTP.P… 1961 NA
3 aruba ABW GDP, PPP (current intern… NY.GDP.MKTP.P… 1962 NA
4 aruba ABW GDP, PPP (current intern… NY.GDP.MKTP.P… 1963 NA
5 aruba ABW GDP, PPP (current intern… NY.GDP.MKTP.P… 1964 NA
6 aruba ABW GDP, PPP (current intern… NY.GDP.MKTP.P… 1965 NA
# Remove rows with NA in the GDP columnPPP_adjusted_gdp_long <- PPP_adjusted_gdp_long |>drop_na(gdp)PPP_adjusted_gdp_long
# Convert the year column to integer and round GDP values to two decimal placesPPP_adjusted_gdp_tidy <- PPP_adjusted_gdp_long |>mutate(year =as.integer(year), gdp =round(as.numeric(gdp), 2) )head(PPP_adjusted_gdp_tidy)
# Read in the datanominal_gdp <-read_excel("imf-dm-export-20240302.xls", sheet ="NGDPD")# Replace "no data" with NA, remove rows with any NAs, and round numbersnominal_gdp <- nominal_gdp |>mutate(across(everything(), ~na_if(., "no data"))) |>drop_na() |>mutate(across(where(is.numeric), ~round(., 2)))# Add a name to the first column if it doesn't have onenames(nominal_gdp)[1] <-"country_name"# Clean the column namesnominal_gdp <- nominal_gdp |>clean_names()# Pivot the data to long formatnominal_gdp_long <- nominal_gdp |>pivot_longer(cols =-country_name, # Excludes the first column which is now named 'country_name'names_to ="year",values_to ="gdp" )# Remove the 'x' from the year columnnominal_gdp_long <- nominal_gdp_long |>mutate(year =str_remove(year, "x")) |>mutate(year =as.numeric(year)) # Convert year to numeric# Pivot the data back to wide formatnominal_gdp_wide <- nominal_gdp_long |>pivot_wider(names_from = year,values_from = gdp )nominal_gdp_wide
#Pivot the tidy data back to long formatnominal_gdp_long_tidy<-nominal_gdp_wide|>pivot_longer(cols =-country_name, # Excludes the first column which is now named 'country_name'names_to ="year",values_to ="gdp" )head(nominal_gdp_long_tidy)
#Population poulation <-read_excel("API_SP.POP.TOTL_DS2_en_excel_v2_218.xls", sheet ="Data", range ="A4:BO270")#Pivot data wide to tidy country namespopulation_wide <- population |>pivot_wider(names_from = year,values_from = population )|>mutate(country =to_snake_case(country))head(population_wide)
#pivot data back to long formatpopulation_long_tidy<- population_wide|>pivot_longer(cols =-country, # This selects all columns except the country for pivotingnames_to ="year", values_to="population")head(population_long_tidy)
# A tibble: 6 × 3
country year population
<chr> <chr> <dbl>
1 afghanistan 1995 17586073
2 afghanistan 1996 18415307
3 afghanistan 1997 19021226
4 afghanistan 1998 19496836
5 afghanistan 1999 19987071
6 afghanistan 2000 20595360