rozpocty_obce.R

library(dplyr)
library(ggplot2)
library(statnipokladna)

local_budgets <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                              year = 2020,
                              month = 12, 
                              dest_dir = "data/budget_data")
functional_categories <- sp_get_codelist("paragraf", 
                                         dest_dir = "data/budget_data")
polozky <- sp_get_codelist("polozka", 
                           dest_dir = "data/budget_data")

# id obce a názvy obcí
id_obce <- sp_get_codelist("ucjed", 
                           dest_dir = "data/budget_data") %>% 
    filter(druhuj_id == 4) %>% # obce
    select(ico, obec, zuj_id) %>% 
    group_by(ico, zuj_id) %>% 
    filter(row_number() == 1) %>% 
    ungroup %>% 
    unique()

local_budgets_final <- local_budgets %>% 
    sp_add_codelist("polozka", by = "polozka") %>% 
    sp_add_codelist(functional_categories, by = "paragraf") # %>% 
    # sp_add_codelist("polozka")

# Data ČSÚ: https://www.czso.cz/csu/czso/pocet-obyvatel-v-obcich-k-112019 
pocet_obyvatel <- readxl::read_excel("data/1300722003.xlsx", skip = 4) %>% 
    janitor::clean_names() %>% 
    filter(!is.na(x3)) %>% 
    select(id_obce = obce, pocet_obyvatel = celkem_4) %>% 
    mutate(pocet_obyvatel = as.numeric(pocet_obyvatel))

local_budget_summary_seskupeni <- local_budgets_final %>% 
    filter(druh == "Výdaje") %>% 
    group_by(seskupeni, ico, ucjed) %>% 
    summarise(
        budget_adopted = sum(budget_adopted), 
        budget_amended = sum(budget_amended), 
        budget_spending = sum(budget_spending)
    ) %>% 
    group_by(ico, ucjed) %>% 
    mutate(
        share_spending = budget_spending / sum(budget_spending) * 100
    ) %>% 
    left_join(., id_obce, by = "ico", relationship = "many-to-one") %>% 
    left_join(., pocet_obyvatel, by = c("zuj_id"="id_obce"))

local_budget_summary_oddil <- local_budgets_final %>% 
    filter(druh == "Výdaje") %>% 
    group_by(oddil, ico, ucjed) %>% 
    summarise(
        budget_adopted = sum(budget_adopted), 
        budget_amended = sum(budget_amended), 
        budget_spending = sum(budget_spending)
    ) %>% 
    group_by(ico, ucjed) %>% 
    mutate(
        share_spending = budget_spending / sum(budget_spending) * 100
    ) %>% 
    left_join(., id_obce, by = "ico", relationship = "many-to-one") %>% 
    left_join(., pocet_obyvatel, by = c("zuj_id"="id_obce"))

local_budget_summary_polozka <- local_budgets_final %>% 
    filter(druh == "Výdaje") %>% 
    group_by(polozka_nazev, ico, ucjed) %>% 
    summarise(
        budget_adopted = sum(budget_adopted), 
        budget_amended = sum(budget_amended), 
        budget_spending = sum(budget_spending)
    ) %>% 
    group_by(ico, ucjed) %>% 
    mutate(
        share_spending = budget_spending / sum(budget_spending) * 100
    ) %>% 
    left_join(., id_obce, by = "ico", relationship = "many-to-one") %>% 
    left_join(., pocet_obyvatel, by = c("zuj_id"="id_obce"))

local_budget_summary_seskupeni %>% 
    filter(seskupeni == "Platy a podobné a související výdaje") %>% 
    ggplot(., aes(x = pocet_obyvatel, y = share_spending)) + 
    geom_point(alpha = 0.2) + 
    scale_x_log10(labels = scales::label_number()) + 
    scale_y_continuous(labels = scales::label_percent(scale = 1, suffix = " %")) +
    labs(x = "Počet obyvatel", 
         y = "Podíl výdajů na \"Platy a podobné související výdaje\" (2020)", 
         title = "Výdaje obcí na platy podle počtu obyvatel",
         caption = "Zdroj dat: Monitor státní pokladny") + 
    theme_minimal()
ggsave("figs/platy.png", width = 8, height = 6)

local_budget_summary_polozka %>% 
    filter(polozka_nazev == "Odměny členů zastupitelstev obcí a krajů") %>% 
    ggplot(., aes(x = pocet_obyvatel, y = share_spending)) + 
    geom_point(alpha = 0.2) + 
    scale_x_log10(labels = scales::label_number()) + 
    scale_y_continuous(labels = scales::label_percent(scale = 1, suffix = " %")) +
    labs(x = "Počet obyvatel", 
         y = "Podíl výdajů na \"Odměny členů zastupitelstev obcí a krajů\" (2020)", 
         title = "Výdaje obcí na odměny zastupitelstva podle počtu obyvatel",
         caption = "Zdroj dat: Monitor státní pokladny") + 
    theme_minimal()
ggsave("figs/odmeny.png", width = 8, height = 6)

Kde je možné získat soubor 1300722003.xlsx ? Ten se v rámci scriptu nestáhne

tady: https://www.czso.cz/csu/czso/pocet-obyvatel-v-obcich-k-112019 (Tab. 3)

Skvělé, díky