model-res-avm/testing_file.R at 5d8583d370fd63b8bafe5cfc333b440092aaf40d · ccao-data/model-res-avm · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
#–– PARAMETERS ––#
run_id_val   <- "2025-05-30-exciting-dan"
grouping_col <- "char_bldg_sf"

# 2025-02-11-charming-eric


#–– SETUP ––#
tictoc::tic.clearlog()
tictoc::tic("Ingest")

purrr::walk(list.files("R/", "\\.R$", full.names = TRUE), source)

suppressPackageStartupMessages({
  library(DBI)
  library(igraph)
  library(noctua)
  library(glue)
  library(dplyr)
})

noctua_options(unload = TRUE)

AWS_ATHENA_CONN_NOCTUA <- dbConnect(
  noctua::athena(),
  rstudio_conn_tab = FALSE
)


#–– LOAD DATA WITH PARAMETERIZED run_id ––#
data <- dbGetQuery(
  conn = AWS_ATHENA_CONN_NOCTUA,
  glue("
    SELECT
      v.meta_tieback_key_pin,
      card.meta_pin,
      card.meta_card_num,
      card.char_bldg_sf,
      card.char_land_sf,
      v.meta_tieback_proration_rate,
      card.pred_card_initial_fmv   AS pred_card_initial_fmv,
      card.pred_card_final_fmv     AS pred_card_final_fmv,
      pin.pred_pin_initial_fmv     AS pred_pin_initial_fmv,
      pin.pred_pin_final_fmv       AS pred_pin_final_fmv
    FROM model.assessment_card AS card
    JOIN model.vw_card_res_input AS v
      ON CAST(card.meta_card_num AS VARCHAR) = CAST(v.meta_card_num AS VARCHAR)
      AND CAST(card.meta_pin        AS VARCHAR) = v.meta_pin
      AND v.year = '2025'
    JOIN model.assessment_pin AS pin
      ON card.meta_pin = pin.meta_pin
      AND card.run_id  = pin.run_id
    WHERE
      v.meta_tieback_key_pin IS NOT NULL
      AND card.run_id       = '{run_id_val}'
  ")
)


group_cols <- c("meta_tieback_key_pin", grouping_col, "meta_card_num")


test <- data %>%
  group_by(across(all_of(group_cols))) %>%
  filter(n_distinct(pred_pin_initial_fmv) != 1) %>%
  mutate(group_label = cur_group_id()) %>%
  ungroup()


test_1 <- data %>%
  group_by(across(all_of(group_cols))) %>%
  filter(n_distinct(pred_card_initial_fmv) != 1) %>%
  mutate(group_label = cur_group_id()) %>%
  ungroup()


test_2 <- data %>%
  group_by(across(all_of(group_cols))) %>%
  filter(n_distinct(pred_card_final_fmv) != 1) %>%
  mutate(group_label = cur_group_id()) %>%
  ungroup()


test_3 <- data %>%
  group_by(across(all_of(group_cols))) %>%
  filter(n_distinct(pred_pin_final_fmv) != 1) %>%
  mutate(group_label = cur_group_id()) %>%
  ungroup()


tictoc::toc()