remotes::install_github("petrbouchal/statnipokladna")

library(statnipokladna)
library(tidyr)
library(dplyr)





local_budgets_17 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                              year = 2017,
                              month = 12)
#> ??? Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ??? Reading data...
#> ??? Transforming data...
#> Warning: Problem with `mutate()` input `vykaz_date`.
#> ??? All formats failed to parse. No formats found.
#> ??? Input `vykaz_date` is `(function (..., quiet = FALSE, tz = NULL, locale = Sys.getlocale("LC_TIME"), ...`.
#> Warning: All formats failed to parse. No formats found.

head(local_budgets_17)
#> # A tibble: 6 x 15
#>   vykaz vtab  vykaz_year vykaz_month ucjed ico   kraj  nuts  polozka_typ
#>   <chr> <chr> <chr>      <chr>       <chr> <chr> <chr> <chr> <chr>      
#> 1 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 2 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 3 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 4 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 5 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 6 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> # . with 6 more variables: paragraf <chr>, polozka <chr>, budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, vykaz_date <date>
head(local_budgets_17[,13:15])


local_budgets_17 <- local_budgets_17 %>% 
  sp_add_codelist("polozka")



colnames(local_budgets_17)

local_budgets_17 <- local_budgets_17 %>% drop_na(druh)

local_budgets_17 <- local_budgets_17 %>% filter(druh == "Výdaje")

local_budgets_17 %>%
  summarise(vydaje = sum(budget_spending))

local_budgets_17 <- local_budgets_17 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)

local_budgets_17 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))



# funkci sp_add_codelist pomocí parametru "by" přimět data spojit jen podle sloupce ico
local_budgets_17 <- local_budgets_17 %>% 
  sp_add_codelist("ucjed", by = "ico")

local_budgets_17 <- local_budgets_17 %>% filter(druhuj_id == "4")

z <- local_budgets_17 %>%
  summarise(obce = sum(budget_spending))



# group by ico
local_budgets_17 %>%
  group_by(ico)


new_df <- local_budgets_17 %>%
  mutate(pocob = as.numeric(pocob))

new_df <- new_df %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

new_df <- new_df %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

new_df %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))

y <- new_df %>%
  ungroup() %>%
  summarise(sum_pocob = sum(pocob))

z/y








expenditure_17_small <- local_budgets_17 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")


total_expend_17_small <- expenditure_17_small %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_17_small %>%
  group_by(ico)


expenditure_17_small <- expenditure_17_small %>%
  mutate(pocob = as.numeric(pocob))

expenditure_17_small <- expenditure_17_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_17_small <- expenditure_17_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_17_small <- expenditure_17_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))

pocob_small_17 <- expenditure_17_small %>%
  ungroup() %>%
  summarise(sum_pocob = sum(pocob))

total_expend_17_small/pocob_small_17
total_invest_17_small/pocob_small_17








expenditure_17_mid <- local_budgets_17 %>% filter(katobyv_id == "7")


total_expend_17_mid <- expenditure_17_mid %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_17_mid %>%
  group_by(ico)


expenditure_17_mid <- expenditure_17_mid %>%
  mutate(pocob = as.numeric(pocob))

expenditure_17_mid <- expenditure_17_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_17_mid <- expenditure_17_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_17_mid <- expenditure_17_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))


pocob_mid_17 <- expenditure_17_mid %>%
  ungroup() %>%
  summarise(sum_pocob = sum(pocob))

total_expend_17_mid/pocob_mid_17
total_invest_17_mid/pocob_mid_17












expenditure_17_large <- local_budgets_17 %>% filter(katobyv_id == "11" | katobyv_id == "12")


total_expend_17_large <- expenditure_17_large %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_17_large %>%
  group_by(ico)


expenditure_17_large <- expenditure_17_large %>%
  mutate(pocob = as.numeric(pocob))

expenditure_17_large <- expenditure_17_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_17_large <- expenditure_17_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_17_large <- expenditure_17_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))


pocob_large_17 <- expenditure_17_large %>%
  ungroup() %>%
  summarise(sum_pocob = sum(pocob))

total_expend_17_large/pocob_large_17
total_invest_17_large/pocob_large_17















expenditure_17_ef <- local_budgets_17 %>% filter(katobyv_id == "5")


total_expend_17_ef <- expenditure_17_ef %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_17_ef %>%
  group_by(ico)


expenditure_17_ef <- expenditure_17_ef %>%
  mutate(pocob = as.numeric(pocob))

expenditure_17_ef <- expenditure_17_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_17_ef <- expenditure_17_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_17_ef <- expenditure_17_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))


pocob_ef_17 <- expenditure_17_ef %>%
  ungroup() %>%
  summarise(sum_pocob = sum(pocob))

total_expend_17_ef/pocob_ef_17
total_invest_17_ef/pocob_ef_17






















local_budgets_18 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                              year = 2018,
                              month = 12)
#> ??? Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ??? Reading data...
#> ??? Transforming data...
#> Warning: Problem with `mutate()` input `vykaz_date`.
#> ??? All formats failed to parse. No formats found.
#> ??? Input `vykaz_date` is `(function (..., quiet = FALSE, tz = NULL, locale = Sys.getlocale("LC_TIME"), ...`.
#> Warning: All formats failed to parse. No formats found.

head(local_budgets_18)
#> # A tibble: 6 x 15
#>   vykaz vtab  vykaz_year vykaz_month ucjed ico   kraj  nuts  polozka_typ
#>   <chr> <chr> <chr>      <chr>       <chr> <chr> <chr> <chr> <chr>      
#> 1 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 2 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 3 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 4 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 5 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 6 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> # . with 6 more variables: paragraf <chr>, polozka <chr>, budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, vykaz_date <date>
head(local_budgets_18[,13:15])


local_budgets_18 <- local_budgets_18 %>% 
  sp_add_codelist("polozka")



colnames(local_budgets_18)

local_budgets_18 <- local_budgets_18 %>% drop_na(druh)

local_budgets_18 <- local_budgets_18 %>% filter(druh == "Výdaje")

local_budgets_18 %>%
  summarise(vydaje = sum(budget_spending))

local_budgets_18 <- local_budgets_18 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)

local_budgets_18 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))



# funkci sp_add_codelist pomocí parametru "by" přimět data spojit jen podle sloupce ico
local_budgets_18 <- local_budgets_18 %>% 
  sp_add_codelist("ucjed", by = "ico")

local_budgets_18 <- local_budgets_18 %>% filter(druhuj_id == "4")

local_budgets_18 %>%
  summarise(obce = sum(budget_spending))


# group by ico
local_budgets_18 %>%
  group_by(ico)


new_df <- local_budgets_18 %>%
  mutate(pocob = as.numeric(pocob))

new_df <- new_df %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

new_df <- new_df %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

new_df %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))







expenditure_18_small <- local_budgets_18 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")


expenditure_18_small %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_18_small %>%
  group_by(ico)


expenditure_18_small <- expenditure_18_small %>%
  mutate(pocob = as.numeric(pocob))

expenditure_18_small <- expenditure_18_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_18_small <- expenditure_18_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_18_small <- expenditure_18_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_18_mid <- local_budgets_18 %>% filter(katobyv_id == "7")


expenditure_18_mid %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_18_mid %>%
  group_by(ico)


expenditure_18_mid <- expenditure_18_mid %>%
  mutate(pocob = as.numeric(pocob))

expenditure_18_mid <- expenditure_18_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_18_mid <- expenditure_18_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_18_mid <- expenditure_18_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_18_large <- local_budgets_18 %>% filter(katobyv_id == "11" | katobyv_id == "12")


expenditure_18_large %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_18_large %>%
  group_by(ico)


expenditure_18_large <- expenditure_18_large %>%
  mutate(pocob = as.numeric(pocob))

expenditure_18_large <- expenditure_18_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_18_large <- expenditure_18_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_18_large <- expenditure_18_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_18_ef <- local_budgets_18 %>% filter(katobyv_id == "5")


expenditure_18_ef %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_18_ef %>%
  group_by(ico)


expenditure_18_ef <- expenditure_18_ef %>%
  mutate(pocob = as.numeric(pocob))

expenditure_18_ef <- expenditure_18_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_18_ef <- expenditure_18_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_18_ef <- expenditure_18_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))

























local_budgets_19 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                                 year = 2019,
                                 month = 12)
#> ??? Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ??? Reading data...
#> ??? Transforming data...
#> Warning: Problem with `mutate()` input `vykaz_date`.
#> ??? All formats failed to parse. No formats found.
#> ??? Input `vykaz_date` is `(function (..., quiet = FALSE, tz = NULL, locale = Sys.getlocale("LC_TIME"), ...`.
#> Warning: All formats failed to parse. No formats found.

head(local_budgets_19)
#> # A tibble: 6 x 15
#>   vykaz vtab  vykaz_year vykaz_month ucjed ico   kraj  nuts  polozka_typ
#>   <chr> <chr> <chr>      <chr>       <chr> <chr> <chr> <chr> <chr>      
#> 1 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 2 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 3 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 4 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 5 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 6 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> # . with 6 more variables: paragraf <chr>, polozka <chr>, budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, vykaz_date <date>
head(local_budgets_19[,13:15])


local_budgets_19 <- local_budgets_19 %>% 
  sp_add_codelist("polozka")



colnames(local_budgets_19)

local_budgets_19 <- local_budgets_19 %>% drop_na(druh)

local_budgets_19 <- local_budgets_19 %>% filter(druh == "Výdaje")

local_budgets_19 %>%
  summarise(vydaje = sum(budget_spending))

local_budgets_19 <- local_budgets_19 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)

local_budgets_19 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))



# funkci sp_add_codelist pomocí parametru "by" přimět data spojit jen podle sloupce ico
local_budgets_19 <- local_budgets_19 %>% 
  sp_add_codelist("ucjed", by = "ico")

local_budgets_19 <- local_budgets_19 %>% filter(druhuj_id == "4")

local_budgets_19 %>%
  summarise(obce = sum(budget_spending))


# group by ico
local_budgets_19 %>%
  group_by(ico)


new_df <- local_budgets_19 %>%
  mutate(pocob = as.numeric(pocob))

new_df <- new_df %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

new_df <- new_df %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

new_df %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))







expenditure_19_small <- local_budgets_19 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")


expenditure_19_small %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_19_small %>%
  group_by(ico)


expenditure_19_small <- expenditure_19_small %>%
  mutate(pocob = as.numeric(pocob))

expenditure_19_small <- expenditure_19_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_19_small <- expenditure_19_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_19_small <- expenditure_19_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_19_mid <- local_budgets_19 %>% filter(katobyv_id == "7")


expenditure_19_mid %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_19_mid %>%
  group_by(ico)


expenditure_19_mid <- expenditure_19_mid %>%
  mutate(pocob = as.numeric(pocob))

expenditure_19_mid <- expenditure_19_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_19_mid <- expenditure_19_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_19_mid <- expenditure_19_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_19_large <- local_budgets_19 %>% filter(katobyv_id == "11" | katobyv_id == "12")


expenditure_19_large %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_19_large %>%
  group_by(ico)


expenditure_19_large <- expenditure_19_large %>%
  mutate(pocob = as.numeric(pocob))

expenditure_19_large <- expenditure_19_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_19_large <- expenditure_19_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_19_large <- expenditure_19_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_19_ef <- local_budgets_19 %>% filter(katobyv_id == "5")


expenditure_19_ef %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_19_ef %>%
  group_by(ico)


expenditure_19_ef <- expenditure_19_ef %>%
  mutate(pocob = as.numeric(pocob))

expenditure_19_ef <- expenditure_19_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_19_ef <- expenditure_19_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_19_ef <- expenditure_19_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))

























local_budgets_20 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                                 year = 2020,
                                 month = 12)
#> ??? Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ??? Reading data...
#> ??? Transforming data...
#> Warning: Problem with `mutate()` input `vykaz_date`.
#> ??? All formats failed to parse. No formats found.
#> ??? Input `vykaz_date` is `(function (..., quiet = FALSE, tz = NULL, locale = Sys.getlocale("LC_TIME"), ...`.
#> Warning: All formats failed to parse. No formats found.

head(local_budgets_20)
#> # A tibble: 6 x 15
#>   vykaz vtab  vykaz_year vykaz_month ucjed ico   kraj  nuts  polozka_typ
#>   <chr> <chr> <chr>      <chr>       <chr> <chr> <chr> <chr> <chr>      
#> 1 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 2 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 3 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 4 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 5 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 6 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> # . with 6 more variables: paragraf <chr>, polozka <chr>, budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, vykaz_date <date>
head(local_budgets_20[,13:15])


local_budgets_20 <- local_budgets_20 %>% 
  sp_add_codelist("polozka")



colnames(local_budgets_20)

local_budgets_20 <- local_budgets_20 %>% drop_na(druh)

local_budgets_20 <- local_budgets_20 %>% filter(druh == "Výdaje")

local_budgets_20 %>%
  summarise(vydaje = sum(budget_spending))

local_budgets_20 <- local_budgets_20 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)

local_budgets_20 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))



# funkci sp_add_codelist pomocí parametru "by" přimět data spojit jen podle sloupce ico
local_budgets_20 <- local_budgets_20 %>% 
  sp_add_codelist("ucjed", by = "ico")

local_budgets_20 <- local_budgets_20 %>% filter(druhuj_id == "4")

local_budgets_20 %>%
  summarise(obce = sum(budget_spending))


# group by ico
local_budgets_20 %>%
  group_by(ico)


new_df <- local_budgets_20 %>%
  mutate(pocob = as.numeric(pocob))

new_df <- new_df %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

new_df <- new_df %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

new_df %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))







expenditure_20_small <- local_budgets_20 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")


expenditure_20_small %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_20_small %>%
  group_by(ico)


expenditure_20_small <- expenditure_20_small %>%
  mutate(pocob = as.numeric(pocob))

expenditure_20_small <- expenditure_20_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_20_small <- expenditure_20_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_20_small <- expenditure_20_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_20_mid <- local_budgets_20 %>% filter(katobyv_id == "7")


expenditure_20_mid %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_20_mid %>%
  group_by(ico)


expenditure_20_mid <- expenditure_20_mid %>%
  mutate(pocob = as.numeric(pocob))

expenditure_20_mid <- expenditure_20_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_20_mid <- expenditure_20_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_20_mid <- expenditure_20_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_20_large <- local_budgets_20 %>% filter(katobyv_id == "11" | katobyv_id == "12")


expenditure_20_large %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_20_large %>%
  group_by(ico)


expenditure_20_large <- expenditure_20_large %>%
  mutate(pocob = as.numeric(pocob))

expenditure_20_large <- expenditure_20_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_20_large <- expenditure_20_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_20_large <- expenditure_20_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))









expenditure_20_ef <- local_budgets_20 %>% filter(katobyv_id == "5")


expenditure_20_ef %>%
  summarise(obce = sum(budget_spending))


# group by ico
expenditure_20_ef %>%
  group_by(ico)


expenditure_20_ef <- expenditure_20_ef %>%
  mutate(pocob = as.numeric(pocob))

expenditure_20_ef <- expenditure_20_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure = sum(budget_spending), .groups = "keep"
  )

expenditure_20_ef <- expenditure_20_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)

avg_expend_20_ef <- expenditure_20_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))

































investment_17 <- local_budgets_17 %>% filter(trida == "Kapitálové výdaje")
investment_18 <- local_budgets_18 %>% filter(trida == "Kapitálové výdaje")
investment_19 <- local_budgets_19 %>% filter(trida == "Kapitálové výdaje")
investment_20 <- local_budgets_20 %>% filter(trida == "Kapitálové výdaje")



investment_17 %>%
  summarise(obce = sum(budget_spending))
investment_18 %>%
  summarise(obce = sum(budget_spending))
investment_19 %>%
  summarise(obce = sum(budget_spending))
investment_20 %>%
  summarise(obce = sum(budget_spending))




# group by ico
investment_17 %>%
  group_by(ico)
investment_18 %>%
  group_by(ico)
investment_19 %>%
  group_by(ico)
investment_20 %>%
  group_by(ico)


new_df <- investment_17 %>%
  mutate(pocob = as.numeric(pocob))

new_df <- new_df %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )

new_df <- new_df %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)

new_df %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))

new_df %>%
  ungroup() %>%
  summarise(sum_pocob = sum(pocob))







investment_17_small <- investment_17 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
investment_18_small <- investment_18 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
investment_19_small <- investment_19 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
investment_20_small <- investment_20 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")


total_invest_17_small <- investment_17_small %>%
  summarise(obce = sum(budget_spending))
investment_18_small %>%
  summarise(obce = sum(budget_spending))
investment_19_small %>%
  summarise(obce = sum(budget_spending))
investment_20_small %>%
  summarise(obce = sum(budget_spending))




# group by ico
investment_17_small %>%
  group_by(ico)
investment_18_small %>%
  group_by(ico)
investment_19_small %>%
  group_by(ico)
investment_20_small %>%
  group_by(ico)


investment_17_small <- investment_17_small %>%
  mutate(pocob = as.numeric(pocob))
investment_18_small <- investment_18_small %>%
  mutate(pocob = as.numeric(pocob))
investment_19_small <- investment_19_small %>%
  mutate(pocob = as.numeric(pocob))
investment_20_small <- investment_20_small %>%
  mutate(pocob = as.numeric(pocob))

investment_17_small <- investment_17_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_18_small <- investment_18_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_19_small <- investment_19_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_20_small <- investment_20_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )

investment_17_small <- investment_17_small %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_18_small <- investment_18_small %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_19_small <- investment_19_small %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_20_small <- investment_20_small %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)

z_17_small <- investment_17_small %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
z_18_small <- investment_18_small %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
z_19_small <- investment_19_small %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
z_20_small <- investment_20_small %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))

avg_invest_17_small <- 3275/3439*z_17_small
avg_invest_18_small <- 3269/3425*z_18_small
avg_invest_19_small <- 3298/3419*z_19_small
avg_invest_20_small <- 3290/3412*z_20_small















investment_17_mid <- investment_17 %>% filter(katobyv_id == "7")
investment_18_mid <- investment_18 %>% filter(katobyv_id == "7")
investment_19_mid <- investment_19 %>% filter(katobyv_id == "7")
investment_20_mid <- investment_20 %>% filter(katobyv_id == "7")


total_invest_17_mid <- investment_17_mid %>%
  summarise(obce = sum(budget_spending))
investment_18_mid %>%
  summarise(obce = sum(budget_spending))
investment_19_mid %>%
  summarise(obce = sum(budget_spending))
investment_20_mid %>%
  summarise(obce = sum(budget_spending))


# group by ico
investment_17_mid %>%
  group_by(ico)
investment_18_mid %>%
  group_by(ico)
investment_19_mid %>%
  group_by(ico)
investment_20_mid %>%
  group_by(ico)


investment_17_mid <- investment_17_mid %>%
  mutate(pocob = as.numeric(pocob))
investment_18_mid <- investment_18_mid %>%
  mutate(pocob = as.numeric(pocob))
investment_19_mid <- investment_19_mid %>%
  mutate(pocob = as.numeric(pocob))
investment_20_mid <- investment_20_mid %>%
  mutate(pocob = as.numeric(pocob))

investment_17_mid <- investment_17_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_18_mid <- investment_18_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_19_mid <- investment_19_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_20_mid <- investment_20_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )

investment_17_mid <- investment_17_mid %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_18_mid <- investment_18_mid %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_19_mid <- investment_19_mid %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_20_mid <- investment_20_mid %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)

avg_invest_17_mid <- investment_17_mid %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_18_mid <- investment_18_mid %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_19_mid <- investment_19_mid %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_20_mid <- investment_20_mid %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))









investment_17_large <- investment_17 %>% filter(katobyv_id == "11" | katobyv_id == "12")
investment_18_large <- investment_18 %>% filter(katobyv_id == "11" | katobyv_id == "12")
investment_19_large <- investment_19 %>% filter(katobyv_id == "11" | katobyv_id == "12")
investment_20_large <- investment_20 %>% filter(katobyv_id == "11" | katobyv_id == "12")


total_invest_17_large <- investment_17_large %>%
  summarise(obce = sum(budget_spending))
investment_18_large %>%
  summarise(obce = sum(budget_spending))
investment_19_large %>%
  summarise(obce = sum(budget_spending))
investment_20_large %>%
  summarise(obce = sum(budget_spending))


# group by ico
investment_17_large %>%
  group_by(ico)
investment_18_large %>%
  group_by(ico)
investment_19_large %>%
  group_by(ico)
investment_20_large %>%
  group_by(ico)


investment_17_large <- investment_17_large %>%
  mutate(pocob = as.numeric(pocob))
investment_18_large <- investment_18_large %>%
  mutate(pocob = as.numeric(pocob))
investment_19_large <- investment_19_large %>%
  mutate(pocob = as.numeric(pocob))
investment_20_large <- investment_20_large %>%
  mutate(pocob = as.numeric(pocob))

investment_17_large <- investment_17_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_18_large <- investment_18_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_19_large <- investment_19_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_20_large <- investment_20_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )

investment_17_large <- investment_17_large %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_18_large <- investment_18_large %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_19_large <- investment_19_large %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_20_large <- investment_20_large %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)

avg_invest_17_large <- investment_17_large %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_18_large <- investment_18_large %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_19_large <- investment_19_large %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_20_large <- investment_20_large %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))













investment_17_ef <- investment_17 %>% filter(katobyv_id == "5")
investment_18_ef <- investment_18 %>% filter(katobyv_id == "5")
investment_19_ef <- investment_19 %>% filter(katobyv_id == "5")
investment_20_ef <- investment_20 %>% filter(katobyv_id == "5")


total_invest_17_ef <- investment_17_ef %>%
  summarise(obce = sum(budget_spending))
investment_18_ef %>%
  summarise(obce = sum(budget_spending))
investment_19_ef %>%
  summarise(obce = sum(budget_spending))
investment_20_ef %>%
  summarise(obce = sum(budget_spending))


# group by ico
investment_17_ef %>%
  group_by(ico)
investment_18_ef %>%
  group_by(ico)
investment_19_ef %>%
  group_by(ico)
investment_20_ef %>%
  group_by(ico)


investment_17_ef <- investment_17_ef %>%
  mutate(pocob = as.numeric(pocob))
investment_18_ef <- investment_18_ef %>%
  mutate(pocob = as.numeric(pocob))
investment_19_ef <- investment_19_ef %>%
  mutate(pocob = as.numeric(pocob))
investment_20_ef <- investment_20_ef %>%
  mutate(pocob = as.numeric(pocob))


investment_17_ef <- investment_17_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_18_ef <- investment_18_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_19_ef <- investment_19_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )
investment_20_ef <- investment_20_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    investment = sum(budget_spending), .groups = "keep"
  )

investment_17_ef <- investment_17_ef %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_18_ef <- investment_18_ef %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_19_ef <- investment_19_ef %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)
investment_20_ef <- investment_20_ef %>%
  group_by(ico, obec, pocob, investment) %>%
  summarise(
    investment_per_capita = investment/pocob
  ) %>%
  select(ico, obec, pocob, investment, investment_per_capita)


avg_invest_17_ef <- investment_17_ef %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_18_ef <- investment_18_ef %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_19_ef <- investment_19_ef %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))
avg_invest_20_ef <- investment_20_ef %>%
  ungroup() %>%
  summarise(mean_investment_per_capita = mean(investment_per_capita, na.rm = TRUE))



























local_budgets %>%
  summarise(obce = sum(budget_spending))
local_budgets_18 %>%
  summarise(obce = sum(budget_spending))
local_budgets_19 %>%
  summarise(obce = sum(budget_spending))
local_budgets_20 %>%
  summarise(obce = sum(budget_spending))











SNG_17 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                              year = 2017,
                              month = 12)
SNG_18 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2018,
                       month = 12)
SNG_19 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2019,
                       month = 12)
SNG_20 <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2020,
                       month = 12)
#> ??? Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ??? Reading data...
#> ??? Transforming data...
#> Warning: Problem with `mutate()` input `vykaz_date`.
#> ??? All formats failed to parse. No formats found.
#> ??? Input `vykaz_date` is `(function (..., quiet = FALSE, tz = NULL, locale = Sys.getlocale("LC_TIME"), ...`.
#> Warning: All formats failed to parse. No formats found.

head(SNG_17)
head(SNG_18)
head(SNG_19)
head(SNG_20)
#> # A tibble: 6 x 15
#>   vykaz vtab  vykaz_year vykaz_month ucjed ico   kraj  nuts  polozka_typ
#>   <chr> <chr> <chr>      <chr>       <chr> <chr> <chr> <chr> <chr>      
#> 1 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 2 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 3 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 4 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 5 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 6 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> # . with 6 more variables: paragraf <chr>, polozka <chr>, budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, vykaz_date <date>
head(SNG_17[,14:15])
head(SNG_18[,14:15])
head(SNG_19[,14:15])
head(SNG_20[,14:15])


SNG_17 <- SNG_17 %>% 
  sp_add_codelist("polozka")
SNG_18 <- SNG_18 %>% 
  sp_add_codelist("polozka")
SNG_19 <- SNG_19 %>% 
  sp_add_codelist("polozka")
SNG_20 <- SNG_20 %>% 
  sp_add_codelist("polozka")



colnames(SNG_17)
colnames(SNG_18)
colnames(SNG_19)
colnames(SNG_20)

SNG_17 <- SNG_17 %>% drop_na(druh)
SNG_18 <- SNG_18 %>% drop_na(druh)
SNG_19 <- SNG_19 %>% drop_na(druh)
SNG_20 <- SNG_20 %>% drop_na(druh)

SNG_17 <- SNG_17 %>% filter(druh == "Výdaje")
SNG_18 <- SNG_18 %>% filter(druh == "Výdaje")
SNG_19 <- SNG_19 %>% filter(druh == "Výdaje")
SNG_20 <- SNG_20 %>% filter(druh == "Výdaje")

SNG_17 %>%
  summarise(vydaje = sum(budget_spending))
SNG_18 %>%
  summarise(vydaje = sum(budget_spending))
SNG_19 %>%
  summarise(vydaje = sum(budget_spending))
SNG_20 %>%
  summarise(vydaje = sum(budget_spending))

SNG_17 <- SNG_17 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
SNG_18 <- SNG_18 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
SNG_19 <- SNG_19 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
SNG_20 <- SNG_20 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)

SNG_17 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))
SNG_18 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))
SNG_19 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))
SNG_20 %>%
  summarise(vydaje_konsolidovane = sum(budget_spending))



# funkci sp_add_codelist pomocí parametru "by" přimět data spojit jen podle sloupce ico
SNG_17 <- SNG_17 %>% 
  sp_add_codelist("ucjed", by = "ico")
SNG_18 <- SNG_18 %>% 
  sp_add_codelist("ucjed", by = "ico")
SNG_19 <- SNG_19 %>% 
  sp_add_codelist("ucjed", by = "ico")
SNG_20 <- SNG_20 %>% 
  sp_add_codelist("ucjed", by = "ico")

local_budgets_17 <- SNG_17 %>% filter(druhuj_id == "4")
local_budgets_18 <- SNG_18 %>% filter(druhuj_id == "4")
local_budgets_19 <- SNG_19 %>% filter(druhuj_id == "4")
local_budgets_20 <- SNG_20 %>% filter(druhuj_id == "4")


local_budgets_17 %>%
  summarise(obce = sum(budget_spending))
local_budgets_18 %>%
  summarise(obce = sum(budget_spending))
local_budgets_19 %>%
  summarise(obce = sum(budget_spending))
local_budgets_20 %>%
  summarise(obce = sum(budget_spending))




regional_budgets_17 <- SNG_17 %>% filter(druhuj_id == "3")
regional_budgets_18 <- SNG_18 %>% filter(druhuj_id == "3")
regional_budgets_19 <- SNG_19 %>% filter(druhuj_id == "3")
regional_budgets_20 <- SNG_20 %>% filter(druhuj_id == "3")

new_df <- SNG_20 %>% filter(druhuj_id != "3", druhuj_id != "4")
new_df <- SNG_20 %>% filter(druhuj_id == "5")

regional_budgets_17 %>%
  summarise(kraje = sum(budget_spending))
regional_budgets_18 %>%
  summarise(kraje = sum(budget_spending))
regional_budgets_19 %>%
  summarise(kraje = sum(budget_spending))
regional_budgets_20 %>%
  summarise(kraje = sum(budget_spending))

new_df %>%
  summarise(kraje = sum(budget_spending))




View(sp_tables)




central_budgets_17 <- sp_get_table(table_id = "budget-central", # table ID, see `sp_tables`
                       year = 2017,
                       month = 12)
central_budgets_18 <- sp_get_table(table_id = "budget-central", # table ID, see `sp_tables`
                       year = 2018,
                       month = 12)
central_budgets_19 <- sp_get_table(table_id = "budget-central", # table ID, see `sp_tables`
                       year = 2019,
                       month = 12)
central_budgets_20 <- sp_get_table(table_id = "budget-central", # table ID, see `sp_tables`
                       year = 2020,
                       month = 12)


head(central_budgets_17)
head(central_budgets_18)
head(central_budgets_19)
head(central_budgets_20)

head(central_budgets_17[,14:18])
head(central_budgets_18[,14:18])
head(central_budgets_19[,14:18])
head(central_budgets_20[,14:18])


central_budgets_17 <- central_budgets_17 %>% 
  sp_add_codelist("polozka")
central_budgets_18 <- central_budgets_18 %>% 
  sp_add_codelist("polozka")
central_budgets_19 <- central_budgets_19 %>% 
  sp_add_codelist("polozka")
central_budgets_20 <- central_budgets_20 %>% 
  sp_add_codelist("polozka")



colnames(central_budgets_17)
colnames(central_budgets_18)
colnames(central_budgets_19)
colnames(central_budgets_20)

central_budgets_17 <- central_budgets_17 %>% filter(druh == "Výdaje")
central_budgets_18 <- central_budgets_18 %>% filter(druh == "Výdaje")
central_budgets_19 <- central_budgets_19 %>% filter(druh == "Výdaje")
central_budgets_20 <- central_budgets_20 %>% filter(druh == "Výdaje")


central_budgets_17 <- central_budgets_17 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
central_budgets_18 <- central_budgets_18 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
central_budgets_19 <- central_budgets_19 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
central_budgets_20 <- central_budgets_20 %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)



central_expend_17 <- central_budgets_17 %>%
  summarise(vydaje = sum(budget_spending))
central_expend_18 <- central_budgets_18 %>%
  summarise(vydaje = sum(budget_spending))
central_expend_19 <- central_budgets_19 %>%
  summarise(vydaje = sum(budget_spending))
central_expend_20 <- central_budgets_20 %>%
  summarise(vydaje = sum(budget_spending))

sprintf("%.0f", central_expend_17)
sprintf("%.0f", central_expend_18)
sprintf("%.0f", central_expend_19)
sprintf("%.0f", central_expend_20)



























investment_17 <- SNG_17 %>% filter(trida == "Kapitálové výdaje")
investment_18 <- SNG_18 %>% filter(trida == "Kapitálové výdaje")
investment_19 <- SNG_19 %>% filter(trida == "Kapitálové výdaje")
investment_20 <- SNG_20 %>% filter(trida == "Kapitálové výdaje")



investment_17 %>%
  summarise(investice = sum(budget_spending))
investment_18 %>%
  summarise(investice = sum(budget_spending))
investment_19 %>%
  summarise(investice = sum(budget_spending))
investment_20 %>%
  summarise(investice = sum(budget_spending))





SNG_17 %>% filter(trida != "Kapitálové výdaje") %>%
  summarise(investice = sum(budget_spending))







local_investment_17 <- investment_17 %>% filter(druhuj_id == "4")
local_investment_18 <- investment_18 %>% filter(druhuj_id == "4")
local_investment_19 <- investment_19 %>% filter(druhuj_id == "4")
local_investment_20 <- investment_20 %>% filter(druhuj_id == "4")


local_investment_17 %>%
  summarise(obce = sum(budget_spending))
local_investment_18 %>%
  summarise(obce = sum(budget_spending))
local_investment_19 %>%
  summarise(obce = sum(budget_spending))
local_investment_20 %>%
  summarise(obce = sum(budget_spending))




regional_investment_17 <- investment_17 %>% filter(druhuj_id == "3")
regional_investment_18 <- investment_18 %>% filter(druhuj_id == "3")
regional_investment_19 <- investment_19 %>% filter(druhuj_id == "3")
regional_investment_20 <- investment_20 %>% filter(druhuj_id == "3")

new_df <- investment_20 %>% filter(druhuj_id != "3", druhuj_id != "4")
new_df <- investment_20 %>% filter(druhuj_id == "5")

regional_investment_17 %>%
  summarise(kraje = sum(budget_spending))
regional_investment_18 %>%
  summarise(kraje = sum(budget_spending))
regional_investment_19 %>%
  summarise(kraje = sum(budget_spending))
regional_investment_20 %>%
  summarise(kraje = sum(budget_spending))

new_df %>%
  summarise(kraje = sum(budget_spending))























central_investment_17 <- central_budgets_17 %>% filter(trida == "Kapitálové výdaje")
central_investment_18 <- central_budgets_18 %>% filter(trida == "Kapitálové výdaje")
central_investment_19 <- central_budgets_19 %>% filter(trida == "Kapitálové výdaje")
central_investment_20 <- central_budgets_20 %>% filter(trida == "Kapitálové výdaje")



central_investment_17 %>%
  summarise(investice = sum(budget_spending))
central_investment_18 %>%
  summarise(investice = sum(budget_spending))
central_investment_19 %>%
  summarise(investice = sum(budget_spending))
central_investment_20 %>%
  summarise(investice = sum(budget_spending))






















SNG_17_rev <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2017,
                       month = 12)
SNG_18_rev <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2018,
                       month = 12)
SNG_19_rev <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2019,
                       month = 12)
SNG_20_rev <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                       year = 2020,
                       month = 12)
#> ??? Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ??? Reading data...
#> ??? Transforming data...
#> Warning: Problem with `mutate()` input `vykaz_date`.
#> ??? All formats failed to parse. No formats found.
#> ??? Input `vykaz_date` is `(function (..., quiet = FALSE, tz = NULL, locale = Sys.getlocale("LC_TIME"), ...`.
#> Warning: All formats failed to parse. No formats found.

head(SNG_17_rev)
head(SNG_18_rev)
head(SNG_19_rev)
head(SNG_20_rev)
#> # A tibble: 6 x 15
#>   vykaz vtab  vykaz_year vykaz_month ucjed ico   kraj  nuts  polozka_typ
#>   <chr> <chr> <chr>      <chr>       <chr> <chr> <chr> <chr> <chr>      
#> 1 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 2 051   0002. 2019       09          1000. 7508. CZ03  CZ03  3          
#> 3 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 4 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 5 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> 6 051   0001. 2019       09          1000. 0006. CZ010 CZ01. 2          
#> # . with 6 more variables: paragraf <chr>, polozka <chr>, budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, vykaz_date <date>
head(SNG_17_rev[,14:15])
head(SNG_18_rev[,14:15])
head(SNG_19_rev[,14:15])
head(SNG_20_rev[,14:15])


SNG_17_rev <- SNG_17_rev %>% 
  sp_add_codelist("polozka")
SNG_18_rev <- SNG_18_rev %>% 
  sp_add_codelist("polozka")
SNG_19_rev <- SNG_19_rev %>% 
  sp_add_codelist("polozka")
SNG_20_rev <- SNG_20_rev %>% 
  sp_add_codelist("polozka")



colnames(SNG_17_rev)
colnames(SNG_18_rev)
colnames(SNG_19_rev)
colnames(SNG_20_rev)

SNG_17_rev <- SNG_17_rev %>% drop_na(druh)
SNG_18_rev <- SNG_18_rev %>% drop_na(druh)
SNG_19_rev <- SNG_19_rev %>% drop_na(druh)
SNG_20_rev <- SNG_20_rev %>% drop_na(druh)


SNG_17_rev <- SNG_17_rev %>% filter(druh == "Příjmy")
SNG_18_rev <- SNG_18_rev %>% filter(druh == "Příjmy")
SNG_19_rev <- SNG_19_rev %>% filter(druh == "Příjmy")
SNG_20_rev <- SNG_20_rev %>% filter(druh == "Příjmy")


SNG_17_rev %>%
  summarise(prijmy = sum(budget_spending))
SNG_18_rev %>%
  summarise(prijmy = sum(budget_spending))
SNG_19_rev %>%
  summarise(prijmy = sum(budget_spending))
SNG_20_rev %>%
  summarise(prijmy = sum(budget_spending))


SNG_17_rev <- SNG_17_rev %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
SNG_18_rev <- SNG_18_rev %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
SNG_19_rev <- SNG_19_rev %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)
SNG_20_rev <- SNG_20_rev %>% filter(kon_rep == FALSE, kon_kraj == FALSE, kon_okr == FALSE, kon_pol == FALSE)


SNG_17_rev %>%
  summarise(prijmy_konsolidovane = sum(budget_spending))
SNG_18_rev %>%
  summarise(prijmy_konsolidovane = sum(budget_spending))
SNG_19_rev %>%
  summarise(prijmy_konsolidovane = sum(budget_spending))
SNG_20_rev %>%
  summarise(prijmy_konsolidovane = sum(budget_spending))



# funkci sp_add_codelist pomocí parametru "by" přimět data spojit jen podle sloupce ico
SNG_17_rev <- SNG_17_rev %>% 
  sp_add_codelist("ucjed", by = "ico")
SNG_18_rev <- SNG_18_rev %>% 
  sp_add_codelist("ucjed", by = "ico")
SNG_19_rev <- SNG_19_rev %>% 
  sp_add_codelist("ucjed", by = "ico")
SNG_20_rev <- SNG_20_rev %>% 
  sp_add_codelist("ucjed", by = "ico")





transfers_17 <- SNG_17_rev %>% filter(trida == "Přijaté transfery")
transfers_18 <- SNG_18_rev %>% filter(trida == "Přijaté transfery")
transfers_19 <- SNG_19_rev %>% filter(trida == "Přijaté transfery")
transfers_20 <- SNG_20_rev %>% filter(trida == "Přijaté transfery")

transfers_17 %>%
  summarise(transfery = sum(budget_spending))
transfers_18 %>%
  summarise(transfery = sum(budget_spending))
transfers_19 %>%
  summarise(transfery = sum(budget_spending))
transfers_20 %>%
  summarise(transfery = sum(budget_spending))

y %>% filter(trida != "Přijaté transfery") %>%
  summarise(transfery = sum(budget_spending))




















View(sp_codelists)



local_budgets_17 <- local_budgets_17 %>% 
  sp_add_codelist("paragraf", by = "paragraf")
local_budgets_18 <- local_budgets_18 %>% 
  sp_add_codelist("paragraf", by = "paragraf")
local_budgets_19 <- local_budgets_19 %>% 
  sp_add_codelist("paragraf", by = "paragraf")
local_budgets_20 <- local_budgets_20 %>% 
  sp_add_codelist("paragraf", by = "paragraf")




colnames(local_budgets_20)







admin_17 <- local_budgets_17 %>% filter(oddil == "Státní moc, státní správa, územní samospráva a politické strany")
admin_18 <- local_budgets_18 %>% filter(oddil == "Státní moc, státní správa, územní samospráva a politické strany")
admin_19 <- local_budgets_19 %>% filter(oddil == "Státní moc, státní správa, územní samospráva a politické strany")
admin_20 <- local_budgets_20 %>% filter(oddil == "Státní moc, státní správa, územní samospráva a politické strany")
edu_17 <- local_budgets_17 %>% filter(oddil == "Vzdělávání a školské služby")
edu_18 <- local_budgets_18 %>% filter(oddil == "Vzdělávání a školské služby")
edu_19 <- local_budgets_19 %>% filter(oddil == "Vzdělávání a školské služby")
edu_20 <- local_budgets_20 %>% filter(oddil == "Vzdělávání a školské služby")
health_17 <- local_budgets_17 %>% filter(oddil == "Zdravotnictví")
health_18 <- local_budgets_18 %>% filter(oddil == "Zdravotnictví")
health_19 <- local_budgets_19 %>% filter(oddil == "Zdravotnictví")
health_20 <- local_budgets_20 %>% filter(oddil == "Zdravotnictví")













admin_17_small <- admin_17 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
admin_18_small <- admin_18 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
admin_19_small <- admin_19 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
admin_20_small <- admin_20 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
edu_17_small <- edu_17 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
edu_18_small <- edu_18 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
edu_19_small <- edu_19 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
edu_20_small <- edu_20 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
health_17_small <- health_17 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
health_18_small <- health_18 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
health_19_small <- health_19 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")
health_20_small <- health_20 %>% filter(katobyv_id == "1" | katobyv_id == "2" | katobyv_id == "3")





# group by ico
admin_17_small  %>%
  group_by(ico)
admin_18_small  %>%
  group_by(ico)
admin_19_small  %>%
  group_by(ico)
admin_20_small  %>%
  group_by(ico)
edu_17_small  %>%
  group_by(ico)
edu_18_small  %>%
  group_by(ico)
edu_19_small  %>%
  group_by(ico)
edu_20_small  %>%
  group_by(ico)
health_17_small %>%
  group_by(ico) 
health_18_small %>%
  group_by(ico) 
health_19_small %>%
  group_by(ico) 
health_20_small %>%
  group_by(ico) 











admin_17_small <- admin_17_small %>%
  mutate(pocob = as.numeric(pocob))
admin_18_small  <- admin_18_small %>%
  mutate(pocob = as.numeric(pocob))
admin_19_small <- admin_19_small %>%
  mutate(pocob = as.numeric(pocob))
admin_20_small <- admin_20_small %>%
  mutate(pocob = as.numeric(pocob))
edu_17_small  <- edu_17_small %>%
  mutate(pocob = as.numeric(pocob))
edu_18_small <- edu_18_small %>%
  mutate(pocob = as.numeric(pocob))
edu_19_small <- edu_19_small %>%
  mutate(pocob = as.numeric(pocob))
edu_20_small <- edu_20_small %>%
  mutate(pocob = as.numeric(pocob))
health_17_small <- health_17_small %>%
  mutate(pocob = as.numeric(pocob))
health_18_small <- health_18_small %>%
  mutate(pocob = as.numeric(pocob))
health_19_small <- health_19_small %>%
  mutate(pocob = as.numeric(pocob))
health_20_small <- health_20_small %>%
  mutate(pocob = as.numeric(pocob))



admin_17_small <- admin_17_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
   expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_18_small  <- admin_18_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_19_small <- admin_19_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_20_small <- admin_20_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_17_small  <- edu_17_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_18_small <- edu_18_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_19_small <- edu_19_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_20_small <- edu_20_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_17_small <- health_17_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_18_small <- health_18_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_19_small <- health_19_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_20_small <- health_20_small %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )







admin_17_small <- admin_17_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_18_small  <- admin_18_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_19_small <- admin_19_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_20_small <- admin_20_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_17_small  <- edu_17_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_18_small <- edu_18_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_19_small <- edu_19_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_20_small <- edu_20_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_17_small <- health_17_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_18_small <- health_18_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_19_small <- health_19_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_20_small <- health_20_small %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)








avg_admin_17_small <- admin_17_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_18_small <- admin_18_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_19_small <- admin_19_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_20_small <- admin_20_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))



z_17_small <- edu_17_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
z_18_small <- edu_18_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
z_19_small <- edu_19_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
z_20_small <- edu_20_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_17_small <- health_17_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_18_small <- health_18_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_19_small <- health_19_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_20_small <- health_20_small %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))


avg_edu_17_small <- 1461/3439*z_17_small
avg_edu_18_small <- 1485/3425*z_18_small
avg_edu_19_small <- 1498/3419*z_19_small
avg_edu_20_small <- 1462/3412*z_20_small
avg_health_17_small <- 601/3439*y_17_small
avg_health_18_small <- 610/3425*y_18_small
avg_health_19_small <- 666/3419*y_19_small
avg_health_20_small <- 659/3412*y_20_small










admin_17_mid <- admin_17 %>% filter(katobyv_id == "7")
admin_18_mid  <- admin_18 %>% filter(katobyv_id == "7")
admin_19_mid <- admin_19 %>% filter(katobyv_id == "7")
admin_20_mid <- admin_20 %>% filter(katobyv_id == "7")
edu_17_mid  <- edu_17 %>% filter(katobyv_id == "7")
edu_18_mid <- edu_18 %>% filter(katobyv_id == "7")
edu_19_mid <- edu_19 %>% filter(katobyv_id == "7")
edu_20_mid <- edu_20 %>% filter(katobyv_id == "7")
health_17_mid <- health_17 %>% filter(katobyv_id == "7")
health_18_mid <- health_18 %>% filter(katobyv_id == "7")
health_19_mid <- health_19 %>% filter(katobyv_id == "7")
health_20_mid <- health_20 %>% filter(katobyv_id == "7")





# group by ico

edu_17_mid %>%
  group_by(ico)
edu_18_mid %>%
  group_by(ico)
edu_19_mid %>%
  group_by(ico)
edu_20_mid %>%
  group_by(ico)
health_17_mid %>%
  group_by(ico) 
health_18_mid %>%
  group_by(ico) 
health_19_mid %>%
  group_by(ico) 
health_20_mid %>%
  group_by(ico) 



admin_17_mid <- admin_17_mid %>%
  mutate(pocob = as.numeric(pocob))
admin_18_mid  <- admin_18_mid %>%
  mutate(pocob = as.numeric(pocob))
admin_19_mid <- admin_19_mid %>%
  mutate(pocob = as.numeric(pocob))
admin_20_mid <- admin_20_mid %>%
  mutate(pocob = as.numeric(pocob))
edu_17_mid  <- edu_17_mid %>%
  mutate(pocob = as.numeric(pocob))
edu_18_mid <- edu_18_mid %>%
  mutate(pocob = as.numeric(pocob))
edu_19_mid <- edu_19_mid %>%
  mutate(pocob = as.numeric(pocob))
edu_20_mid <- edu_20_mid %>%
  mutate(pocob = as.numeric(pocob))
health_17_mid <- health_17_mid %>%
  mutate(pocob = as.numeric(pocob))
health_18_mid <- health_18_mid %>%
  mutate(pocob = as.numeric(pocob))
health_19_mid <- health_19_mid %>%
  mutate(pocob = as.numeric(pocob))
health_20_mid <- health_20_mid %>%
  mutate(pocob = as.numeric(pocob))






admin_17_mid <- admin_17_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_18_mid  <- admin_18_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_19_mid <- admin_19_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_20_mid <- admin_20_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_17_mid  <- edu_17_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_18_mid <- edu_18_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_19_mid <- edu_19_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_20_mid <- edu_20_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_17_mid <- health_17_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_18_mid <- health_18_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_19_mid <- health_19_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_20_mid <- health_20_mid %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )











admin_17_mid <- admin_17_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_18_mid  <- admin_18_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_19_mid <- admin_19_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_20_mid <- admin_20_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_17_mid  <- edu_17_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_18_mid <- edu_18_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_19_mid <- edu_19_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_20_mid <- edu_20_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_17_mid <- health_17_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_18_mid <- health_18_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_19_mid <- health_19_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_20_mid <- health_20_mid %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)











avg_admin_17_mid <- admin_17_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_18_mid  <- admin_18_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_19_mid <- admin_19_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_20_mid <- admin_20_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_17_mid  <- edu_17_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_18_mid <- edu_18_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_19_mid <- edu_19_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_20_mid <- edu_20_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))


y_17_mid <- health_17_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_18_mid <- health_18_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_19_mid <- health_19_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_20_mid <- health_20_mid %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))



avg_health_17_mid <- 84/144*y_17_mid
avg_health_18_mid <- 90/143*y_18_mid
avg_health_19_mid <- 93/147*y_19_mid
avg_health_20_mid <- 100/147*y_20_mid













admin_17_large <- admin_17 %>% filter(katobyv_id == "11" | katobyv_id == "12")
admin_18_large  <- admin_18 %>% filter(katobyv_id == "11" | katobyv_id == "12")
admin_19_large <- admin_19 %>% filter(katobyv_id == "11" | katobyv_id == "12")
admin_20_large <- admin_20 %>% filter(katobyv_id == "11" | katobyv_id == "12")
edu_17_large  <- edu_17 %>% filter(katobyv_id == "11" | katobyv_id == "12")
edu_18_large <- edu_18 %>% filter(katobyv_id == "11" | katobyv_id == "12")
edu_19_large <- edu_19 %>% filter(katobyv_id == "11" | katobyv_id == "12")
edu_20_large <- edu_20 %>% filter(katobyv_id == "11" | katobyv_id == "12")
health_17_large <- health_17 %>% filter(katobyv_id == "11" | katobyv_id == "12")
health_18_large <- health_18 %>% filter(katobyv_id == "11" | katobyv_id == "12")
health_19_large <- health_19 %>% filter(katobyv_id == "11" | katobyv_id == "12")
health_20_large <- health_20 %>% filter(katobyv_id == "11" | katobyv_id == "12")





# group by ico

health_17_large %>%
  group_by(ico)
health_18_large %>%
  group_by(ico)
health_19_large %>%
  group_by(ico)
health_20_large %>%
  group_by(ico)



admin_17_large <- admin_17_large %>%
  mutate(pocob = as.numeric(pocob))
admin_18_large  <- admin_18_large %>%
  mutate(pocob = as.numeric(pocob))
admin_19_large <- admin_19_large %>%
  mutate(pocob = as.numeric(pocob))
admin_20_large <- admin_20_large %>%
  mutate(pocob = as.numeric(pocob))
edu_17_large  <- edu_17_large %>%
  mutate(pocob = as.numeric(pocob))
edu_18_large <- edu_18_large %>%
  mutate(pocob = as.numeric(pocob))
edu_19_large <- edu_19_large %>%
  mutate(pocob = as.numeric(pocob))
edu_20_large <- edu_20_large %>%
  mutate(pocob = as.numeric(pocob))
health_17_large <- health_17_large %>%
  mutate(pocob = as.numeric(pocob))
health_18_large <- health_18_large %>%
  mutate(pocob = as.numeric(pocob))
health_19_large <- health_19_large %>%
  mutate(pocob = as.numeric(pocob))
health_20_large <- health_20_large %>%
  mutate(pocob = as.numeric(pocob))




admin_17_large <- admin_17_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_18_large  <- admin_18_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_19_large <- admin_19_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_20_large <- admin_20_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_17_large  <- edu_17_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_18_large <- edu_18_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_19_large <- edu_19_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_20_large <- edu_20_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_17_large <- health_17_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_18_large <- health_18_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_19_large <- health_19_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_20_large <- health_20_large %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )







admin_17_large <- admin_17_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_18_large  <- admin_18_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_19_large <- admin_19_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_20_large <- admin_20_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_17_large  <- edu_17_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_18_large <- edu_18_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_19_large <- edu_19_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_20_large <- edu_20_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_17_large <- health_17_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_18_large <- health_18_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_19_large <- health_19_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_20_large <- health_20_large %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)










avg_admin_17_large <- admin_17_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_18_large  <- admin_18_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_19_large <- admin_19_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_20_large <- admin_20_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_17_large  <- edu_17_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_18_large <- edu_18_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_19_large <- edu_19_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_edu_20_large <- edu_20_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_health_17_large <- health_17_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_health_18_large <- health_18_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_health_19_large <- health_19_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_health_20_large <- health_20_large %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))














admin_17_ef <- admin_17 %>% filter(katobyv_id == "5")
admin_18_ef  <- admin_18 %>% filter(katobyv_id == "5")
admin_19_ef <- admin_19 %>% filter(katobyv_id == "5")
admin_20_ef <- admin_20 %>% filter(katobyv_id == "5")
edu_17_ef  <- edu_17 %>% filter(katobyv_id == "5")
edu_18_ef <- edu_18 %>% filter(katobyv_id == "5")
edu_19_ef <- edu_19 %>% filter(katobyv_id == "5")
edu_20_ef <- edu_20 %>% filter(katobyv_id == "5")
health_17_ef <- health_17 %>% filter(katobyv_id == "5")
health_18_ef <- health_18 %>% filter(katobyv_id == "5")
health_19_ef <- health_19 %>% filter(katobyv_id == "5")
health_20_ef <- health_20 %>% filter(katobyv_id == "5")




# group by ico

edu_17_ef %>%
  group_by(ico)
edu_18_ef %>%
  group_by(ico)
edu_19_ef %>%
  group_by(ico)
edu_20_ef %>%
  group_by(ico)
health_17_ef %>%
  group_by(ico) 
health_18_ef %>%
  group_by(ico) 
health_19_ef %>%
  group_by(ico) 
health_20_ef %>%
  group_by(ico) 





admin_17_ef <- admin_17_ef %>%
  mutate(pocob = as.numeric(pocob))
admin_18_ef  <- admin_18_ef %>%
  mutate(pocob = as.numeric(pocob))
admin_19_ef <- admin_19_ef %>%
  mutate(pocob = as.numeric(pocob))
admin_20_ef <- admin_20_ef %>%
  mutate(pocob = as.numeric(pocob))
edu_17_ef  <- edu_17_ef %>%
  mutate(pocob = as.numeric(pocob))
edu_18_ef <- edu_18_ef %>%
  mutate(pocob = as.numeric(pocob))
edu_19_ef <- edu_19_ef %>%
  mutate(pocob = as.numeric(pocob))
edu_20_ef <- edu_20_ef %>%
  mutate(pocob = as.numeric(pocob))
health_17_ef <- health_17_ef %>%
  mutate(pocob = as.numeric(pocob))
health_18_ef <- health_18_ef %>%
  mutate(pocob = as.numeric(pocob))
health_19_ef <- health_19_ef %>%
  mutate(pocob = as.numeric(pocob))
health_20_ef <- health_20_ef %>%
  mutate(pocob = as.numeric(pocob))








admin_17_ef <- admin_17_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_18_ef  <- admin_18_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_19_ef <- admin_19_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
admin_20_ef <- admin_20_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_17_ef  <- edu_17_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_18_ef <- edu_18_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_19_ef <- edu_19_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
edu_20_ef <- edu_20_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_17_ef <- health_17_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_18_ef <- health_18_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_19_ef <- health_19_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )
health_20_ef <- health_20_ef %>%
  group_by(ico, obec, pocob) %>%
  summarise(
    expenditure  = sum(budget_spending), .groups = "keep"
  )










admin_17_ef <- admin_17_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_18_ef  <- admin_18_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_19_ef <- admin_19_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
admin_20_ef <- admin_20_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_17_ef <- edu_17_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_18_ef <- edu_18_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_19_ef <- edu_19_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
edu_20_ef <- edu_20_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_17_ef <- health_17_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_18_ef <- health_18_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_19_ef <- health_19_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)
health_20_ef <- health_20_ef %>%
  group_by(ico, obec, pocob, expenditure) %>%
  summarise(
    expenditure_per_capita = expenditure/pocob
  ) %>%
  select(ico, obec, pocob, expenditure, expenditure_per_capita)














avg_admin_17_ef <- admin_17_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_18_ef  <- admin_18_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_19_ef <- admin_19_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
avg_admin_20_ef <- admin_20_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))





z_17_ef <- edu_17_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
z_18_ef <- edu_18_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
z_19_ef <- edu_19_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
z_20_ef <- edu_20_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_17_ef <- health_17_ef %>%
  ungroup() %>%
  smasummarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_18_ef <- health_18_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_19_ef <- health_19_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))
y_20_ef <- health_20_ef %>%
  ungroup() %>%
  summarise(mean_expenditure_per_capita = mean(expenditure_per_capita, na.rm = TRUE))


avg_edu_17_ef <- 746/748*z_17_ef
avg_edu_18_ef <- 752/754*z_18_ef
avg_edu_19_ef <- 767/769*z_19_ef
avg_edu_20_ef <- 774/776*z_20_ef
avg_health_17_ef <- 315/748*y_17_ef
avg_health_18_ef <- 325/754*y_18_ef
avg_health_19_ef <- 339/769*y_19_ef
avg_health_20_ef <- 336/776*y_20_ef








































sessionInfo()