CCA Plusvalía - Mejorado

1. Carga de datos —-

parcelas <- st_read("https://cdn.buenosaires.gob.ar/datosabiertos/datasets/secretaria-de-desarrollo-urbano/parcelas/parcelas.geojson", quiet = TRUE) %>%
  st_make_valid() %>%
  rename(SMP = smp) %>%
  mutate(SMP = str_trim(SMP))

CCA <- read_csv(here("GCBA", "CCA_Patr_1.csv"), col_names = TRUE)

## Rows: 5074 Columns: 10
## ── Column specification ────────────────────────────────────────────────────────
## Delimiter: ","
## chr (6): SMP, Direccion, Barrio, proteccion, Uso, ubicacion
## dbl (4): Sup_edif, CCAEC_1, CCAEC_2, CCAEC_3
## 
## ℹ Use `spec()` to retrieve the full column specification for this data.
## ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.

altura_parcelaria <- st_read(here("250225_Edificación_por_SMP"), quiet = TRUE)

# Join polygon geometry from 'parcelas' into 'CCA' by SMP
CCA <- CCA %>%
  left_join(parcelas %>% select(SMP, geometry), by = "SMP") %>%
  st_as_sf()

library(dplyr)

CCA <- CCA %>%
  mutate(
    # CCAEC_1
    CCAEC_1_monto_0_10x300 = CCAEC_1 * 0.10 * 56,
    CCAEC_1_monto_0_27x600 = CCAEC_1 * 0.27 * 206,
    CCAEC_1_monto_0_35x900 = CCAEC_1 * 0.35 * 421,

    # CCAEC_2
    CCAEC_2_monto_0_10x300 = CCAEC_2 * 0.10 * 56,
    CCAEC_2_monto_0_27x600 = CCAEC_2 * 0.27 * 206,
    CCAEC_2_monto_0_35x900 = CCAEC_2 * 0.35 * 421,

    # CCAEC_3
    CCAEC_3_monto_0_10x300 = CCAEC_3 * 0.10 * 56,
    CCAEC_3_monto_0_27x600 = CCAEC_3 * 0.27 * 206,
    CCAEC_3_monto_0_35x900 = CCAEC_3 * 0.35 * 421
  )

2. Limpieza y joins —-

# Extraer atributos relevantes de altura
altura_atrib <- altura_parcelaria %>% 
  st_drop_geometry() %>%
  rename(SMP = SMP) %>%
  select(SMP, ALTURA_MAX, VOLUMEN, SUPERFICIE, AREA_LIBRE, AREA_PARC) %>%
  mutate(SMP = str_trim(SMP))

# Join de altura a CCA
CCA <- CCA %>%
  left_join(altura_atrib, by = "SMP")

# Cargar superficie edificable (usa siempre rutas relativas si es posible)
constructibilidad <- st_read(here("Superficie_Edificable_y_Existente_por_Parcela"), quiet = TRUE)

CCA <- CCA %>%
  left_join(
    constructibilidad %>% 
      st_drop_geometry() %>%
      select(SMP, SUP_EXIST, NIV_Ley, M2_TOT_Ley),
    by = "SMP"
  )

3. Datos de DGROC_APH —-

DGROC_APH <- read_excel(here("DGROC_APH.xlsx")) %>%
  mutate(FECHA_CREACION_1 = dmy_hms(FECHA_CREACION_1)) %>%
  filter(!is.na(SMP)) %>%
  arrange(SMP, desc(FECHA_CREACION_1)) %>%
  group_by(SMP) %>%
  slice(1) %>%
  ungroup()

## Warning: There was 1 warning in `mutate()`.
## ℹ In argument: `FECHA_CREACION_1 = dmy_hms(FECHA_CREACION_1)`.
## Caused by warning:
## ! All formats failed to parse. No formats found.

4. Normalización de descripciones —-

# Función para normalizar descripciones
normalize_desc <- function(x) {
  case_when(
    str_detect(x, regex("aviso de obra", ignore_case = TRUE)) ~ "Aviso de Obra",
    str_detect(x, regex("demolicion|demolición", ignore_case = TRUE)) ~ "Permiso/Solicitud Demolición",
    str_detect(x, regex("plano.*obra civil", ignore_case = TRUE)) ~ "Registro/Regularización Plano Obra Civil",
    str_detect(x, regex("regularizaci", ignore_case = TRUE)) ~ "Regularización de Obra",
    TRUE ~ x
  )
}

DGROC_APH <- DGROC_APH %>% mutate(DESCRIPCION_REGULADA = normalize_desc(DESCRIPCION_CARATULA))

CCA <- CCA %>%
  left_join(
    DGROC_APH %>% 
      select(SMP, anio = AÑO),
    by = "SMP"
  )

CCA_dgroc <- CCA  %>% filter(anio !="")

Nos quedamos con las obras que tienen SMP

library(dplyr)
library(dplyr)
library(DT)
library(scales)

## Warning: package 'scales' was built under R version 4.3.3

## 
## Attaching package: 'scales'

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

## The following object is masked from 'package:readr':
## 
##     col_factor

CCA = CCA %>% filter(AREA_PARC < 5000)

tabla_resumen <- st_drop_geometry(CCA) %>%
  filter(anio >2020) %>% 
  group_by(anio) %>%
  summarise(
    n = n(),
    `Monto más chico` = sum(CCAEC_1_monto_0_10x300, na.rm = TRUE),
    `Monto medio` = sum(CCAEC_2_monto_0_27x600, na.rm = TRUE),
    `Monto más grande` = sum(CCAEC_3_monto_0_35x900, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  mutate(
    n = comma(n),
    `Monto más chico` = dollar(`Monto más chico`, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto medio` = dollar(`Monto medio`, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto más grande` = dollar(`Monto más grande`, prefix = "$", big.mark = ".", decimal.mark = ",")
  )

datatable(
  tabla_resumen,
  rownames = FALSE,
  options = list(
    pageLength = 10,
    autoWidth = TRUE,
    dom = 't', # solo tabla
    columnDefs = list(list(className = 'dt-center', targets = 0:4))
  )
)

# Calcular totales por año (ya lo tenés en tabla_resumen base sin formatear)
tabla_base <- st_drop_geometry(CCA) %>%
  filter(anio >2020) %>% 
  group_by(anio) %>%
  summarise(
    n = n(),
    monto_chico = sum(CCAEC_1_monto_0_10x300, na.rm = TRUE),
    monto_medio = sum(CCAEC_2_monto_0_27x600, na.rm = TRUE),
    monto_grande = sum(CCAEC_3_monto_0_35x900, na.rm = TRUE),
    .groups = "drop"
  )

# Calcular promedio anual total
promedio_anual_total <- tabla_base %>%
  summarise(
    `Promedio anual n` = mean(n),
    `Promedio anual Monto más chico` = mean(monto_chico),
    `Promedio anual Monto medio` = mean(monto_medio),
    `Promedio anual Monto más grande` = mean(monto_grande)
  ) %>%
  mutate(
    `Promedio anual n` = round(`Promedio anual n`, 0),
    `Promedio anual Monto más chico` = dollar(`Promedio anual Monto más chico`, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Promedio anual Monto medio` = dollar(`Promedio anual Monto medio`, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Promedio anual Monto más grande` = dollar(`Promedio anual Monto más grande`, prefix = "$", big.mark = ".", decimal.mark = ",")
  )

datatable(
  promedio_anual_total,
  rownames = FALSE,
  options = list(dom = 't', autoWidth = TRUE,
                 columnDefs = list(list(className = 'dt-center', targets = 0:(ncol(promedio_anual_total)-1)))
  )
)

9. Análisis espacial avanzado con ADPS y Microcentro —-

# 1. Read adps
adps <- st_read(here("GCBA", "adps"), quiet = TRUE)

wkt <- "POLYGON ((-58.3813282 -34.5966174, -58.3803841 -34.6178813, -58.3653637 -34.617069, -58.3674237 -34.5991608, -58.3714148 -34.5959108, -58.3813282 -34.5966174))"
# 2. Create microcentro geometry from WKT string
microcentro_poly <- st_as_sfc(wkt, crs = 4326)

# 3. Convert to sf and add columns
microcentro_poly <- st_sf(
  Id = NA_integer_,
  Nombre = "microcentro_2",
  m2 = NA_real_,
  geometry = microcentro_poly
)

# 4. Reproject microcentro_poly to match adps CRS
microcentro_poly <- st_transform(microcentro_poly, st_crs(adps))

# 5. Bind rows
adps_extended <- rbind(adps, microcentro_poly)

library(dplyr)
library(sf)
library(scales)
library(DT)

# Make sure CRS matches
CCA <- st_transform(CCA, st_crs(adps_extended))

# Intersection
CCA_intersect <- st_intersection(CCA, adps_extended)  %>%
  filter(!is.na(anio))

## Warning: attribute variables are assumed to be spatially constant throughout
## all geometries

tabla_resumen <- CCA_intersect %>%
  st_drop_geometry() %>%
  filter(anio > 2020) %>%
  group_by(anio) %>%
  summarise(
    n = n(),
    `Monto más chico` = sum(CCAEC_1_monto_0_10x300, na.rm = TRUE),
    `Monto medio` = sum(CCAEC_2_monto_0_27x600, na.rm = TRUE),
    `Monto más grande` = sum(CCAEC_3_monto_0_35x900, na.rm = TRUE),
    .groups = "drop"
  )

# Keep n numeric, format money columns as strings
tabla_resumen <- tabla_resumen %>%
  mutate(
    `Monto más chico` = dollar(`Monto más chico`, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto medio` = dollar(`Monto medio`, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto más grande` = dollar(`Monto más grande`, prefix = "$", big.mark = ".", decimal.mark = ",")
  )

datatable(
  tabla_resumen,
  rownames = FALSE,
  options = list(
    pageLength = 10,
    autoWidth = TRUE,
    dom = 't',
    columnDefs = list(
      list(className = 'dt-center', targets = 0:4),
      list(render = JS(
        "function(data, type, row, meta) {",
        "return type === 'display' && meta.col === 1 ? data.toLocaleString() : data;",
        "}"
      ), targets = 1)
    )
  )
)

tabla_base <- CCA_intersect %>%
  st_drop_geometry() %>%
  filter(anio > 2020) %>%
  group_by(anio) %>%
  summarise(
    n = n(),
    chico = sum(CCAEC_1_monto_0_10x300, na.rm = TRUE),
    medio = sum(CCAEC_2_monto_0_27x600, na.rm = TRUE),
    grande = sum(CCAEC_3_monto_0_35x900, na.rm = TRUE),
    .groups = "drop"
  )

promedio_anual <- summarise(tabla_base,
  n_prom = mean(n),
  chico_prom = mean(chico),
  medio_prom = mean(medio),
  grande_prom = mean(grande)
) %>%
  mutate(
    n_prom = round(n_prom),
    across(chico_prom:grande_prom, ~dollar(.x, prefix="$", big.mark=".", decimal.mark=","))
  )

datatable(promedio_anual, rownames=FALSE, options=list(dom='t', autoWidth=TRUE))

mapview(filter(CCA_intersect, anio!="")) + mapview(adps_extended)

library(dplyr)
library(sf)
library(scales)
library(DT)

CCA_bajos = CCA %>%
  filter(!is.na(anio), ALTURA_MAX < 15)

mapview(CCA_bajos) + mapview(adps_extended)

tabla_resumen_baja <- CCA %>%
  st_drop_geometry() %>%
  filter(anio > 2020, ALTURA_MAX < 15) %>%
  group_by(anio) %>%
  summarise(
    n = n(),
    monto_chico = sum(CCAEC_1_monto_0_10x300, na.rm = TRUE),
    monto_medio = sum(CCAEC_2_monto_0_27x600, na.rm = TRUE),
    monto_grande = sum(CCAEC_3_monto_0_35x900, na.rm = TRUE),
    .groups = "drop"
  ) %>%
  mutate(
    n_promedio = n,  # average per year = n / n_year = n because single row
    monto_chico_prom = monto_chico / n,
    monto_medio_prom = monto_medio / n,
    monto_grande_prom = monto_grande / n
  ) %>%
  mutate(
    n_fmt = format(n, big.mark = "."),
    `Monto más chico` = dollar(monto_chico, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto medio` = dollar(monto_medio, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto más grande` = dollar(monto_grande, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto más chico promedio` = dollar(monto_chico_prom, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto medio promedio` = dollar(monto_medio_prom, prefix = "$", big.mark = ".", decimal.mark = ","),
    `Monto más grande promedio` = dollar(monto_grande_prom, prefix = "$", big.mark = ".", decimal.mark = ",")
  ) %>%
  select(
    anio,
    n = n_fmt,
    `Monto más chico`,
    `Monto medio`,
    `Monto más grande`,
    `Monto más chico promedio`,
    `Monto medio promedio`,
    `Monto más grande promedio`
  )

## Warning: There was 1 warning in `mutate()`.
## ℹ In argument: `n_fmt = format(n, big.mark = ".")`.
## Caused by warning in `prettyNum()`:
## ! 'big.mark' and 'decimal.mark' are both '.', which could be confusing

datatable(
  tabla_resumen_baja,
  rownames = FALSE,
  options = list(
    pageLength = 10,
    autoWidth = TRUE,
    dom = 't',
    columnDefs = list(
      list(className = 'dt-center', targets = 0:(ncol(tabla_resumen_baja)-1))
    )
  )
)

library(dplyr); library(scales); library(DT)

tabla_base_baja <- CCA %>%
  st_drop_geometry() %>%
  filter(anio > 2020, ALTURA_MAX < 15) %>%
  group_by(anio) %>%
  summarise(
    n = n(),
    chico = sum(CCAEC_1_monto_0_10x300, na.rm = TRUE),
    medio = sum(CCAEC_2_monto_0_27x600, na.rm = TRUE),
    grande = sum(CCAEC_3_monto_0_35x900, na.rm = TRUE),
    .groups = "drop"
  )

promedio_anual_baja <- tabla_base_baja %>%
  summarise(
    n_prom = mean(n),
    chico_prom = mean(chico),
    medio_prom = mean(medio),
    grande_prom = mean(grande)
  ) %>%
  mutate(
    n_prom = round(n_prom),
    across(chico_prom:grande_prom, ~dollar(.x, prefix="$", big.mark=".", decimal.mark=","))
  )

datatable(promedio_anual_baja, rownames = FALSE, options = list(dom='t', autoWidth = TRUE))