brshallo

SQL Example: Summarizing Data with UNION ALL vs. GROUP BY

Data Setup

We create a table with products and segments:

-- Create the sample table
CREATE TABLE sales_data (
    product VARCHAR(10),
    segment VARCHAR(10),

brshallo

library(tidyverse)

sim_risk_ratios <- function(x){
  events <- map2(rep(c(TRUE, FALSE), 5), c(31, 414 - 31, 82, 1492 - 82, 252, 4832 - 252, 423, 11831 - 423, 52, 1509-52), rep) %>% unlist()
  outcomes <- tibble(
    group = map2(c("<8 h", "8<10 h", "10<12 h", "12-16 h", ">16 h"), c(414, 1492, 4832, 11831, 1509), rep) %>% unlist()) %>% 
    mutate(event_sim = sample(events, n(), replace = TRUE)) %>% 
    group_by(group) %>% 
 summarise(risk = mean(event_sim))

brshallo

library(magrittr)

find_in_files <- function(path, pattern){
  path %>% 
    fs::dir_ls(recurse = TRUE, type = "file", regexp = "(\\.[rR])$") %>% 
    purrr::map(~grep(pattern, readLines(.x, warn = FALSE), value = TRUE)) %>% 
    purrr::keep(~length(.x) > 0)
}

brshallo

library(tidyverse)

urls <- tibble(month = month.name, month_num = 1:12) %>% 
  cross_join(tibble(year = 2019:2023)) %>% 
  arrange(year) %>% 
  mutate(year_month = make_date(year = year, month = month_num)) %>% 
  filter(year_month < floor_date(today(), "months")) %>% 
  mutate(urls = paste0("https://www.seattle.gov/documents/Departments/SDCI/Resources/Stats/", year, month, "Summary", ".xlsx"))

brshallo

library(tidyverse)
library(httr)
library(jsonlite)

# downloaded data from: https://data.seattle.gov/Permitting/Building-Permits/76t5-zqzr
data_permits <- read_csv("Building_Permits.csv")

data_permits %>% 
 filter(PermitTypeDesc == "New") %&gt;% 

brshallo

# This example only includes a value in the rolling mean() if the close date on
# the historical dates comes after the snapshot date for row of interest


### CREATE SAMPLE DATA
library(tidyverse)
library(slider)
library(lubridate)

sample_size <- 5000

brshallo

# This example only includes a value in the rolling mean() if the close date on
# the historical dates comes after the snapshot date for row of interest


### CREATE SAMPLE DATA
library(tidyverse)
library(slider)

sample_size <- 5000
obs_per_day <- 100

brshallo

library(tidyverse)

lag_multiple <- function(x, n_vec){
  map(n_vec, lag, x = x) %>% 
    set_names(paste0("lag", n_vec)) %>% 
    as_tibble()
}

df <- tibble(a = 1:10, b = 11:20, c= 21:30)

brshallo

library(dplyr)
library(ggplot2)
library(modeldata)

data(ames)

set.seed(123)
ames %>% 
  sample_n(200) %>%

brshallo

library(priceR)
library(dplyr)
library(tidyr)
library(purrr)
library(lubridate)

# Create rates lookup table

pminmax <- function(x, y) {
  paste(pmin.int(x, y), pmax.int(x, y), sep = ".")