Marquee University

e-Publications@Marquee

!!&$#&"!$#" !!&&!!!!"



Identifying potential headings for Authority work

using III Sierra, MS Excel and OpenRe#ne

Lynn K. Whi$enberger

Marquee University&%)!!! $)$

$"!'($#!"%#!""

In order to participate in a newly established Wisconsin NACO funnel project, Marquette University’s

Raynor Library needed to identify author/corporate names currently in the Catalog

Lists of names represented in the catalog were easy to pull. Faculty Publications and Cujé Collection

materials have unique location codes in our catalog, which made it easy to gather the relevant

bibliographic records using the create lists function in III Sierra. From those lists, I exported only the

author and added author fields (MARC fields 100/110 and 700/710). I stripped subfields e and t from the

export, as that made it easier to identify and remove duplicates in Excel and OpenRefine. The author

and added author fields were exported as a | (pipe) delimited file. Sierra offers the option of specifying

delimiters for repeated fields, and I used the same delimiter for both fields and repeated fields, which

made it easier to convert the resulting file into one long list in Excel.

one column of data. A time consuming process, but not terrible.

Our Institutional Repository (IR) is built on the Digital Commons platform and the list of faculty names is

natively exported as an Excel spreadsheet. Here, the author name exports into three columns: last

name, first name, middle name/initial. Using the concatenate function in Excel, I was able to ‘glue’ the

pieces of the author name together into the same format as the data from the catalog (Last Name

(comma space) First Name (space) middle name/initial). To convert the column of built names from a

formula to text, I had to copy the column, and then paste as values into a new column.

left all the headings in the list. For MU Faculty, I did a little more cleanup to remove corporate names. In

Excel, I used the “Conditional Formatting”-“Highlight Cells Rules”- “Text that contains” functionality.

Using a list of common corporate name stopwords (e.g. Association, Inc., University, etc..) I was able to

find corporate body names in the list and remove them. To identify obvious non-Marquette authors, I

cleaned the list by using the same “Conditional Formatting …” functionality and searching for “Text that

contains” birthdates in the 1600s, 1700s, and early 1800s.

Once the data was in one column and the preliminary cleanup work done, I was ready to use Open

Refine (OR) to clean up the data more thoroughly. From there, I could reconcile the names against VIAF

presence or absence of middle initials, single letter typos in the name, etc.

To de-duplicate values in OR, I used the “Edit Cells” and “Cluster and Edit” functionality.

The default clustering algorithm is method = key collision and keying function = fingerprint. This

algorithm caught many variants of the same name. In my case, they were typically with punctuation

variations.

In this example, there were only two headings I wanted to merge.

For my last de-duplication pass, I selected the “metaphone3” keying function. This caught bigger

variations: instances where there was a date present, where middle names spelled out, or there were

names with uniform titles.

This method casts a much broader net, so it is worth the time to scan through the list and select the

name changed enough to move it in the list.

After sorting, it was time to remove duplicates. In OR, this is done by “Edit cells” – “Blank down.”

The “Blank down” function works by blanking every row that matches the value of the preceding one

based on the row ID. (http://googlerefine.blogspot.ca/2011/08/remove-duplicate.html)

Once duplicate values were blanked out, I hid the blank rows by applying “Facet” – “Customized facets”

– “Facet by blank”

amount of heavy lifting in creating JSON scripts to run in OpenRefine to accomplish this very task. The

scripts can be found on GitHub at https://github.com/mcarruthers/LCNAF-Named-Entity-Reconciliation.

There are three different flavors of script: Personal Names, Corporate Names, and Generic Names. I

recommend reading through Matt’s instructions on using the scripts before proceeding.

I used the Personal Names script for the Marquette Faculty list, and the Generic Names script for the

Cujé list as that one was a mix of personal and corporate names.

The first thing I needed to do was to change the OR column name to “Name” (“Edit column” -- “Rename

this column”)

In this example, I was working with the Marquette Faculty list, so I copied the Personal Names script

from the GitHub .txt file, and then pasted it into the “Apply Operation History” window.

tab), and then click the “Apply” button