# ---- load data.table package ----
library(data.table)

# ---- set up weathers ----
weathers_var <- c("wind.sp", "temp", "dew.point", "atm.press", "rh")
weathers_label <- c("Wind speed", "Temperature", "Dew point", "Atmospheric pressure", "Relative humidity")
weathers_unit <- c(" km/h", "&#8451;", "&#8451;", " hPa", "%")

# ---- met data read-in and processing ----
met <- fread("https://raw.githubusercontent.com/USCbiostats/data-science-data/master/02_met/met_all.gz")
# select columns
met <- met[, c("USAFID", "day", "hour", weathers_var), with = F]

# ---- station data read-in and processing ----
stations <- fread("ftp://ftp.ncdc.noaa.gov/pub/data/noaa/isd-history.csv")
# filter stations in US and select columns
stations <- stations[CTRY == "US", .(USAF, `STATION NAME`, STATE, LON, LAT)]

# drop NAs (999999) in USAF
stations[, USAF := as.integer(USAF)]
stations[, USAF := fifelse(USAF == 999999, NA_integer_, USAF)]
stations <- stations[!is.na(USAF)]

# drop NAs in STATE
stations[, STATE := fifelse(STATE == "", NA_character_, STATE)]
stations <- stations[!is.na(STATE)]

# keep only one data point for each USAF
stations[, n := 1:.N, by = .(USAF)]
stations <- stations[n == 1,][, n := NULL]

# ---- merge station into met ----
met <- merge(met, stations, by.x = "USAFID", by.y = "USAF", all.x = T)

# number of records and median value of weathers at station level
met_station <- met[, c(.N, lapply(.SD, median, na.rm = T)), 
                   by = .(USAFID, STATE, `STATION NAME`, LON, LAT),
                   .SDcols = weathers_var]

# median value of weathers at state level
met_state <- met[, lapply(.SD, median, na.rm = T), by = STATE,.SDcols = weathers_var]
# merge state-level median values
met_station <- merge(met_station, met_state, by = "STATE", suffixes = c(".station", ".state"))

# median value of weathers at country level
met_us <- met[, lapply(.SD, median, na.rm = T), .SDcols = weathers_var]
# add country-level median values
colnames(met_us) <- paste0(colnames(met_us), ".us")
met_station <- cbind(met_station, met_us)

library(kableExtra)
kable(met_station, row.names = T) |> kable_styling(font_size = 12) |> scroll_box(width = "100%", height = "150px")