# ==============================================================================
# 1. SETUP & LIBRARIES
# ==============================================================================
library(tidyverse)
library(janitor)
library(readxl)
library(stringr)
# ---------------------------------------------------------
# Define Paths & Helpers
# ---------------------------------------------------------
rbi_path <- "C:/Users/CEGIS/Desktop/work/sectoral_scan/r_gva/data/2026_07_31_cleaned/rbiestates/ESTATES23012026.XLSX"
gsdp_path <- "C:/Users/CEGIS/Desktop/work/sectoral_scan/r_gva/data/2026_07_31_cleaned/longdf/Bernstein_Master_GVA_GSDP_Long.csv"
target_states <- c("Karnataka", "Tamil Nadu", "Maharashtra", "Odisha")
fix_states <- function(x) {
case_when(
str_detect(x, "(?i)Delhi") ~ "Delhi",
str_detect(x, "(?i)Jammu") ~ "Jammu & Kashmir",
str_detect(x, "(?i)Andaman") ~ "Andaman & Nicobar Islands",
TRUE ~ str_trim(x)
)
}
# ==============================================================================
# 2. RAW DATA INGESTION & CLEANING
# ==============================================================================
rbi_raw <- read_excel(rbi_path, sheet = "Data") %>% clean_names()
gsdp_raw <- read_csv(gsdp_path, show_col_types = FALSE) %>% clean_names()
gsdp_curr <- gsdp_raw %>%
mutate(state = fix_states(state), year = as.character(year)) %>%
filter(metric == 'GSDP', price_base == "Current") %>%
select(state, year, gsdp_cr = value)
rbi_clean <- rbi_raw %>%
rename(state = state_ut, year = fiscal_year) %>%
mutate(
state = fix_states(state),
year = str_replace(year, "^(\\d{4})-\\d{2}(\\d{2})$", "\\1-\\2"),
exp_cr = case_when(account > 0 ~ account, revised > 0 ~ revised, budget > 0 ~ budget, TRUE ~ NA_real_)
) %>%
filter(!is.na(exp_cr))
# ==============================================================================
# 3. MAPPING RBI BUDGET HEADS & CREATING MASTER_DF_COMPLETE
# ==============================================================================
rbi_metrics <- rbi_clean %>%
mutate(
var_name = case_when(
appendix == "Appendix-2" & budget_head == "Total: TOTAL EXPENDITURE (I+II+III)" ~ "state_total_revex",
appendix == "Appendix-4" & budget_head == "Total$: TOTAL CAPITAL DISBURSEMENTS (Excluding Public Accounts)" ~ "state_total_capex",
appendix == "Appendix-2" & budget_head == "I.A.1: Education, Sports, Art and Culture" ~ "edu_dev_revex",
appendix == "Appendix-2" & budget_head == "II.C.4.i: Education, Sports, Art and Culture" ~ "edu_nondev_revex",
appendix == "Appendix-4" & budget_head == "I.1.a.1: Education, Sports, Art and Culture" ~ "edu_dev_capex_outlay",
appendix == "Appendix-4" & budget_head == "IV.1.a.1: Education, Sports, Art and Culture" ~ "edu_dev_capex_loan",
appendix == "Appendix-2" & budget_head %in% c("I.A.2: Medical and Public Health", "I.A.3: Family Welfare") ~ "health_dev_revex",
appendix == "Appendix-2" & budget_head %in% c("II.C.4.ii: Medical and Public Health", "II.C.4.iii: Family Welfare") ~ "health_nondev_revex",
appendix == "Appendix-4" & budget_head %in% c("I.1.a.2: Medical and Public Health", "I.1.a.3: Family Welfare") ~ "health_dev_capex_outlay",
appendix == "Appendix-4" & budget_head %in% c("IV.1.a.2: Medical and Public Health", "IV.1.a.3: Family Welfare") ~ "health_dev_capex_loan",
appendix == "Appendix-2" & budget_head == "I.A.10: Nutrition" ~ "nut_dev_revex",
TRUE ~ NA_character_
)
) %>%
filter(!is.na(var_name)) %>%
group_by(state, year, var_name) %>%
summarise(exp = sum(exp_cr, na.rm = TRUE), .groups = "drop") %>%
pivot_wider(names_from = var_name, values_from = exp, values_fill = 0)
# Safety check: Ensure all columns exist before calculating totals
cols_needed <- c(
"state_total_revex", "state_total_capex",
"edu_dev_revex", "edu_nondev_revex", "edu_dev_capex_outlay", "edu_dev_capex_loan",
"health_dev_revex", "health_nondev_revex", "health_dev_capex_outlay", "health_dev_capex_loan",
"nut_dev_revex"
)
for(col in cols_needed) { if(!col %in% names(rbi_metrics)) rbi_metrics[[col]] <- 0 }
# Merge with GSDP and calculate final overarching metrics
master_df_complete <- gsdp_curr %>%
left_join(rbi_metrics, by = c("state", "year")) %>%
mutate(
year_num = as.numeric(str_extract(year, "^\\d{4}")),
total_revex = state_total_revex,
total_capex = state_total_capex,
total_exp = total_revex + total_capex,
edu_revex = edu_dev_revex + edu_nondev_revex,
edu_capex = edu_dev_capex_outlay + edu_dev_capex_loan,
tot_edu_exp = edu_revex + edu_capex,
health_revex = health_dev_revex + health_nondev_revex,
health_capex = health_dev_capex_outlay + health_dev_capex_loan,
tot_health_exp = health_revex + health_capex,
nut_revex = nut_dev_revex,
nut_capex = 0,
tot_nut_exp = nut_revex + nut_capex
)
# ==============================================================================
# 4. WITHIN-STATE PERCENTAGE CALCULATIONS & RESHAPING
# ==============================================================================
selected_states_df_complete <- master_df_complete %>%
filter(state %in% target_states) %>%
mutate(
pct_totexp_edu = (tot_edu_exp / total_exp) * 100,
pct_totexp_health = (tot_health_exp / total_exp) * 100,
pct_totexp_nut = (tot_nut_exp / total_exp) * 100,
pct_revex_edu = (edu_revex / total_revex) * 100,
pct_revex_health = (health_revex / total_revex) * 100,
pct_revex_nut = (nut_revex / total_revex) * 100,
pct_capex_edu = if_else(total_capex > 0, (edu_capex / total_capex) * 100, 0),
pct_capex_health = if_else(total_capex > 0, (health_capex / total_capex) * 100, 0),
pct_capex_nut = if_else(total_capex > 0, (nut_capex / total_capex) * 100, 0)
)
within_state_long <- selected_states_df_complete %>%
select(
state, year, year_num,
`Total Exp_Education` = pct_totexp_edu, `Total Exp_Health` = pct_totexp_health, `Total Exp_Nutrition` = pct_totexp_nut,
`RevEx_Education` = pct_revex_edu, `RevEx_Health` = pct_revex_health, `RevEx_Nutrition` = pct_revex_nut,
`CapEx_Education` = pct_capex_edu, `CapEx_Health` = pct_capex_health, `CapEx_Nutrition` = pct_capex_nut
) %>%
pivot_longer(
cols = -c(state, year, year_num), # CRITICAL FIX: Explicitly ignore ID columns
names_to = c("Category", "Sector"),
names_sep = "_",
values_to = "pct_value"
) %>%
mutate(Sector = factor(Sector, levels = c("Education", "Health", "Nutrition")))
# ==============================================================================
# 5. THEME & PLOTTING LOOP
# ==============================================================================
c <- "Source: RBI State Finances (eSTATES Database), GSDP - MoSPI.\nEducation: Education, Sports, Art and Culture [I.A.1 + II.C.4.i + I.1.a.1 + IV.1.a.1]. Nutrition [I.A.10].\nHealth: Medical and Public Health [I.A.2 + II.C.4.ii + I.1.a.2 + IV.1.a.2] & Family Welfare [I.A.3 + II.C.4.iii + I.1.a.3 + IV.1.a.3]."
# Reusing the modified theme that prevents clipping
theme_publication <- function() {
theme_bw() +
theme(
plot.title = element_text(hjust = 0.5, face = "bold", size = 14),
panel.border = element_rect(colour = "black", fill = NA, linewidth = 1.2),
axis.title = element_text(face = "bold", size = 12),
axis.text = element_text(face = "bold", size = 10, color = "black"),
axis.text.x = element_text(angle = 45, hjust = 1),
plot.caption = element_text(face = "bold", hjust = 0, size = 8.5, lineheight = 1.1),
strip.background = element_rect(fill = "grey90", color = "black", linewidth = 1),
strip.text = element_text(face = "bold", size = 11),
legend.position = "top",
legend.title = element_text(face = "bold", size = 11),
legend.text = element_text(size = 11),
# CRITICAL FIX: Align title and caption to the full plot width
plot.title.position = "plot",
plot.caption.position = "plot",
# Unified padding
plot.margin = margin(t = 10, r = 10, b = 15, l = 10)
)
}
# Loop over states and generate the Total Expenditure Share plots
for (st in target_states) {
df_state <- within_state_long %>% filter(state == st)
if(nrow(df_state) == 0) next
p <- ggplot(df_state %>% filter(Category == "Total Exp"), aes(x = year_num, y = pct_value, color = Sector, group = Sector)) +
geom_line(linewidth = 1.2) +
geom_point(size = 3) +
scale_x_continuous(breaks = unique(df_state$year_num), labels = unique(df_state$year)) +
scale_y_continuous(labels = function(x) paste0(round(x, 2), "%")) +
scale_color_manual(values = c("Education" = "#2ca02c", "Health" = "#ff7f0e", "Nutrition" = "#d62728")) +
labs(
title = str_wrap(paste0(st, ": Sectoral Expenditure as a Percentage of Total State Expenditure"), width = 90),
x = "Financial Year",
y = "Percentage of Total Expenditure",
color = "Sector",
caption = c
) +
theme_publication()
print(p)
}
