949 fields: creating multiple 949's using OpenRefine

Using OpenRefine to create 949 fields for multiple volumes and extra copies

From a Batch Work Original Cataloging project

10/23/2017

1 Editing and merging the 949 columns into one column:
2 Moving cell information from every-other-row into a new column:
3 II-Remove the rows with blanks (quick and easy method):

Merging the 949 columns in OpenRefine, then moving the two copies’ 949s into the same row:

In the spreadsheets volumes and Copies were on two separate rows. Eventually each volume’s 949 item information will need to be in the same row, but before that, each volume’s item has to have its 949 subfield info linked together. Once merged, each item information (949a, 949i, 949v or 949c) would at first, still be in one row while the rows are still separate; then the 2^nd 949 would be moved to the first volume/copy’s row.

Editing and merging the 949 columns into one column:

1-If not already done, use this GREL to merge the two parts of the 949a1 & 2 call number portions, naming the new column 949a: cells[“949a1”].value+” “+cells[“949a2”].value

2-The file of 2Duets already has a volume column, but the Copies file needs to have a copy number column created. First, use a Text filter on 949a of: c.2 to bring up only the copy two items:

3-On 949a, Edit column, Add column based on this column, New column name: 949c, and type the number 2 in the Expression box to get a column filled with 2’s:

3-The new 949c column copy 2 data has been interpreted as green numbers by OpenRefine, so use the GREL: value.floor().toString(). The new column should now display all the 2’s in black. To double-check, you can click on a cell’s edit, and check that the Data type is text:

4-Close the Text filter to return to the rest of the file. Then do a Text facet on the new 949c column and click on (blank) to bring in all the copy 1 items:

5-On 949c, use Edit cells, Transform, and type the number 1 to create the copy 1 numbers:

Afterwards, there should be no rows displaying in the (blank) Text facet; and “1” and “2” should have equal numbers of rows. Close the Text facet.

6-Once again the new copy 1 values have been interpreted as numbers. This time, bring up the first copy 1 cell’s edit, then change Data type to text, and click on Apply to All Identical Cells:

7-On the 949a column, string together two GREL replace functions: value.replace(", c.2","").replace(", c.2","")

including the comma space in front of each copy number, to remove them from the call numbers:

8-All the 949 components, except for 949a, will need their subfield delimiters added to the front of the column.

The 949c column only has two values, so string together two GREL replaces: value.replace("1","$c1").replace("2","$c2")

For the barcodes, copy and paste the first few barcode digits into a GREL replace : value.replace("390600","$i390600")

Make absolutely sure you don’t add or delete digits from the barcodes!

9-Make a new column out of the 949 components, with the name: 949volume or 949copy.

On the 949a column, Edit column, Add column based on this column;

Use this GREL for volumes: cells["949a"].value+" "+cells["949v"].value+" "+cells["949i"].value

For copies, use this GREL: cells["949a"].value+" "+cells["949c"].value+" "+cells["949i"].value

10-Remove the all old 949 subfield part columns (949a1 and 949a2, 949a, 949i, 949v): column Dropdown; Edit columns; Remove column, or go to All; Edit columns, Re-order/remove columns:

Moving cell information from every-other-row into a new column:

Move every other cell in a column to a new column:

Click on the 949volume or 949copy column’s dropdown; choose Transpose; Transpose cells in rows into columns:

A window will pop up, asking: How many rows to transpose. The default is set to 2, and that is what is needed, so click OK:

Spot check the result to make sure the 949copies moved to the correct row:

The resulting 949 columns have spaces in front of the subfield delimiters, so clean them up using: value.replace(" $","$")

II-Remove the rows with blanks (quick and easy method):

1-Delete all the rows without 949 cells using the facet method of row deletion.

On the 949copy or 949volume column, chose Facet, Text facet, and select (blank) at the end of the results.

This should show half of all rows in the file (21 of 42 total, in this example). Any rows showing will be deleted in the next step:

Go to All, Edit rows, Remove all matching rows, and the rows will all disappear:

Close the facet to return to the remaining rows with all the 949volume or 949copy item information:

Done!