Greetings! I have been assigned to carry on work that a recently resigned colleague had been doing. Her knowledge of R was fairly advanced, while mine remains rather limited. I'm struggling to fully understand what some of her code does, exactly. I'm trying to look up functions and packages as I go through her code but that is taking a long time and there is some time pressure here. The whole thing is a bit frustrating. Anywho, I'm hoping folks here can maybe help me understand what various chunks of code are doing and would appreciate any and all help!
First, the packages in use:
library(stringr)
library(reshape2)
library(lubridate)
library(zoo)
library(odbc)
library(DBI)
library(sqldf)
library(dplyr)
library(dbplyr)
Next, in the following bit my understanding is that a date range is set and two data objects (num & denom) are created from visits.YTD (which is created with a SQL query earlier), then combined in a data frame "score.2022.all", but there's a lot of nuances in there that I don't understand. Apologies if this is too much to ask or unclear.
attr.CMS.2022 <- dbGetQuery(con, "SELECT * FROM [PWA].[REDACTED].ACOMSSPassignedbeneficiaries2022PatID")
# ---Num all snapshots---
begin.date <- base::as.Date('2022-01-01')
end.date <- base::as.Date('2022-12-31') # can be date of snapshot; next update should be for CY 2023
theDate <- begin.date
num <-NA
date.chain <-NA
excluded.num <- c()
for(i in 1:(end.date-begin.date+1)){
zzz<-visits.YTD %>%
filter(CONTACT_DATE == theDate) %>%
inner_join(attr.CMS.2022, by=c("PAT_ID" = "EpicPatientID")) %>%
filter(!PAT_ID %in% excluded.num) %>%
distinct(PAT_ID) %>%
tally()
yyy <-visits.YTD %>%
filter(CONTACT_DATE == theDate) %>%
inner_join(attr.CMS.2022, by=c("PAT_ID" = "EpicPatientID")) %>%
select(PAT_ID) %>%
distinct(PAT_ID)
num[i] <- zzz
date.chain[i] <- as.character(theDate)
excluded.num <- unique(unlist(c(excluded.num, yyy), use.names = F))
theDate <- theDate+1
}
num <-unlist(num, use.names = F)
date <-unlist(date.chain, use.names = F)
# ---Denom all snapshots---
begin.date <- base::as.Date("2022-01-01")
end.date <- base::as.Date("2022-12-31") # next update should be for CY 2023
theDate <- begin.date
denom <-NA
date.chain <-NA
excluded.den <- c()
for(i in 1:(end.date-begin.date+1)){
zzz<-visits.YTD %>%
filter(CONTACT_DATE == theDate) %>%
filter(!PAT_ID %in% excluded.den) %>%
distinct(PAT_ID) %>%
tally()
yyy <-visits.YTD %>%
filter(CONTACT_DATE == theDate) %>%
select(PAT_ID) %>%
distinct(PAT_ID)
denom[i] <- zzz
excluded.den <- unique(unlist(c(excluded.den, yyy), use.names = F))
date.chain[i] <- as.character(theDate)
theDate <- theDate+1
}
denom <-unlist(denom, use.names = F)
date <-unlist(date.chain, use.names = F)
score.2022.all <- data.frame(num, date)
score.2022.all$denom <- denom
#---put the numerator and denom together
score.2022.all <- score.2022.all %>%
arrange(date) %>%
mutate(num = ifelse(as.Date(date, "%Y-%m-%d") < Sys.Date(), num, NA)) %>%
mutate(denom = ifelse(as.Date(date, "%Y-%m-%d") < Sys.Date(), denom, NA)) %>%
mutate(numcu = ifelse(as.Date(date, "%Y-%m-%d") < Sys.Date(), cumsum(num), NA)) %>%
mutate(denomcu = ifelse(as.Date(date, "%Y-%m-%d") < Sys.Date(), cumsum(denom), NA)) %>%
mutate(score = ifelse(as.Date(date, "%Y-%m-%d") < Sys.Date(), numcu/denomcu, NA)) %>%
mutate(dailyscore = num/denom)
score.2022.all$rolling7 <- zoo::rollmean(score.2022.all$dailyscore, 7, align='right', fill=NA)
score.2022.all[score.2022.all=="NaN"] <- NA
dbWriteTable(con, "ACOProviders", score.2022.all)
dbGetQuery(con, "DROP TABLE [PWA].[REDACTED].ACOQPAttrEligibleVisits2022ScoreAll")
dbGetQuery(con, "SELECT * INTO [PWA].[REDACTED].ACOQPAttrEligibleVisits2022ScoreAll FROM ACOProviders")
dbGetQuery(con, "DROP TABLE ACOProviders")