library(tidyverse)
library(readxl)
library(here) Data Importing
Problem 1
Your boss knows that they will be asking her about how the implications of the electric vehicle (EV) market and policy choices on the demand for copper and cobalt.
Use the workflow developed in this chapter to import and tidy worksheet
2.3 EVfrom the dataset.Use the tidied dataset to come up with 5 compelling data visualizations that illustrate key actionable insights about how policy scenarios, and technological scenarios will impact demand for copper and cobalt.
Data import is time consuming when you’re first learning, so we’re limiting this to one practice question. Make your insights awesome.
path_to_sheet <- here("未命名_files", "CM_Data_Explorer.xlsx")
path_to_sheet[1] "/Users/far/未命名_files/CM_Data_Explorer.xlsx"read_key_minerals_sheet <- partial(
.f = read_excel,
path = path_to_sheet,
sheet = "2.3 EV",
col_names = FALSE
)
sheet_header <- read_key_minerals_sheet(range = "A4:W5")
sheet_header# A tibble: 2 × 23
...1 ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10 ...11 ...12 ...13
<lgl> <dbl> <lgl> <chr> <dbl> <dbl> <dbl> <dbl> <dbl> <lgl> <chr> <dbl> <dbl>
1 NA NA NA State… NA NA NA NA NA NA Anno… NA NA
2 NA 2022 NA 2025 2030 2035 2040 2045 2050 NA 2025 2030 2035
# ℹ 10 more variables: ...14 <dbl>, ...15 <dbl>, ...16 <dbl>, ...17 <lgl>,
# ...18 <chr>, ...19 <dbl>, ...20 <dbl>, ...21 <dbl>, ...22 <dbl>,
# ...23 <dbl>sheet_header_processed <- sheet_header |>
t() |>
as_tibble() |>
rename(scenario = V1, year = V2) |>
fill(scenario) |>
replace_na(list(scenario = "Current Year"))
sheet_header_processed# A tibble: 23 × 2
scenario year
<chr> <chr>
1 Current Year <NA>
2 Current Year 2022
3 Current Year <NA>
4 Stated policies scenario 2025
5 Stated policies scenario 2030
6 Stated policies scenario 2035
7 Stated policies scenario 2040
8 Stated policies scenario 2045
9 Stated policies scenario 2050
10 Stated policies scenario <NA>
# ℹ 13 more rowsmineral_name <- read_key_minerals_sheet(range = "A7") |>
pull()
mineral_name[1] "Constrained nickel supply"mineral_info <- read_key_minerals_sheet(range = "A8:W18")
mineral_info# A tibble: 11 × 23
...1 ...2 ...3 ...4 ...5 ...6 ...7 ...8 ...9 ...10
<chr> <dbl> <lgl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <lgl>
1 Copper 3.81e+2 NA 617. 1.39e+3 1.74e+3 2.23e+3 2.42e+3 2.31e+3 NA
2 Cobalt 6.35e+1 NA 61.2 3.80e+1 2.91e+1 3.54e+1 4.09e+1 4.74e+1 NA
3 Graphite 5.57e+2 NA 936. 1.59e+3 1.78e+3 1.69e+3 1.49e+3 1.08e+3 NA
4 Lithium 6.96e+1 NA 123. 2.19e+2 3.07e+2 4.07e+2 4.50e+2 4.13e+2 NA
5 Manganese 7.45e+1 NA 53.9 1.56e+2 3.36e+2 5.33e+2 7.12e+2 8.58e+2 NA
6 Nickel 3.13e+2 NA 554. 5.13e+2 5.89e+2 6.30e+2 6.92e+2 6.88e+2 NA
7 Silicon 8.70e+0 NA 40.5 1.39e+2 2.24e+2 3.52e+2 3.74e+2 3.73e+2 NA
8 Neodymium 3.96e+0 NA 7.25 1.22e+1 1.51e+1 1.88e+1 2.18e+1 2.29e+1 NA
9 Dysprosi… 4.13e-1 NA 0.741 1.21e+0 1.48e+0 1.84e+0 2.13e+0 2.23e+0 NA
10 Praseody… 5.94e-1 NA 1.09 1.83e+0 2.27e+0 2.83e+0 3.27e+0 3.43e+0 NA
11 Terbium 8.06e-2 NA 0.148 2.52e-1 3.14e-1 3.91e-1 4.52e-1 4.74e-1 NA
# ℹ 13 more variables: ...11 <dbl>, ...12 <dbl>, ...13 <dbl>, ...14 <dbl>,
# ...15 <dbl>, ...16 <dbl>, ...17 <lgl>, ...18 <dbl>, ...19 <dbl>,
# ...20 <dbl>, ...21 <dbl>, ...22 <dbl>, ...23 <dbl>mineral_info_col_names <- names(mineral_info)
mineral_info_col_names [1] "...1" "...2" "...3" "...4" "...5" "...6" "...7" "...8" "...9"
[10] "...10" "...11" "...12" "...13" "...14" "...15" "...16" "...17" "...18"
[19] "...19" "...20" "...21" "...22" "...23"sheet_headers_and_col_names <- sheet_header_processed |>
add_column(mineral_info_col_names = mineral_info_col_names)
sheet_headers_and_col_names# A tibble: 23 × 3
scenario year mineral_info_col_names
<chr> <chr> <chr>
1 Current Year <NA> ...1
2 Current Year 2022 ...2
3 Current Year <NA> ...3
4 Stated policies scenario 2025 ...4
5 Stated policies scenario 2030 ...5
6 Stated policies scenario 2035 ...6
7 Stated policies scenario 2040 ...7
8 Stated policies scenario 2045 ...8
9 Stated policies scenario 2050 ...9
10 Stated policies scenario <NA> ...10
# ℹ 13 more rowsmineral_info_long <- mineral_info |>
rename(indicator = `...1`) |>
pivot_longer(cols = -indicator,
names_to = "mineral_info_col_names") |>
add_column(mineral_name)
mineral_info_long# A tibble: 242 × 4
indicator mineral_info_col_names value mineral_name
<chr> <chr> <dbl> <chr>
1 Copper ...2 381. Constrained nickel supply
2 Copper ...3 NA Constrained nickel supply
3 Copper ...4 617. Constrained nickel supply
4 Copper ...5 1389. Constrained nickel supply
5 Copper ...6 1736. Constrained nickel supply
6 Copper ...7 2233. Constrained nickel supply
7 Copper ...8 2418. Constrained nickel supply
8 Copper ...9 2313. Constrained nickel supply
9 Copper ...10 NA Constrained nickel supply
10 Copper ...11 732. Constrained nickel supply
# ℹ 232 more rowscombined_data <- mineral_info_long |>
left_join(sheet_headers_and_col_names, by = join_by(mineral_info_col_names)) |>
filter(!is.na(year)) |>
mutate(unit = case_when(
indicator == "Share of clean technologies in total demand" ~ "Percent",
.default = "kiloton"
),
year = as.integer(year)
) |>
select(mineral_name, indicator, scenario, unit, year, value)
combined_data# A tibble: 209 × 6
mineral_name indicator scenario unit year value
<chr> <chr> <chr> <chr> <int> <dbl>
1 Constrained nickel supply Copper Current Year kilo… 2022 381.
2 Constrained nickel supply Copper Stated policies scenar… kilo… 2025 617.
3 Constrained nickel supply Copper Stated policies scenar… kilo… 2030 1389.
4 Constrained nickel supply Copper Stated policies scenar… kilo… 2035 1736.
5 Constrained nickel supply Copper Stated policies scenar… kilo… 2040 2233.
6 Constrained nickel supply Copper Stated policies scenar… kilo… 2045 2418.
7 Constrained nickel supply Copper Stated policies scenar… kilo… 2050 2313.
8 Constrained nickel supply Copper Announced pledges scen… kilo… 2025 732.
9 Constrained nickel supply Copper Announced pledges scen… kilo… 2030 2113.
10 Constrained nickel supply Copper Announced pledges scen… kilo… 2035 3587.
# ℹ 199 more rowsmineral_name_range <- "A7"
mineral_info_range <- "A8:W18"
mineral_name <- read_key_minerals_sheet(range = mineral_name_range) |>
pull()
mineral_info <- read_key_minerals_sheet(range = mineral_info_range)
mineral_info_col_names <- names(mineral_info)
mineral_info_long <- mineral_info |>
rename(indicator = `...1`) |>
pivot_longer(cols = -indicator,
names_to = "mineral_info_col_names") |>
add_column(mineral_name)
combined_data <- mineral_info_long |>
left_join(sheet_headers_and_col_names, by = join_by(mineral_info_col_names)) |>
# filter out what were empty columns (where years are NA)
filter(!is.na(year)) |>
# case_when is supercharged if else
mutate(unit = case_when(
indicator == "Share of clean technologies in total demand" ~ "Percent",
.default = "kiloton"
),
# convert the year column from character to numeric
year = as.integer(year)
) |>
select(mineral_name, indicator, scenario, unit, year, value)
combined_data# A tibble: 209 × 6
mineral_name indicator scenario unit year value
<chr> <chr> <chr> <chr> <int> <dbl>
1 Constrained nickel supply Copper Current Year kilo… 2022 381.
2 Constrained nickel supply Copper Stated policies scenar… kilo… 2025 617.
3 Constrained nickel supply Copper Stated policies scenar… kilo… 2030 1389.
4 Constrained nickel supply Copper Stated policies scenar… kilo… 2035 1736.
5 Constrained nickel supply Copper Stated policies scenar… kilo… 2040 2233.
6 Constrained nickel supply Copper Stated policies scenar… kilo… 2045 2418.
7 Constrained nickel supply Copper Stated policies scenar… kilo… 2050 2313.
8 Constrained nickel supply Copper Announced pledges scen… kilo… 2025 732.
9 Constrained nickel supply Copper Announced pledges scen… kilo… 2030 2113.
10 Constrained nickel supply Copper Announced pledges scen… kilo… 2035 3587.
# ℹ 199 more rowsread_iea_mineral_table <-
function(mineral_name_range, mineral_info_range) {
mineral_name <-
read_key_minerals_sheet(range = mineral_name_range) |>
pull()
mineral_info <- read_key_minerals_sheet(range = mineral_info_range)
mineral_info_col_names <- names(mineral_info)
mineral_info_long <- mineral_info |>
rename(indicator = `...1`) |>
pivot_longer(cols = -indicator,
names_to = "mineral_info_col_names") |>
add_column(mineral_name)
combined_data <- mineral_info_long |>
left_join(sheet_headers_and_col_names, by = join_by(mineral_info_col_names)) |>
filter(!is.na(year)) |>
mutate(
unit = case_when(
indicator == "Share of clean technologies in total demand" ~ "Percent",
.default = "kiloton"
),
year = as.integer(year)
) |>
select(mineral_name, indicator, scenario, unit, year, value)
combined_data
}constrained_nickel_supply_table <- read_iea_mineral_table(
mineral_name_range = "A7",
mineral_info_range = "A8:W19"
)
wilder_use_of_silicon_table <- read_iea_mineral_table(
mineral_name_range = "A22",
mineral_info_range = "A23:W34"
)
faster_uptake_table <- read_iea_mineral_table(
mineral_name_range = "A37",
mineral_info_range = "A38:W49"
)
limited_battery_size_table<- read_iea_mineral_table(
mineral_name_range = "A67",
mineral_info_range = "A68:W79"
)
lower_battery_size_table <- read_iea_mineral_table(
mineral_name_range = "A52",
mineral_info_range = "A53:W64"
)
steps_table <- read_iea_mineral_table(
mineral_name_range = "A82",
mineral_info_range = "A83:W94"
)
final_iea_ev_table <- steps_table |>
bind_rows(constrained_nickel_supply_table) |>
bind_rows(wilder_use_of_silicon_table) |>
bind_rows(faster_uptake_table) |>
bind_rows(lower_battery_size_table) |>
bind_rows(limited_battery_size_table)
final_iea_ev_table# A tibble: 1,368 × 6
mineral_name indicator scenario unit year value
<chr> <chr> <chr> <chr> <int> <dbl>
1 STEPS - Base case Copper Current Year kiloton 2022 373.
2 STEPS - Base case Copper Stated policies scenario kiloton 2025 580.
3 STEPS - Base case Copper Stated policies scenario kiloton 2030 1044.
4 STEPS - Base case Copper Stated policies scenario kiloton 2035 1219.
5 STEPS - Base case Copper Stated policies scenario kiloton 2040 1421.
6 STEPS - Base case Copper Stated policies scenario kiloton 2045 1458.
7 STEPS - Base case Copper Stated policies scenario kiloton 2050 1346.
8 STEPS - Base case Copper Announced pledges scenario kiloton 2025 689.
9 STEPS - Base case Copper Announced pledges scenario kiloton 2030 1592.
10 STEPS - Base case Copper Announced pledges scenario kiloton 2035 2570.
# ℹ 1,358 more rowswrite_csv(final_iea_ev_table,here("未命名_files", "ev.csv"))
clean_data <- here("未命名_files", "ev.csv") |>
read_csv()ggplot(final_iea_ev_table)+
aes(x=year, y=value, color=scenario) +
geom_point(shape = "circle") +
labs(title='Trend Analysis of Copper Demand', x='Year', y='Copper Demand (kiloton)') +
theme_minimal()
first_last <- final_iea_ev_table[final_iea_ev_table$year %in% c(min(final_iea_ev_table$year), max(final_iea_ev_table$year)),]
ggplot(first_last, aes(x=scenario, y=value, fill=factor(year))) +
geom_bar(stat='identity', position='dodge') +
labs(title='Comparison of Demand in the First and Last Year', x='Scenario', y='Copper Demand (kiloton)') +
theme(axis.text.x = element_text(angle = 45, hjust = 1, vjust = 1), plot.title = element_text(hjust = 0.5))
ggplot(final_iea_ev_table, aes(x=scenario, y=value, fill=scenario)) +
geom_violin() +
labs(title='Demand Distribution Across Scenarios', x='Scenario', y='Copper Demand (kiloton)') +
theme(axis.text.x = element_text(angle = 45, hjust = 1, vjust = 1), plot.title = element_text(hjust = 0.5))
ggplot(final_iea_ev_table, aes(x=factor(year), y=value, fill=scenario)) +
geom_bar(stat='identity', position='fill') +
labs(title='Proportion of Total Demand by Year', x='Year', y='Proportion of Demand') +
theme_minimal()
final_iea_ev_table <- final_iea_ev_table %>%
arrange(scenario, year) %>%
group_by(scenario) %>%
mutate(prev_value = lag(value),
growth_rate = (value - prev_value) / prev_value)
avg_growth <- final_iea_ev_table %>%
group_by(scenario) %>%
summarise(average_growth_rate = mean(growth_rate, na.rm = TRUE))
ggplot(avg_growth, aes(x=scenario, y=average_growth_rate, fill=scenario)) +
geom_bar(stat='identity') +
labs(title='Average Yearly Growth Rate by Scenario', x='Scenario', y='Average Growth Rate') +
theme(axis.text.x = element_text(angle = 45, hjust = 1, vjust = 1), plot.title = element_text(hjust = 0.5))