The Statistical Abstract as tidy tables • knbsdata

library(knbsdata)

The Statistical Abstract is KNBS’s annual compendium, and since the 2025 edition its chapter tables ship as Excel workbooks on the KNBS website — eighteen chapters from Land & Water to Social & Economic Inclusion. knbs_publications() finds those workbooks and knbs_download_publication() fetches them verified; this vignette is about the step beyond: reading a table inside a workbook as clean tidy observations.

Curated, not guessed

A published spreadsheet is a print layout, not a dataset: title rows, merged headers, section headings, footnote lines, dot leaders, provisional marks. A universal spreadsheet-to-tidy converter would have to guess its way through all of that, and a wrong guess produces plausible, wrong data — the worst outcome this package knows.

So extraction here is curated. Each supported table has a hand-verified recipe in the package’s registry: which sheet, which title anchor, which rows are section headings, what the unit is, and several reference values read from the workbook by hand at curation time. Every extraction re-checks all of it. If KNBS reshuffles a sheet or revises a pinned figure, you get knbs_error_schema_changed or knbs_error_validation_failed with an explanation — never silently re-arranged numbers.

Curated so far, 48 tables across 13 chapters:

Chapter 2, National Accounts — all fifteen tables: the production accounts, GDP by activity at current and constant prices, expenditure on GDP, capital formation, national income and saving.
Chapters 3–16 — 33 more: wage employment by sector (3); the Post Office Savings Bank and insurance business (4); principal export commodities (6); crop sales and producer prices (7); forest plantations and wildlife (8); electricity connections and fuel sales (9); producer price indices and production of selected commodities (10); construction output by county and cement (11); visitor arrivals, departures and hotel occupancy (12); railway earnings, vehicle registration and inspection, Mombasa port and aircraft (13); school enrolment, including by county (15); and registered births and deaths by county (16).

Not every table can be curated this way. A survey of all 267 tables in the chapter workbooks found 38 in the layout the engine reads — items in rows, years across — and five of those were left out: four run across two sheets, and one puts Male, Female and Total under each year, which the engine now refuses rather than misreading. The registry is the extension point for the rest.

Listing and fetching

knbs_abstract_tables()
#> <knbs_catalogue>
#> Title: 2025 Statistical Abstract: curated tables
#> Rows: 48
#> Source: Kenya National Bureau of Statistics
#> 
#> # A tibble: 48 × 6
#>    table_id title                                   chapter unit  sheet workbook
#>    <chr>    <chr>                                   <chr>   <chr> <chr> <chr>   
#>  1 2.1      Annual Production Accounts by Industry… Chapte… KSh … Tabl… NA-Stat…
#>  2 2.2      Gross Domestic Product by Activity, 20… Chapte… KSh … Tabl… NA-Stat…
#>  3 2.3      Contribution to Gross Domestic Product… Chapte… per … Tabl… NA-Stat…
#>  4 2.4      Gross Domestic Product by Activity, 20… Chapte… KSh … Tabl… NA-Stat…
#>  5 2.5      Growth of Gross Domestic Product by Ac… Chapte… per … Tabl… NA-Stat…
#>  6 2.6a     Expenditure on the Gross Domestic Prod… Chapte… KSh … Tabl… NA-Stat…
#>  7 2.6b     Expenditure on the Gross Domestic Prod… Chapte… per … Tabl… NA-Stat…
#>  8 2.6c     Expenditure on the Gross Domestic Prod… Chapte… KSh … Tabl… NA-Stat…
#>  9 2.6d     Growth in Expenditure on the Gross Dom… Chapte… per … Tabl… NA-Stat…
#> 10 2.7a     Gross Fixed Capital Formation, 2015 - … Chapte… KSh … Tabl… NA-Stat…
#> # ℹ 38 more rows

gdp <- knbs_abstract_table("2.2")     # GDP by activity, current prices
gdp[gdp$industry == "GDP at market prices", c("period", "status", "value")]
#> # A tibble: 10 x 3
#>    period status         value
#>     <int> <chr>          <dbl>
#>  1   2015 NA           6884326
#>  2   2016 NA           7594064
#>  ...
#>  9   2023 revised     15033610
#> 10   2024 provisional 16224478

A table id works, and so does enough of a title:

saving <- knbs_abstract_table("disposable income")   # resolves to 2.9

Reading the columns

period is the integer year, and status carries the source’s own year flags: "provisional" (printed *), "revised" (printed +), NA for final years.
The row dimension keeps its meaning as a named column: industry for the GDP tables, expenditure_category, purpose for capital formation.
Sectioned tables carry their section as a second named column. In the production accounts ("2.1"), industry is the section and measure the row — output, intermediate consumption, value added, compensation.
A row the source prints without figures — “Less: Subsidies” in the production accounts — is kept with NA values, because that is what the source shows.
The tables print totals beside their parts: “GDP at market prices”, then “All industries”, then each industry, then sub-industries such as “Growing of crops”. parent names the row each item is a component of — industries have "All industries", sub-industries their industry, headline totals and memo items NA — so summing within one parent never double counts, while summing a whole year does. The parts are checked to add up to their parent, within rounding, on every read. Imports enter GDP with a negative sign, and knbs_metadata() says so. The accounts tables (2.1, 2.8, 2.9) are linked by accounting identities instead and have no parent.
Units are stated in knbs_metadata(), not repeated on every row; a mixed-unit table (2.8 carries KSh million aggregates and per-capita KSh) says so there.

County tables join everything else

The county tables carry geo_code, the census frame code of each row, so they join the census, knbs_geographies() and anything passed through knbs_county_code(). The total row is "00". Registered births per 1,000 people by county, from the Abstract’s Table 16.11 and the 2019 census:

library(dplyr)

births <- knbs_abstract_table("16.11") |>          # by county of occurrence
  filter(period == 2019L, geo_code != "00")
pop <- knbs_get_indicator("POPCEN0002", geo_level = "county") |>
  filter(sex == "total", age_type == "total")

births |>
  inner_join(pop, by = "geo_code", suffix = c("_births", "_pop")) |>
  mutate(births_per_1000 = 1000 * value_births / value_pop) |>
  arrange(desc(births_per_1000))
#> Kericho 37.0, Busia 34.4, Nairobi City 33.4, ... Mandera 14.3, Wajir 8.2

All 47 counties join. Read the result as registration, not fertility: the table counts births registered, by the county where they occurred, so it reflects civil-registration coverage and where hospitals are as much as birth rates.

A county table is refused, rather than served, if its rows stop accounting for the 47 counties exactly once — a county lost, doubled, or renamed past recognition.

Units, footnotes and sources

Where a sheet prints a unit per row — Table 10.7’s sugar in tonnes, soft drinks in thousands of litres — it comes through as a unit column. Footnote marks printed against labels (“Coffee1,2”) are removed from the label, and what the footnote said is kept in knbs_metadata()’s notes, along with the source the sheet credits:

knbs_metadata(knbs_abstract_table("7.6"))$notes
#> ... "Coffee and tea are auction prices; coffee refers to coffee beans
#>      and tea to black tea." "Rice paddy prices for 2020 and 2021 are
#>      revised." "Source as printed: Marketing Boards, Millers and
#>      Ginneries." ...

Into the tidyverse

Every result is a tibble, so dplyr verbs work directly — and they keep the provenance: filter it, rank it, transform it, and knbs_metadata() still answers on the result. Aggregations and reshapes return plain tibbles, because a derived table is your data, not the source’s.

library(dplyr)
library(tidyr)

# The largest industries in 2024. Keeping one level of the table - the
# parts of "All industries" - leaves out the totals and the sub-industries
# that would otherwise crowd the ranking.
knbs_abstract_table("2.2") |>
  filter(period == 2024, parent == "All industries") |>
  slice_max(value, n = 5) |>
  select(industry, value, status)

# The breakdown of one industry
knbs_abstract_table("2.2") |>
  filter(period == 2024, parent == "Agriculture, forestry and fishing")

# Recreate the printed-table look: industries by year, wide
knbs_abstract_table("2.4") |>
  filter(industry %in% c("Agriculture, forestry and fishing",
                         "Manufacturing", "GDP at market prices")) |>
  select(industry, period, value) |>
  pivot_wider(names_from = period, values_from = value)

# Sectioned tables group naturally
knbs_abstract_table("2.1") |>
  filter(measure == "Gross value added at basic prices", period == 2024) |>
  arrange(desc(value))

# And straight into ggplot2
library(ggplot2)
knbs_abstract_table("2.5") |>
  filter(industry == "GDP at market prices") |>
  ggplot(aes(period, value)) +
  geom_col() +
  labs(title = "Real GDP growth", y = "per cent")

To hand the data to something that dislikes subclasses, drop the wrapper with tibble::as_tibble().

Provenance travels

meta <- knbs_metadata(gdp)
meta$source_url   # the workbook on knbs.or.ke
meta$notes        # edition, sheet, workbook SHA-256, flag legend
knbs_cite(gdp)

The workbook itself is downloaded once through the package’s verify layer — an HTML page arriving where a spreadsheet was expected is refused — cached locally, and hashed; the SHA-256 in the notes says exactly which file the numbers came from.

What this is not

Not a universal parser. Only curated tables extract; everything else in the workbooks remains available as verified downloads through knbs_download_publication().
Not a modelling of the tables’ arithmetic. Aggregate rows (“GDP at market prices”, “Total”) are rows like any other, as printed; nothing is summed, derived or reconciled for you. parent records how the printed rows nest, and the check that parts add up guards the extraction — it never adjusts a figure.
Not immune to source revisions — by design. A changed workbook stops extraction until the registry is re-verified against it. That is the honest failure mode.