Adam Homework 2-28

Quarto

Quarto enables you to weave together content and executable code into a finished document. To learn more about Quarto see https://quarto.org.

Running Code

When you click the Render button a document will be generated that includes both content and the output of embedded code. You can embed code like this:

library(tidyverse)

Warning: package 'tidyverse' was built under R version 4.3.2

Warning: package 'ggplot2' was built under R version 4.3.2

Warning: package 'dplyr' was built under R version 4.3.2

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.3     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.4     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ 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(readxl)

Warning: package 'readxl' was built under R version 4.3.2

library(here)

Warning: package 'here' was built under R version 4.3.2

here() starts at C:/Users/adams/Downloads

path_to_sheet <- here("Data Raw", "CM_Data_Explorer.xlsx")
path_to_sheet

[1] "C:/Users/adams/Downloads/Data Raw/CM_Data_Explorer.xlsx"

read_key_minerals_sheet <- partial(
  .f = read_excel,
  path = path_to_sheet,
  sheet = "1 Total demand for key minerals",
  col_names = FALSE
)

sheet_header <- read_key_minerals_sheet(range = "A4:W5")

New names:
• `` -> `...1`
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`

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"))

Warning: The `x` argument of `as_tibble.matrix()` must have unique column names if
`.name_repair` is omitted as of tibble 2.0.0.
ℹ Using compatibility `.name_repair`.

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 rows

mineral_name <- read_key_minerals_sheet(range = "A7") |> 
  pull()

New names:
• `` -> `...1`

mineral_name

[1] "Copper"

mineral_info <- read_key_minerals_sheet(range = "A8:W18")

New names:
• `` -> `...1`
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`

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 Solar PV  6.82e+2 NA    7.79e+2 9.07e+2 9.25e+2 9.59e+2 1.12e+3 1.26e+3 NA   
 2 Wind      3.94e+2 NA    4.28e+2 6.46e+2 5.95e+2 5.32e+2 6.39e+2 7.21e+2 NA   
 3 Other lo… 8.50e+1 NA    6.90e+1 1.08e+2 8.69e+1 8.47e+1 1.10e+2 1.17e+2 NA   
 4 Electric… 3.73e+2 NA    5.80e+2 1.04e+3 1.22e+3 1.42e+3 1.46e+3 1.35e+3 NA   
 5 Grid bat… 2.04e+1 NA    3.84e+1 8.27e+1 1.54e+2 2.25e+2 2.38e+2 2.20e+2 NA   
 6 Electric… 4.18e+3 NA    4.58e+3 6.51e+3 6.51e+3 6.58e+3 7.11e+3 6.98e+3 NA   
 7 Hydrogen… 3.20e-3 NA    3.88e-3 6.76e-3 6.53e-3 6.99e-3 8.90e-3 1.45e-2 NA   
 8 Total cl… 5.74e+3 NA    6.47e+3 9.30e+3 9.49e+3 9.80e+3 1.07e+4 1.06e+4 NA   
 9 Other us… 1.98e+4 NA    2.03e+4 2.15e+4 2.20e+4 2.34e+4 2.51e+4 2.57e+4 NA   
10 Total de… 2.55e+4 NA    2.68e+4 3.08e+4 3.15e+4 3.32e+4 3.58e+4 3.64e+4 NA   
11 Share of… 2.25e-1 NA    2.42e-1 3.01e-1 3.01e-1 2.96e-1 2.98e-1 2.93e-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 rows

mineral_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 Solar PV  ...2                    682. Copper      
 2 Solar PV  ...3                     NA  Copper      
 3 Solar PV  ...4                    779. Copper      
 4 Solar PV  ...5                    907. Copper      
 5 Solar PV  ...6                    925. Copper      
 6 Solar PV  ...7                    959. Copper      
 7 Solar PV  ...8                   1122. Copper      
 8 Solar PV  ...9                   1262. Copper      
 9 Solar PV  ...10                    NA  Copper      
10 Solar PV  ...11                   923. Copper      
# ℹ 232 more rows

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

# A tibble: 209 × 6
   mineral_name indicator scenario                   unit     year value
   <chr>        <chr>     <chr>                      <chr>   <int> <dbl>
 1 Copper       Solar PV  Current Year               kiloton  2022  682.
 2 Copper       Solar PV  Stated policies scenario   kiloton  2025  779.
 3 Copper       Solar PV  Stated policies scenario   kiloton  2030  907.
 4 Copper       Solar PV  Stated policies scenario   kiloton  2035  925.
 5 Copper       Solar PV  Stated policies scenario   kiloton  2040  959.
 6 Copper       Solar PV  Stated policies scenario   kiloton  2045 1122.
 7 Copper       Solar PV  Stated policies scenario   kiloton  2050 1262.
 8 Copper       Solar PV  Announced pledges scenario kiloton  2025  923.
 9 Copper       Solar PV  Announced pledges scenario kiloton  2030 1177.
10 Copper       Solar PV  Announced pledges scenario kiloton  2035 1369.
# ℹ 199 more rows

mineral_name_range <- "A7"
mineral_info_range <- "A8:W18"


mineral_name <- read_key_minerals_sheet(range = mineral_name_range) |> 
  pull()

New names:
• `` -> `...1`

mineral_info <- read_key_minerals_sheet(range = mineral_info_range)

New names:
• `` -> `...1`
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`

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

# A tibble: 209 × 6
   mineral_name indicator scenario                   unit     year value
   <chr>        <chr>     <chr>                      <chr>   <int> <dbl>
 1 Copper       Solar PV  Current Year               kiloton  2022  682.
 2 Copper       Solar PV  Stated policies scenario   kiloton  2025  779.
 3 Copper       Solar PV  Stated policies scenario   kiloton  2030  907.
 4 Copper       Solar PV  Stated policies scenario   kiloton  2035  925.
 5 Copper       Solar PV  Stated policies scenario   kiloton  2040  959.
 6 Copper       Solar PV  Stated policies scenario   kiloton  2045 1122.
 7 Copper       Solar PV  Stated policies scenario   kiloton  2050 1262.
 8 Copper       Solar PV  Announced pledges scenario kiloton  2025  923.
 9 Copper       Solar PV  Announced pledges scenario kiloton  2030 1177.
10 Copper       Solar PV  Announced pledges scenario kiloton  2035 1369.
# ℹ 199 more rows

read_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
  }

copper_table <- read_iea_mineral_table(
  mineral_name_range = "A7",
  mineral_info_range = "A8:W18"
)

New names:
New names:
• `` -> `...1`

cobalt_table <- read_iea_mineral_table(
  mineral_name_range = "A21",
  mineral_info_range = "A22:W29"
)

New names:
New names:
• `` -> `...1`

lithium_table <- read_iea_mineral_table(
  mineral_name_range = "A32",
  mineral_info_range = "A33:W38"
)

New names:
New names:
• `` -> `...1`

nickel_table <- read_iea_mineral_table(
  mineral_name_range = "A41",
  mineral_info_range = "A42:W51"
)

New names:
New names:
• `` -> `...1`

neodymium_table <- read_iea_mineral_table(
  mineral_name_range = "A54",
  mineral_info_range = "A55:W60"
)

New names:
New names:
• `` -> `...1`

final_iea_minerals_table <- copper_table |> 
  bind_rows(cobalt_table) |> 
  bind_rows(lithium_table) |>
  bind_rows(nickel_table) |>
  bind_rows(neodymium_table)

final_iea_minerals_table

# A tibble: 779 × 6
   mineral_name indicator scenario                   unit     year value
   <chr>        <chr>     <chr>                      <chr>   <int> <dbl>
 1 Copper       Solar PV  Current Year               kiloton  2022  682.
 2 Copper       Solar PV  Stated policies scenario   kiloton  2025  779.
 3 Copper       Solar PV  Stated policies scenario   kiloton  2030  907.
 4 Copper       Solar PV  Stated policies scenario   kiloton  2035  925.
 5 Copper       Solar PV  Stated policies scenario   kiloton  2040  959.
 6 Copper       Solar PV  Stated policies scenario   kiloton  2045 1122.
 7 Copper       Solar PV  Stated policies scenario   kiloton  2050 1262.
 8 Copper       Solar PV  Announced pledges scenario kiloton  2025  923.
 9 Copper       Solar PV  Announced pledges scenario kiloton  2030 1177.
10 Copper       Solar PV  Announced pledges scenario kiloton  2035 1369.
# ℹ 769 more rows

library(tidyquant)

Warning: package 'tidyquant' was built under R version 4.3.2

Loading required package: PerformanceAnalytics

Warning: package 'PerformanceAnalytics' was built under R version 4.3.2

Loading required package: xts
Loading required package: zoo

Attaching package: 'zoo'

The following objects are masked from 'package:base':

    as.Date, as.Date.numeric


######################### Warning from 'xts' package ##########################
#                                                                             #
# The dplyr lag() function breaks how base R's lag() function is supposed to  #
# work, which breaks lag(my_xts). Calls to lag(my_xts) that you type or       #
# source() into this session won't work correctly.                            #
#                                                                             #
# Use stats::lag() to make sure you're not using dplyr::lag(), or you can add #
# conflictRules('dplyr', exclude = 'lag') to your .Rprofile to stop           #
# dplyr from breaking base R's lag() function.                                #
#                                                                             #
# Code in packages is not affected. It's protected by R's namespace mechanism #
# Set `options(xts.warn_dplyr_breaks_lag = FALSE)` to suppress this warning.  #
#                                                                             #
###############################################################################

Attaching package: 'xts'

The following objects are masked from 'package:dplyr':

    first, last


Attaching package: 'PerformanceAnalytics'

The following object is masked from 'package:graphics':

    legend

Loading required package: quantmod
Loading required package: TTR
Registered S3 method overwritten by 'quantmod':
  method            from
  as.zoo.data.frame zoo 

equity_data <- c("REMX", "SPY") |> 
  tq_get()
equity_data

# A tibble: 5,112 × 8
   symbol date        open  high   low close volume adjusted
   <chr>  <date>     <dbl> <dbl> <dbl> <dbl>  <dbl>    <dbl>
 1 REMX   2014-01-02  108.  109.  107.  108.  12900     77.8
 2 REMX   2014-01-03  108.  109.  108.  108.   3833     78.1
 3 REMX   2014-01-06  109.  109.  108.  108.   7967     77.9
 4 REMX   2014-01-07  108.  108.  108.  108.   5133     77.8
 5 REMX   2014-01-08  107.  108.  105.  105.  14200     76.1
 6 REMX   2014-01-09  106.  106.  105.  105.  13600     76.0
 7 REMX   2014-01-10  105.  107.  105.  106.   5967     76.7
 8 REMX   2014-01-13  106.  106.  105.  105.   3000     75.9
 9 REMX   2014-01-14  106.  107.  106.  106.   4867     76.9
10 REMX   2014-01-15  107.  109.  107.  109.  26133     78.4
# ℹ 5,102 more rows

equity_data |> 
  group_by(symbol) |> 
  mutate(adjusted_index_100 = adjusted/first(adjusted) * 100) |> 
  ungroup() |> 
  mutate(name = case_when(
    symbol == "REMX" ~ "Critical Minerals",
    symbol == "SPY" ~ "S&P 500"
  )) |> 
  ggplot(aes(x = date, y = adjusted_index_100, color = fct_reorder2(name, date, adjusted_index_100))) +
  geom_line() +
  scale_y_continuous(labels = scales::label_dollar()) +
  labs(title = "$100 Invested For 10 Years: Critical Minerals vs. S&P 500",
       subtitle = "Some catchy takeaway for your subtitle",
       x = "",
       y = "Value of $100 Invested 10 Years Ago",
       color = "", 
       caption = "Data: Yahoo Finance | Calculations: Me")

#Homework Question 1

read_key_minerals_sheet_2 <- partial(
  .f = read_excel,
  path = path_to_sheet,
  sheet = "2.3 EV",
  col_names = FALSE
)

sheet_header_2 <- read_key_minerals_sheet_2(range = "A4:W5")

New names:
• `` -> `...1`
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`

sheet_header_2

# 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_2 <- sheet_header_2 |> 
  t() |>
  as_tibble() |>
  rename(scenario = V1, year = V2) |>
  fill(scenario) |>
  replace_na(list(scenario = "Current Year"))
sheet_header_processed_2

# 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 rows

subject_desc_range <- "A7"
subject_desc <- read_key_minerals_sheet_2(range = subject_desc_range) |> pull()

New names:
• `` -> `...1`

subject_info_range <- "A8:A18"
subject_info <- read_key_minerals_sheet_2(range = subject_info_range)

New names:
• `` -> `...1`

subject_info_col_names <- names(subject_info)

subject_desc <- read_key_minerals_sheet_2(range = "A7") |> 
  pull()

New names:
• `` -> `...1`

subject_desc

[1] "Constrained nickel supply"

subject_info <- read_key_minerals_sheet_2(range = "A8:W18")

New names:
• `` -> `...1`
• `` -> `...2`
• `` -> `...3`
• `` -> `...4`
• `` -> `...5`
• `` -> `...6`
• `` -> `...7`
• `` -> `...8`
• `` -> `...9`
• `` -> `...10`
• `` -> `...11`
• `` -> `...12`
• `` -> `...13`
• `` -> `...14`
• `` -> `...15`
• `` -> `...16`
• `` -> `...17`
• `` -> `...18`
• `` -> `...19`
• `` -> `...20`
• `` -> `...21`
• `` -> `...22`
• `` -> `...23`

subject_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>

subject_info_col_names <- names(subject_info)
subject_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_2 <- sheet_header_processed_2 |> 
  add_column(subject_info_col_names = subject_info_col_names)
sheet_headers_and_col_names_2

# A tibble: 23 × 3
   scenario                 year  subject_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 rows

read_iea_mineral_table_2 <- function(subject_desc_range, subject_info_range) {
  subject_name <- read_key_minerals_sheet_2(range = subject_desc_range) |> pull()
  
  subject_info <- read_key_minerals_sheet_2(range = subject_info_range)
  
  if (nrow(subject_info) == 0) {
    return(tibble())  # Return an empty tibble if no data is present
  }
  
  col_names <- seq_len(ncol(subject_info))
  col_names[1] <- "indicator"  # Rename the first column to "indicator"
  
  sheet_headers_and_col_names_2 <- sheet_header_processed_2 |> 
    add_column(subject_info_col_names = subject_info_col_names)

  
  # Specify columns to convert to character
  cols_to_char <- setdiff(col_names, "value")  # Exclude "value" column
  
  subject_info_long <- subject_info |>
    set_names(sheet_headers_and_col_names_2) |>
    mutate(across(all_of(cols_to_char), as.character)) |>
    pivot_longer(cols = -indicator, names_to = "subject_info_col_names") |>
    add_column(subject_name)  # Add subject_name column

}

read_iea_mineral_table_2 <- function(subject_desc_range, subject_info_range) {
  subject_name <- read_key_minerals_sheet_2(range = subject_desc_range) |> pull()
  
  subject_info <- read_key_minerals_sheet_2(range = subject_info_range)
  
  subject_info_long <- subject_info |>
    rename(indicator = `...1`) |>
    pivot_longer(cols = -indicator,
                 names_to = "subject_info_col_names") |>
    add_column(subject_name)
  
  combined_data <- subject_info_long |>
    left_join(sheet_headers_and_col_names_2, by = join_by(subject_info_col_names)) |>
    filter(!is.na(year)) |>
    mutate(
      unit = case_when(
        indicator == "Total EV" ~ "Total",
        .default = "kiloton"
      ),
      year = as.integer(year)
    ) |>
    select(subject_name, indicator, scenario, unit, year, value)
  
  combined_data
}

constrained_nick_table <- read_iea_mineral_table_2(
  subject_desc_range = "A7",
  subject_info_range = "A8:W18"
)

New names:
New names:
• `` -> `...1`

wider_silicon_table <- read_iea_mineral_table_2(
  subject_desc_range = "A22",
  subject_info_range = "A23:W33"
)

New names:
New names:
• `` -> `...1`

faster_uptake_batteries_table <- read_iea_mineral_table_2(
  subject_desc_range = "A37",
  subject_info_range = "A38:W48"
)

New names:
New names:
• `` -> `...1`

lower_battery_table <- read_iea_mineral_table_2(
  subject_desc_range = "A52",
  subject_info_range = "A53:W63"
)

New names:
New names:
• `` -> `...1`

STEPS_table <- read_iea_mineral_table_2(
  subject_desc_range = "A82",
  subject_info_range = "A83:W93"
)

New names:
New names:
• `` -> `...1`

final_iea_minerals_table_2 <- constrained_nick_table |> 
  bind_rows(wider_silicon_table) |>
  bind_rows(faster_uptake_batteries_table)|>
  bind_rows(lower_battery_table) |>
  bind_rows(STEPS_table)
final_iea_minerals_table_2

# A tibble: 1,045 × 6
   subject_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.
# ℹ 1,035 more rows

ggplot(final_iea_minerals_table_2, aes(x = indicator, y = value, fill = indicator)) + geom_col()

ggplot(final_iea_minerals_table_2, aes(x = subject_name, y = value, fill = subject_name)) + geom_col()

ggplot(final_iea_minerals_table_2, aes(x = scenario, y = value, fill = subject_name)) + geom_point()

ggplot(final_iea_minerals_table_2, aes(x = (indicator = "Copper"), y = subject_name, fill = subject_name)) + geom_col()

ggplot(final_iea_minerals_table_2, aes(x = year, y = value, fill = indicator)) + geom_col()