I use the rio-Package to import one excel-file into my tidyverse. I only need the first two columns: The zip-code (plz) and the inhabitants (einwohner).
library(tidyverse)
library(rio)
url <- "https://dam-api.bfs.admin.ch/hub/api/dam/assets/32229177/master"
bev22 <- import(url, setclass="tibble", skip=3, sheet="2022") %>%
select(plz=1, einwohner=2)
bev20 <- import(url, setclass="tibble", skip=3, sheet="2020") %>%
select(plz=1, einwohner=2)
And so on...works well, but gets tedious very quickly.
So I tried:
bev <- import_list(url, setclass="tibble", skip=3, rbind=T) %>%
select(plz=1, einwohner=2, sheet="_file")
That works, it seems like all the sheets are imported (although without the name of the year), but everything except the first sheet is full of NAs:
bev %>%
drop_na() %>%
count(sheet)
As far as I can tell, all the sheets have the same structure. What am I doing wrong?
The issue is that you skip the first three rows. As a result the values in the row for Switzerland become the column names, i.e. the first column is named Schweiz for all sheets but the second and and all other columns are named according to the values for Switzerland, i.e. the column names differ and hence the values are put in different columns when binding by row. As a result, after binding by rows the second column contains only the values for the first sheet.
Here is an illustration of the issue where I only selected the first two columns and rows per sheet:
library(tidyverse)
library(rio)
url <- "https://dam-api.bfs.admin.ch/hub/api/dam/assets/32229177/master"
file <- tempfile(fileext = ".xlsx")
download.file(url, destfile = file)
import_list(file, skip = 3) |>
lapply(\(x) select(x, 1:2) |> head(2)) |>
bind_rows()
#> Schweiz 8962258 8815385 8738791 8670300 8606033 8544527 8484130 8419550
#> 1 1000 4332 NA NA NA NA NA NA NA
#> 2 1003 6762 NA NA NA NA NA NA NA
#> 3 1000 NA 4072 NA NA NA NA NA NA
#> 4 1003 NA 6707 NA NA NA NA NA NA
#> 5 1000 NA NA 4101 NA NA NA NA NA
#> 6 1003 NA NA 6679 NA NA NA NA NA
#> 7 1000 NA NA NA 3991 NA NA NA NA
#> 8 1003 NA NA NA 6528 NA NA NA NA
#> 9 1000 NA NA NA NA 3579 NA NA NA
#> 10 1003 NA NA NA NA 6539 NA NA NA
#> 11 1000 NA NA NA NA NA 3434 NA NA
#> 12 1003 NA NA NA NA NA 7515 NA NA
#> 13 1000 NA NA NA NA NA NA 3448 NA
#> 14 1003 NA NA NA NA NA NA 7826 NA
#> 15 1000 NA NA NA NA NA NA NA 3329
#> 16 1003 NA NA NA NA NA NA NA 10479
#> 17 1000 NA NA NA NA NA NA NA NA
#> 18 1003 NA NA NA NA NA NA NA NA
#> 19 1000 NA NA NA NA NA NA NA NA
#> 20 1003 NA NA NA NA NA NA NA NA
#> 21 1000 NA NA NA NA NA NA NA NA
#> 22 1003 NA NA NA NA NA NA NA NA
#> 23 1000 NA NA NA NA NA NA NA NA
#> 24 1003 NA NA NA NA NA NA NA NA
#> 25 1000 NA NA NA NA NA NA NA NA
#> 26 1003 NA NA NA NA NA NA NA NA
#> 27 1000 NA NA NA NA NA NA NA NA
#> 28 1003 NA NA NA NA NA NA NA NA
#> 8327126 8237666 8139631 8039060 7954662 7870134
#> 1 NA NA NA NA NA NA
#> 2 NA NA NA NA NA NA
#> 3 NA NA NA NA NA NA
#> 4 NA NA NA NA NA NA
#> 5 NA NA NA NA NA NA
#> 6 NA NA NA NA NA NA
#> 7 NA NA NA NA NA NA
#> 8 NA NA NA NA NA NA
#> 9 NA NA NA NA NA NA
#> 10 NA NA NA NA NA NA
#> 11 NA NA NA NA NA NA
#> 12 NA NA NA NA NA NA
#> 13 NA NA NA NA NA NA
#> 14 NA NA NA NA NA NA
#> 15 NA NA NA NA NA NA
#> 16 NA NA NA NA NA NA
#> 17 3072 NA NA NA NA NA
#> 18 9326 NA NA NA NA NA
#> 19 NA 2964 NA NA NA NA
#> 20 NA 8269 NA NA NA NA
#> 21 NA NA 3041 NA NA NA
#> 22 NA NA 8615 NA NA NA
#> 23 NA NA NA 2886 NA NA
#> 24 NA NA NA 6974 NA NA
#> 25 NA NA NA NA 2832 NA
#> 26 NA NA NA NA 6256 NA
#> 27 NA NA NA NA NA 2995
#> 28 NA NA NA NA NA 5685
Instead I would suggest to skip only the first two rows and get rid of the entry for Switzerland after binding by row. Additionally you can make your life a bit easier using dplyr::bind_rows which allows to add a year column automatically.
bev <- import_list(file, skip = 2) |>
dplyr::bind_rows(.id = "year") |>
select(year, plz = 2, einwohner = 3) |>
filter(!is.na(einwohner), plz != "Schweiz")
count(bev, year, !is.na(einwohner))
#> year !is.na(einwohner) n
#> 1 2010 TRUE 3186
#> 2 2011 TRUE 3190
#> 3 2012 TRUE 3190
#> 4 2013 TRUE 3189
#> 5 2014 TRUE 3189
#> 6 2015 TRUE 3185
#> 7 2016 TRUE 3183
#> 8 2017 TRUE 3184
#> 9 2018 TRUE 3182
#> 10 2019 TRUE 3182
#> 11 2020 TRUE 3182
#> 12 2021 TRUE 3183
#> 13 2022 TRUE 3180
#> 14 2023 TRUE 3180