Location Shifting with Open Refine and WMS Collection Management API

In my last post on using OCLC’s APIs and OpenRefine, I talked about using the APIs to manage holdings in WorldCat. In this post, we’re going to build upon skills and Jython code in the last post. The basic concepts are the same here, but we’ll need to do two API calls to accomplish a different task: changing item locations. You may want to follow this method if your library is moving or undertaking a shifting project. If you haven’t been following the series, please make sure you go back to the previous post to get Jython and the Python Requests module installed.

Creating a Project

As in the first blog post in the series, we need to start a new project by getting data. For this project, we’re going to cut and paste CSV data from a sample file in our GitHub repository into OpenRefine. The CSV file has three columns: barcode, newLocation, and tempLocation. The first column contains the item barcode; the second column contains the new location code for the item; the third contains a temporary location code, which, depending on your use case, you may want to use instead of a new permanent location. It is important to note that I'm ONLY working with Monographic data in this example. Updating multi-part or serials item data is dramatically more difficult. I don't reccomend attempting it using this methdology.

OpenRefine offers a preview of the data and then you use the “Create Project” button to pull the data into OpenRefine to work with. Once the data is in OpenRefine, it looks a bit like a spreadsheet.

Adding the JSON Python library

In our previous posts, we installed Jython and added the request python library. For this project we want to work with JSON so we also need the JSON python library. To install this run the following command in your home directory

sudo jython pip install json

Getting an Access Token

As in our previous two posts, you’ll need to use the API Explorer to obtain an Access Token. However, this time we need an Access Token for WMS Collection Management API.

1. Go to the DevNet page for OAuth, ClientCredentialGrant GetToken.
   
2. Choose “Use my credentials.”
3. Enter your WSKey, secret, principal ID, and principal ID namespace (IDNS). (You can find out more about how to get a principal ID and principal IDNS by reading the User Level Authentication and Authorization page.)
   
4. For the prompted URL, fill in the following:
   1. authenticatingInstitutionID: your library’s registry ID
   2. contextInstitutionID: your library’s registry ID
   3. scopes: WMS_Collection_Management (the ID(s) for the service you want to access)
5. Click the “Send the Request” button.
   Look at the JSON response, and find the value labeled “access_token.” This will look something like: tk_ruQqgWU2PZsBdkMTq2Po73PCvYxWybJgzlIR.
   
   

Making the API Call to Read the Copy

Now that you have an Access Token, you need to make a request to the WMS Collection Management API in order read the copy. Use the dropdown on the “barcode” column to “Edit Column -> Add column based on this column”. Name the column “Read Copy”. Set the Language Jython and use the following expression:

import sys
sys.path.append('/{path_to_jython}/jython2.7.0/Lib/site-packages')
import requests

request_url = 'https://circ.sd00.worldcat.org/LHR?q=barcode: ' + value
headers={'Authorization': “Bearer tk_123456789”, 'Accept': 'application/atom+json'}
try:
    r = requests.get(request_url, headers=headers)
    r.raise_for_status()
    response_body = r.json()    
    return json.dumps(response_body["entries"][0])
except requests.exceptions.HTTPError as err:
    return “Read failed. ” + err.response.status_code

Make sure to substitute your own access token for the tk_123456789.

Updating the JSON for the Copy with the new location

Since we read the Copy based on barcode, we need to know the actual Copy URL in order to update it with the new location value. (The copy URL is based on the copy ID or copy accession number) We can get this value from the JSON that was returned when we read the Copy by barcode. Use the dropdown on the “Read Copy” column to “Edit Column -> Add column based on this column”. Name the column “CopyURL” and use the following expression to get the Copy ID.

parseJson(value).content.id 

Making the API Call to Update the Copy

Now that we have the URL we need to update the Copy, we need to write code to do two things.

1. Update the JSON for the copy with the new location so we can pass it in as the request body
2. Make a second request to the WMS Collection Management API to update the Copy data.  

Use the dropdown on the “Read Copy” column to “Edit Column -> Add column based on this column”. Name the column “Update Copy”. Set the Language Jython and use the following expression:

import sys
sys.path.append('/{path_to_jython}/jython2.7.0/Lib/site-packages')
import requests
import json

request_url = cells["CopyURL"].value

headers={'Authorization': “Bearer tk_123456789”, 'Accept': 'application/atom+json', 'Content-Type': 'application/atom+json'} 

copy = json.loads(value)
copy['content']['shelvingLocation'] = cells['newLocation'].value
updated_copy = json.dumps(copy)

try:
	r = requests.put(request_url, headers=headers, data=updated_copy)
	r.raise_for_status()
	return “Copy successfully updated”
except requests.exceptions.HTTPError as err:
	return “Copy not updated. ” + err.response.status_code

This code will make the request and add the results to a new column.

Adding a Temporary Location

Another task that you might want to perform on a set of copies is adding a temporary location. Use the dropdown on the “Read Copy” column to “Edit Column -> Add column based on this column”. Name the column “Add Temporary Location”. Set the Language Jython and use the following expression:

import sys
sys.path.append('/{path_to_jython}/jython2.7.0/Lib/site-packages')
import requests
import json

request_url = cells["CopyURL"].value

headers={'Authorization': “Bearer tk_123456789”, 'Accept': 'application/atom+json', 'Content-Type': 'application/atom+json'}

copy = json.loads(value)
copy['content']['holding'][0]['temporaryLocation'].append(cells['tempLocation'].value)
updated_copy = json.dumps(copy)

try:
    r = requests.put(request_url, headers=headers, data=updated_copy)
    r.raise_for_status()
    return “Copy successfully updated”
except requests.exceptions.HTTPError as err:
    return “Copy not updated. ” + str(err.response.status_code)

Removing Temporary Locations

The last typical task that you might want to perform on a set of copies is removing a temporary location. Use the dropdown on the “Read Copy” column to “Edit Column -> Add column based on this column”. Name the column “Remove Temporary Location”. Set the Language Jython and use the following expression:

import sys
sys.path.append('/{path_to_jython}/jython2.7.0/Lib/site-packages')
import requests
import json

request_url = cells["CopyURL"].value

headers={'Authorization': “Bearer tk_123456789”, 'Accept': 'application/atom+json, 'Content-Type': 'application/atom+json''}

copy = json.loads(value)
if copy['content']['holding'][0]['temporaryLocation']:
    del copy['content']['holding'][0]['temporaryLocation'][:]
updated_copy = json.dumps(copy)

try:
    r = requests.put(request_url, headers=headers, data=updated_copy)
    r.raise_for_status()
    return “Copy successfully updated”
except requests.exceptions.HTTPError as err:
    return “Copy not updated. ” + str(err.response.status_code)

Next steps

Thus far in this series of posts, we’ve looked at how Open Refine can be used with OCLC APIs to perform data manipulations on library metadata for bibliographic records and copy records. In our next post we’ll look at using OpenRefine and the Identity Management API to create users in the system.