After exporting the expenditures and revenues that were related to Refund funds (identified by searching for string containing “refund” and then also identifying the fund number because, in early years, data requested did not have fund labels associated with fund numbers. Yes, you could do just the fund numbers in the command, but if anything changes, looking for strings that contain “refund” would hopefully alert the coder that something new existed which would otherwise be more difficult if only using fund numbers).
Source Code
---title: 'Tax Refunds'author: "AWM"format: html: toc: true code-fold: true df-print: paged---```{r setup, warning=FALSE, message=FALSE}library(tidyverse)library(haven)library(formatR)library(lubridate)library(smooth)library(forecast)library(scales)library(kableExtra)library(ggplot2)library(readxl)library(tidyverse)library(data.table)library(quantmod)library(geofacet)library(janitor)library(cmapplot)theme_set(theme_classic() )knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE )exp_temp <- read_csv("./data/exp_temp.csv") # %>% # mutate(in_ff = if_else( fund %in% c("0278", "0378", "0121", "0380"), 0, in_ff))rev_temp <- read_csv("./data/rev_temp.csv") # %>% # mutate(in_ff = if_else( fund %in% c("0278", "0378", "0121", "0380"), 0, in_ff))``````{r}#| column: page#exp_temp %>%mutate(obj_type =str_sub(obj_seq_type, 1, 1),obj_2dig =str_sub(obj_seq_type, 1, 2),obj_of_exp =str_sub(obj_seq_type, 1, 3),obj_of_exp =str_pad(obj_of_exp, width =4, "right", pad ="0"),obj_expenditure_label =case_when( obj_of_exp =="9900"~"9900 - One-Time Rebates", obj_of_exp =="9910"~"9910 - Income Tax Refunds", obj_of_exp =="9920"~"9920 - Other Tax Refunds", obj_of_exp =="9930"~"9930 - Other Refunds", obj_of_exp >9930~"9931 & Up - Other Refunds") ) %>%filter(#fy == 2023 & object >=9000#& object <=9930# & agency == 492 ) %>%summarize(Refunds =round(sum(expenditure)), .by =c(fy, obj_expenditure_label )) %>%pivot_wider (names_from ="obj_expenditure_label", values_from ="Refunds") %>%arrange(fy) ``````{r}#| column: page# only income tax refund funds from Department of Revenuerev_refundfund <- rev_temp %>%filter(agency!="799"&!rev_type %in%c("99", "98", "72") & fund =="0278"& agency =="492") %>%reframe(Refund_Rev =sum(receipts), .by = fy)rev_temp %>%filter(agency!="799"& fund =="0278"&!rev_type %in%c("99", "98", "72")) %>%reframe(Refund_Rev =sum(receipts), .by =c(fy, source_name_AWM)) %>%pivot_wider(names_from = source_name_AWM, values_from = Refund_Rev) %>% kable %>%kable_classic()exp_refundfund <- exp_temp %>%filter(agency!="799"& fund =="0278"& agency =="492") %>%reframe(Refund_Exp =sum(expenditure), .by = fy)exp_temp %>%filter(agency!="799"& fund =="0278") %>%reframe(Refund_Exp =sum(expenditure), .by =c(fy, sequence, object)) %>%pivot_wider(names_from = sequence, values_from = Refund_Exp) %>% kable %>%kable_classic()exp_temp %>%filter(agency!="799"& fund =="0278"& object >=9000) %>%reframe(Refund_Exp =sum(expenditure), .by =c(fy, obj_seq_type)) %>%pivot_wider(names_from = obj_seq_type, values_from = Refund_Exp) %>% kable %>%kable_classic()exp_temp %>%filter(agency!="799"& fund =="0278"& object >=9000) %>%# mutate(wh_approp_name = ifelse(is.na(wh_approp_name), "INCOME TAX REFUNDS",# ifelse(wh_approp_name == "INCOME TAX REFUND (RETURN DD)", "INCOME TAX REFUNDS (RETURN DD)", wh_approp_name) )#) %>% reframe(Refund_Exp =sum(expenditure), .by =c(fy, wh_approp_name)) %>%pivot_wider(names_from = wh_approp_name, values_from = Refund_Exp) %>%mutate(TotalRefund =sum(`INCOME TAX REFUNDS`+`INCOME TAX REFUNDS (RETURN DD)`+`INCOME TAX REFUNDS (UNITARY)`+`INCOME TAX REFUNDS (NON-UNI)`+`ONE TIME TAX REBATES`, na.rm=TRUE ) ) %>% kable %>%kable_classic()left_join(rev_refundfund, exp_refundfund) %>%mutate(Refund_diff = Refund_Rev - Refund_Exp) %>% kable %>%kable_classic()``````{r}#| layout-ncol: 2#| column: page# get all individual and corporate income tax revenue items income_rev <- rev_temp %>%filter(!rev_type %in%c("99", "98", "72")) %>%filter(rev_type %in%c( "02", "03" ) )rev_temp %>%# no transfers, bond proceeds, prior year refundsfilter(!rev_type %in%c("99", "98", "72")) %>%filter(rev_type %in%c( "02") ) %>%group_by(fy) %>%summarize("Ind. Inc. Receipts, gross refunds & PPRT"=sum(receipts))rev_temp %>%filter(!rev_type %in%c("99", "98", "72") & fund !="0278"& rev_type %in%c( "02") ) %>%group_by(fy) %>%summarize(" Ind. Inc. Receipts, gross PPRT, net Refunds"=sum(receipts))``````{r}#| label: tbl-taxrefunds-newcode-revenue#| column: screen-insettax_refunds_rev <- rev_temp %>%filter(!rev_type %in%c("99", "98", "72")) %>%filter(str_detect(fund_name_ab, "REFUND") |# search string for refund, just in case fund %in%c("0121", "0278", "0378") #| # refund funds# source %in% c("1905", "1906", "1923", "2138") # amnesty rev sources )tax_refunds_rev %>%#filter(rev_type_name == )group_by(fy, rev_type, source, source_name_AWM ) %>%arrange(fy) %>%summarize(refunds_rev =round(sum(receipts)) ) %>%pivot_wider(id_cols =c(rev_type, source, source_name_AWM), names_from = fy, values_from = refunds_rev) %>%arrange(rev_type)tax_refunds_rev %>%group_by(fy, rev_type, source, source_name_AWM, fund ) %>%arrange(fy) %>%summarize(refunds_rev =round(sum(receipts)) ) %>%pivot_wider(id_cols =c(rev_type, source, source_name_AWM, fund), names_from = fy, values_from = refunds_rev)# expenditure Objects related to refunds refund_expenses <- exp_temp %>%filter( #agency == 492 | (object >=9900& object <=9941) & expenditure >0) refund_expenses %>%group_by(object, fy) %>%summarize(exp =round(sum(expenditure))) %>%pivot_wider(names_from ="fy", values_from ="exp")# excellist <- list(income_rev, tax_refunds_exp, tax_refunds_rev)# writexl::write_xlsx(excellist, "taxrefunds_check.xlsx")``````{r}#| label: tbl-taxrefunds-newcode-exp#| column: screen-insetexp_temp <- exp_temp %>%mutate(wh_approp_name =if_else(obj_seq_type =="99100008", "INCOME TAX REFUNDS (UNITARY)", if_else(obj_seq_type =="99100108", "INCOME TAX REFUNDS (NON-UNI)", if_else(obj_seq_type =="99100208", "INCOME TAX REFUNDS (RETURN DD)", as.character(wh_approp_name)), as.character(wh_approp_name))))tax_refunds_exp <- exp_temp %>%filter(agency !=799) %>%filter(str_detect(fund_name_ab, "REFUND")| fund %in%c(#"0121", "0278"# "0378", "0380", "0671" ) )tax_refunds_exp %>%group_by(fy, fund, fund_name_ab) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(id_cols =c(fund, fund_name_ab), names_from = fy, values_from = refunds_exp)# Matches IOC# Includes the $1 billion one-time abatement in FY 2023tax_refunds_exp %>%group_by(fy, obj_seq_type, wh_approp_name) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(names_from = fy, values_from = refunds_exp)``````{r}#| label: tbl-taxrefunds-groupedtables#| column: screen-inset#| include: FALSEtax_refunds_exp %>%filter(agency !="799") %>%group_by(fy, appr_org, org_name, fund, fund_name_ab) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(id_cols =c(appr_org, org_name, fund, fund_name_ab), names_from = fy, values_from = refunds_exp)tax_refunds_exp %>%filter(agency !="799") %>%group_by(fy, object, fund, fund_name_ab) %>%summarize(refunds_exp =sum(expenditure)) %>%pivot_wider(id_cols =c(object, fund, fund_name_ab), names_from = fy, values_from = refunds_exp) %>%arrange(object)# excellist <- list(tax_refunds_exp, tax_refunds_rev)# writexl::write_xlsx(excellist, "taxrefunds_check.xlsx")```After exporting the expenditures and revenues that were related to Refund funds (identified by searching for string containing "refund" and then also identifying the fund number because, in early years, data requested did not have fund labels associated with fund numbers. Yes, you could do just the fund numbers in the command, but if anything changes, looking for strings that contain "refund" would hopefully alert the coder that something new existed which would otherwise be more difficult if only using fund numbers).