---
tags: ucsd-carpentries
---

# 2022 UC Carpentries Fall Workshop (OpenRefine)

**Workshop Details**
Dates: September 6th - 13th, 2022
Time: 9am - 12pm

**Workshop Agenda:**
https://ucsdlib.github.io/2022-09-06-carpentries-uc/

## Day 6: OpenRefine
**Software Installation:**
http://openrefine.org/download.html
* Download and Install OpenRefine latest Version
* Windows kit zip file for Windows
* Mac kit for MacOS

**Lesson Data (download)**
https://raw.githubusercontent.com/LibraryCarpentry/lc-open-refine/gh-pages/data/doaj-article-sample.csv
* right click and “save as” .csv file to your desktop

## NOTES:
**Introduction to OpenRefine**
Intro Slides: https://docs.goog
le.com/presentation/d/1XaF9x9243BOSktfMS8YMhiPohe525HWplhr7FG7PXjw/edit?usp=sharing

Extensions and Distributions can be found here: http://openrefine.org/download.html

**Importing data into OpenRefine**

**Layout of OpenRefine, Rows vs Records**

**Faceting and filtering**
Scatterplot facets are less commonly used. For further information on these see the tutorial at https://web.archive.org/web/20190105063215/http://enipedia.tudelft.nl/wiki/OpenRefine_Tutorial#Exploring_the_data_with_scatter_plots.

**Clustering**
For more information on the methods used to create Clusters, see https://github.com/OpenRefine/OpenRefine/wiki/Clustering-In-Depth

**Working with columns and sorting**

**Introduction to Transformations**
Full documentation for the GREL is available at https://docs.openrefine.org/manual/grelfunctions.

**Writing Transformations**

**Transformations - Undo and Redo**

**Transforming Strings, Numbers, Dates and Booleans**

**Transformations - Handling Arrays**

**Exporting data**

**Looking Up Data**
CrossRef API: https://github.com/CrossRef/rest-api-doc).
Read more about the CrossRef service: http://www.crossref.org.

OpenRefine has a function for extracting data from JSON (sometimes referred to as ‘parsing’ the JSON). The ‘parseJson’ function is explained in more detail at https://docs.openrefine.org/manual/grelfunctions/#format-based-functions-json-html-xml.

The official User Manual provides detailed information about the reconciliation feature: https://docs.openrefine.org/manual/reconciling

One of the most common ways of using the reconciliation option in OpenRefine is with an extension (see below for more on extensions to OpenRefine) which can use linked data sources for reconciliation. The RDF extension by Stuart Kenny can be downloaded from https://github.com/stkenny/grefine-rdf-extension/releases.

Other extensions are available to do reconciliation against local data such as csv files (see http://okfnlabs.org/reconcile-csv/) and maintained lists of values (see http://okfnlabs.org/projects/nomenklatura/index.html).

For more information on using Reconciliation services see https://docs.openrefine.org/manual/reconciling.

A list of Extensions (not necessarily complete) is given on the OpenRefine downloads page at http://openrefine.org/download.html.

## Workshop Day 6 ### First name and Last Name/Organization/Dept./Email | Name
(first & last) | Organization | Dept. | Email |
| ------------------------- | ------------ | ----- | --------------- |
| (example) Jane Doe |UCSD | IT | [e-mail] | |Roberto Silva
|Roberto Silva |UCSD |SIO |[e-mail] |Kenan Chan
|UCSD |SIO |[e-mail] |
|Tom Le|UCM||[e-mail]|
|Nicole Rosenberg | UCSD | Scripps| [e-mail] | | |
| Aleks Leszczynska | UCSD | | [e-mail] | | |
| Edgar Reyna | UCLA | Urban Planning | [e-mail] | |
Oishee Misra | UCSD | Economics |[e-mail]
| | | |
| Marta Sala Climent |UCSD | Medicine | [e-mail] | Charles Faulhaber|Spanish & Portuguese UCB|[e-mail] | | | | | |
| Becky Miller |UCB | Library | [e-mail]| |
|Apisit Kaewsanit | UCSF |Epidemiology and Biostats | [e-mail] | | |
| KYLE ROKES UCSB [e-mail]|
|Dayana Elizalde |UCR | [e-mail] | | |
| Alissa Jae Lazo-Kim | UCSD DBMI internship [e-mail] | | | | |Shang Su |U Toledo|Cell and Cancer Biology|[e-mail]|
|Jessica Wu-Woods|UCR|Microbiology|[e-mail]|
|||||
|||||
|||||
|||||
|||||
|||||
|||||
|||||
|||||
|||||
|||||
|||||

## Day 6 Exercises
1. **Splitting Subjects into separate cells**
What separator character is used in the Subjects cells?
How would you split these subject words into individual cells?

2. **Joining the Subjects column back together**
Using what we’ve learned, now Join the Subjects back together

3. **Which licences are used for articles in this file?**
Use a text facet for the licence column and answer these questions:
What is the most common Licence in the file?
How many articles in the file don’t have a licence assigned?

4. **Find all publications without a DOI**
Use the Facet by blank function to find all publications in this data set without a DOI

5. **Correct the Language values via a facet**
Create a Text facet on the language column and correct the variation in the EN and English values.

## Day 6 Reflection
Please enter how you will use the OpenRefine in your work or research here:
1. To organize metadata
2. Clean up data
3. To reconcile PhiloBiblon project data with VIAF and Wikidata

## Day 6 Questions:
Please enter any questions not answered during live session here:
1.

## OpenRefine Resources:
Official wiki List of OpenRefine External Resources: https://github.com/OpenRefine/OpenRefine/wiki/External-Resources
Getting started with OpenRefine by Thomas Padilla: http://thomaspadilla.org/dataprep/
Cleaning Data with OpenRefine by Seth van Hooland, Ruben Verborgh and Max De Wilde: http://programminghistorian.org/en/lessons/cleaning-data-with-openrefine
Blog posts on using OpenRefine from Owen Stephens: http://www.meanboyfriend.com/overdue_ideas/tag/openrefine/?orderby=date&order=ASC
Identifying potential headings for Authority work using III Sierra, MS Excel and OpenRefine: https://epublications.marquette.edu/lib_fac/81/
Free your metadata website: https://freeyourmetadata.org/
Data Munging Tools in Preparation for RDF: Catmandu and LODRefine by Christina Harlow: https://journal.code4lib.org/articles/11013
Cleaning Data with OpenRefine by John Little: https://libjohn.github.io/openrefine/
OpenRefine Blog: https://openrefine.org/category/blog.html

### End Day 6
Feedback form:
https://forms.gle/5qgx8X6H3GRMacwD6