.Rhistory

filter(ppd_id == "NULL")
# Plans collected in Reason that do not have ppd_id
all_plans_2021_2022_all_ids %>% select(plan_id, ppd_id, plan_name_hgarb, plan_name_legacydatabase) %>%
distinct() %>%
filter(ppd_id != "NULL")
all_plans_2021_2022_all_ids %>% select(plan_id, ppd_id, plan_name_hgarb, plan_name_legacydatabase) %>%
distinct()
planid_planname_ppdid_legacydatabase
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL")
planid_planname_ppdid_legacydatabase
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL")
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL")
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL") %>% write.csv("linking_reasonPlanID_ppdID.csv")
View(ppd_plans_name)
planid_planname_ppdid_legacydatabase
planid_planname_ppdid_legacydatabase %>% filter(ppd_id == "NULL")
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL")
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL") %>% write.csv("linking_reasonPlanID_ppdID.csv")
planid_planname_ppdid_legacydatabase %>% filter(ppd_id != "NULL") %>% write.csv("output/linking_reasonPlanID_ppdID.csv")
filelist_2023 <- list.files("data/2023", pattern = "_2023.xlsx")
filelist_2023
filelist_2023 <- list.files("data/2023", pattern = "_2023.xlsx")
df = data.frame()
for (filename in filelist_2023) {
plan <- read_1_file("data/2023", filename)
df = rbind(df, plan) %>% distinct()
}
filelist_2023
read_1_file("data/2023", "Alabama_JRF_updated_2023.xlsx")
read_1_file <- function (folder, filename) {
filename = paste0(folder, filename)
### sheet 1
s1 = import(filename, sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:9) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
### sheet 2
s2 = import(filename, sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
###sheet 3
s3 = import(filename, sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
# extract plan_id
plan_id <- import(filename, sheet = "Database Input") %>%
select(2) %>% colnames()
result <- s1 %>%
full_join(s2, by = c("full_name", "fye")) %>%
full_join(s3, by = c("full_name", "fye")) %>%
arrange(fye) %>%
#adding plan_id
mutate(plan_id = plan_id)
return(result)
}
#folder = "data/HGarb_Updates_2022/"
# Test 1 case
read_1_file("data/HGarb_Updates_2022/", "Alabama_ERS_updated_2022.xlsx")
read_1_file("data/2023", "Alabama_JRF_updated_2023.xlsx")
read_1_file("data/2023", "Alabama_JRF_updated_2022.xlsx")
read_1_file <- function (folder, filename) {
filename = paste0(folder, filename)
print(filename)
### sheet 1
s1 = import(filename, sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:9) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
### sheet 2
s2 = import(filename, sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
###sheet 3
s3 = import(filename, sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
# extract plan_id
plan_id <- import(filename, sheet = "Database Input") %>%
select(2) %>% colnames()
result <- s1 %>%
full_join(s2, by = c("full_name", "fye")) %>%
full_join(s3, by = c("full_name", "fye")) %>%
arrange(fye) %>%
#adding plan_id
mutate(plan_id = plan_id)
return(result)
}
#folder = "data/HGarb_Updates_2022/"
# Test 1 case
read_1_file("data/HGarb_Updates_2022/", "Alabama_ERS_updated_2022.xlsx")
read_1_file("data/2023", "Alabama_JRF_updated_2022.xlsx")
read_1_file("data/2023/", "Alabama_JRF_updated_2022.xlsx")
read_1_file <- function (folder, filename) {
filename = paste0(folder, filename)
### sheet 1
s1 = import(filename, sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:9) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
### sheet 2
s2 = import(filename, sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
###sheet 3
s3 = import(filename, sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
# extract plan_id
plan_id <- import(filename, sheet = "Database Input") %>%
select(2) %>% colnames()
result <- s1 %>%
full_join(s2, by = c("full_name", "fye")) %>%
full_join(s3, by = c("full_name", "fye")) %>%
arrange(fye) %>%
#adding plan_id
mutate(plan_id = plan_id)
return(result)
}
filelist_2023 <- list.files("data/2023", pattern = "_2023.xlsx")
df = data.frame()
for (filename in filelist_2023) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
df = data.frame()
for (filename in filelist_2023[1:10]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
df_2023 <- df
df_2023
df_2023_1_10 <- df
df_2023_1_10
df = data.frame()
for (filename in filelist_2023[11:20]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[11:15]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[11:12]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[12:13]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[13:14]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[15]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
filelist_2023[15]
for (filename in filelist_2023[16:20]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[21:30]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[31:40]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[31:35]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[36:39]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[36:38]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[36:37]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[38]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
filelist_2023[38]
for (filename in filelist_2023[39:45]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
filelist_2023
for (filename in filelist_2023[39:57]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
filelist_2023 <- list.files("data/2023", pattern = "_2023.xlsx")
df = data.frame()
for (filename in filelist_2023[1:14, 16:37, 39:57]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[c(1:14, 16:37, 39:57)]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
df_2023_regular <- df
df_2023_regular
df = data.frame()
for (filename in filelist_2023[15, 38]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
for (filename in filelist_2023[c(15, 38)]) {
plan <- read_1_file("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
filelist_2023[15]
s1 = import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:9) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
s1
import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "GASB 68", skip = 1) %>% clean_names()
import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:10)
s1 = import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:10) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
s1
import(filename, sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names()
### sheet 2
s2 = import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:22) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
s2
import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye))
### sheet 2
s2 = import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
###sheet 3
s3 = import(filename, sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
###sheet 3
s3 = import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
s3
read_1_file_2023 <- function (folder, filename) {
filename = paste0(folder, filename)
### sheet 1
s1 = import(filename, sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:10) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
### sheet 2
s2 = import(filename, sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
###sheet 3
s3 = import(filename, sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
# extract plan_id
plan_id <- import(filename, sheet = "Database Input") %>%
select(2) %>% colnames()
result <- s1 %>%
full_join(s2, by = c("full_name", "fye")) %>%
full_join(s3, by = c("full_name", "fye")) %>%
arrange(fye) %>%
#adding plan_id
mutate(plan_id = plan_id)
return(result)
}
read_1_file_2023(filelist_2023[15])
filelist_2023[15]
df = data.frame()
for (filename in filelist_2023[c(15, 38)]) {
plan <- read_1_file_2023("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
s1
s2
s3
# extract plan_id
plan_id <- import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "Database Input") %>%
select(2) %>% colnames()
plan_id
result <- s1 %>%
full_join(s2, by = c("full_name", "fye")) %>%
full_join(s3, by = c("full_name", "fye")) %>%
arrange(fye) %>%
#adding plan_id
mutate(plan_id = plan_id)
result
read_1_file_2023(filelist_2023[15])
read_1_file_2023("data/2023/", filelist_2023[15])
read_1_file_2023("data/2023/", filelist_2023[38])
read_1_file_2023("data/2023/", filelist_2023[15])
read_1_file_2023("data/2023/", filelist_2023[15]) %>% colnames() ->test1
read_1_file_2023("data/2023/", filelist_2023[38]) %>% colnames() ->test2
setdiff(test1, test2)
setdiff(test2, test1)
read_1_file_2023("data/2023/", filelist_2023[30]) %>% colnames() ->test3
setdiff(test2, test3)
setdiff(test3, test1)
setdiff(test1, test2)
setdiff(test1, test3)
setdiff(test1, test2)
test1
test3
read_1_file_2023("data/2023/", filelist_2023[31]) %>% colnames() ->test3
test3
setdiff(test1, test2)
read_1_file_2023("data/2023/", filelist_2023[15]) %>%
colnames()
setdiff(test1, test2)
setdiff(test1, test3)
setdiff(test1, test3)
read_1_file_2023("data/2023/", filelist_2023[32]) %>% colnames() ->test3
setdiff(test1, test3)
test1
test1 %>% select(x5)
read_1_file_2023("data/2023/", filelist_2023[15]) %>% select(x5)
s1 %>% select(x5)
s2 %>% select(x5)
filelist_2023[15]
import("data/2023/Delaware_PERS_NSPPP_updated_2023.xlsx", sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23)
import("data/2023/Michigan_JRS_updated_2023.xlsx", sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye))
read_1_file_2023("data/2023/", "Delaware_PERS_NSPPP_updated_2023.xlsx")
read_1_file_2023("data/2023/", "Delaware_PERS_NSPPP_updated_2023.xlsx") %>%
select(-x5)
df_15 <- read_1_file_2023("data/2023/", filelist_2023[15]) %>%
select(-x5)
df_15
read_1_file_2023("data/2023/", filelist_2023[38]) %>% colnames() ->test2
setdiff(test2, test3) # 15 has these that others dont: x5, "tot_total_amt"
df_38 <- read_1_file_2023("data/2023/", filelist_2023[15]) %>%
rename(tot_total_amt = x12)
filelist_2023[38]
df_38 <- read_1_file_2023("data/2023/", filelist_2023[38]) %>%
rename(tot_total_amt = x12)
# exceptions
df_2023_15 <- read_1_file_2023("data/2023/", filelist_2023[15]) %>%
select(-x5)
df_2023_38 <- read_1_file_2023("data/2023/", filelist_2023[38]) %>%
rename(tot_total_amt = x12)
filelist_2023 <- list.files("data/2023", pattern = "_2023.xlsx")
read_1_file_2023 <- function (folder, filename) {
filename = paste0(folder, filename)
### sheet 1
s1 = import(filename, sheet = "GASB 68", skip = 1) %>% clean_names() %>%
filter(!is.na(fye)) %>% slice(1:10) %>%
# Differentiate some cols from sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_gasb")))
### sheet 2
s2 = import(filename, sheet = "Actuarial Valuation", skip = 1) %>% #"Actuarial Valuation"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
mutate(fye = as.double(fye)) %>%  # solve a few cases in sheet 2
# Differentiate some cols from sheet 2 & sheet 3
setnames(
old =  c("actuarial_return", "market_return","ava" , "aal","ual", "funded_ratio_old", "payroll"),
new = c(paste0(c("actuarial_return", "market_return","ava" , "aal", "ual", "funded_ratio_old", "payroll"), "_from_valuation")))
###sheet 3
s3 = import(filename, sheet = "CAFR", skip = 1) %>% #"CAFR"
clean_names() %>%
filter(!is.na(fye)) %>% slice(1:23) %>%
# Differentiate some cols from sheet 2 & sheet 3
setnames(old = c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"),
new = c(paste0(c("actuarial_return", "market_return", "ava", "aal", "ual", "funded_ratio_old",
"payroll"), "_from_acfr"))) %>%
# Differentiate sheet 1 & sheet 3
setnames(
old = c("adec_amt", "adec_paid_amt", "adec_missed"),
new = c(paste0(c("adec_amt", "adec_paid_amt", "adec_missed"), "_from_acfr")))
# extract plan_id
plan_id <- import(filename, sheet = "Database Input") %>%
select(2) %>% colnames()
result <- s1 %>%
full_join(s2, by = c("full_name", "fye")) %>%
full_join(s3, by = c("full_name", "fye")) %>%
arrange(fye) %>%
#adding plan_id
mutate(plan_id = plan_id)
return(result)
}
df = data.frame()
for (filename in filelist_2023[c(1:14, 16:37, 39:57)]) {
plan <- read_1_file_2023("data/2023/", filename)
df = rbind(df, plan) %>% distinct()
}
df_2023_regular <- df
all_plans_2023 <- rbind(df_2023_regular, df_2023_15, df_2023_38)
all_plans_2023
all_plans_2023 %>% select(full_name) %>% distinct()
all_plans_2023 %>% write.csv("all_plans_2023.csv")
all_plans_2023 %>% write.csv("output/all_plans_2023.csv")