Recipe and walkthrough - Joining the first two cells in a column and moving the third up

 If I had this sample spreadsheet:

And I wanted to transform it to this spreadsheet:

I can use the column array conversion, a trick with array arithmetic, and nested ifs to do so in one GREL expression. Or I can use three transforms.

Assuming that all data in the "Subject" column is three subject and only three subjects per call#, and that there are no blank lines in the column, do the following:

1. Add a blank column.
2. Put in a placeholder value in first cell.
3. Move column to the beginning. If using an older version of Open Refine, switch to record view.
4. Transform with: if(mod(rowIndex, 3) == 0, row.record.cells["Subject"].value[rowIndex] + "; " +row.record.cells["Subject"].value[rowIndex+1], value)
5. Transform subject column again: if(mod(rowIndex, 3) == 1, row.record.cells["Subject"].value[rowIndex+1], value)
6. Last transform: if(mod(rowIndex, 3) == 2, "", value)
7. If you were in record view, switch back to rows. Delete placeholder column.

OR:

1. Add a blank column.
2. Put in a placeholder value in first cell.
3. Move column to the beginning. If using an older version of Open Refine, switch to record view.
4. Transform on the subject column and use this piece of GREL:

if(mod(rowIndex, 3) == 0, row.record.cells["Subject"].value[rowIndex] + "; " +row.record.cells["Subject"].value[rowIndex+1], if(mod(rowIndex,3)== 1, row.record.cells["Subject"].value[rowIndex+1], ""))
5. If you were in record view, switch back to rows. Delete placeholder column.

Recipe and walkthrough - Join selected cells in a column

Again, I'll be posting a recipe first and then a detailed walkthrough below.

Recipe:
Given this truncated sample data:

Assuming that there are more entries that aren't being shown, if I only want to join the two subject headings in Column 4 for Ammanati, Bartolomeo:

1.  Make sure you're in the row view before you proceed
2. Add column based on any column, with whatever name (I'm calling it index), and "" in the Expression box.
3.  Edit the very first cell in index, put in a placeholder value. (NOTE: put in one placeholder value only, doing two won't work.)
4. Move index to the first column position.
5. Switch to record view. (Note : this appears to be optional in Open Refine 2.9, but you may need to do it for older versions.)
6.   Because I found it hard to read recipes with other people's column names, I am referring to Column 2, as the <test column>. (Since this is the column you use as the test condition for the if statement). Column 4 will be referred to as the <edit column>, to indicate which column I want to edit.
1. Transform on <edit column> : if(cells["<test column>"].value.contains("Ammanati"), row.record.cells["<edit column>"].value[rowIndex] + ";" + row.record.cells["<edit column>"].value[rowIndex+1], value)
7.  Transform on <test column> and create a placeholder row: if(row.record.cells["<test column>"].value[rowIndex-1].contains("Ammanati"), "place", value)
8.  Switch to row view.
9. Custom text facet on <test column>: value.contains("place"), select true
10. Star the placeholder rows.
11.  Remove the custom facet and facet by star
12. All->edit rows->remove matching rows. 
13. Remove the index column.

---

 Long, detailed explanation:

Recipe and walkthrough: counting up repeats of a word in a sentence

How do you count up the occurrences of <word> in a sentence? (or, in this case, find how many times "Nintendo" is in a 538?

sum(forEach(value.split(" "), temp, if(temp.contains("Nintendo"), 1, 0)))
 

Explanation:

Walkthrough of extracting metadata from Fedora 3 and reconciling with Open Refine

Ruth Tillman wrote an extensive article on updating Fedora 3 metadata and using Open Refine to reconcile: http://journal.code4lib.org/articles/11179

Walkthrough of the Geonames Recon Service

Christina Harlow has a thorough post on configuring the Geonames Recon Service: http://christinaharlow.com/walkthrough-of-geonames-recon-service

Two recipes for extracting the last n words in a string

Method 1: rpartition

Again, I'm going to post the recipe up here, and go into depth below. If you want the last <n> words in any sentence, you can do a transform with:
rpartition(value, /(\s\S+){<n>}$/)[1]

Be sure that all your data is more than <n> words. If you have sentences that are exactly n words, you'll get a null.  You can either custom facet out that data with a :
length(value.split(" ")) ><n>

(what the length GREL does is described in this post.)

or use an if function:
 if (length(value.split(" ")) ><n>, rpartition(value,  /(\s\S+){<n>}$/)[1], value)


Method 2: Array arithmetic with split
 Since you can access any element in split() by using the expression split()[<index>], you can use length(value.split(" ")) to calculate the indexes you need.

To get the last <n> words in any sentence, do a transform with:
value.split(" ")[length(value.split(" "))-<n>] + " " +
value.split(" ")[length(value.split(" "))-<n-1>] + " " +
value.split(" ")[length(value.split(" "))-<n-2>] + " " +
value.split(" ")[length(value.split(" "))-<n-3>] +
etc. until <n-whatever> = 1

ex. If I want the last 4 words in any sentence, my transform GREL would be:
value.split(" ")[length(value.split(" "))-4] + " " +
value.split(" ")[length(value.split(" "))-3] + " " +
value.split(" ")[length(value.split(" "))-2] + " " +
value.split(" ")[length(value.split(" "))-1] 

Again, you will get unexpected results if your sentence is less than n words because negative indexes will wrap around. Either facet the short sentences away or use an if function:

 if (length(value.split(" ")) ><n>, value.split(" ")[length(value.split(" "))-<n>] + " " + value.split(" ")[length(value.split(" "))-<n-1>] + " " + value.split(" ")[length(value.split(" "))-<n-2>] + " " + value.split(" ")[length(value.split(" "))-<n-3>] + <etc.>, value)

Custom faceting using booleans

So, here's some sample data:

For the editing I want to do, I would like only the lines with v.<number>(<year>)-v<number>(<year>)

Since all of the notations vary, I tried the custom facet:
value.startsWith("v")

The problem is that lines containing data like this weren't excluded:
v.34(2008)-;v.1(1974)-v.26(2000)

Walkthrough on writing a complex if function

Say that from this sample data of a 538 field from video game records, you're only supposed to remove "System requirements" from the entries with a video game console:

System requirements: Nintendo 64

System requirements: Nintendo 64

System requirements: Nintendo 64; designed for N64 Rumble Pak

System requirements: PlayStation 2

System requirements: PlayStation 2

System requirements: Windows 95/98; 133MHz Pentium or faster processor; Windows 95/98; 64MB RAM; Microsoft DirectX 6.0 (included); quad speed CD-ROM drive; PCI or AGP graphics card; 4MB 3D accelerator for 3D graphics support (optional); 16-bit sound card; keyboard and mouse; joystick (optional); gamepad (optional)

System requirements: PlayStation 2

System requirements: PlayStation 2, memory card (for PS2) 94 KB

System requirements: PlayStation 2

System requirements: Nintendo 64. Designed for N64 Rumble Pak

System requirements: Nintendo Entertainment System

System requirements: Windows 98SW/ME/2000/XP; Pentium III 800 MHz or faster; 256 MB RAM; ; 2.5 GB free hard disk space; 32 MB hardware T&L compatible video card; Windows compatible sound card; DirectX version 9.0c (included) or higher;  8x or faster CD-ROM drive

System requirements: PlayStation 2, memory card (for PS2) 177 KB

Introduction to loops and forRange tutorial with variable data

Problem : I have a column of cells with variable date ranges:
1907-1913
1910-1911
1908-1911
etc.

The cells have to be converted to:
1907; 1908; 1909; 1910; 1911; 1912; 1913
1910; 1911
1908; 1909; 1910; 1911

etc.

Open Refine can be used to convert this. Using add column based on this column, you would use the following GREL command:

forRange(toNumber(substring(value, 0,4)), toNumber(substring(value, 5,9)) + 1, 1, currentYear, currentYear).join("; ")

Yes, it looks daunting. But let's break it down, starting with the forRange command: