OpenRefine Recipes

by OpenRefine GitHub repository contributors

This page collects OpenRefine recipes, small workflows and code fragments that show you how to achieve specific things with OpenRefine.

Available at this URL: https://github.com/OpenRefine/OpenRefine/wiki/Recipes

Including:

String Manipulation

Here are some examples of possible types of common string manipulation operations that you might encounter and how they can be achieved with the Refine Expression Language (GREL). See also GREL String Functions.

• Change “2010-05-31T01:10:0Z” to “05/31/2010”
• Trim whitespace from beginning and end of values
• Titlecase that works on hyphenated names
• Replacing Chars, Punctuation, etc using regular expressions
• “blah_2342_blah_1232” -> “2342_blah_1232”
• “ac:d:e” -> “b:c:d”
• Pad with leading zeroes
• Separate letters and digits e.g. Aug13 -> Aug 13
• Parse an IP address to extract a Country Name, Latitude & Longitude
• Get HTTP Response Header field (e.g. redirect location
• Parse JSON and Create Custom Arrays using forEach()
• Removing duplicate rows when Exact values are found in a column
• Handling duplicate patterns found in cells within a column
• Facet and Count duplicate patterns found in a cell value at each row
• Create a new column based on the value of “Star” or “Flag”
• Find a sub pattern that exists at the end of a string
• Remove the last word in a string
• “00003400340300004” -> [“000034″,”0034″,”03″,”00004”]
• split / map / join
• Merging all columns in a project
• Facet for rows with a certain number of blank cells
• Rows with exactly one blank cell (true/false facet)
• Rows with less than two blank cells (true/false facet)
• XML parsing & stripping
• Replacing diacritic (accent) characters
• “Aïn Témuchent” —> “Aïn Témuchent”
• Question Marks � showing in your data

Numerical Conversions

• Convert to Decimal Latitude or Longitude
• Convert Epoch time to Date/Time as String

Error Handling

• forNonBlank

 

 

Spot Potential Encoding Issues

Spot Values Potentially Placed in the wrong Column

ISBN Calculations & Manipulations

• ISBN-10
• Convert an ISBN-10 to ISBN-13

 

Shift values in multiple rows

• Parse and format Phone Numbers
• Remove/extract words contained in a file

 

Archived using Internet Archive on 4/27/2017:  https://web.archive.org/web/20170427193504/https://github.com/OpenRefine/OpenRefine/wiki/Recipes