Data cleaning

Load packages

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.5.2     ✔ tibble    3.3.0
✔ lubridate 1.9.4     ✔ tidyr     1.3.1
✔ purrr     1.1.0     
── 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

Load data

data <- read_csv("data/energy_data.csv")
Rows: 259 Columns: 10
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (1): Country
dbl (9): electricity_access_percent, electricity_generating_capacity_kW, coa...

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
glimpse(data)
Rows: 259
Columns: 10
$ Country                                <chr> "AFGHANISTAN", "AKROTIRI", "ALB…
$ electricity_access_percent             <dbl> 97, NA, 100, 99, 59, 100, 48, 1…
$ electricity_generating_capacity_kW     <dbl> 776000, NA, 2531000, 21694000, …
$ coal_metric_tons                       <dbl> 2096000, NA, 9000, 0, NA, 54600…
$ petroleum_bbl_per_day                  <dbl> 0, NA, 16100, 1414800, 0, 47200…
$ refined_petroleum_products_bbl_per_day <dbl> 0, NA, 5638, 627900, 0, 1, 5348…
$ refined_petroleum_exports_bbl_per_day  <dbl> 0, NA, 3250, 578800, 0, 562400,…
$ refined_petroleum_imports_bbl_per_day  <dbl> 34210, NA, 26660, 82930, 2346, …
$ natural_gas_cubic_meters               <dbl> 80193000, NA, 42050000, 8785397…
$ carbon_dioxide_emissions_Mt            <dbl> 7893000, NA, 3, 151, 355000, 28…
data2 <- read_csv("data/demographics_data.csv")
Rows: 259 Columns: 14
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
chr (6): Country, Population_Growth_Rate, Total_Literacy_Rate, Male_Literacy...
dbl (7): Birth_Rate, Death_Rate, Net_Migration_Rate, Median_Age, Sex_Ratio, ...
num (1): Total_Population

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
glimpse(data2)
Rows: 259
Columns: 14
$ Country                 <chr> "AFGHANISTAN", "AKROTIRI", "ALBANIA", "ALGERIA…
$ Total_Population        <dbl> 39232003, NA, 3101621, 44758398, 44620, 85468,…
$ Population_Growth_Rate  <chr> "2.26%", NA, "0.19%", "1.27%", "1.74%", "0.11%…
$ Birth_Rate              <dbl> 34.79, NA, 12.48, 17.84, 16.18, 6.87, 41.42, 1…
$ Death_Rate              <dbl> 12.08, NA, 7.36, 4.33, 6.19, 7.98, 7.80, 4.72,…
$ Net_Migration_Rate      <dbl> 0.10, NA, 3.22, 0.81, 27.36, 0.00, 0.19, 10.48…
$ Median_Age              <dbl> 19.5, NA, 34.3, 28.9, 27.2, 46.2, 15.9, 35.7, …
$ Sex_Ratio               <dbl> 1.02, NA, 0.97, 1.03, 0.99, 1.05, 0.96, 0.89, …
$ Infant_Mortality_Rate   <dbl> 103.06, NA, 10.54, 19.22, 9.87, 3.39, 57.20, 3…
$ Total_Fertility_Rate    <dbl> 4.53, NA, 1.55, 2.47, 2.13, 1.46, 5.76, 1.72, …
$ Total_Literacy_Rate     <chr> "37.3%", NA, "98.4%", "81.4%", NA, "100%", "71…
$ Male_Literacy_Rate      <chr> "39.4%", NA, "38.8%", "41.3%", NA, "35.3%", "8…
$ Female_Literacy_Rate    <chr> "7.2%", NA, "6%", "0.7%", NA, "28.3%", "62.4%"…
$ Youth_Unemployment_Rate <chr> "20.2%", NA, "27.8%", "31.9%", NA, "36.9%", "1…

Standardise dataset

# Values in the dataset were not consistent; some co2 emissions stats were in Metric tons and some were in Megatons. By multiplying all cases below a realistic threshold for metric tons by a million, I can change all the units from megatons to metric tons. Exceptions were excluded.

data_mt <- data |>
   filter(!Country %in% c("NIUE", "ARUBA", "ANDORRA", "LIECHTENSTEIN", "GIBRALTAR")) |>
  mutate(emissions_tons = case_when(carbon_dioxide_emissions_Mt < 10000 ~ carbon_dioxide_emissions_Mt * 1000000, TRUE ~ carbon_dioxide_emissions_Mt))

data_mt 
# A tibble: 254 × 11
   Country        electricity_access_p…¹ electricity_generati…² coal_metric_tons
   <chr>                           <dbl>                  <dbl>            <dbl>
 1 AFGHANISTAN                        97                 776000          2096000
 2 AKROTIRI                           NA                     NA               NA
 3 ALBANIA                           100                2531000             9000
 4 ALGERIA                            99               21694000                0
 5 AMERICAN SAMOA                     59                  47000               NA
 6 ANGOLA                             48                7344000                0
 7 ANGUILLA                          100                     NA               NA
 8 ANTARCTICA                         NA                      0               NA
 9 ANTIGUA AND B…                    100                 117000               NA
10 ARCTIC OCEAN                       NA                     NA               NA
# ℹ 244 more rows
# ℹ abbreviated names: ¹​electricity_access_percent,
#   ²​electricity_generating_capacity_kW
# ℹ 7 more variables: petroleum_bbl_per_day <dbl>,
#   refined_petroleum_products_bbl_per_day <dbl>,
#   refined_petroleum_exports_bbl_per_day <dbl>,
#   refined_petroleum_imports_bbl_per_day <dbl>, …

Total oil consumption

# By adding products to imports, we can gauge a countries' usable supply of refined petroleum. By subtracting exports, we gain a clearer picture on the petroleum used.

data_total <- data_mt |>
  mutate(oil_bbl_total = refined_petroleum_products_bbl_per_day + refined_petroleum_imports_bbl_per_day - refined_petroleum_exports_bbl_per_day)

glimpse(data_total)
Rows: 254
Columns: 12
$ Country                                <chr> "AFGHANISTAN", "AKROTIRI", "ALB…
$ electricity_access_percent             <dbl> 97, NA, 100, 99, 59, 48, 100, N…
$ electricity_generating_capacity_kW     <dbl> 776000, NA, 2531000, 21694000, …
$ coal_metric_tons                       <dbl> 2096000, NA, 9000, 0, NA, 0, NA…
$ petroleum_bbl_per_day                  <dbl> 0, NA, 16100, 1414800, 0, 11976…
$ refined_petroleum_products_bbl_per_day <dbl> 0, NA, 5638, 627900, 0, 53480, …
$ refined_petroleum_exports_bbl_per_day  <dbl> 0, NA, 3250, 578800, 0, 30340, …
$ refined_petroleum_imports_bbl_per_day  <dbl> 34210, NA, 26660, 82930, 2346, …
$ natural_gas_cubic_meters               <dbl> 80193000, NA, 42050000, 8785397…
$ carbon_dioxide_emissions_Mt            <dbl> 7893000, NA, 3, 151, 355000, 19…
$ emissions_tons                         <dbl> 7893000, NA, 3000000, 151000000…
$ oil_bbl_total                          <dbl> 34210, NA, 29048, 132030, 2346,…

Remove unwanted columns

data_clean <- data_total |>
  select(-petroleum_bbl_per_day, -refined_petroleum_exports_bbl_per_day, -refined_petroleum_imports_bbl_per_day, -electricity_access_percent)
glimpse(data_clean)
Rows: 254
Columns: 8
$ Country                                <chr> "AFGHANISTAN", "AKROTIRI", "ALB…
$ electricity_generating_capacity_kW     <dbl> 776000, NA, 2531000, 21694000, …
$ coal_metric_tons                       <dbl> 2096000, NA, 9000, 0, NA, 0, NA…
$ refined_petroleum_products_bbl_per_day <dbl> 0, NA, 5638, 627900, 0, 53480, …
$ natural_gas_cubic_meters               <dbl> 80193000, NA, 42050000, 8785397…
$ carbon_dioxide_emissions_Mt            <dbl> 7893000, NA, 3, 151, 355000, 19…
$ emissions_tons                         <dbl> 7893000, NA, 3000000, 151000000…
$ oil_bbl_total                          <dbl> 34210, NA, 29048, 132030, 2346,…

Merge population data

merged_data <- data_clean |>
  left_join(data2 %>% select(Country, Total_Population), 
            by = "Country")
glimpse(merged_data)
Rows: 254
Columns: 9
$ Country                                <chr> "AFGHANISTAN", "AKROTIRI", "ALB…
$ electricity_generating_capacity_kW     <dbl> 776000, NA, 2531000, 21694000, …
$ coal_metric_tons                       <dbl> 2096000, NA, 9000, 0, NA, 0, NA…
$ refined_petroleum_products_bbl_per_day <dbl> 0, NA, 5638, 627900, 0, 53480, …
$ natural_gas_cubic_meters               <dbl> 80193000, NA, 42050000, 8785397…
$ carbon_dioxide_emissions_Mt            <dbl> 7893000, NA, 3, 151, 355000, 19…
$ emissions_tons                         <dbl> 7893000, NA, 3000000, 151000000…
$ oil_bbl_total                          <dbl> 34210, NA, 29048, 132030, 2346,…
$ Total_Population                       <dbl> 39232003, NA, 3101621, 44758398…

Rename columns

data_clean <- merged_data |>
  rename(country = `Country`,
         electricity_generated_kw = `electricity_generating_capacity_kW`,
         coal_mt = `coal_metric_tons`,
         oil_bbl_pd = `refined_petroleum_products_bbl_per_day`,
         nat_gas_cm = `natural_gas_cubic_meters`,
         co_emissions_mt = `carbon_dioxide_emissions_Mt`,
         population = `Total_Population`)
glimpse(data_clean)
Rows: 254
Columns: 9
$ country                  <chr> "AFGHANISTAN", "AKROTIRI", "ALBANIA", "ALGERI…
$ electricity_generated_kw <dbl> 776000, NA, 2531000, 21694000, 47000, 7344000…
$ coal_mt                  <dbl> 2096000, NA, 9000, 0, NA, 0, NA, NA, NA, NA, …
$ oil_bbl_pd               <dbl> 0, NA, 5638, 627900, 0, 53480, NA, NA, 0, NA,…
$ nat_gas_cm               <dbl> 80193000, NA, 42050000, 87853976000, NA, 6767…
$ co_emissions_mt          <dbl> 7893000, NA, 3, 151, 355000, 19, NA, 28000, 7…
$ emissions_tons           <dbl> 7893000, NA, 3000000, 151000000, 355000, 1900…
$ oil_bbl_total            <dbl> 34210, NA, 29048, 132030, 2346, 134740, NA, N…
$ population               <dbl> 39232003, NA, 3101621, 44758398, 44620, 35981…

Convert barrels per day to metric tons per year

# Benchmark conversion according to BP and International Energy Agency (IEA) 1 barrel = 0.136 metric tons
# metric tons/day = barrels/day * 0.135
# metric tons/year = barrels/day * 0.135 * 365

data_clean1 <- data_clean |>
  mutate(refined_petrol_mt = oil_bbl_total * 0.135 *365,
         refined_petrol_mt = round(refined_petrol_mt, 2)) |>
  select(-oil_bbl_total, -oil_bbl_pd)
         
glimpse(data_clean1)
Rows: 254
Columns: 8
$ country                  <chr> "AFGHANISTAN", "AKROTIRI", "ALBANIA", "ALGERI…
$ electricity_generated_kw <dbl> 776000, NA, 2531000, 21694000, 47000, 7344000…
$ coal_mt                  <dbl> 2096000, NA, 9000, 0, NA, 0, NA, NA, NA, NA, …
$ nat_gas_cm               <dbl> 80193000, NA, 42050000, 87853976000, NA, 6767…
$ co_emissions_mt          <dbl> 7893000, NA, 3, 151, 355000, 19, NA, 28000, 7…
$ emissions_tons           <dbl> 7893000, NA, 3000000, 151000000, 355000, 1900…
$ population               <dbl> 39232003, NA, 3101621, 44758398, 44620, 35981…
$ refined_petrol_mt        <dbl> 1685697.8, NA, 1431340.2, 6505778.2, 115599.1…

Form emissions per capita

data_clean1 <- data_clean1 |>
  mutate(emissions_per_capita = emissions_tons / population)



glimpse(data_clean1)
Rows: 254
Columns: 9
$ country                  <chr> "AFGHANISTAN", "AKROTIRI", "ALBANIA", "ALGERI…
$ electricity_generated_kw <dbl> 776000, NA, 2531000, 21694000, 47000, 7344000…
$ coal_mt                  <dbl> 2096000, NA, 9000, 0, NA, 0, NA, NA, NA, NA, …
$ nat_gas_cm               <dbl> 80193000, NA, 42050000, 87853976000, NA, 6767…
$ co_emissions_mt          <dbl> 7893000, NA, 3, 151, 355000, 19, NA, 28000, 7…
$ emissions_tons           <dbl> 7893000, NA, 3000000, 151000000, 355000, 1900…
$ population               <dbl> 39232003, NA, 3101621, 44758398, 44620, 35981…
$ refined_petrol_mt        <dbl> 1685697.8, NA, 1431340.2, 6505778.2, 115599.1…
$ emissions_per_capita     <dbl> 0.2011878, NA, 0.9672362, 3.3736686, 7.956073…

Save as csv file

write.csv(data_clean1, file = "data/dataclean1.csv")