An R package for using data from the Czech public finance database (Monitor Státní pokladny) • statnipokladna

The goal of statnipokladna is to provide programmatic access to open data from the Státní pokladna system. Státní pokladna is a comprehensive budgeting, reporting and accounting system for Czech public organisations. This package provides user-friendly ways to access the open data from that system available at https://monitor.statnipokladna.cz. The vignettes in the package also provide an introduction to the underlying data.

Installation

You can install the released version from CRAN:

install.packages("statnipokladna")

You can install the current development release of statnipokladna from GitHub with:

remotes::install_github("petrbouchal/statnipokladna",
                        build_vignettes = TRUE,
                        ref = github_release())

or the latest in-development version with

remotes::install_github("petrbouchal/statnipokladna",
                         build_vignettes = TRUE)

I also keep binaries in a drat repo, which you can access by

install.packages("statnipokladna", repos = "https://petrbouchal.xyz/drat")

Bug reports

Please report bugs at https://github.com/petrbouchal/statnipokladna/issues.

What this package enables you to do:

get cleaned-up, ready to analyse data frames based on open data dumps from the public finance database
- the package draws on the online data and returns a clean data frame
- the resulting data is ready to merge into time series
- time series is built based on user input
do this through a consistent API which supplements some of the documentation that is missing from the official endpoints (still subject to some change)
access registers published alongside the data (e.g. lists of public organisations with their identifiers and metadata), some of which can be useful in other contexts
augment the core data with the desired type of register

See the Get started vignette for background on the underlying data.

How does this compare to the official analytical interface?

no limit on the number of data points
no limits on the number of organisations, unlike the official interface which forces you to use a filter on some tables
different reports (local gov, central gov…) in one place in consistent form
much faster for analysis (the current version of the online interface takes long to render)
reproducible!!! While the online interface provides a permanent link to your analysis, this must be copied manually and does not necessarily provide an easily legible overview of how the analysis was produced (filters, columns etc.)
no need for the web => excel => R dance
drawback: for some reports, the data is published in different forms for different time periods (pre- and post-2015)
drawback: consolidation must be done manually

Future development

the official system has been partially overhauled in February 2020 and I am trying to find out about which parts of its new API will remain stable and can be used externally. Depending on the result, some functionality in this package can be streamlined and some can be added - e.g.

listing of available releases
checking against existing releases and data sets
retrieving some previously unpublished data e.g. calculated indicators and budget responsibility monitoring

Getting started

library(statnipokladna)

Get data from a particular part (file) of a dataset (“výkaz”):

local_budgets <- sp_get_table(table_id = "budget-local", # table ID, see `sp_tables`
                           year = 2019,
                           month = 9)
#> ℹ Files already in ~/sp_data/finm/2019/09, not downloading. Set `redownload = TRUE` if needed.
#> ℹ Reading data...
#> ℹ Transforming data...

The data is automatically downloaded to a temp directory, so it will be reused by future calls to sp_get_table() made in the same session, unless you set force_redownload = TRUE. You set the dest_dir parameter e.g. to ".", a directory will be created in your current working directory and the data will be downloaded into it so that it can persist across sessions.

It is a rather raw-looking data frame…

head(local_budgets)
#> # A tibble: 6 x 15
#>   vykaz vtab  per_yr per_m ucjed ico   kraj  nuts  `0CI_TYPE` paragraf polozka
#>   <chr> <chr> <chr>  <chr> <chr> <chr> <chr> <chr> <chr>      <chr>    <chr>  
#> 1 051   0002… 2019   09    1000… 7508… CZ03  CZ03  3          6409     5364   
#> 2 051   0002… 2019   09    1000… 7508… CZ03  CZ03  3          6409     5909   
#> 3 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1111   
#> 4 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1112   
#> 5 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1113   
#> 6 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1121   
#> # … with 4 more variables: budget_adopted <dbl>, budget_amended <dbl>,
#> #   budget_spending <dbl>, period_vykaz <date>

but it has been cleaned up, and can be enriched with any of the metadata codelists:

functional_categories <- sp_get_codelist("paragraf")
#> ℹ Codelist file already in '~/sp_data/', not downloading. Set `redownload = TRUE` if needed.
#> ℹ Processing codelist data

functional_categories
#> # A tibble: 552 x 7
#>    paragraf skupina pododdil oddil paragraf_nazev          start_date end_date  
#>    <chr>    <chr>   <chr>    <chr> <chr>                   <date>     <date>    
#>  1 0000     0       0        0     Pro příjmy (technický … 1900-01-01 9999-12-31
#>  2 1011     1       101      10    Udržování výrobního po… 1900-01-01 9999-12-31
#>  3 1012     1       101      10    Podnikání a restruktur… 1900-01-01 9999-12-31
#>  4 1013     1       101      10    Genetický potenciál ho… 1900-01-01 9999-12-31
#>  5 1014     1       101      10    Ozdravování hospodářsk… 1900-01-01 9999-12-31
#>  6 1019     1       101      10    Ostatní zemědělská a p… 1900-01-01 9999-12-31
#>  7 1021     1       102      10    Organizace trhu s prod… 1900-01-01 9999-12-31
#>  8 1022     1       102      10    Organizace trhu s výro… 1900-01-01 9999-12-31
#>  9 1023     1       102      10    Organizace trhu s prod… 1900-01-01 9999-12-31
#> 10 1024     1       102      10    Organizace trhu s výro… 1900-01-01 9999-12-31
#> # … with 542 more rows

This contains all codes for this codelist, some of which are not valid for the time period of our core data. The function add_codelist() resolves this automatically.

As you can see below, you can

add multiple codelists in one pipe
add a codelist without downloading it first - just pass its ID to the function as a character instead of an object.

Codelists are also cached, but you have one in your namespace, you can pass it as an object, provided that it has the right columns.

local_budgets %>% 
  sp_add_codelist(functional_categories) %>% 
  sp_add_codelist("polozka")
#> Warning: The `keep` argument of `group_map()` is deprecated as of dplyr 1.0.0.
#> Please use the `.keep` argument instead.
#> This warning is displayed once every 8 hours.
#> Call `lifecycle::last_warnings()` to see where this warning was generated.
#> ℹ Codelist file already in '~/sp_data/', not downloading. Set `redownload = TRUE` if needed.
#> ℹ Processing codelist data
#> # A tibble: 1,189,627 x 27
#>    vykaz vtab  per_yr per_m ucjed ico   kraj  nuts  `0CI_TYPE` paragraf polozka
#>    <chr> <chr> <chr>  <chr> <chr> <chr> <chr> <chr> <chr>      <chr>    <chr>  
#>  1 051   0002… 2019   09    1000… 7508… CZ03  CZ03  3          6409     5364   
#>  2 051   0002… 2019   09    1000… 7508… CZ03  CZ03  3          6409     5909   
#>  3 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1111   
#>  4 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1112   
#>  5 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1113   
#>  6 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1121   
#>  7 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1122   
#>  8 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1211   
#>  9 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1332   
#> 10 051   0001… 2019   09    1000… 0006… CZ010 CZ01… 2          0000     1333   
#> # … with 1,189,617 more rows, and 16 more variables: budget_adopted <dbl>,
#> #   budget_amended <dbl>, budget_spending <dbl>, period_vykaz <date>,
#> #   skupina <chr>, pododdil <chr>, oddil <chr>,
#> #   functional_categories_paragraf_nazev <chr>,
#> #   functional_categories_start_date <date>,
#> #   functional_categories_end_date <date>, polozka_start_date <date>,
#> #   polozka_end_date <date>, trida <chr>, seskupeni <chr>, podseskupeni <chr>,
#> #   polozka_nazev <chr>

Download a whole “výkaz” (dataset/data dump):

sp_get_dataset("finm") # dataset ID, see `sp_datasets`
#> Warning: Either year or month not set.
#> Using defaults of 2018 and 12.
#> ● Set these values explicitly for reproducibility as the defaults may change in the future
#>   to provide access to the latest data by default.
#> ℹ Files already in ~/sp_data/finm/2018/12, not downloading. Set `redownload = TRUE` if needed.

This will put the files in a temp directory.

Then look at its documentation:

statnipokladna::sp_get_dataset_doc("finm")
#> ℹ Getting dataset documentation from http://monitor.statnipokladna.cz/data/struktura/finm.xlsx
#> ℹ File downloaded to '~/sp_data/finm.xlsx'.

You can get details of all the available tables in the sp_tables data frame; for datasets, see sp_datasets.

Background information

Note that while the package provides a bridge from complicated data dumps to a clean data structure, you still need quite a bit of domain knowledge to be able analyse the data safely.

See the “How the data works” vignette (in Czech only, the terminology is impossible to translate) for an overview of the structure of the data on which this package draws. This also contains some notes useful for interpreting the data.

A subset of this information is in the Get started vignette.

There is also a log of various data gotchas I discovered, also in Czech only, stored in the data issues vignette.

A basic glossary of some of the terms used in the data sets is at http://monitor.statnipokladna.cz/metodika/.

Note

Not created or endorsed by the Czech Ministry of Finance, who produce the data - but they definitely deserve credit for releasing the data and maintaining the application.

Acknowledgments

Thanks to @smallhillcz and the Státní pokladna/Monitor developers and maintainers for responding to queries and generally keeping the thing running.

Contributing

See CONTRIBUTING.md for a guide on how to contribute to the project.

Please note that the ‘statnipokladna’ project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.