Automated Belleair Chloride Report

Load packages:

library(dplyr)

Attaching package: 'dplyr'

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

    filter, lag

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

    intersect, setdiff, setequal, union

library(ggplot2)
library(readxl)
library(purrr)
library(lubridate)

Attaching package: 'lubridate'

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

    date, intersect, setdiff, union

library(scales)

Attaching package: 'scales'

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

    discard

library(here)

here() starts at /Users/ayeshanaveed/Downloads

data_dir <- here("chloride_data")
excel_file <- here("BelleairChlorideReport.xlsx")

Clean Excel sheets:

# Get sheet names
sheet_names <- excel_sheets(excel_file)

# Function to read each sheet with some warning suppression
read_sheet_safe <- function(sheet) {
  suppressMessages(
    suppressWarnings(
      read_excel(excel_file, sheet = sheet, .name_repair = "universal", guess_max = 10000)
    )
  )
}

# Read all sheets
results <- map(sheet_names, safely(read_sheet_safe))
names(results) <- sheet_names

# Identify sheets that failed to import (have only charts/figures)
failed_sheets <- names(results)[map_lgl(results, ~ !is.null(.x$error))]
print(failed_sheets)

character(0)

# Exclude sheets with only charts/figures
valid_sheets <- setdiff(sheet_names, failed_sheets)

# Read only sheets with data
all_data <- map(valid_sheets, read_sheet_safe) %>% set_names(valid_sheets)

CL Limits:

cl_limits_all <- tibble::tribble(
  ~well_number, ~start_date, ~end_date, ~cl_value,
  2, as.Date("2007-07-01"), as.Date("2018-01-07"), 120,
  2, as.Date("2018-01-08"), as.Date("2021-12-06"), 135,
  3, as.Date("2007-07-01"), as.Date("2022-12-05"), 250,
  5, as.Date("2007-07-01"), as.Date("2017-12-03"), 140,
  5, as.Date("2017-12-03"), as.Date("2023-11-06"), 170,
  6, as.Date("2005-01-16"), as.Date("2017-11-06"), 130,
  6, as.Date("2017-11-06"), as.Date("2023-11-06"), 410,
  7, as.Date("2007-07-01"), as.Date("2017-11-06"), 170,
  7, as.Date("2017-12-04"), as.Date("2023-11-06"), 225,
  9, as.Date("2007-07-01"), as.Date("2017-11-06"), 100,
  9, as.Date("2017-11-06"), as.Date("2023-11-06"), 110,
  10, as.Date("2007-07-01"), as.Date("2017-11-06"), 80,
  10, as.Date("2017-12-04"), as.Date("2023-11-06"), 110
)

Function 1 - Plotting Period of Record for Production Wells

plot_prod_well <- function(well_number, all_data, cl_limits_all) {
  sheet_name <- paste0("Cl Well ", well_number)
  if (!sheet_name %in% names(all_data)) {
    stop(paste("Sheet", sheet_name, "not found in all_data."))
  }
  df <- all_data[[sheet_name]]
  df <- df[, !duplicated(names(df))]
  col_names <- names(df)
  
  # Find columns
  date_col   <- col_names[grepl("^Date$", col_names, ignore.case = TRUE)][1]
  cl_col     <- col_names[grepl(paste0("Well\\.", well_number, "\\.CL"), col_names)][1]
  loess_col  <- col_names[grepl("^LOESS", col_names)][1]
  
  if (is.na(date_col) | is.na(cl_col) | is.na(loess_col)) {
    stop("Could not find one or more required columns in the sheet: ",
         paste(c(date_col, cl_col, loess_col), collapse = ", "))
  }
  print(colnames(df))
  
  # Take care of Excel serial dates and filter date range
  df <- df %>%
    mutate(
      Date = if (inherits(.data[[date_col]], "Date")) {
        .data[[date_col]]
      } else if (inherits(.data[[date_col]], "POSIXct") || inherits(.data[[date_col]], "POSIXlt")) {
        as.Date(.data[[date_col]])
      } else if (is.numeric(.data[[date_col]])) {
        as.Date(.data[[date_col]], origin = "1899-12-30")
      } else {
        as.Date(as.character(.data[[date_col]]), format = "%m/%d/%y")
      },
      CL = as.numeric(.data[[cl_col]]),
      LOESS = as.numeric(.data[[loess_col]])
    ) %>%
    filter(
      !is.na(Date) & 
      Date > as.Date("1970-01-01") & Date < as.Date("2030-01-01") &
      !is.na(CL) & !is.na(LOESS)
    )
  
  # For x-axis: ticks at every January & labels every 5th year
  jan_years <- seq(
    from = as.Date(paste0(format(min(df$Date, na.rm=TRUE), "%Y"), "-01-01")),
    to   = as.Date(paste0(format(max(df$Date, na.rm=TRUE), "%Y"), "-01-01")),
    by = "year"
  )
  
  # Filter CL limits for each well
  cl_limits_df <- cl_limits_all %>%
    filter(well_number == !!well_number)
  
  p <- ggplot(df, aes(x = Date)) +
    geom_point(
      aes(y = CL, fill = "Chloride Concentrations"),
      shape = 23, size = 0.5, alpha = 0.5, color = "darkblue", stroke = 1.2
    ) +
    geom_line(aes(y = LOESS, color = "LOESS (Chloride Concentrations)"), size = 1) +
    geom_segment(
      data = cl_limits_df,
      aes(x = start_date, xend = end_date, y = cl_value, yend = cl_value, color = "CL Limit"),
      inherit.aes = FALSE,
      size = 1.2
    ) +
    scale_x_date(
      breaks = jan_years,
      labels = function(d) {
        y <- as.numeric(format(d, "%Y"))
        ifelse(y %% 5 == 0, format(d, "%b-%y"), "")
      },
      expand = expansion(mult = c(0.01, 0.01))
    ) +
    scale_color_manual(
      values = c(
        "LOESS (Chloride Concentrations)" = "magenta",
        "CL Limit" = "red"
      )
    ) +
    scale_fill_manual(
      values = c(
        "Chloride Concentrations" = "lightblue"
      )
    ) +
    labs(
      title = paste("Period of Record - Production Well", well_number),
      x = "Date",
      y = "Chloride Concentration (mg/L)",
      color = NULL,
      fill = NULL
    ) +
    theme_minimal() +
    theme(
      legend.position = "top",
      axis.text.x = element_text(angle = 0, hjust = 0.5)
    )
  
  print(p)
}

Plot Period of Record - Production Cl Wells

plot_prod_well(2, all_data, cl_limits_all)

 [1] "Date"               "Well.2.CL"          "LOESS.2023"        
 [4] "LOESS.2023.Fig.2.2" "...5"               "...6"              
 [7] "...7"               "...8"               "...9"              
[10] "...10"              "...11"              "...12"             
[13] "...13"              "...14"              "...15"             
[16] "...16"              "...17"              "...18"             
[19] "...19"              "...20"              "...21"             
[22] "...22"              "...23"              "...24"             
[25] "...25"              "...26"              "...27"             
[28] "...28"              "...29"              "...30"             
[31] "...31"              "...32"              "...33"             
[34] "...34"              "...35"              "...36"             
[37] "...37"              "...38"              "...39"             
[40] "...40"              "...41"              "...42"             
[43] "...43"              "...44"              "...45"             
[46] "...46"              "...47"              "...48"             
[49] "...49"              "...50"              "...51"             
[52] "...52"              "...53"              "...54"             
[55] "...55"              "...56"              "...57"             
[58] "...58"              "...59"              "...60"             
[61] "...61"              "...62"             

Warning: Using `size` aesthetic for lines was deprecated in ggplot2 3.4.0.
ℹ Please use `linewidth` instead.

plot_prod_well(3, all_data, cl_limits_all)

 [1] "Date"           "Well.3.CL"      "LOESS.2023"     "LOESS.5yr.2023"
 [5] "...5"           "...6"           "...7"           "...8"          
 [9] "...9"           "...10"          "...11"          "...12"         
[13] "...13"          "...14"          "...15"          "...16"         
[17] "...17"          "...18"          "...19"          "...20"         
[21] "...21"          "...22"          "...23"          "...24"         
[25] "...25"          "...26"          "...27"          "...28"         
[29] "...29"          "...30"          "...31"          "...32"         
[33] "...33"          "...34"          "...35"          "...36"         
[37] "...37"          "...38"          "...39"          "...40"         
[41] "...41"         

plot_prod_well(5, all_data, cl_limits_all)

[1] "Date"              "Well.5.CL...2"     "LOESS.2023"       
[4] "...4"              "Date.5yr"          "Well.5.CL...6"    
[7] "..5.yr.Loess.2023"

plot_prod_well(6, all_data, cl_limits_all)

 [1] "Date"             "Well.6.CL"        "LOESS.2023"       "..5yr.LOESS.2023"
 [5] "...5"             "...6"             "...7"             "...8"            
 [9] "...9"             "...10"            "...11"            "...12"           
[13] "...13"            "...14"            "...15"            "...16"           
[17] "...17"            "...18"            "...19"           

plot_prod_well(7, all_data, cl_limits_all)

[1] "Date"             "Well.7.CL"        "LOESS.2023"       "..5yr.LOESS.2023"

plot_prod_well(9, all_data, cl_limits_all)

 [1] "Date"             "Well.9.CL"        "LOESS.2023"       "..5yr.LOESS.2023"
 [5] "...5"             "...6"             "...7"             "...8"            
 [9] "...9"             "...10"            "...11"            "...12"           
[13] "...13"            "...14"            "...15"            "...16"           
[17] "...17"            "...18"            "...19"            "...20"           
[21] "...21"            "...22"            "...23"            "...24"           
[25] "...25"            "...26"            "...27"            "...28"           
[29] "...29"            "...30"            "...31"           

plot_prod_well(10, all_data, cl_limits_all)

 [1] "Date"                 "Well.10.CL"           "LOESS.2023"          
 [4] "..5yr.LOESS.2023"     "...5"                 "...6"                
 [7] "...7"                 "...8"                 "...9"                
[10] "...10"                "...11"                "...12"               
[13] "...13"                "...14"                "...15"               
[16] "...16"                "...17"                "...18"               
[19] "...19"                "...20"                "...21"               
[22] "...22"                "...23"                "...24"               
[25] "...25"                "...26"                "...27"               
[28] "...28"                "...29"                "...30"               
[31] "...31"                "...32"                "...33"               
[34] "...34"                "...35"                "...36"               
[37] "...37"                "...38"                "...38459"            
[40] "..36.274527080846518" "...42"