CONFIDENTIAL - TRQ fill rates data

Archived pages

func_old <- function(url, year, category){
  
  read_tables <- read_html(url) %>% 
    html_nodes("table") %>% 
    html_table()
  
  dta <- tibble()
  
  for (k in 1:length(read_tables)){
    
    ##############################################################
    
    if(ncol(read_tables[[k]]) == 3 & nrow(read_tables[[k]]) >= 3){
      
      df <- read_tables[[k]] %>%
        dplyr::filter(X1 != "")
      
      negotiated_level <- df$X3[str_detect(df$X3, "Negotiated")] %>%
        str_extract_all("\\d+", simplify = TRUE) %>%
        paste(collapse = "") %>%
        as.numeric()
      
      
      product <- df$X1 %>% 
        str_subset("CUSMA|CPTPP|CETA") %>%
        str_remove("CUSMA - ") %>%
        str_remove("CPTPP - ") %>%
        str_remove("CETA - ") %>%
        str_remove("Commodity Code2:") %>%
        str_remove_all("Total|-") %>%
        str_trim() %>%
        unique()
      
      
      if(str_detect(df$X1[2], "Tariff Code|Commodity Code")){
        
        df <- df %>%
          slice(-1)
        
      } 
      
      df <- df %>%
        janitor::row_to_names("find_header") %>%
        rename(`Tariff Code` = 1,
               `Tariff Description` = 2,
               `Permit Quantity` = 3) %>%
        dplyr::filter(!str_detect(`Tariff Code`, "CUSMA"),
                      !str_detect(`Tariff Code`, "CPTPP"),
                      !str_detect(`Tariff Code`, "CETA")) %>%
        mutate(`Tariff Description` = str_remove(`Tariff Description`, "CUSMA - ") %>% 
                 str_remove("CPTPP - ") %>%
                 str_remove("CETA - ") %>%
                 str_to_title(),
               `Permit Quantity` = str_remove_all(`Permit Quantity`, ",") %>% as.numeric(),
               Product = product,
               `Negotiated Level` = negotiated_level,
               Year = year,
               Category = category) 
      
      dta <- bind_rows(dta, df)
      
    }
    
    ##############################################################
    
    if(ncol(read_tables[[k]]) == 4 & nrow(read_tables[[k]]) >= 3){
      
      df <- read_tables[[k]] %>%
        dplyr::filter(X1 != "")
      
      
      product <- df$X1 %>% 
        str_subset("CUSMA|CPTPP|CETA") %>%
        str_remove("CUSMA - ") %>%
        str_remove("CPTPP - ") %>%
        str_remove("CETA - ") %>%
        str_remove("Commodity Code2:") %>%
        str_remove_all("Total|-") %>%
        str_trim() %>%
        unique()
      
      negotiated_level <- df$X4[str_detect(df$X4, "Negotiated")] %>%
        str_extract_all("\\d+", simplify = TRUE) %>%
        paste(collapse = "") %>%
        as.numeric()
      
      
      if(str_detect(df$X2[2], "Tariff Code|Commodity Code")){
        
        df <- df %>%
          slice(-1)
        
      } 
      
      df <- df %>%
        janitor::row_to_names("find_header") %>%
        dplyr::select(-1) %>%
        rename(`Tariff Code` = 1,
               `Tariff Description` = 2,
               `Permit Quantity` = 3) %>%
        dplyr::filter(!str_detect(`Tariff Code`, "CUSMA"),
                      !str_detect(`Tariff Code`, "CPTPP"),
                      !str_detect(`Tariff Code`, "CETA"),
                      !str_detect(`Tariff Code`, "Total")) %>%
        mutate(`Tariff Description` = str_remove(`Tariff Description`, "CUSMA - ") %>% 
                 str_remove("CPTPP - ") %>%
                 str_remove("CETA - ") %>%
                 str_to_title(),
               `Permit Quantity` = str_remove_all(`Permit Quantity`, ",") %>% as.numeric(),
               Product = product,
               `Negotiated Level` = negotiated_level,
               Year = year,
               Category = category) 
      
      dta <- bind_rows(dta, df)
      
    }
    
  }
  
  return(dta %>% dplyr::select(Category, Year, Product, `Tariff Description`, `Tariff Code`, `Permit Quantity`, `Negotiated Level`))
  
}

CUSMA

dta_cusma <- tibble()

# Dairy products - dairy year

dta_cusma <- dta_cusma %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CUSMA-20.htm", year = "2019-20", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CUSMA-21.htm", year = "2020-21", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CUSMA-22.htm", year = "2021-22", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CUSMA.htm", year = "2022-23", category = "Dairy Products"))

# Dairy products - calendar year

dta_cusma <- dta_cusma %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CUSMA-20.htm", year = "2020", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CUSMA-21.htm", year = "2021", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CUSMA-22.htm", year = "2022", category = "Dairy Products")) 

# Cheese

dta_cusma <- dta_cusma %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CUSMA-20.htm", year = "2020", category = "Cheese")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CUSMA-21.htm", year = "2021", category = "Cheese")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CUSMA-22.htm", year = "2022", category = "Cheese")) 

dta_cusma <- dta_cusma %>%
  distinct()

CPTPP

dta_cptpp <- tibble()

# Dairy products - dairy year

dta_cptpp <- dta_cptpp %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/ARCHIVE_2019_APRMT61C-D-DY-CPTPP.htm", year = "2018-19", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CPTPP-20.htm", year = "2019-20", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CPTPP-21.htm", year = "2020-21", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CPTPP-22.htm", year = "2021-22", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-DY-CPTPP.htm", year = "2022-23", category = "Dairy Products"))


# Dairy products - calendar year

dta_cptpp <- dta_cptpp %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/ARCHIVE_2018_APRMT61C-D-CY-cptpp.htm", year = "2018", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CPTPP-19.htm", year = "2019", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CPTPP-20.htm", year = "2020", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CPTPP-21.htm", year = "2021", category = "Dairy Products")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-D-CY-CPTPP-22.htm", year = "2022", category = "Dairy Products")) 

# Cheese

dta_cptpp <- dta_cptpp %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CPTPP-19.htm", year = "2019", category = "Cheese")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CPTPP-20.htm", year = "2020", category = "Cheese")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CPTPP-21.htm", year = "2021", category = "Cheese")) %>%
  bind_rows(func_old(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-CPTPP-22.htm", year = "2022", category = "Cheese")) 


dta_cptpp <- dta_cptpp %>%
  distinct()

Archived pages but other than CUSMA and CPTPP

func_cheese <- function(url, year, category){
  
  read_tables <- read_html(url) %>% 
    html_nodes("table") %>% 
    html_table()
  
  df <- read_tables[[5]]
  
  df0 <- df %>%
    dplyr::filter(str_detect(X1, "Tariff Code|Commodity Code")) %>%
    pivot_longer(-X1) %>%
    dplyr::filter(!str_detect(value, "Tariff Description|Commodity Description")) %>%
    mutate(`Trade agreement` = str_split(value, ":", simplify = TRUE)[,1] %>%
             str_replace(" Tariff Rate Quota", "") %>%
             str_replace(" Access Level", ""),
           `Access Quantity` = str_replace_all(value, ",", "") %>%
             str_extract_all("\\d+", simplify = FALSE) %>%
             as.numeric()) %>%
    dplyr::select(`Trade agreement`, `Access Quantity`) 

  df1 <- df %>%
    dplyr::filter(!(str_detect(X1, "Tariff|Commodity|Cheese"))) 
  
  colnames(df1) <- c("Tariff Code", "Tariff Description", df0$`Trade agreement`)
  
  df1 <- df1 %>%
    pivot_longer(-(1:2), names_to = "Trade agreement", values_to = "Permit Quantity") %>%
    mutate(`Permit Quantity` = str_replace_all(`Permit Quantity`, ",", "") %>%
             str_extract_all("\\d+", simplify = FALSE) %>%
             as.numeric())
  
  df <- left_join(df1, df0) %>%
    mutate(Year = year,
           Category = category) 
  
  df <- df %>%
    mutate(Product = case_when(`Trade agreement` == "CETA Cheese" ~ "Cheeses of All Types",
                               `Trade agreement` == "CETA Industrial Cheese" ~ "Industrial Cheeses",
                               `Trade agreement` == "CPTPP Cheese" ~ "Cheeses of All Types",
                               `Trade agreement` == "CPTPP Industrial Cheese" ~ "Industrial Cheeses",
                               `Trade agreement` == "CPTPP Mozzarella and Prepared Cheese" ~ "Mozzarella and Prepared Cheeses",
                               `Trade agreement` == "WTO - EU" ~ "Cheeses of All Types",
                               `Trade agreement` == "WTO - NON-EU" ~ "Cheeses of All Types",
                               .default = "To check"),
           `Trade agreement`= case_when(`Trade agreement` == "CETA Cheese" ~ "CETA",
                               `Trade agreement` == "CETA Industrial Cheese" ~ "CETA",
                               `Trade agreement` == "CPTPP Industrial Cheese" ~ "CPTPP",
                               `Trade agreement` == "CPTPP Cheese" ~ "CPTPP",
                               `Trade agreement` == "CPTPP Mozzarella and Prepared Cheese" ~ "CPTPP",
                               .default = `Trade agreement`)) %>%
    filter(!str_detect(`Trade agreement`, "CPTPP"))
  
  
  return(df %>% dplyr::select(Category, Year, `Tariff Description`, `Tariff Code`, `Trade agreement`, Product, `Permit Quantity`, `Access Quantity`))
  
}

dta_cheese <- func_cheese(url = "https://www.eics-scei.gc.ca/report-rapport/Arc_2018_APRMT61C-C.htm", year = "2018", category = "Cheese") %>%
  bind_rows(func_cheese(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-19.htm", year = "2019", category = "Cheese")) %>%
  bind_rows(func_cheese(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-20.htm", year = "2020", category = "Cheese")) %>%
  bind_rows(func_cheese(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C.htm", year = "2021", category = "Cheese")) %>%
  bind_rows(func_cheese(url = "https://www.eics-scei.gc.ca/report-rapport/APRMT61C-C-22.htm", year = "2022", category = "Cheese")) 
  

New pages

Data in the new pages combine the different agreements.

func_new <- function(url, year, category){
  
  read_tables <- read_html(url) %>% 
    html_nodes("table") %>% 
    html_table()
  
  dta <- tibble()

  df <- read_tables[[7]] %>%
      dplyr::select(-X1) %>%
      slice(-1)

  df0 <- df %>%
    dplyr::filter(X3 == "" | str_detect(X3, "Access")) %>%
    janitor::row_to_names(1) %>%
    janitor::clean_names() %>%
    pivot_longer(-(x:x_2)) %>%
    dplyr::select(name, `Access Quantity` = value) %>%
    mutate(`Trade agreement` = case_when(str_detect(name, "cptpp") ~ "CPTPP",
                                         str_detect(name, "cusma") ~ "CUSMA",
                                         str_detect(name, "ceta") ~ "CETA",
                                         str_detect(name, "supplemental") ~ "Supplemental",
                                         str_detect(name, "irep") ~ "IREP",
                                         str_detect(name, "wto_cheeses_of_all_types_trq_eu") ~ "WTO - EU",
                                         str_detect(name, "wto_cheeses_of_all_types_trq_non_eu_") ~ "WTO - NON-EU",
                                         .default = "Other to check"),
           name = str_remove(name, "_trq_kg"),
           name = str_remove(name, "_2"),
           name = str_remove(name, "_kg"),
           name = str_remove(name, "_Kg"),
           name = str_remove(name, "_trq_non_eu"),
           name = str_remove(name, "_trq_eu"),
           Product = str_match(name, '_\\w+$') %>% 
             str_replace_all("_", " ") %>% 
             str_trim() %>% 
             str_to_title(),
           `Access Quantity` = str_remove_all(`Access Quantity`, ",") %>% as.numeric()) %>%
    dplyr::select(-name) %>%
    distinct()
  
  df1 <- df %>%
    dplyr::filter(!(str_detect(X3, "Total") | str_detect(X3, "Access"))) %>%
    janitor::row_to_names(1) %>%
    janitor::clean_names() %>%
    pivot_longer(-(x:x_2)) %>%
    dplyr::select(`Tariff Code` = x, `Tariff Description` = x_2, name, `Permit Quantity` = value) %>%
    mutate(`Trade agreement` = case_when(str_detect(name, "cptpp") ~ "CPTPP",
                                         str_detect(name, "cusma") ~ "CUSMA",
                                         str_detect(name, "ceta") ~ "CETA",
                                         str_detect(name, "supplemental") ~ "Supplemental",
                                         str_detect(name, "irep") ~ "IREP",
                                         str_detect(name, "wto_cheeses_of_all_types_trq_eu") ~ "WTO - EU",
                                         str_detect(name, "wto_cheeses_of_all_types_trq_non_eu_") ~ "WTO - NON-EU",
                                         .default = "Other to check"),
           name = str_remove(name, "_trq_kg"),
           name = str_remove(name, "_2"),
           name = str_remove(name, "_kg"),
           name = str_remove(name, "_Kg"),
           name = str_remove(name, "_trq_non_eu"),
           name = str_remove(name, "_trq_eu"),
           Product = str_match(name, '_\\w+$') %>% 
             str_replace_all("_", " ") %>% 
             str_trim() %>% 
             str_to_title(),
           `Permit Quantity` = str_remove_all(`Permit Quantity`, ",") %>% as.numeric(),
           `Tariff Description` = str_to_title(`Tariff Description`)) %>%
    distinct()
  
  df <- left_join(df1, df0) %>%
    mutate(Year = year,
           Category = category) 
      
  return(df %>% dplyr::select(Category, Year, Product, `Tariff Description`, `Tariff Code`, `Trade agreement`, `Permit Quantity`, `Access Quantity`))
  
}

# Dairy year

dta_new <- func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk.htm", year = "2026-27", category = "Dairy Products") %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Butter_and_Cream_Powders.htm", year = "2026-27", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cream.htm", year = "2026-27", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk_Powder.htm", year = "2026-27", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Skim_Milk_Powders.htm", year = "2026-27", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Whey_Powder.htm", year = "2026-27", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk-26.htm", year = "2025-26", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Butter_and_Cream_Powders-26.htm", year = "2025-26", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cream-26.htm", year = "2025-26", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk_Powder-26.htm", year = "2025-26", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Skim_Milk_Powders-26.htm", year = "2025-26", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Whey_Powder-26.htm", year = "2025-26", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk-25.htm", year = "2024-25", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Butter_and_Cream_Powders-25.htm", year = "2024-25", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cream-25.htm", year = "2024-25", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk_Powder-25.htm", year = "2024-25", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Skim_Milk_Powders-25.htm", year = "2024-25", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Whey_Powder-25.htm", year = "2024-25", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk-24.htm", year = "2023-24", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Butter_and_Cream_Powders-24.htm", year = "2023-24", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cream-24.htm", year = "2023-24", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Milk_Powder-24.htm", year = "2023-24", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Skim_Milk_Powders-24.htm", year = "2023-24", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Whey_Powder-24.htm", year = "2023-24", category = "Dairy Products"))


# Calendar year
  
dta_new <- dta_new %>%  
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Yogurt_and_Buttermilk.htm", year = "2026", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cheese.htm", year = "2026", category = "Cheese")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Concentrated_Milk.htm", year = "2026", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Other_Dairy_and_food_preparations.htm", year = "2026", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Ice_Cream.htm", year = "2026", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Powdered_Buttermilk.htm", year = "2026", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Products_Consisting_of_Natural_Milk_Constituents.htm", year = "2026", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Ice_Cream-25.htm", year = "2025", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Powdered_Buttermilk-25.htm", year = "2025", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Products_Consisting_of_Natural_Milk_Constituents-25.htm", year = "2025", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Yogurt_and_Buttermilk-25.htm", year = "2025", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Concentrated_Milk-25.htm", year = "2025", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Other_Dairy_and_food_preparations-25.htm", year = "2025", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cheese-25.htm", year = "2025", category = "Cheese")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Ice_Cream-24.htm", year = "2024", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Powdered_Buttermilk-24.htm", year = "2024", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Products_Consisting_of_Natural_Milk_Constituents-24.htm", year = "2024", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Yogurt_and_Buttermilk-24.htm", year = "2024", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Concentrated_Milk-24.htm", year = "2024", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Other_Dairy_and_food_preparations-24.htm", year = "2024", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cheese-24.htm", year = "2024", category = "Cheese")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Ice_Cream-23.htm", year = "2023", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Powdered_Buttermilk-23.htm", year = "2023", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Products_Consisting_of_Natural_Milk_Constituents-23.htm", year = "2023", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Yogurt_and_Buttermilk-23.htm", year = "2023", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Concentrated_Milk-23.htm", year = "2023", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Other_Dairy_and_food_preparations-23.htm", year = "2023", category = "Dairy Products")) %>%
  bind_rows(func_new(url = "https://www.eics-scei.gc.ca/report-rapport/Utilization_Data-Cheese-23.htm", year = "2023", category = "Cheese")) 

Assemble data

dta <- dta_cusma %>%
  mutate(`Trade agreement` = "CUSMA") %>%
  rename(`Access Quantity` = `Negotiated Level`) %>%
  bind_rows(dta_cptpp %>%
            mutate(`Trade agreement` = "CPTPP") %>%
            rename(`Access Quantity` = `Negotiated Level`)) %>%
  bind_rows(dta_new) %>%
  bind_rows(dta_cheese) %>%
  mutate(Product = str_replace_all(Product, "And", "and"),
         Product = str_replace_all(Product, "Powder", "Powders"),
         Product = str_replace_all(Product, "Cheese", "Cheeses"),
         Product = str_replace_all(Product, "Of", "of"),
         Product = str_replace_all(Product, "Or", "or"),
         Product = str_replace_all(Product, "Ice cream and mixes", "Ice Cream and Mixes"),
         Product = str_replace_all(Product, "Diary", "Dairy"),
         Product = str_replace_all(Product, "Powdersed", "Powdered"),
         Product = str_replace_all(Product, "Products Consisting of Natural Milk Constituents", "Products Consisting of NMC"),
         Product = str_replace_all(Product, "Products of Natural Milk Constituents", "Products Consisting of NMC"),
         Product = str_replace_all(Product, "ss", "s")) %>%
  dplyr::filter(!(str_detect(Product, "Cheese") & str_detect(Category, "Dairy Products"))) %>%
  distinct() 

write_csv(dta, "Canada dairy TRQs utilization.csv")