loading . . . Suggesting new relations in ROR from Wikidata I was looking at the different NFDI consortia in the Research Organization Registry (ROR), and found that the only two that have a parent relations to the NFDI (`ror:05qj6w324`) are NFDI4DS (`ror:00bb4nn95`) and MaRDI (`ror:04ncnzm65`). This felt strange to me, so I started looking around Wikidata to see if I could automatically make a curation sheet to send along to them. I found that Wikidata already has detailed pages for all NFDI consortia, and that they also include relationships to the parent. This blog post is about the steps I took to write a workflow to find relationships in Wikidata that are appropriate for submission to ROR.
## Getting Wikidata
In Wikidata, an entity can be annotated with a ROR identifier via property `P6782`. I wanted to write a SPARQL query for the Wikidata Query Service to retrieve all triples for which both the subject and object have and ROR identifier.
SELECT ?subject ?subjectROR ?subjectLabel ?predicate ?object ?objectROR ?objectLabel
{
?subject ?predicate ?object ;
wdt:P6782 ?subjectROR .
?object wdt:P6782 ?objectROR .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],mul,en". }
}
While I now know this query should return about 67K rows, at the time, I ran into the issue that it was too complicated and caused the Wikidata Query Service to timeout. The next step in any investigation with a blasphemous `?subject ?predicate ?object` pattern is to look into the predicates and try to cut them down. I set to reformulating the query to count the frequency of appearance of each predicate.
SELECT DISTINCT ?p ?pLabel (COUNT(?p) as ?count)
{
?subject wdt:P6782 ?subjectROR;
?predicate ?object .
?object wdt:P6782 ?objectROR .
?p wikibase:directClaim ?predicate .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],mul,en". }
}
GROUP BY ?p ?pLabel
ORDER BY DESC(?count)
This query uses the sneaky `wikibase:directClaim` to map between the `wd:` entity namespace and `wdt:` direct property namespace so the query service could look up the label for the link. The problem was, this query was still too heavy and caused a timeout. Therefore, I had to simplify the query to just get the counts without the label, then use a second query and join the data externally (I also tried a nested query along the way, but it still timed out).
SELECT DISTINCT ?predicate (COUNT(?predicate) as ?count)
{
?subject wdt:P6782 ?subjectROR ;
?predicate ?object .
?object wdt:P6782 ?objectROR .
}
GROUP BY ?predicate
ORDER BY DESC(?count)
With that out of the way, I tried re-writing the original query by formatting in the 147 predicates I pulled out into the `VALUES ?predicate { ... }` (abbreviated), like:
SELECT ?subject ?subjectROR ?subjectLabel ?predicate ?object ?objectROR ?objectLabel
{
VALUES ?predicate { ... }
?subject ?predicate ?object ;
wdt:P6782 ?subjectROR .
?object wdt:P6782 ?objectROR .
SERVICE wikibase:label { bd:serviceParam wikibase:language "[AUTO_LANGUAGE],mul,en". }
}
This still caused timeouts, so I resorted to a loop in Python, which also let me simplify the query to skip the Wikidata IDs and just pull out RORs for the subject and object (where the `{...}` gets replaced with a different property on each):
SELECT ?subjectROR ?objectROR
WHERE {
?subjectROR ^wdt:P6782/wdt:{...}/wdt:P6782 ?objectROR .
}
I really like this because it uses paths to reduce the need to specify the middle entities which donāt get used. I donāt know if the SPARQL engine is able to optimize on it, but itās cool. Maybe not so readable, but cool. The loop created a super-sized TSV with the predicate and labels added back.
The workflow I implemented for this lives in https://github.com/cthoyt/ror-wikidata-enrichment. The data from Wikidata is in this file, licensed under CC0.
Do you want this workflow to better reflect your organization? Check out my other blog post on how to curate data about your research organization: https://cthoyt.com/2021/01/17/organization-organization.html.
## Getting ROR
Iāve previously implemented a source in PyOBO that wraps downloading and structuring RORās data dump into a readily usable format, so getting RORās triples was as easy as:
import pyobo
df = pyobo.get_relations_df("ror")
I also had to map the part of and has part relations from BFO to Wikidata properties. I did this by hand because it was faster than doing it the sustainable way, which would have been to pull the mappings from SSSOM-like annotations in the BFO ontology or from Wikidata itself (since I curated those into Wikidata years ago when we were preparing the (unpublished) relation ontology paper).
I made an intermediate output of all of thet triples here, licensed under CC0.
## Putting it all together
While Iām glossing over a few steps that you can grok by reading my python script, it was possible to finish getting the data in the right shape to compare with tools in PyOBO and the Bioregistry
The final step was to take the difference between the Wikidata triples and the ROR triples, filter for triples that make sense within the ROR schema (which for now is just part of and has part relationships), and then dump the results out. There were around 67K records before filtering around 2.8K after filtering. Here are a few examples:
subjectROR | subjectLabel | predicate | predicateLabel | objectROR | objectLabel
---|---|---|---|---|---
00k4nrj32 | Essex County Hospital | P361 | part of | 02wnqcb97 | National Health Service
022efad20 | University of GabĆØs | P527 | has part(s) | 01hwc7828 | Institut des RĆ©gions Arides
04p4gjp18 | Center of Excellence on Hazardous Substance Management | P361 | part of | 028wp3y58 | Chulalongkorn University
04tnv7w23 | Ćcole SupĆ©rieure Polytechnique dāAntsiranana | P361 | part of | 00pd4qq98 | UniversitĆ© dāAntsiranana
02f4ya153 | Barro Colorado Island | P361 | part of | 01pp8nd67 | Smithsonian Institution
## Coda
The point of all of this was to automate adding the missing NFDI consortia relationships to the parent NFDI organization in ROR, because Iām interested in creating queries over the organization landscape related to NFDI to support an upcoming section on Internationalization. And like most things in my work life, I ended up cleaning some data and making upstream contributions along the way. Letās see how receptive ROR is to this! The triples are all here and I can easily make them a different format for submission.
* * *
Caveat: if you look into the data, you might notice that some of the entities donāt have labels. I realized this is happening because I havenāt updated my PyOBO importer to get the 2.0 data dump from ROR, and Iām stuck on old version 1.36. This can be fixed independently of this workflow. Hereās the rows related to the NFDI consortia that need new relations, which are all missing labels until I fix this.
subjectROR | subjectLabel | predicate | predicateLabel | objectROR | objectLabel
---|---|---|---|---|---
00enhv193 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
02cxb1m07 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
03xrvbe74 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
020tty630 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
04ncnzm65 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
01f5dqg10 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
001jhv750 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
0310v3480 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
01d2qgg03 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
01k9z4a50 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
03a4sp974 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
05wwzbv21 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
0305k8y39 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
0238fds33 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
03f6sdf65 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
0033j3009 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
01vnkaz16 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
01v7r4v08 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
04dy2xw62 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
01xptp363 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
034pbpe12 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
05nfk7108 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
00r0qs524 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
00bb4nn95 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur
03fqpzb44 | | P361 | part of | 05qj6w324 | Nationale Forschungsdateninfrastruktur https://cthoyt.com/2025/09/25/enriching-ror-with-wikidata.html
