Last active
July 3, 2023 08:51
-
-
Save skvrnami/437e6777166b5d8f65c1ab273186bfd3 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
Skvělé, díky
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Kde je možné získat soubor 1300722003.xlsx ? Ten se v rámci scriptu nestáhne