solutions

Exercises 1

  1. Download the file from this address into the “data” folder and name it “internet_usage.xlsx” using R: https://api.worldbank.org/v2/en/indicator/IT.NET.USER.ZS?downloadformat=excel
f_internet_usage <- "data/internet_usage.xlsx"
curl::curl_download(
  url = "https://api.worldbank.org/v2/en/indicator/IT.NET.USER.ZS?downloadformat=excel",
  destfile = f_internet_usage
)
  1. Reproduce this plot (hint: you can get grey out cases with gghighlight):

library(tidyverse)
── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors
library(gghighlight)
internet_usage <- readxl::read_xls(f_internet_usage, sheet = "Data", skip = 3)
internet_usage |>
  pivot_longer(
    cols = matches("\\d+"),
    names_to = "year",
    values_to = "internet_usage"
  ) |>
  filter(!is.na(internet_usage)) |>
  mutate(year = as.integer(year)) |>
  ggplot(aes(x = year, y = internet_usage, colour = `Country Name`)) +
  geom_line() +
  gghighlight(
    `Country Name` %in%
      c("United Kingdom", "United States", "Germany", "Qatar"),
    use_group_by = FALSE
  ) +
  labs(
    x = NULL,
    y = NULL,
    title = "Individuals using the Internet (% of population)"
  )
label_key: `Country Name`

ggsave("media/individuals-using-the-internet.png", width = 9, height = 6)
  1. I created a terrible CSV file. Try to read it in anyway: “data/csv-bad-example.csv”
bad_csv <- rio::import("data/csv-bad-example.csv", skip = 3, fill = TRUE)
  1. Even when you manage to read in the file, the table still has issue. Explain what they are and how to correct them (hint: look at the data types of the columns)
  • line 268 contains only 5 values instead of 6
  • lines 218 and 219 are missing
  • line 204 contains the headers again

Exercises 3

  1. When might it make sense to treat a folder full of files as a database?
  • when you have files which all have the same data structure
  1. When could it make sense to save data in an arrow database full of csv files?
  • when a real database would be too much hassle
  • when you want to add new data repeatedly (e.g., when you run a web scraping script every day)
  1. arrow also supports a file format called Parquet. Using it instead of CSV files increases speed and reduces file sizes, while it is also widely used by other languages and programs. Create a new folder where you store the Houses of Commons data in one file per speaker (hint read ?arrow::write_parquet first).

You can solve this exercise by changing the code that wrote the csv files:

if (!dir.exists("data/house_of_commons")) {
  # I split this into file just for demonstration puposes
  corp_hoc_list <- split(corp_hoc_df, corp_hoc_df$party)
  dir.create("data/house_of_commons")
  for (party in names(corp_hoc_list)) {
    rio::export(
      corp_hoc_list[[party]],
      paste0("data/house_of_commons/", party, ".csv")
    )
  }
}
list.files("data/house_of_commons")

First, we need to change the code that split up the data per party, into data per speaker:

corp_hoc <- "data/Corp_HouseOfCommons_V2.rds"
corp_hoc_df <- readRDS(corp_hoc) |>
  mutate(date = as.Date(date))
corp_hoc_list <- split(corp_hoc_df, corp_hoc_df$speaker)

Now we can create a new folder to store the new files:

dir.create("data/house_of_commons_parquet")
Warning in dir.create("data/house_of_commons_parquet"):
'data/house_of_commons_parquet' already exists

Next, we have to loop over each speaker and export them to their own file. We do this in the same way as before, but use write_parquet instead. Reading the help file, I find this example:

tf1 <- tempfile(fileext = ".parquet")
write_parquet(data.frame(x = 1:5), tf1)

It looks like write_parquet works in the same way as rio::export! So all we need to change is the function name and the file extension:

for (speaker in names(corp_hoc_list)) {
  arrow::write_parquet(
    corp_hoc_list[[speaker]],
    paste0("data/house_of_commons_parquet/", speaker, ".parquet")
  )
}
  1. Connect to the “database” of Parquet files and make a plot showing how many entries there are over the years per party
library(arrow)
Some features are not enabled in this build of Arrow. Run `arrow_info()` for more information.

Attaching package: 'arrow'
The following object is masked from 'package:lubridate':

    duration
The following object is masked from 'package:utils':

    timestamp
corp_hoc_con <- open_dataset(
  sources = "data/house_of_commons_parquet",
  format = "parquet"
)
corp_hoc_con |>
  count(party, year = lubridate::year(date)) |>
  collect() |>
  ggplot(aes(x = year, y = n, colour = party)) +
  geom_line()