# Issue augmenting Wikidata QIDs in OR **URL:** \ **Category:** Support and Helpdesk\ **Created:** [January 4, 2023, 10:23am UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303 "2023-01-04T10:23:09Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![MSH](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@MSH](https://forum.openrefine.org/u/MSH)\ **Post date:** [January 4, 2023, 10:23am UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/1 "2023-01-04T10:23:09Z")
Hi all - so nice to find the OR Discourse 🙂, & helpful to look through some of the existing issues on this topic - I have read around the issue I’m having & initially wondered whether it could be a proxy issue as described [here](https://forum.openrefine.org/t/error-in-wikidata-reconciliation/241/11). I am having issues using the reconciliation feature to pull down some extra data on a column of Wikidata QIDs. The eventual workflow will be to pull down each QID’s equivalent VIAF ID, and then to run another round of reconciliation to pull properly formed corporate names from VIAF. When I try reconciling the column of QIDs, either by _Reconcile_ → _Use values as identifiers_, or by _Edit column_ → _Add columns from reconciled values_, I only get the opportunity to match my values to Qid values, with no other Wikidata metadata listed: ![image](https://europe1.discourse-cdn.com/flex017/uploads/openrefine/original/1X/887a28902f5ddef9d07377f7838ca24e5643bc65.png) The reconciliation of my QIDs works fine but I’m struggling to pull down the extra fields I’m looking for; any help much appreciated, I’m not convinced it’s a proxy issue (as I can successfully reconcile QIDs into their matching values), but can’t augment or pull up a list of associated values like VIAF IDs. Thanks for your help & hoping everyone well here, MSH ---
**Author:** ![ostephens](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ostephens/32/8_2.png) [@ostephens](https://forum.openrefine.org/u/ostephens)\ **Post date:** [January 4, 2023, 10:41am UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/2 "2023-01-04T10:41:43Z")
Hi and welcome @MSH Usually at the screen that you are displaying in the screenshot I’d expect to type in a property name in the “Add Property” box and for that to show available properties - what happens if you type into that box. I’ve included some screenshots from an example ![Screenshot 2023-01-04 at 10.39.09](https://europe1.discourse-cdn.com/flex017/uploads/openrefine/original/1X/85f7c5c5dc4c85a09f37d15d3ab21a50bc40ef43.png) ![Screenshot 2023-01-04 at 10.39.21](https://europe1.discourse-cdn.com/flex017/uploads/openrefine/original/1X/49e9fd27dd15c6c2f668caa8c5db4bf0ce00be7f.png) If this isn’t working please give some more details about the project (if possible sharing the whole project here is great, but if not some example data and screenshots of the actual project would be helpful) and which version of OpenRefine you are using - the more information you can share the easier it is for people to help. Thanks and best wishes Owen ---
**Author:** ![ostephens](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ostephens/32/8_2.png) [@ostephens](https://forum.openrefine.org/u/ostephens)\ **Post date:** [January 4, 2023, 10:48am UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/3 "2023-01-04T10:48:59Z")
Just to be clear - the process for adding data from Wikidata to your project would be: 1. reconcile an existing column in your project to Wikidata entities 2. use “Add columns from reconciled values” to pull additional data down from Wikidata entities You have to have first reconciled something in your existing project to do the second step. You mention the _Reconcile_ → _Use values as identifiers_ function and I just want to clarify that this is essentially a shortcut where you already have the IDs from a service that supports reconciliation (e.g. a list of Wikidata IDs) and you just want to skip the full reconciliation process and assert that the identifiers you have should be treated as already reconciled (this is a very useful shortcut but it does skip any quality assurance on the process - even if the IDs don’t exist on the target service they will be marked as reconciled with this approach). ---
**Author:** ![MSH](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@MSH](https://forum.openrefine.org/u/MSH)\ **Post date:** [January 4, 2023, 1:05pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/4 "2023-01-04T13:05:37Z")
Hey Owen - thank you so much for this !, especially for the prompt reply while we are all getting back up to speed - hope the New Year OK so far. In response to the first reply: many thanks, this is exactly the issue I was having, & as soon as I put in VIAF as a prompt I could map the IDs; apologies, I’d seen a few screenshots that made me think I’d get an index of columns straight away but this was an easy fix. Re: the second post, I’m making sure to reconcile in advance but find the _Use values as identifiers_ shortcut very helpful, particularly given the process of reconciling against no particular type given there is some variety in the QIDs. As it stands I’ve followed the correct sequence in your post & successfully generated VIAF IDs where they exist, and have a new column to reconcile & augment with the names I’m looking for. At the moment, when trying to pull down data from these reconciled values I run into the “This reconciliation service does not support data extension…” error; I have updated the VIAF service URL to **[https://refine.codefork.com/reconcile/viaf](https://refine.codefork.com/reconcile/viaf)**, per codefork, & have also tried using the LC proxy service with similar results: ![image](https://europe1.discourse-cdn.com/flex017/uploads/openrefine/original/1X/a146c3a5224192205c472cfd3ade8ea62072be16.png) At a glance these reconciled values don’t seem as firm as the Wikidata QIDs - I still have some options to confirm the matches - but nonetheless it doesn’t seem like I can continue to query the VIAF server. Happy to upload a sample of the dataset if easier to look this over, & FWIW all of this is happening in 3.6.2 having just updated. Thank you again for helping me over the reconciliation hurdles, & no worries if no obvious path forward beyond pulling down the VIAF #s. ---
**Author:** ![ostephens](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ostephens/32/8_2.png) [@ostephens](https://forum.openrefine.org/u/ostephens)\ **Post date:** [January 5, 2023, 10:12am UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/5 "2023-01-05T10:12:36Z")
Hi @MSH Yes - unfortunately I’m not aware of any VIAF reconciliation service that supports the “data extension” part of the reconciliation API - which is the bit that OpenRefine uses to do the “Add columns from reconciled values” step. While it doesn’t cover every reconciliation service running in the world, the ‘testbench’ at [Reconciliation service test bench](https://reconciliation-api.github.io/testbench/#/) will show you which parts of the API is supported by various services - the “Extend data” (last column) is the one required for the “Add columns from reconciled values” function. However - all is not lost 🙂 You can still add data from VIAF to your project, it just takes a couple more steps and a little more effort. The approach you can take is “Add column by fetching URLs” to fetch data from VIAF (there are various different formats supported by VIAF for this), and then extract in the information you need from the response you get (which will depend on which URL/format you request from VIAF). As an example - you can use the following steps: 1. From a column containing a VIAF ID, use “Add column by fetching URLS” 2. In the expression box use GREL `"https://viaf.org/viaf/"+value+"/justlinks.json"` 3. This should populate a new column with some coded content using the JSON format - which will look a bit like: ```auto "BIBSYS":["90251896"], "BNF":["http://catalogue.bnf.fr/ark:/12148/cb12265567w"], "CAOONL":["ncf11115758"], "DNB":["http://d-nb.info/gnd/6128261-3"], "ISNI":["0000000404235368"], "J9U":["987007257563005171"], "LC":["n79009603"], "NII":["DA06530721"], "NKC":["xx0131303"], "NLA":["000035721919"], "Identities":["https://www.worldcat.org/identities/lccn-n79009603"]} ``` 1. On your new column which contains the JSON, do “Add column based on this column” 2. In the expression box use the GREL `value.parseJson().LC.join("|")` The outcome would be a new column with the LC NAF ID (in this case) in it Depending on which information you want from VIAF you may need to request a different URL (for example using the suffix `/marc21.xml` instead of `/justlinks.json` will get you the full VIAF record in MARCXML) and then the extraction of the information from the data would be different (I’ve used probably the simplest example above). Please feel free to post more about exactly which information you need from VIAF and I can provide some guidance on which format might be best and how to extract the right data from the response (which is not always straightforward) Owen ---
**Author:** ![MSH](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@MSH](https://forum.openrefine.org/u/MSH)\ **Post date:** [January 5, 2023, 4:37pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/6 "2023-01-05T16:37:18Z")
Hi again @ostephens - thank you for this!, both flagging the issue with the VIAF service & walking me through the workaround; much appreciated. I went back to an [old OR training video](https://www.youtube.com/watch?v=5tsyz3ibYzk) recently & was delighted to discover the JSON parsing you mentioned - had never played with this but am quite often looking for a single value in an otherwise busy JSON object, having this quick GREL extract code is incredibly helpful. Have successfully managed to pull out the LC code from VIAF following the instructions above - interestingly (/annoyingly, please feel free to leave me puzzling with this one as the complications pile up) this prompts another step of reconciliation as I’m eventually looking to return a properly formed name, with the chain of transformations looking like: Wikidata QID → VIAF ID → LC nID → corresponding LC name In this example I’d query the nID _n81032917_ and return _‎‡a Fields, Gracie,‏ ‎‡d 1898-1979_; there isn’t an obvious service for this on the test bench list but I’m sure a variety of them can accomplish it, & the Library of Congress has had various Linked Data APIs operating for a while that I can try to work with. Great to see a few of the reconciliation services on the library & archives side (like SNAC) building OpenRefine into their plans & will try to chronicle any progress here if no further developments 🙂 ---
**Author:** ![todrobbins](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/todrobbins/32/57_2.png) [@todrobbins](https://forum.openrefine.org/u/todrobbins)\ **Post date:** [January 5, 2023, 6:16pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/7 "2023-01-05T18:16:01Z")
> [@ostephens](#): > > In the expression box use GREL `"https://viaf.org/viaf/"+value+"/justlinks.json"` Ooh, this is a really helpful tidbit! Thanks! ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [January 5, 2023, 6:38pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/8 "2023-01-05T18:38:23Z")
> In this example I’d query the nID _n81032917_ and return _‎‡a Fields, Gracie,‏ ‎‡d 1898-1979_; there isn’t an obvious service for this on the test bench list but I’m sure a variety of them can accomplish it, & the Library of Congress has had various Linked Data APIs operating for a while that I can try to work with. By changing the extension on the base URL ([https://id.loc.gov/authorities/names/n81032917](https://id.loc.gov/authorities/names/n81032917)), you can get a variety of different formats, including JSON, NT, etc, You can also use content negotiation headers, but changing the URL is probably easier. Because it's MADS/SKOS RDF, it's kind of a mess to parse as JSON, but the info is there. Actually, on second thought, the MARC XML format might be the easiest to handle - [https://id.loc.gov/authorities/names/n81032917.marcxml.xml](https://id.loc.gov/authorities/names/n81032917.marcxml.xml) Tom ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [January 5, 2023, 7:54pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/9 "2023-01-05T19:54:49Z")
The LC NAF (and other LoC linked data services) also has an “interesting” lightweight way to get the preferred label using just the HEAD method. Unfortunately, I don’t think it can be used in OpenRefine, but folks using Python or other languages/tools might be able to take advantage. ```auto curl -Is [https://id.loc.gov/authorities/names/n81032917](https://id.loc.gov/authorities/names/n81032917) | grep x-preflabel x-preflabel: Fields, Gracie, 1898-1979 x-preflabel-escaped: Fields, Gracie, 1898-1979 x-preflabel-encoded: Fields%2C%20Gracie%2C%201898-1979 ``` The MARC XML format isn’t actually a delight to work with either, so here’s a cheat code to get the 100a from the payload returned by a .marcxml.xml URL: ```auto value.parseXml().select('marcxml|datafield[tag=100] > marcxml|subfield[code=a]')[0].xmlText() ``` getting the d subfield is left as an exercise for the reader.😉 Because the data is pretty constrained and unlikely to change format, you can also “cheat” a little to simplify things like: ```auto value.parseXml().select('[tag=100] > [code=a]')[0].xmlText() ``` or get fancy and return all the 100 subfields joined by pipe (|) characters: ```auto forEach(value.parseXml().select('[tag=100] > [code]'), subfield, subfield.xmlText()).join('|') ``` Tom ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [January 5, 2023, 8:13pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/10 "2023-01-05T20:13:32Z")
Ugh! The mail forwarder apparently can’t handle something as simple as monospaced code snippets in an HTML email. Sorry about the mess! ---
**Author:** ![ostephens](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/ostephens/32/8_2.png) [@ostephens](https://forum.openrefine.org/u/ostephens)\ **Post date:** [January 6, 2023, 10:04am UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/11 "2023-01-06T10:04:32Z")
> the chain of transformations looking like: > _Wikidata QID → VIAF ID → LC nID → corresponding LC name_ > In this example I’d query the nID _n81032917_ and return _‎‡a Fields, Gracie,‏ ‎‡d 1898-1979_ As @tfmorris suggests it would be possible to do this via the [id.loc.gov](http://id.loc.gov) service, but possibly you can do it directly from VIAF as well which might save you a step. So rather than using `"https://viaf.org/viaf/"+value+"/justlinks.json"` as I suggest above, instead you could use `"https://viaf.org/viaf/"+value+"/marc21.xml"` Using the **Age UK** example (VIAF ID = 158063944) this will return some XML starting like: ```auto 00000nz##a22.....n..4500 viaf158063944 OCoLC 230102n||azznnabbn##########|a#aaa####|# http://viaf.org/viaf/158063944 uri VIAF VIAF Age Concern England (ISNI)0000000404235368 ``` There will be repeated 710 fields (mx:datafield with tag = 710) in this XML and you can use the GREL `parseXml()` to pull out the LC one. You probably already know, but the 710 field is specific for corporate authors/organisations, and if you were to do the same thing for a person (e.g. Gracie Fields) then you'd be looking for a 700 field instead. There are going to be a variety of ways to extract the data you want from the XML, but a couple of examples might be: ```auto filter(value.parseXml().select("mx|datafield[tag=710]"),x,x.select("mx|subfield[code=0]")[0].ownText().startsWith("(LC"))).join("") ``` and ```auto forEach(value.parseXml().select("mx|subfield[code=0]"),x,if(x.ownText().startsWith("(LC)"),x.parent(),null)).join("") ``` The first of these specifically looks for the 710 fields and then filters down to those with "(LC)" in the subfield zero, whereas the second will extract any field with a subfield zero that starts with "(LC)" no matter what the MARC field. There are many variations on this theme for selecting the appropriate fields and @tfmorris's examples contain some alternative `select()` statements you could also use as the basis of the xml parsing. Once you have it down to the relevant fields you can extract the various subfields using `parseXml()` `select()` and `ownText()` appropriately. For example, from the previous step if we get the output: ```auto Age Concern England (Organization) (LC)n 79009603 ``` then we can extract the subfield a by: ```auto value.parseXml().select("mx|subfield[code=a]")[0].ownText() ``` Parsing the XML requires some slightly more involved processing than the `justlinks.json` but hopefully between the examples given by @tfmorris and the ones I've offered here there is enough to show how it can be done. Please do ask if there's anything more I can clarify. Owen ---
**Author:** ![MSH](https://avatars.discourse-cdn.com/v4/letter/m/e19b73/32.png) [@MSH](https://forum.openrefine.org/u/MSH)\ **Post date:** [January 6, 2023, 4:31pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/12 "2023-01-06T16:31:28Z")
Thank you @tfmorris!, this is wonderful - what a great tour through all the different outputs; both the XML & JSON _are_ a little unwieldy but contain all the data I needed, & the regular expressions meant I could somewhat clumsily try to pare down to just the name values. Quick shout to the old OR Google Group archives where I found a [few](https://groups.google.com/g/openrefine/c/jRfxtqPuqDU) [others](https://groups.google.com/g/openrefine/c/X_PZCDaMaDU) working with similar data. I made it as far as extracting some of the fields from the LC data while doing some trial & error around the correct GREL syntax for fields like _datafield tag=“100”_. After this, I’ve gone back to Owen’s great final steps, & can successfully return the 710/700 values I want using the second of the two extract snippets above, all from the original VIAF. Depending on what these look like when I tackle a series of personal rather than corporate names I might go back & join a few of the split subfields, but generally things are looking great. Thank you both for the extremely patient help on this! We’re cataloguing some websites over here & the lack of controlled data on authors or issuing organisations can be a big limitation, so being able to work this up from fairly easily assigned Wikidata IDs is a great step 🙂. Thanks for thinking this through from start to end; I’m writing up the workflow we’ve used so we can see where else it might be useful - will post any further updates here. TY again & TY for continued work making OR so useful. ---
**Author:** ![tfmorris](https://dub1.discourse-cdn.com/flex017/user_avatar/forum.openrefine.org/tfmorris/32/17_2.png) [@tfmorris](https://forum.openrefine.org/u/tfmorris)\ **Post date:** [January 6, 2023, 5:14pm UTC](https://forum.openrefine.org/t/issue-augmenting-wikidata-qids-in-or/303/13 "2023-01-06T17:14:46Z")
> > ![](https://europe1.discourse-cdn.com/flex017/uploads/openrefine/original/1X/bfb9c17f0822673bf1e46f9dfcc4008d8c74f3e3.png "MSH") | [MSH](https://forum.openrefine.org/u/msh) > > January 6 | > > > > - | - | > > Quick shout to the old OR Google Group archives where I found a [few](https://groups.google.com/g/openrefine/c/jRfxtqPuqDU) [others](https://groups.google.com/g/openrefine/c/X_PZCDaMaDU) working with similar data. Glad you found my decade old VIAF answer useful. 😁 I hope we don't lose that 12+ year trove of information! Tom