List of fpr_xfm_{*} Functions present in PSCIS Excel that require Transformations

[NewGraphEnvironment]

# path <- system.file("extdata", "pscis_phase1.xlsm", package = "fpr")

library(ngr)
library(knitr)
library(tidyverse)

path <- "~/Projects/repo/fpr/inst/extdata/pscis_phase1.xlsm"

f <- ngr::ngr_xl_map_formulas(path = path, 
                              sheet = "PSCIS Assessment Worksheet", 
                              rowid_colnames = 4, 
                              rowid_formulas = 5)
    
f2 <- f |> 
  dplyr::mutate(
    formula_name = formula_colname,
    formula_name = stringr::str_replace_all(formula_name, "stream_width_ratio", "swr"),
    formula_name = stringr::str_replace_all(formula_name, "culvert_length", "culvert_len"),
    formula_name = stringr::str_replace_all(formula_name, "culvert_", "cv_"),
    formula_name = 
      dplyr::case_when(
        stringr::str_detect(formula_name, "_score$") ~ 
          stringr::str_c("fpr_xfm_paw_score_", stringr::str_remove(formula_name, "_score$")),
        TRUE ~ 
          stringr::str_c("fpr_xfm_paw_", formula_name)
      ),
    url = paste0("https://www.newgraphenvironment.com/fpr/reference/", formula_name, ".html")
  )

f2 |> 
 knitr::kable(
 )

address_rowless	address	row	formula	formula_rowless	formula_colname	formula_with_col_names	formula_name	url
AF	AF5	5	IF(ISBLANK(Z5), 0, IF(ISBLANK(N5) < 0, 0,Z5 / N5))	IF(ISBLANK(Z), 0, IF(ISBLANK(N) < 0, 0,Z / N))	stream_width_ratio	IF(ISBLANK(downstream_channel_width_meters), 0, IF(ISBLANK(diameter_or_span_meters) < 0, 0,downstream_channel_width_meters / diameter_or_span_meters))	fpr_xfm_paw_swr	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_swr.html
AG	AG5	5	IF(O5<15,0,IF(AND(O5>=15,O5<30),3,IF(O5>=30,6,0)))	IF(O<15,0,IF(AND(O>=15,O<30),3,IF(O>=30,6,0)))	culvert_length_score	IF(length_or_width_meters<15,0,IF(AND(length_or_width_meters>=15,length_or_width_meters<30),3,IF(length_or_width_meters>=30,6,0)))	fpr_xfm_paw_score_cv_len	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_score_cv_len.html
AH	AH5	5	IF(P5 = “No”, 10, IF(ISBLANK(N5), 0, IF(ISBLANK(Q5), 0, IF(OR(Q5 / N5 <= 0.2, Q5 < 0.3), 5, IF(OR(Q5 / N5 >= 0.2, Q5 >= 0.3), 0)))))	IF(P = “No”, 10, IF(ISBLANK(N), 0, IF(ISBLANK(Q), 0, IF(OR(Q / N <= 0.2, Q < 0.3), 5, IF(OR(Q / N >= 0.2, Q >= 0.3), 0)))))	embed_score	IF(continuous_embeddedment_yes_no = “No”, 10, IF(ISBLANK(diameter_or_span_meters), 0, IF(ISBLANK(average_depth_embededdment_meters), 0, IF(OR(average_depth_embededdment_meters / diameter_or_span_meters <= 0.2, average_depth_embededdment_meters < 0.3), 5, IF(OR(average_depth_embededdment_meters / diameter_or_span_meters >= 0.2, average_depth_embededdment_meters >= 0.3), 0)))))	fpr_xfm_paw_score_embed	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_score_embed.html
AI	AI5	5	IF(V5<0.15,0,IF(AND(V5>=0.15,V5<0.3),5,IF(V5>=0.3,10,0)))	IF(V<0.15,0,IF(AND(V>=0.15,V<0.3),5,IF(V>=0.3,10,0)))	outlet_drop_score	IF(outlet_drop_meters<0.15,0,IF(AND(outlet_drop_meters>=0.15,outlet_drop_meters<0.3),5,IF(outlet_drop_meters>=0.3,10,0)))	fpr_xfm_paw_score_outlet_drop	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_score_outlet_drop.html
AJ	AJ5	5	IF(Y5<1,0,IF(AND(Y5>=1,Y5<3),5,IF(Y5>=3,10,0)))	IF(Y<1,0,IF(AND(Y>=1,Y<3),5,IF(Y>=3,10,0)))	culvert_slope_score	IF(culvert_slope_percent<1,0,IF(AND(culvert_slope_percent>=1,culvert_slope_percent<3),5,IF(culvert_slope_percent>=3,10,0)))	fpr_xfm_paw_score_cv_slope	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_score_cv_slope.html
AK	AK5	5	IF(AF5<1,0,IF(AND(AF5>=1,AF5<1.3),3,IF(AF5>=1.3,6,0)))	IF(AF<1,0,IF(AND(AF>=1,AF<1.3),3,IF(AF>=1.3,6,0)))	stream_width_ratio_score	IF(stream_width_ratio<1,0,IF(AND(stream_width_ratio>=1,stream_width_ratio<1.3),3,IF(stream_width_ratio>=1.3,6,0)))	fpr_xfm_paw_score_swr	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_score_swr.html
AL	AL5	5	AG5 + AH5 + AI5 + AJ5 + AK5	AG + AH + AI + AJ + AK	final_score	culvert_length_score + embed_score + outlet_drop_score + culvert_slope_score + stream_width_ratio_score	fpr_xfm_paw_score_final	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_score_final.html
AM	AM5	5	IF(ISBLANK(L5), ““, IF(L5 =”Open Bottom Structure”, “Passable”, IF(L5 = “Other”, “Unknown”, IF(AL5 < 15, “Passable”, IF(AND(AL5 >= 15,AL5 <= 19), “Potential”, IF(AL5 > 19, “Barrier”, ““))))))	IF(ISBLANK(L), ““, IF(L =”Open Bottom Structure”, “Passable”, IF(L = “Other”, “Unknown”, IF(AL < 15, “Passable”, IF(AND(AL >= 15,AL <= 19), “Potential”, IF(AL > 19, “Barrier”, ““))))))	barrier_result	IF(ISBLANK(crossing_type), ““, IF(crossing_type =”Open Bottom Structure”, “Passable”, IF(crossing_type = “Other”, “Unknown”, IF(final_score < 15, “Passable”, IF(AND(final_score >= 15,final_score <= 19), “Potential”, IF(final_score > 19, “Barrier”, ““))))))	fpr_xfm_paw_barrier_result	https://www.newgraphenvironment.com/fpr/reference/fpr_xfm_paw_barrier_result.html

# Add Markdown checkboxes
f3 <- f2 |> 
  dplyr::mutate(
    formula_with_checkbox = stringr::str_c("- [ ] ", formula_name)
  )

Print the list of checkboxes

cat(f3$formula_with_checkbox, sep = "\n")

• fpr_xfm_paw_swr
• fpr_xfm_paw_score_cv_len
• fpr_xfm_paw_score_embed #102
• fpr_xfm_paw_score_outlet_drop #105
• fpr_xfm_paw_score_cv_slope #106
• fpr_xfm_paw_score_swr #108
• fpr_xfm_paw_score_final #109
• fpr_xfm_paw_barrier_result #110

^{Created on 2025-01-14 with reprex v2.1.0}