top of page
GFUS Zoom Background3 (2)_edited.jpg

Blog Posts

Using OpenRefine to assess open research at your organization

Chris Erdmann

The goal of this post is to share the process I used to collect open research publications data, using primarily OpenRefine, to understand the impact of our Open Access (OA) Publication Policy at the Michael J. Fox Foundation (MJFF). Inspired by the Year of Open Science, it is my hope that the practical recipes shared here can help others assess the impact of open research at their organizations. 


Tools used

For this work, I used three free tools which you can either use online or download to your computer:

  • Google Sheets - A spreadsheet program included as part of the free, web-based Google Docs Editors suite offered by Google. Wikipedia

  • OpenRefine - An open-source desktop application for data cleanup and transformation to other formats, an activity commonly known as data wrangling. Wikipedia

  • Publish or Perish - A software program that retrieves and analyzes academic citations. 


Databases searched

Databases searched were selected based on indexing strength (in biomedical research) and the number of results they returned. Wikipedia has a list of academic databases and search engines, but I focused on a core list that included Google Scholar, Dimensions, Europe PMC, Scopus, and Web of Science, highlighted in this recent study. I searched all available fields in these databases for the string - Michael J “Fox Foundation”.


I used Publish or Perish to automate the process of exporting records from Google Scholar queries. Due to Google Scholar rate limitations, only the first 1000 records can be exported, but at least you can filter your queries, for instance, by year. To export records and download data from Dimensions, registration is required; the free version limits users to exporting 500 records at a time. Filtering is also available, by year, and/or OA status. Scopus and Web of Science are available via subscriptions (e.g., libraries).


Reconciling database results via DOIs 

With the exported records from each database, I used the digital object identifier (DOI) fields to find unique matches and reconcile the DOIs into one merged list. First, I uploaded the database-exported data, in either Excel or CSV format, to separate tabs in the same Google Spreadsheet. I then used the VLOOKUP function and recipe featured in Image 1 to find matches and unique DOIs. For details on the VLOOKUP recipe used, see Cross Checking/Finding Duplicate DOIs below. 


Image 1: Example Google Sheet demonstrating the VLOOKUP DOI matching approach. 


Retrieving Crossref metadata from the reconciled list of DOIs

I turned to Crossref, with favorable metadata license information, to retrieve metadata for the reconciled DOIs. Crossref is an official DOI registration agency for publisher metadata (including journal article metadata) in addition to other sources such as preprints, grants, and standards. I used OpenRefine, a free, open-source tool used for wrangling data, to import the reconciled DOIs and fetch associated metadata via the Crossref application programming interface (API) in JSON (Javascript Object Notation), an open standard file format and data interchange format. To learn more about OpenRefine and how to use it, see Library Carpentry: OpenRefine. I then parsed the imported JSON into separate fields using the recipes found in the  Crossref GREL Expressions section below. What is particularly helpful about OpenRefine is that you can facet by these fields (e.g., Select a column, Facet -> Text Facet) and sort by counts. In some cases, like the Subject field, you may need to Edit Cells -> split multi-valued cells by a delimiter, e.g. semicolon, before using the Text Facet option. 


Image 2: Screenshot of OpenRefine where Crossref fields have been retrieved from the reconciled DOIs. Download the example OpenRefine project and Import (the) Project, not Create (the) Project, in your OpenRefine application to see the recipes via the Undo / Redo tab. 


Using Unpaywall to find open access information connected to DOIs

Another helpful service that can be used to enhance Crossref data is Unpaywall, where content is harvested from over 50,000 journals and OA repositories, but it is handy for OA status and links to OA versions of articles. Once again I used OpenRefine and DOIs to fetch and parse Unpaywall data. See Fetch Unpaywall Metadata from DOIs and Unpaywall GREL Expressions below.


Image 3: Screenshot of OpenRefine where Unpaywall JSON and fields have been retrieved from the reconciled DOIs. Download the example OpenRefine project and Import (the) Project, not Create (the) Project, in your OpenRefine application to see the Unpaywall recipes used via the Undo / Redo tab. 



Data/software citations from DataCite

I queried DataCite, an international DOI registration agency, similar to Crossref, but geared towards data citation and associated metadata, to see if there were relationships between the list of reconciled DOIs and any data/software citations. The query returned the number of relationships between the DOI queried and additional DOIs and their URLs. See the Fetch DataCite Relationships from DOIs section for the OpenRefine recipes. To determine the sources of the DOIs, see the section on Leveraging the Crossref/DataCite APIs for Prefix Information


Note: Download the example OpenRefine project and Import (the) Project, not Create (the) Project, in your OpenRefine application to see the DataCite recipes used via the Undo / Redo tab


Challenges with data/software citations

The sharing of data and software by authors across publishers and journals is not standard and rates of sharing remain low. Data/software sharing comes in various forms (slide 12), and unfortunately, it is still common to see statements such as “data available upon reasonable request”. Persistent identifiers such as DOIs and Research Resource Identifiers (RRIDs), are extremely valuable to sharing data/software in a Findable, Accessible, Interoperable, and Reusable (FAIR) manner. This means we can unlock the potential of the underlying metadata associated with their DOIs to facilitate the discovery and reuse of data/software. To unlock this potential, with a DOI, authors can generate a citation from a repository (e.g., Zenodo) where they have preserved their data/software, cite it in their availability statement (slide 18), and include the citation in their references. Publishers can tag these citations and Crossref can make these paper-data/software relationships available via Event Data. It doesn’t always work this way though, and so we have a number of initiatives working towards mining this information from research publications (e.g., Open Global Data Citation Corpus, Software Mentions, policy compliance, and tracking). The Additional Ideas section below also has some ideas/recipes if you’d like to attempt mining data/software DOIs/RRIDs from research publications yourself. 


Conclusion

I hope the recipes and ideas below are helpful. When I started this process, not all this information was readily available and so I am sharing it with others that might be trying to do similar work. If you would like to visualize and share the bibliographic information you’ve collected in OpenRefine, I used the Lens.org Collections feature to create a dashboard (after registering) from the DOIs acknowledging MJFF. 


Throughout this process, it was not always apparent if/how I could contribute information back to the systems mentioned above to improve the discovery of MJFF-funded research. For instance, I learned that you can email support@unpaywall.org if you want to update OA information in Unpaywall. I also learned that updating MJFF grant information in Crossref and tying it to research publications can improve the discovery of MJFF research in the systems that rely on their data. In the spirit of the Year of Open Science, let’s work together to make it easier for the community to contribute information back to these systems. 


In closing, persistent identifiers (PIDs), particularly DOIs, were invaluable to matching and reconciling open data from the different systems I used and I especially want to thank Unpaywall, DataCite, and Crossref. I also want to thank Michelle Durborow (Michael J. Fox Foundation), Chelle Gentemann (NASA), Kelly Stathis (DataCite), Elisha Wood-Charlson (KBASE/Lawrence Berkeley National Lab), Sonya Dumanis (Aligning Science Across Parkinson's), Juliane Schneider (Pacific Northwest National Laboratory), Christine Kirkpatrick (San Diego Supercomputer Center), Kimberly Mann Bruch (San Diego Supercomputer Center), Rachael Lammey (Crossref), Kristen Ratan (ICOR), Martin Magdinier (OpenRefine), Yuhan Rao (NCICS), and Tom Morris (OpenRefine) for their help and feedback. 


Recipes


Cross Checking/Finding Duplicate DOIs 

For cross-checking DOIs from database exports, use VLOOKUP in Google Sheets.

Example:

=VLOOKUP($A2,DB!$A$2:$A$100,1,FALSE)

$A2 - Cell with DOI where you are trying to match against a range of DOIs

DB! - The name of the database worksheet/tab that you are pulling the range of DOIs from

$A$2:$A$100 - Range of cells in the database worksheet/tab that contain DOIs to check against

1 - Index, returns the DOI if it is a match

FALSE - Looks for an exact match


Fetch Crossref Metadata from DOIs 

In OpenRefine, choose the column with DOIs. Use Edit Column > Add column by fetching URLs. Add the GREL expression below:

Make sure the DOI ‘value’ is in the following format:

10.7554/eLife.79771


Remember to name the new column that will be created, for instance, CrossrefJSON, and consider a 3000 or 4000 throttle delay (to speed up the process). Also, consider faceting your search depending on how many records/DOIs there are to fetch.


You can also append your email address to the API call, for example:"https://api.crossref.org/works/"+value+”&mailto=name@xyz.com”


Crossref GREL expressions

The following are OpenRefine GREL expressions to extract data from the CrossrefJSON into separate columns using Edit Column -> Add column. 

Publisher

value.parseJson().message.publisher

Title

value.parseJson().message["title"][0]

Journal Title

value.parseJson().message["container-title"][0]

Abstract

value.parseJson().message.abstract

Published Date

value.parseJson().message.published["date-parts"][0]

Year

value.parseJson().message.published["date-parts"][0][0]

Month

value.parseJson().message.published["date-parts"][0][1]

Day

value.parseJson().message.published["date-parts"][0][2]

ISSN (Electronic)

value.parseJson().message["issn-type"][1].value

Type

value.parseJson().message.type

Author

forEach(value.parseJson().message.author,v,v.family + ', ' + v.given).join(';')

Affiliation

forEach(value.parseJson().message.author,v,v.affiliation[0].name).join(';')

Referenced By

value.parseJson().message["is-referenced-by-count"]

Subject

forEach(value.parseJson().message.subject,v,v).join(';')

Has Preprint

value.parseJson().message.relation["has-preprint"]

BibTeX/Citation

From DOI, Edit Column > Add column by fetching URLs

Author Count

From Author, Edit Column > Add column based on this column…value.split(";").length() 


Fetch Unpaywall metadata from DOIs

Similar to the Fetch Crossref Metadata from DOIs section, you can retrieve OA status and other information from Unpaywall using OpenRefine and DOIs.


Edit Column > Add column by fetching URLs"https://api.unpaywall.org/v2/"+value+"?email=email@xyz.com"

Unpaywall GREL expressions

The following are OpenRefine GREL expressions to extract data from the UnpaywallJSON into separate columns using Edit Column -> Add column. 

OA Status

value.parseJson().oa_status

License

value.parseJson().best_oa_location.license

Repository Copy

value.parseJson().has_repository_copy

URL

value.parseJson().best_oa_location.url

PDF

value.parseJson().best_oa_location.url_for_pdf

URL Landing Page

value.parseJson().best_oa_location.url_for_landing_page


Fetch DataCite relationships from DOIs

Similar to the Fetch Crossref Metadata from DOIs section, you can retrieve dataset to scholarly article relationships from DataCite via OpenRefine using DOIs.

Edit Column > Add column by fetching URLs

Alternatively, to potentially capture more relationships, the following are broader searches:

Total (Relationships)

value.parseJson().meta.total

Object (Data/Software) Relationship URLs

forEach(value.parseJson().data,v,v.attributes["obj-id"]).join(';')

Visualize relationships via DataCite Commons:E.g., https://commons.datacite.org/doi.org/10.5880/icgem.2015.1

Leveraging the Crossref/DataCite APIs for prefix information

The following API calls can be used to retrieve prefix information for DOIs, or in other words, the repository names for DOIs. 

Look Up Crossref Prefix Information

Retrieve DataCite Client (Repository) ID 

Look Up DataCite Client ID


Additional ideas

The following are additional data sources/approaches that can be explored.

Directory of Open Access Journals (DOAJ):

OpenRefine Reconciliation Services

Examples: Wikidata, ROR

OpenAire Research Graph

Scholix (Event Data)

Extracting DOIs (and RRIDs) from HTML

Edit Column -> Add column based on this column (Python / Jython)import re 

matches = re.findall(r"\b10\.\d{4,9}/[-.;()/:\w]+", value) 

return ";".join(matches)

A regular expression for RRIDs: 

RRID\:\S*

RRID API

Extract Country from Affiliation

Mining PDFs

GROBID - GeneRation Of Bibliographic Data

2 views0 comments

Kommentare


bottom of page