121 lines
8.2 KiB
R
121 lines
8.2 KiB
R
GET_DATA_ECON_YEAR <- function(YEAR){
|
|
DAT_YEAR <- ifelse(YEAR>2030,2030,YEAR)
|
|
HEADER <- paste0('./Raw_Output/Final_Output/')
|
|
FILES <- list.files(HEADER)
|
|
FILES <- paste0(HEADER,FILES[grep(paste0(DAT_YEAR,"-"),FILES)])
|
|
ECON_FILE <- FILES[grep("Economic",FILES)]
|
|
ECON <- read_csv(ECON_FILE)
|
|
ECON <- ECON %>% rename(COUNTY=DestinationRegion,EVENT=EventName,IMPACT_TYPE=ImpactType,EVENT=EventName) %>% rename(TOPI=TaxesOnProductionAndImports,OUTPUT=Output,EMP=Employment,EMP_COMP=EmployeeCompensation,PROP_INC=ProprietorIncome,WAGE_EMP=WageAndSalaryEmployment,PROP_EMP=ProprietorEmployment,OPI=OtherPropertyIncome,IND_CODE=IndustryCode,IND_DESC=IndustryDescription) %>% select(-OriginRegion,-TagName)
|
|
ECON$COUNTY <- gsub(" County, WY \\(2023\\)","",ECON$COUNTY )
|
|
ECON <- ECON %>% filter(COUNTY!="Totals")
|
|
ECON$MAJOR_EVENT <- NA
|
|
ECON$MAJOR_EVENT %>% unique
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT %in% c("Royalties EnWyo (Albany)","Royalties UW (Albany)","Total Profits (Campbell)" ),"Income", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT %in% c("Well Equipment (Campbell)","Well Field Support (Campbell)","Well Road Work (Campbell)" ),"Skid development", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT=="Natural Gas Prod (Campbell)" ,"Natural gas prod.", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT %in% c("Transportation (Goshen)","Transportation (Weston)","Transportation (Washakie)" ),"Transportation", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT %in% c("Beet Proc. Captital (Washakie)","Beet Proc. Captital (Goshen)"),"Facility construction", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT %in% c("Proc. Op Cost (Goshen)","Proc. Op Cost (Washakie)"),"Beet processing", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(ECON$EVENT %in% c("Beet Purchase (Washakie)","SBS purchase (Washakie)","Beet Purchase (Goshen)"),"Beet purchases", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT <- ifelse(is.na(ECON$MAJOR_EVENT),"Beet purchases", ECON$MAJOR_EVENT )
|
|
ECON$MAJOR_EVENT %>% unique
|
|
ECON$YEAR <- YEAR
|
|
ECON <- ECON %>% select(YEAR,COUNTY,MAJOR_EVENT,everything())
|
|
return(ECON)
|
|
}
|
|
|
|
|
|
GET_DATA_TAX_YEAR <- function(YEAR){
|
|
DAT_YEAR <- ifelse(YEAR>2030,2030,YEAR)
|
|
HEADER <- paste0('./Raw_Output/Final_Output/')
|
|
FILES <- list.files(HEADER)
|
|
FILES <- paste0(HEADER,FILES[grep(paste0(DAT_YEAR,"-"),FILES)])
|
|
TAX_FILE <- FILES[grep("Tax",FILES)]
|
|
TAX <- read_csv(TAX_FILE)
|
|
TAX <- TAX %>% rename(COUNTY_TAX=County,STATE_TAX=State,FEDERAL_TAX=Federal,SUBCOUNTY_TAX=SubCountyGeneral,SPECIAL_TAX=SubCountySpecialDistricts,COUNTY=ModelName,TOTAL_TAX=Total,IMPACT_TYPE=Impact,EVENT=EventName) %>% select(-GroupName,-TagName)
|
|
TAX <- TAX %>% mutate(WY_TOTAL_TAX=TOTAL_TAX-FEDERAL_TAX)
|
|
TAX$COUNTY <- gsub(" County, WY \\(2023\\)","",TAX$COUNTY )
|
|
TAX <- TAX %>% filter(COUNTY!="Totals")
|
|
TAX$IMPACT_TYPE <- gsub("3 - ","",gsub("2 - ","",gsub("1 - ","",TAX$IMPACT_TYPE)))
|
|
|
|
TAX$MAJOR_EVENT <- NA
|
|
TAX$MAJOR_EVENT %>% unique
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT %in% c("Royalties EnWyo (Albany)","Royalties UW (Albany)","Total Profits (Campbell)" ),"Income", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT %in% c("Well Equipment (Campbell)","Well Field Support (Campbell)","Well Road Work (Campbell)" ),"Skid development", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT=="Natural Gas Prod (Campbell)" ,"Natural gas prod.", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT %in% c("Transportation (Goshen)","Transportation (Weston)","Transportation (Washakie)" ),"Transportation", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT %in% c("Beet Proc. Captital (Washakie)","Beet Proc. Captital (Goshen)"),"Facility construction", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT %in% c("Proc. Op Cost (Goshen)","Proc. Op Cost (Washakie)"),"Beet processing", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(TAX$EVENT %in% c("Beet Purchase (Washakie)","SBS purchase (Washakie)","Beet Purchase (Goshen)"),"Beet purchases", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT <- ifelse(is.na(TAX$MAJOR_EVENT),"Beet purchases", TAX$MAJOR_EVENT )
|
|
TAX$MAJOR_EVENT %>% unique
|
|
TAX$YEAR <- YEAR
|
|
TAX <- TAX %>% select(YEAR,COUNTY,MAJOR_EVENT,everything())
|
|
return(TAX)
|
|
}
|
|
|
|
|
|
GET_EVENT_DATA_YEAR <- function(DAT_YEAR){
|
|
ECON_EVENT_LEVEL <- GET_DATA_ECON_YEAR(DAT_YEAR) %>% group_by(YEAR,COUNTY,MAJOR_EVENT,EVENT,IMPACT_TYPE) %>% summarize(OUTPUT=sum(OUTPUT),EMP=sum(EMP),WAGE_EMP=sum(WAGE_EMP),PROP_EMP=sum(PROP_EMP),EMP_COM=sum(EMP_COMP),PROP_INC=sum(PROP_INC),TOPI=sum(TOPI),OPI=sum(OPI))
|
|
RES <- ECON_EVENT_LEVEL %>% full_join(GET_DATA_TAX_YEAR(DAT_YEAR))
|
|
RES[is.na(RES)] <- 0
|
|
return(RES)
|
|
}
|
|
|
|
|
|
GET_MAJOR_EVENT_DATA_YEAR <- function(DAT_YEAR){
|
|
ECON_EVENT_LEVEL <- GET_DATA_ECON_YEAR(DAT_YEAR) %>% group_by(YEAR,COUNTY,MAJOR_EVENT,IMPACT_TYPE) %>% summarize(OUTPUT=sum(OUTPUT),EMP=sum(EMP),WAGE_EMP=sum(WAGE_EMP),PROP_EMP=sum(PROP_EMP),EMP_COM=sum(EMP_COMP),PROP_INC=sum(PROP_INC),TOPI=sum(TOPI),OPI=sum(OPI))
|
|
TAX_EVENT_LEVEL <- GET_DATA_TAX_YEAR(DAT_YEAR)%>% group_by(YEAR,COUNTY,MAJOR_EVENT,IMPACT_TYPE) %>% summarize(SUBCOUNTY_TAX=sum(SUBCOUNTY_TAX),SPECIAL_TAX=sum(SPECIAL_TAX),COUNTY_TAX=sum(COUNTY_TAX),STATE_TAX=sum(STATE_TAX),TOTAL_TAX=sum(TOTAL_TAX),WY_TOTAL_TAX=sum(WY_TOTAL_TAX))
|
|
RES <- ECON_EVENT_LEVEL %>% full_join(TAX_EVENT_LEVEL)
|
|
RES[is.na(RES)] <- 0
|
|
return(RES)
|
|
}
|
|
GET_TOTAL_YEAR <- function(DAT_YEAR){
|
|
ECON_EVENT_LEVEL <- GET_DATA_ECON_YEAR(DAT_YEAR) %>% group_by(YEAR) %>% summarize(OUTPUT=sum(OUTPUT),EMP=sum(EMP),WAGE_EMP=sum(WAGE_EMP),PROP_EMP=sum(PROP_EMP),EMP_COM=sum(EMP_COMP),PROP_INC=sum(PROP_INC),TOPI=sum(TOPI),OPI=sum(OPI))
|
|
TAX_EVENT_LEVEL <- GET_DATA_TAX_YEAR(DAT_YEAR)%>% group_by(YEAR) %>% summarize(SUBCOUNTY_TAX=sum(SUBCOUNTY_TAX),SPECIAL_TAX=sum(SPECIAL_TAX),COUNTY_TAX=sum(COUNTY_TAX),STATE_TAX=sum(STATE_TAX),TOTAL_TAX=sum(TOTAL_TAX),WY_TOTAL_TAX=sum(WY_TOTAL_TAX))
|
|
RES <- ECON_EVENT_LEVEL %>% full_join(TAX_EVENT_LEVEL)
|
|
RES[is.na(RES)] <- 0
|
|
return(RES)
|
|
}
|
|
|
|
|
|
GET_EVENT_SUMMARY_YEAR <- function(DAT_YEAR){
|
|
RES <- GET_MAJOR_EVENT_DATA_YEAR(DAT_YEAR) %>% group_by(YEAR,MAJOR_EVENT) %>% summarize(TAX_DIR_PER=sum(ifelse(IMPACT_TYPE=="Direct",WY_TOTAL_TAX,0))/sum(WY_TOTAL_TAX),EMP_DIR_PER=sum(ifelse(IMPACT_TYPE=="Direct",EMP,0))/sum(EMP),TAX_INDIR_PER=sum(ifelse(IMPACT_TYPE=="Indirect",WY_TOTAL_TAX,0))/sum(WY_TOTAL_TAX),EMP_INDIR_PER=sum(ifelse(IMPACT_TYPE=="Indirect",EMP,0))/sum(EMP),OUTPUT=sum(OUTPUT),EMP=sum(EMP), WAGE_EMP=sum(WAGE_EMP),PROP_EMP=sum(PROP_EMP), EMP_COM=sum(EMP_COM),PROP_INC=sum(PROP_INC),TOPI=sum(TOPI),OPI=sum(OPI), SUBCOUNTY_TAX=sum(SUBCOUNTY_TAX), SPECIAL_TAX=sum(SPECIAL_TAX),COUNTY_TAX=sum(COUNTY_TAX), STATE_TAX=sum(STATE_TAX), TOTAL_TAX=sum(TOTAL_TAX), WY_TOTAL_TAX=sum(WY_TOTAL_TAX))
|
|
return(RES)
|
|
}
|
|
GET_SUMMARY_DATA <- function(YEARS=2025:2034){
|
|
RES <- GET_EVENT_SUMMARY_YEAR(YEARS[1])
|
|
for(i in 2:length(YEARS)){
|
|
RES <- rbind(RES,GET_EVENT_SUMMARY_YEAR(YEARS[i]))
|
|
}
|
|
return(RES)
|
|
}
|
|
GET_EVENT_SUMMARY_YEAR <- function(DAT_YEAR){
|
|
RES <- GET_MAJOR_EVENT_DATA_YEAR(DAT_YEAR) %>% group_by(YEAR,MAJOR_EVENT) %>% summarize(TAX_DIR_PER=sum(ifelse(IMPACT_TYPE=="Direct",WY_TOTAL_TAX,0))/max(sum(WY_TOTAL_TAX),0.001),EMP_DIR_PER=sum(ifelse(IMPACT_TYPE=="Direct",EMP,0))/max(sum(EMP),0.001),TAX_INDIR_PER=sum(ifelse(IMPACT_TYPE=="Indirect",WY_TOTAL_TAX,0))/max(sum(WY_TOTAL_TAX),0.001),EMP_INDIR_PER=sum(ifelse(IMPACT_TYPE=="Indirect",EMP,0))/max(sum(EMP),0.001),,OUTPUT=sum(OUTPUT),EMP=sum(EMP), WAGE_EMP=sum(WAGE_EMP),PROP_EMP=sum(PROP_EMP), EMP_COM=sum(EMP_COM),PROP_INC=sum(PROP_INC),TOPI=sum(TOPI),OPI=sum(OPI), SUBCOUNTY_TAX=sum(SUBCOUNTY_TAX), SPECIAL_TAX=sum(SPECIAL_TAX),COUNTY_TAX=sum(COUNTY_TAX), STATE_TAX=sum(STATE_TAX), TOTAL_TAX=sum(TOTAL_TAX), WY_TOTAL_TAX=sum(WY_TOTAL_TAX))
|
|
return(RES)
|
|
}
|
|
|
|
GET_EVENT_DATA <- function(YEARS=2025:2034){
|
|
RES <- GET_EVENT_DATA_YEAR(YEARS[1])
|
|
for(i in 2:length(YEARS)){
|
|
RES <- rbind(RES,GET_EVENT_DATA_YEAR(YEARS[i]))
|
|
}
|
|
return(RES)
|
|
}
|
|
GET_DETAIL_ECON_DATA <- function(YEARS=2025:2034){
|
|
RES <- GET_DATA_ECON_YEAR(YEARS[1])
|
|
for(i in 2:length(YEARS)){
|
|
RES <- rbind(RES,GET_DATA_ECON_YEAR(YEARS[i]))
|
|
}
|
|
return(RES)
|
|
}
|
|
GET_TOTAL_SUMMARY<- function(YEARS=2025:2034){
|
|
RES <- GET_TOTAL_YEAR(YEARS[1])
|
|
for(i in 2:length(YEARS)){
|
|
RES <- rbind(RES,GET_TOTAL_YEAR(YEARS[i]))
|
|
}
|
|
RES <- RES %>% mutate(VALUE_ADDED=PROP_INC+EMP_COM+TOPI+OPI)
|
|
return(RES)
|
|
}
|
|
|