Working with External IDs and OpenRefine reconciliation

One of the most important properties when working with a content partnership is the external id associated with the partner institution. All properties in Wikidata have a specific data type, which specifies what kind of data is stored in the property. Some available data types are item (a QID), quantity (a number with or without a unit), string (a text), monolingual text (a text with language), and URL (usually web pages). 

One of these data types is the external identifier.

Under the hood, it is just a string (a text). However, it has some special characteristics that string properties do not have. Statements with external identifiers are separated in the item page from the regular statements in the section “Identifiers”. They can have a formatter URL property (P1630) that defines the URL that displays or describes the item in the external database.

These properties are essential for reconciliation between the data in the partner’s database and in Wikidata because they provide an easy, stable, usually unique link between them. We can easily query in Wikidata a table that matches the QID with the external ID, like below:

SELECT ?item ?id WHERE {
  ?item wdt:P10701 ?id.
}

This specific property is Reflora ID (P10701), the external identifier of the Reflora database, from our partner Museu Paraense Emílio Goeldi at Wikimedia Brasil. It will return a simple table with QID <-> Reflora ID.

We can also prepare maintenance queries that should return zero. Considering that the match between QID and the external ID is unique, there should be no item with two external IDs and no external ID occurring in two items:

SELECT ?item (GROUP_CONCAT(?extId; separator="|") AS ?idJoined)
WHERE {
  ?item wdt:P10701 ?extId.
}
GROUP BY ?item
HAVING (COUNT(?extId) >= 2)

This query groups by item and filters a count of 2 or more external ids. The external ids are shown together in the same line, joined by a pipe. They can be split in spreadsheets if desired.

The other query will be the same but reverting the item and external id:

SELECT ?extId (GROUP_CONCAT(?item; separator="|") AS ?itemJoined)
WHERE {
  ?item wdt:P10701 ?extId.
}
GROUP BY ?extId
HAVING (COUNT(?item) >= 2)

Data coming from the partner’s database will usually use this external ID, or its URL, as the reference for statements in Wikidata, usually together with the retrieved property (P813). For example, in the item Q140271191, the reference was created like this in QuickStatements 3:

… | S248 | Q111699614 | S10701 | "FB7392" | S813 | 2026-06-17T00:00:00Z/11

Using stated in (P248) for the Reflora Database (Q111699614) and the external identifier of the specific item in the middle (P10701). Because this is a reference command in QuickStatements, P’s are switched for S’s, meaning Source.

Now, to add or modify data, we need to have a good reconciliation first and preferably have our maintenance queries returning zero. If new items are added to the Reflora database, before creating them in Wikidata, even if the external identifier is not there, we should check if the item already exists; if it does, we can only append the external identifier instead of creating a duplicate.

There are multiple ways to do that, but one of the most common ones is using the reconciliation for Wikidata available in OpenRefine. The reconciliation method works incredibly well, especially for messy data, since it can match things even if they have small mistakes or distinctions between them. When reconciling, you can also define a column in your dataset to match a specific property for a statement. Now, your reconciliation not only depends on labels and of which “instance of (P31)” is the item, but also on statements as well.

However, when you need to match things exactly, in contexts where one or two letters are different, like Ficus gomelleira and Ficus gameleira, the reconciliation can match incorrectly. The column-property adjustment is also not strict, so it can match items that do not have the statement as well, although having a smaller score.

When working with taxonomy and scientific nomenclature, we usually want to exactly match scientific names and the parent taxon as well. In order to best work with the reconciliation, we can do the following: reconcile normally, knowing that maybe we can have false positives. Then, using the OpenRefine feature of extracting statements from reconciled items, create columns with the values that we wanted to match in the first place. With this, we can use the powerful spreadsheet features of OpenRefine to better filter and refine the reconciliation we did.

The important lesson here is that the reconciliation in OpenRefine does not end when you click “reconcile”, but when you decide to end it. You can always filter and adapt the reconciliation result to better adapt it to your needs.