For Project 2, our objective is to make 3 different ELT pipelines for 3 different wide-format .csv files.
For this set of data, I am looking at energy uses and sources based on country. I would like to get it in a longer format, do some general analysis and plotting to get a lay of the land, and then look into
The Code
library(knitr)
Warning: package 'knitr' was built under R version 4.5.3
library(tidyverse)
Warning: package 'tidyverse' was built under R version 4.5.3
Warning: package 'ggplot2' was built under R version 4.5.3
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr 1.2.0 ✔ readr 2.2.0
✔ forcats 1.0.1 ✔ stringr 1.6.0
✔ ggplot2 4.0.3 ✔ tibble 3.3.1
✔ lubridate 1.9.5 ✔ tidyr 1.3.2
✔ purrr 1.2.1
── 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(lubridate)
For my data, I wanted not only to look at energy data but also population data from those countries. I felt like there could be an interesting correlation between population and energy usage. I used Claude to generate a .csv of population data that I could then join.
Energy <-read.csv("electricity_production_by_country.csv")Population <-read.csv("country_population.csv")kable(head(Energy))
Location
Total_TWh
Coal_TWh
Gas_TWh
Other_fossil_TWh
Nuclear_TWh
Hydro_TWh
Wind_TWh
Solar_TWh
Bioenergy_TWh
Other_renewables_TWh
Year
World
31746.92
10491.47
6903.64
823.92
2813.09
4433.08
2711.33
2778.23
702.83
89.33
2025
China
10579.70
5773.02
317.94
88.71
487.35
1395.70
1135.44
1174.92
206.62
NA
2025
United States
4519.79
737.15
1807.34
31.72
784.78
241.70
464.39
388.82
46.19
17.70
2025
India
2081.60
1474.15
48.54
4.10
53.83
177.97
103.98
195.99
23.04
NA
2025
Russia
1192.79
219.52
533.17
14.47
218.65
199.73
3.64
2.84
0.77
NA
2025
Japan
1029.86
330.08
337.57
25.39
94.09
73.90
12.85
101.06
54.92
NA
2025
kable(head(Population))
Location
Year
Population
Afghanistan
2009
27466101
Afghanistan
2010
28284089
Afghanistan
2011
29347708
Afghanistan
2012
30560034
Afghanistan
2013
31622704
Afghanistan
2014
32792523
Here is the bulk of my data tidying and pivoting. I first pivoted the energy sources into their own column, and then I cleaned and organized the data. Following that, I did a left_join on the population data in order to get a full set of data that I could then manipulate.
Warning: There was 1 warning in `mutate()`.
ℹ In argument: `Population = as.integer(Population)`.
Caused by warning:
! NAs introduced by coercion to integer range
kable(head(Energy2))
Location
Energy_Source
Value_TWh
Population
World
Total
31746
NA
World
Coal
10491
NA
World
Gas
6903
NA
World
Other_fossil
823
NA
World
Nuclear
2813
NA
World
Hydro
4433
NA
The data I was most interested in was seeing how the populations intersect with the energy usage data. I made some scatter plots not only to compare Population with energy usage but also population with per capital energy usage to see if different countries had different energy efficiencies.
Warning in scale_y_log10(labels = scales::comma): log-10 transformation
introduced infinite values.
What I’m seeing here is that the top 3 each tell a story. First, China and India have a similar population, but you see that India has a much lower energy use per capita. With America being a few hundred million behind those two, it has a higher energy use per capita. Energy use per capita does not take into account industrial energy usage.
Now lets look at energy distribution between “clean” and “dirty” energy distributions.
`summarise()` has regrouped the output.
ℹ Summaries were computed grouped by Energy_Source and Energy_Class.
ℹ Output is grouped by Energy_Source.
ℹ Use `summarise(.groups = "drop_last")` to silence this message.
ℹ Use `summarise(.by = c(Energy_Source, Energy_Class))` for per-operation
grouping (`?dplyr::dplyr_by`) instead.
kable(head(Energy4))
Energy_Source
Energy_Class
Total_Energy_Use
Bioenergy
Dirty
666
Coal
Dirty
10458
Gas
Dirty
6808
Hydro
Clean
4378
Nuclear
Clean
2807
Other_fossil
Dirty
754
ggplot(Energy4, aes(x = Energy_Class, y = Total_Energy_Use, fill = Energy_Source)) +geom_col() +scale_y_continuous(labels = scales::comma) +labs(x =NULL, y ="TWh", fill ="Source")
This shows a decent visual of the breakdown of energy usage between clean and dirty energy sources.
I wanted to take it a step further so I also asked Claude for help with the visual. This is what it put out.
There are a few things to take away from this breakdown. We can see that there is not a clear correlation between Energy per capita and population, as there are many factors such as industry and culture that affect power usage. We can see a direct proportion between population and energy use however.
We also observe that when you look at the world total usages of energy, it almost looks to be 50% clean and 50% dirty energy. Obviously, we are constantly pushing as a society towards cleaner energy sources, but I would expect to have seen a higher value for dirty energy sources. The use of coal as the major source of fuel was surprising given USA’s stance on it, but understandably it is an affordable and notorious method of energy generation.
