Linking Entities to Wikidata Database

Learning Objectives

The tutorial at hand provides a pipeline to extract and annotate geographic locations from any source of text. By combining named entity recognition with automatically querying the Wikidata API it enables computational social scientists to smoothly analyse geographic references.

By the end of this tutorial, you will be able to:

1. Know the basics of Wikidata and how to query it
2. Understand how to link entities, in particular places, to their unique identifier (ID) in the Wikidata database
3. Be able to implement steps to increase the quality of the linkage process

Target audience

This tutorial is aimed at beginners with some knowledge in R and basic understandings of API queries (no worries, we will do it together 😉).

Setting up the computational environment

The following R packages are required:

require(tidyverse)
require(WikidataQueryServiceR)
require(DT)
require(jsonlite)

Duration

Around 20 min

Social Science Usecase(s)

This method has been used in previous studies to evaluate how the legislatory system affects geographical representation (Birkenmaier et al., 2024).

Introduction

This tutorial guides computational social scientists by linking geographic entities mentioned in texts, specifically places, to their unique identifiers in Wikidata using R.

library(WikidataQueryServiceR)

# Define the SPARQL query
query <- "
SELECT ?countryLabel ?population
WHERE {
  ?country wdt:P31 wd:Q6256. # Instance of country
  ?country wdt:P1082 ?population. # Population property
  SERVICE wikibase:label { bd:serviceParam wikibase:language 'en'. }
}
ORDER BY DESC(?population)
LIMIT 10
"

# Run the query
results <- query_wikidata(query)

# View the data
head(results)

# A tibble: 6 × 2
  countryLabel               population
  <chr>                           <dbl>
1 People's Republic of China 1442965000
2 India                      1326093247
3 United States               340110988
4 Indonesia                   275439000
5 Pakistan                    223773700
6 Brazil                      213421037

Link Entities to Their Unique Identifier in Wikidata

library(tibble)
library(dplyr)
library(DT)

# Sample dataset
library(tibble)


#We manually create the toy dataset
df_full <- tibble(
  text = c(
    "I recently visited Augsburg, and I must say, it's truly a city that values community spirit.",
    "Berlin is not just our capital; it stands as a testament to resilience and progress.",
    "You know, I’ve spoken to people across the country, and one thing is clear: they all want change.",
    "When I was in Nordrhein-Westfalen last week, I saw firsthand the challenges in our infrastructure.",
    "I’ve heard concerns from folks in Zappendorf, and we need to address their needs urgently.",
    "I grew up visiting Munich and Berlin often, and both cities show us what true innovation looks like.",
    "Just last month in Hamburg, I joined a discussion on how we tackle climate change together.",
    "I’ve talked to families across the nation, and their stories inspire me to work harder every day.",
    "In Frankfurt, I met with local leaders who are driving our economy forward in remarkable ways.",
    "When I think of Dresden and Leipzig, I think of their rich history and the vibrant future they’re building.",
    "I had the honor of addressing Parliament in London; it’s always inspiring to see democracy in action.",
    "Brussels and Paris remind us that international cooperation is the cornerstone of our success.",
    "We’ve spoken about many issues, but let me tell you what I heard from people in small towns like yours.",
    "Cologne has always been a city of hope and opportunity, and it’s time we build on that legacy.",
    "In Stuttgart last week, I saw how advancements in green energy are transforming communities.",
    "Düsseldorf and Bonn are examples of what happens when we invest in collaboration and innovation.",
    "I’ll never forget what someone from my hometown said: 'If you believe in us, we’ll deliver.'",
    "Vienna is not just a cultural hub; it’s where some of the most important global ideas take shape.",
    "Zurich and Geneva embody the spirit of international unity and progress that we need today.",
    "When I walked the streets of Amsterdam, I saw the perfect blend of history and modern vision."
  ),
  locations = c(
    "Augsburg",
    "Berlin",
    "",
    "Nordrhein-Westfalen",
    "Zappendorf",
    "Munich, Berlin",
    "Hamburg",
    "",
    "Frankfurt",
    "Dresden, Leipzig",
    "London",
    "Brussels, Paris",
    "",
    "Cologne",
    "Stuttgart",
    "Düsseldorf, Bonn",
    "",
    "Vienna",
    "Zurich, Geneva",
    "Amsterdam"
  )
)

datatable(
  df_full,
  options = list(pageLength = 7, scrollX = TRUE)
)

Data Linkage of Places

We start by processing the dataset (df_full) to extract and clean location names. The separate_rows() function splits multiple locations listed in one cell into separate rows, while additional transformations remove trailing dots and empty rows. The result is a clean list of location names ready for further processing. From the cleaned dataset (df_temp), we extract a unique list of location names using the unique() function. This ensures that only distinct location names are processed when creating the ID dataset. The list of search terms serves as the input for querying Wikidata.

# Extract and clean location names
df_temp <- df_full |> 
  separate_rows(locations, sep = ",\\s*") %>%
  mutate(locations = sub("\\.+$", "", locations)) %>%
  filter(locations != "")  # Remove empty rows

# Extract unique search terms for creating the ID dataset
search_terms <- unique(df_temp$locations)
search_terms

 [1] "Augsburg"            "Berlin"              "Nordrhein-Westfalen"
 [4] "Zappendorf"          "Munich"              "Hamburg"            
 [7] "Frankfurt"           "Dresden"             "Leipzig"            
[10] "London"              "Brussels"            "Paris"              
[13] "Cologne"             "Stuttgart"           "Düsseldorf"         
[16] "Bonn"                "Vienna"              "Zurich"             
[19] "Geneva"              "Amsterdam"          

We initialize an empty data frame (id_data) to store the mapping between search terms and their corresponding Wikidata IDs (QIDs). This dataset will be populated iteratively as we query Wikidata for each location name.

id_data <- data.frame('search_term' = character(), 
                      'QID' = character(), stringsAsFactors = FALSE)

The get_wikidata_id() function is designed to query Wikidata and fetch up to three relevant QIDs for each search term. At its core, it filters results to only geogprahic entities (e.g., locations with coordinates) and excludes irrelevant entities such as disambiguation pages (for a full explanation, see the callout box below). The returned QIDs are then concatenated into a comma-separated string.

# Function to fetch IDs from Wikidata and return as a comma-separated list
get_wikidata_id <- function(search_term) {
  query <- sprintf("
SELECT ?item (COUNT(?sitelink) AS ?sites) WHERE {
  SERVICE wikibase:mwapi {
    bd:serviceParam wikibase:api 'EntitySearch' .
    bd:serviceParam wikibase:endpoint 'www.wikidata.org' .
    bd:serviceParam mwapi:search '%s' .
    bd:serviceParam mwapi:language 'de' .
    ?item wikibase:apiOutputItem mwapi:item.
  }
  ?item wdt:P625 ?coordinateLocation. # Ensure it has geographic coordinates
  ?sitelink schema:about ?item.
  MINUS {?item wdt:P31 wd:Q4167410.} # Exclude disambiguation pages
} GROUP BY ?item ORDER BY DESC(?sites) LIMIT 3", search_term)
  
  result <- query_wikidata(query)
  
  if (nrow(result) > 0) {
    # Extract the last part of each URL and concatenate into a comma-separated list
    qid_list <- paste(sub(".*/", "", result$item), collapse = ", ")
    return(qid_list)
  } else {
    return(NA)
  }
}

In the matching step, we then query each search term, extract the IDs, and updates the id_data dataset with the corresponding QIDs. The tryCatch function ensures the process continues even if an error occurs for a specific term. Additionally, a Sys.sleep pause is included between queries to prevent overloading the Wikidata API, making the process robust and efficient for large datasets.

#Fetch IDs for the first term
for (term in search_terms[1:3]) {
  print(paste("Processing:", term))
  tryCatch({
    qid <- get_wikidata_id(term)
    id_data <- rbind(id_data, data.frame('search_term' = term, 'QID' = qid, stringsAsFactors = FALSE))
  }, error = function(e) {
    message("Error processing term: ", term)
  })
  Sys.sleep(1)  # Pause to prevent API rate limiting
}

[1] "Processing: Augsburg"

[1] "Processing: Berlin"

[1] "Processing: Nordrhein-Westfalen"

id_data

          search_term                   QID
1            Augsburg Q2749, Q10415, Q10414
2              Berlin  Q64, Q152087, Q56036
3 Nordrhein-Westfalen        Q1198, Q320642

Thus, we get a list of potential matches (ranked by the number of sitelinks). Lets inspect the output for our first input: “Augsburg”

# Add a column to identify valid instances
fetch_wikidata_properties <- function(q_ids) {
  entity_url <- 'https://www.wikidata.org/w/api.php'
  
  # Initialize a dataframe to store results
  properties_data <- tibble(q_id = character(), properties = character())
  
  for (q_id in q_ids) {
    print(paste("Processing:", q_id))
    
    params <- list(
      action = 'wbgetentities',
      format = 'json',
      ids = q_id,
      language = 'de'
    )
    
    response <- httr::GET(entity_url, query = params)
    
    if (response$status_code == 200) {
      data <- fromJSON(rawToChar(response$content))
      
      # Extract all instances (P31) as a comma-separated string
      instances <- data$entities[[q_id]]$claims$P31$mainsnak$datavalue$value$id
      instances_list <- paste(instances, collapse = ", ")
      
      # Add to the results dataframe
      properties_data <- bind_rows(
        properties_data,
        tibble(q_id = q_id, properties = instances_list)
      )
    } else {
      print(paste("Failed to fetch data for", q_id, ". Status code:", response$status_code))
    }
  }
  
  return(properties_data)
}

q_ids <- id_data |> 
  separate_rows(QID, sep = ",\\s*") |>  # Separate multiple QIDs into individual rows
  pull(QID)  # Extract the QID column as a vector for processing

# Fetch properties for the QIDs
properties_data <- bind_rows(lapply(q_ids, fetch_wikidata_properties))  # Apply the function to each QID and combine results

[1] "Processing: Q2749"
[1] "Processing: Q10415"
[1] "Processing: Q10414"
[1] "Processing: Q64"
[1] "Processing: Q152087"
[1] "Processing: Q56036"
[1] "Processing: Q1198"
[1] "Processing: Q320642"

# View the resulting dataframe
print(properties_data)

# A tibble: 8 × 2
  q_id    properties                                                            
  <chr>   <chr>                                                                 
1 Q2749   Q1549591, Q1187811, Q1547289, Q253030, Q42744322, Q19953632, Q8563189…
2 Q10415  Q85482556                                                             
3 Q10414  Q85482556                                                             
4 Q64     Q1901835, Q200250, Q1307779, Q15974307, Q42744322, Q133442, Q11440198…
5 Q152087 Q1767829, Q62078547, Q45400320, Q115427560                            
6 Q56036  Q171441, Q15239622                                                    
7 Q1198   Q1221156                                                              
8 Q320642 Q414147                                                               

# Define properties to exclude
exclude_properties <- c("Q85482556")  # Example: Remove rural district of Bavaria

# Merge id_data with properties_data
merged_data <- id_data |> 
  separate_rows(QID, sep = ",\\s*") |>  # Expand QIDs
  left_join(properties_data, by = c("QID" = "q_id"))  # Merge with properties_data

# Filter out items containing excluded properties
filtered_data <- merged_data |> 
  rowwise() |> 
  mutate(
    # Check if any of the excluded properties exist in the `properties` column
    has_excluded_properties = any(strsplit(properties, ",\\s*")[[1]] %in% exclude_properties)
  ) |> 
  filter(!has_excluded_properties) |>  # Remove rows with excluded properties
  ungroup()

# Pick the first QID for each search term
final_data <- filtered_data |> 
  group_by(search_term) |> 
 slice(1) |>  # Select the first row for each search term
  ungroup()
# View the final result
print(final_data)

# A tibble: 3 × 4
  search_term         QID   properties                    has_excluded_propert…¹
  <chr>               <chr> <chr>                         <lgl>                 
1 Augsburg            Q2749 Q1549591, Q1187811, Q1547289… FALSE                 
2 Berlin              Q64   Q1901835, Q200250, Q1307779,… FALSE                 
3 Nordrhein-Westfalen Q1198 Q1221156                      FALSE                 
# ℹ abbreviated name: ¹​has_excluded_properties