How we created a dashboard on internet speed in Europe

What’s the average internet speed in Europe? Our new dashboard shows the extent of the digital divide between, and within, European countries – here’s how it was made and what's the key R code behind it.

Ornaldo Gjergji – OBC Transeuropa

Published On: March 25th, 2021

The dashboard on the average internet speed in Europe created by OBC Transeuropa for EDJNet is based on data provided by Speedtest by Ookla Global Fixed and Mobile Network Performance Maps, based on analysis by Ookla of Speedtest Intelligence data for the first quarter of 2022.

The data consists in a gridded spatial data frame. Every tile of the grid contains several internet speed measurements, voluntarily sent by users through the Speedtest platform.

Joining tiles with European administrative units

In order to get a sense of the state of internet speed across Europe, I joined Ookla data with the shapefiles of Europe’s Nomenclature of Territorial Units for Statistics (NUTS), and Local Administrative Units (LAU).

Copy to Clipboard

## Libraries
if (!require("pacman")) install.packages("pacman")
pacman::p_load_gh("teamokla/ooklaOpenDataR") # Source for Speedtest data
pacman::p_load_gh("giocomai/latlon2map") # Source for European geometries
pacman::p_load(tidyverse, 
               sf, 
               here, 
               lubridate)

## Geometries
lau <- ll_get_lau_eu() %>% 
  rename("id" = "GISCO_ID",  
         "name" = "LAU_NAME") %>% 
  select(id, name)

nuts_3 <- ll_get_nuts_eu(level = 3, resolution = 1) %>% 
  rename("id" = "NUTS_ID", 
         "name" = "NAME_LATN") %>% 
  select(id, name)

nuts_2 <- ll_get_nuts_eu(level = 2, resolution = 1) %>%
  rename("id" = "NUTS_ID", 
         "name" = "NAME_LATN") %>% 
  select(id, name)

nuts_0 <- ll_get_nuts_eu(level = 0, resolution = 1) %>%
  rename("id" = "NUTS_ID", 
         "name" = "NAME_LATN") %>% 
  select(id, name)

## Vectors to loop 
year <- c(2019, 2020, 2021, 2022)
quarter <- c(1, 2, 3, 4)
level <- c("lau", "nuts_3", "nuts_2", "nuts_0")

## Folders
dir.create(here("data"))
dir.create(here("data", "raw_data"))

## Turn off spherical geometries
sf_use_s2(F)

## Loop to download raw data/analyse/write outputs
for (i in year) {
  
  for (j in quarter) {

if (!file.exists(here("data", "raw_data", paste(i, j, sep = "_") %>% paste0(".rds")))) {
          
          df <- get_performance_tiles("fixed", year = i, quarter = j, sf = T) %>%
          write_rds(here("data", "raw_data", paste(i, j, sep = "_") %>% paste0(".rds"))) 
      
      for (k in level) {
        
       dir.create(here("data", k)) 
        
        st_join(get(k), df) %>% 
          st_set_geometry(NULL) %>%
          group_by(id, name) %>% 
          summarise(across(contains("avg"), weighted.mean, w = tests, na.rm = T)) %>% 
          mutate(quarter = yq(paste(i, j, sep = "_")),
                 avg_d = round(avg_d_kbps/1000, 2), 
                 avg_u = round(avg_u_kbps/1000, 2),
                 avg_l = round(avg_lat_ms, 2), 
                 .keep = "unused") %>%
          write_csv(here("data", k, paste(k, i, j, sep = "_") %>% paste0(".csv")))
        
      }

} else {

df <- read_rds(here("data", "raw_data", paste(i, j, sep = "_") %>% paste0(".rds")))
      
      for (k in level) {
        
        dir.create(here("data", k)) 
        
        st_join(get(k), df) %>%
          st_set_geometry(NULL) %>%
          group_by(id) %>% 
          summarise(across(contains("avg"), weighted.mean, w = tests, na.rm = T)) %>% 
          mutate(quarter = yq(paste(i, j, sep = "_")),
                 avg_d = round(avg_d_kbps/1000, 2),
                 avg_u = round(avg_u_kbps/1000, 2),
                 avg_l = round(avg_lat_ms, 2), 
                 .keep = "unused") %>%
          write_csv(here("data", k, paste(k, i, j, sep = "_") %>% paste0(".csv")))
        
      }
      
    }
    
  }
  
}

## Timeseries
dir.create(here("data", "timeseries"))

for (k in level) {
  
  list.files(here("data", k)) %>%
    map_df(~read_csv(here("data", k, .x))) %>% 
    arrange(id, quarter) %>% 
    write_csv(here("data", "timeseries", paste("timeseries_", ".csv", sep = k)))
  
}

The values of download speed, upload speed, and latency for each administrative unit were obtained by calculating the average of all the observations within the administrative unit’s boundaries, weighted by the number of tests. Average download and upload speed values are expressed in megabit per second (Mbps), not to be mistaken with megabyte per second (MBps). Average latency values are expressed in milliseconds (ms).

Matching municipalities (LAU) with regions (NUTS 2)

To match European municipalities with higher administrative units I relied on the systematization of Eurostat correspondence tables made here by EDJNet. In this way I could assign European cities to their respective sub-national level administrative units and compare the differences among European regions.

Copy to Clipboard

## Libraries
if (!require("pacman")) install.packages("pacman")
pacman::p_load(tidyverse, 
               sf)

## We need to match the data at the city level with their respective regions and countries
## The dataset obtained by this matching will be used for the maps and the density plots
matcher <- read_csv("https://raw.githubusercontent.com/EDJNet/lau_centres/main/lau_nuts_concordance_by_geo/lau_2020_nuts_2021_concordance_by_geo.csv") %>%
  select(gisco_id, nuts_2) %>% 
  left_join(nuts_2 %>% 
              st_set_geometry(NULL) %>% 
              rename("nuts_2" = "id",
                     "region" = "name"))

lau_matched <- read_csv(here("data", "lau", "lau_2022_1.csv")) %>% 
  mutate(country_id = substr(id, 1, 2)) %>% 
  drop_na(avg_d) %>% #some small cities do not have data and their internet speed is NA
  left_join(matcher, by = c("id" = "gisco_id")) %>% 
  left_join(lau) %>% 
  left_join(nuts_0 %>% 
              st_set_geometry(NULL) %>% 
              rename("country_id" = "id", "Country" = "name")) %>% 
  mutate(City = name,
         Country = str_replace_all(Country, "/", "-"),
         region = str_replace_all(region, "/", "-"),
         "Average Download Speed" = avg_d %>% paste0(" Mbps"),
         "Average Upload Speed" = avg_u %>% paste0(" Mbps"),
         "Average Latency" = avg_l %>% paste0(" ms")) %>% 
  st_set_geometry(.$geometry)