How to make your messy data usable? / OpenRefine

How to make your messy data usable? Diana Pilvar Data Manager Institute of Computer Science University of Tartu Training coordinator ELIXIR-Estonia [email protected] General Information Please write your name for attendance check If you have questions, you can ask them right away Learning outcomes Describe spreadsheet best practices Compare Excel and OpenRefine Apply transforms (cell editing, column editing, transposing) in OpenRefine Write simple expressions in OpenRefine Match your dataset with that of an external source Poll What does a good reusable spreadsheet look like? When you re-used somebody else’s tables, how easy was to understand it? What kind of mistakes have you found in tables? Spreadsheet best practices Based on : Karl W. Broman & Kara H. Woo (2018) Data Organization in Spreadsheets, The American Statistician, 72:1, 2-10, DOI: 10.1080/00031305.2017.1375989 https://datacarpentry.org/spreadsheet-ecology-lesson/02-common-mistakes#commonspreadsheet-errors Principle 1: Be Consistent DO USE DO NOT USE (in the same project) consistent codes for categorical variables. “Male”, “M” and “male” consistent fixed code for any missing values “NA” and sometimes “-” and “ “ consistent variable names “Glucose_10wk” and “gluc_10weeks,” and “10 week glucose” consistent subject identifiers “153”; “mouse153”, “Mouse153”, “mouse-153” consistent data layout in multiple files. Different data layout consistent file names. “Serum_batch1_date” and “Batch2_serum_date” consistent format for all dates (YYYY-MM-DD) “8/1/2015” and “8-1-15” consistent phrases in your notes. “dead” and “Deceased” Be careful about extra spaces within cells. blank cell ≠ single space; “male” ≠ “ male ” Examples Good Name Good Alternative Avoid Max_temp_C MaxTemp Maximum Temp (°C) Precipitation_mm Precipitation precmm Mean_year_growth MeanYearGrowth Mean growth/year sex sex M/F weight weight w. cell_type CellType Cell Type Observation_01 first_observation 1st Obs Principle 2: Use meaningful file names Try not to use spaces It makes programming harder. Analyst needs to surround everything in “” Prefer underscores _ and hyphens - Avoid special characters ~ ! @ # $ % ^ & * ( ) ` ; < > ? , [ ] { } ' " | They often have special meanings in programming languages Keep it short, but meaningful Avoid using word “final” There will always be “final_2” Principle 3: Write Dates as YYYY-MM-DD global ISO 8601 standard Microsoft Excel and dates*. Different export problems on Mac and Windows Solutions: Store year, month and day in separate columns or YYYYMMDD or ‘YYYYMMDD Everything is not a date: Format →Cells, choose “Text” https://xkcd.com/1179/ * https://datacarpentry.org/spreadsheet-ecology-lesson/03-dates-as-data.html Raise a hand if… You open an Excel file and start typing and nothing happens, and then you select a cell and you can start typing. Where did all of that initial text go? Well, sometimes it got entered into some random cell, to be discovered later during data analysis. Principle 9: Do Not Use Font Color or Highlighting as Data Suspicious data or data that should be ignored add another column with an indicator variable (e.g., ”trusted” or “outlier” with values TRUE or FALSE) Principle 10: Make Backups Principle 11: Use Data Validation to Avoid Errors Check for each column Do values fall into expected range Example: blood pressure normal range 90-140 mm/Hg systolic; values 300 or 40 are not expected A list of possible values Cities in a country Clinical diagnosis Text, in the expected length Postal code Principle 12: Save the Data in Plain Text Files Keep a copy in .csv or .tsv Not pretty, but can be opened with a variety of different programs Poll Have you been using these best practices? Yes, all of them Yes, some of these practices I have heard of them, but implementation is hard No, never heard, haven’t used them Will you try to use them now? Yes I will try (no promises) No (I like my way better) Summary Be consistent Write dates like YYYY-MM-DD No empty cells, no merged cells Put just one thing in a cell Organize the data as a single rectangle Create a data dictionary Based on : Karl W. Broman & Kara H. Woo (2018) Data Organization in Spreadsheets, The American Statistician, 72:1, 2-10, DOI: 10.1080/00031305.2017.1375989 https://datacarpentry.org/spreadsheet-ecology-lesson/02-common-mistakes#common-spreadsheet-errors Do not include calculations in the raw data files Do not use font color or highlighting as data Choose good file names Make backups Use data validation to avoid data entry errors Save the data in plain text files. Any questions? Previous name Google Refine, created by Metaweb Technologies, Inc Acquired by Google in 2010, October 2012 renamed OpenRefine Available in more than 15 languages (not in Estonian) Keeps you data private until you are ready to share it Can run it using GUI or command line Practical lesson on OpenRefine Exercises I Reordering and renaming columns Sorting data Filtering data Clustering Undo/redo Exporting data Based on materials (introduction) developed by Owen Stephens on the behalf of the British Library. CC-BY 4.0 license. http://www.meanboyfriend.com/overdue_ideas/wp-content/uploads/2014/11/Introduction-to-OpenRefine-handout-CC-BY.pdf Similar things done in https://librarycarpentry.org/lc-open-refine/index.html OpenRefine Installation https://openrefine.org/download Install OpenRefine 3.9.5 version (or newer version). There are versions for Windows, Linux and Mac OS X with additional teaching on how to download it. Windows and Linux needs Java to be installed before installing OpenRefine, although there is the Windows version with Java included also. Mac users need to give permission to the program Extra guidance: https://openrefine.org/docs/manual/installing Running OpenRefine Open program command line window - Ignore that Put http://127.0.0.1:3333/ in the web browser Compatible browsers Google Chrome Chromium Opera Microsoft Edge Safari Minor rendering and performance issues on other browsers such as Firefox. Internet Explorer not supported. File formats accepted by OpenRefine comma-separated values (CSV) or text-separated values (TSV) Text files Fixed-width columns JSON XML OpenDocument spreadsheet (ODS) Excel spreadsheet (XLS or XLSX) PC-Axis (PX) MARC RDF data (JSON-LD, N3, N-Triples, Turtle, RDF/XML) Wikitext How it looks like Increasing memory allocation default 1 gigabyte (GB) of memory (1024MB). If you… feel that OpenRefine is running slowly, or you are getting “out of memory” errors Have more than one million total cells Have an input file size of more than 50 megabytes (MB) Have more than 50 rows per record in records mode A good practice is to start with no more than 50% of whatever memory is left over after the estimated usage of your operating system Detailed instructions for Windows, Mac and Linux https://openrefine.org/docs/manual/installing#increasing-memory-allocation Creating a project Click ‘Create Project’ Choose ‘Get Data from this Computer’ Click ‘Choose Files’ Locate the file called ‘Practice_dataset.csv’ Click ‘Next’ Importing data Test which version works for you Columns are separated by Custom → ; commas (CSV) Parse next 1 line(s) as column headers Store blank rows UTF-8 Saving OpenRefine saves all of your actions (everything you can see in the Undo/Redo panel). It doesn’t save facets filters view number of rows showing Sorting column collapsing Autosaving default every five minutes To save current facets and filters, click Permalink. The project will reload with a different URL, which you can then copy and save elsewhere. TASK: Undo and re-order Let’s keep the shelfmark column after all but reorder the columns again! If you have undone something, it asks for confirmation to rewrite history TASK: Undo and re-order Sorting Column → Sort.. Sort by publication year, smallest to largest Sorting Now we have sort drop-down menu Unlike Excel ‘Sorts’ in OpenRefine are temporary remove the ‘Sort’, the data will go back to its original ‘unordered’ state. ‘Sort’ drop down menu reverse the sort order remove existing sorts make sorts permanent You can sort on multiple columns at the same time. TASK TASK: Sort author name from A → Z. See what happened Try Reverse sort Remove Author sort Go to Undo/Redo tab Notice how sorting is absent Reorder rows permanently by Publication year Go to Undo/Redo tab Sorting can now be undone Text filter Column drop down menu → Text filter Look left on the sidebar Try typing London displays only the rows containing that particular phrase Try other cities: Cambridge After you are done with this facet/filter, close it. or it will affect your future analysis Facet Place of publication → Facet → Text facet Sort by count Facet Use case overview of the data in a project Easy to notice typos/ inconsistencies Click on include. See what it does You can also change values directly from here using Edit ❗ Data validation principle Facet Year of publication → Facet → Text facet Sort by count Notice there are brackets where there shouldn’t be Clustering Clustering function enables you to find similar values across a facet and merge them together. example “New York” and “new york” More information about the algorithms https://openrefine.org/docs/technicalreference/clustering-in-depth ❗ Be consistent principle Task Year of publication → Edit cells → Cluster and editClick on Cluster Try multiple methods and keying functions! Check if facet by numbers now has all numerical values If not Try clustering Try editing in text facet Don’t forget to transform again to numbers Check the Place of Publication too. ❗ Be consistent principle ❗ Data validation principle Exporting the workflow OpenRefine saves every change you make. JSON (Javascript Object Notation) export JSON script and apply it to other data files ❗ Backups principle Reproducibility Exporting table In the right corner there is Export Importing the workflow If you have multiple files to clean they all have the same type of errors have the same column names Save the JSON script, open a new file in OpenRefine, paste the script and run it. This gives you a quick way to clean all of your related data. Poll Have you understood things so far? What remains confusing? Tip: Ask advice from AI chatbots, they know some OpenRefine! Be careful with GREL commands. A small break Practical lesson on OpenRefine Exercises II GRELbased transformations Extensions Reconciliation Based on materials (introduction) developed by Owen Stephens on the behalf of the British Library. CC-BY 4.0 license. http://www.meanboyfriend.com/overdue_ideas/wpcontent/uploads/2014/11/Introduction-to-OpenRefine-handout-CC-BY.pdf Transforms in OpenRefine clean correct codify extend your data NB! Transforms are one click away, no need to write code. GREL syntax General Refine Expression Language. In GREL, functions can use either of these two syntaxes: functionName(value, options) value.functionName(options) Where value means the values in the current column Full notation Dot notation functionName(value, options) value.functionName(options) trim(value) value.trim() length(trim(value)) value.trim().length() https://docs.openrefine.org/manual/grel GREL syntax Example Description FirstName.cells Access the cell in the column named “FirstName” of the current row cells["First Name"] Access the cell in the column called “First Name” of the current row row.columnNames[4] Will return the name of the fifth column Dot notation can be used to access the member fields of variables For referring to column names that contain spaces, use square brackets instead of dot notation Square brackets to get substrings and sub-arrays, and single items from arrays https://docs.openrefine.org/manual/grel GREL functions String functions Length of string as a number Takes any value type (string, number, date, boolean, error, null) and gives a string version of that value Can use rounding of numbers Test if a string starts/ends with a certain letter or contains it Case conversion Remove leading and trailing whitespace or specified letters (Trimming) Substrings Find and replace String parsing and splitting Encoding and hashing Detect language https://docs.openrefine.org/manual/grelfunctions GREL functions Boolean functions Logical operator to evaluate conditions with output being True/False Format-based functions Jsoup XML and HTML parsing URI parsing (when given a link) Array functions Size of array Creating sub-array Checking if array contains desired string Reverse array Sort array Sum array Join the items in the array with sep, and returns it all as a string Duplicate removal https://docs.openrefine.org/manual/grelfunctions GREL functions Date functions Current time according to your system clock Convert object to date Given two dates, returns a number indicating the difference in a given time unit Change a date by the given amount in the given unit of time Return part of a date Math functions Other functions Count given value Cross-information https://docs.openrefine.org/manual/grelfunctions Transformations value.toUppercase() toUppercase(value) converts the current value to uppercase value.toLowercase() toLowercase(value) converts the current value to lowercase value.toTitlecase() toTitlecase(value) converts the current value to titlecase (i.e. each word starts with an uppercase character, and all other characters are converted to lowercase value.trim() trim(value) removes any “whitespace” characters (e.g. spaces, tabs) from the start or end of the current value value.substring(number from, optional number to) substring(value, number from, optional number to) finds the first (number from) X (number to) characters of the current value value.replace(“string to find”, “replacement string”) replace(value, “string to find”, “replacement string”) finds the letter “X” in the current value and replaces it with the letter “Y” “string“ + value “string“ + value adds (concatenates) the world “XXXX” to the front of the current value TASK: Harmonize author names Put the names in Title Case: Use Facets (to see what is wrong) Transform GREL: value.toTitlecase() Python: return value.title() ❗ Be consistent principle Answer: Title Case Use Facets and the GREL expression value.toTitlecase() to put the titles in Title Case Let’s look at the “Author” column (Text facet) We see several things: Some names are written FIRSTNAME LASTNAME Some names are written LASTNAME, FIRSTNAME Some have one name in capital letters Some have all names in capital letters Click the dropdown menu on the “Author” column Choose Edit cells→Transform… In the Expression box type value.toTitlecase() Click OK ❗ Be consistent principle TASK: Using Boolean functions to fix author names a crude test → looking for commas Author → Facet→Custom text facet... In the Expression box type value.contains(",") or return bool("," in value) (Python) ‘contains’ function outputs a Boolean value, facet that contains ‘false’ and ‘true’. Python one gives 0 and 1. 0 meaning false, 1 meaning true ❗ Be consistent principle Include “true” boolean facets On the “Author” column, use the dropdown menu and select Edit cells →Transform Expression value.split(", ") or return value.split(", ") include a space after the comma inside the split expression to avoid extra spaces in your author name later See how this creates an array with two members in each row in the Preview column UNFORTUNATELY, arrays cannot appear directly in an OpenRefine cell any more So if you apply the command, nothing happens visually TASK: Using Boolean functions to fix author names ❗ Be consistent principle To find both the ’s’ and ‘z’ spellings of ‘organize/organise’): /organi.e/ Specify exact numbers of repetitions or a max/min number Use curly brackets: /a{2}/ Matches ‘aa’ /a{2,4}/ Matches any of ‘aa’, ‘aaa’, ‘aaaa’ Regular expressions (Regex) http://www.meanboyfriend.com/overdue_ideas/wp-content/uploads/2014/11/Introduction-to-OpenRefine-handout-CC-BY.pdf https://docs.openrefine.org/manual/expressions#expressions Regular expressions These can be combined with ‘repetition’ operators, which allow you to say how many times a character or pattern is repeated. Repetition character Meaning Explanation/Example * The preceding character/expression can be repeated any number of times (including 0) /.*/ Any text string at all (any character repeated any number of times + The preceding character/expression can be repeated one or more times /head\s+/rest/ Matches “head rest” (one space), “head rest” (two spaces), but not “headrest” ? The preceding character/expression can be repeated 1 or 0 times /colou?r/ Matches both words “color” and “colour” {X} The preceding character/expression can be repeated X number of times /a{2}/ Matches the letter “a” appearing twice (“aa”) /a{2,4}/ Matches the letter “a” appearing a minimum of two times or maximum of four times (“aa”, “aaa”, “aaaa”) GREL-supported regex Wrap regex between a pair of forward slashes (/). For example, in value.replace(/\s+/, " ") the regular expression in here is \s+, and the syntax used in the expression wraps it with forward slashes (/\s+/). \s any whitespace character (spaces, tabs, newlines, etc.) X+ X occurring one or more times Do not use slashes to wrap regular expressions outside of a GREL expression. On the GREL functions page, functions that support regex will indicate that with a “p” for “pattern.” https://docs.openrefine.org/manual/grel https://docs.openrefine.org/manual/grelfunctions Jython-supported regex Jython is an implementation of the Python programming language designed to run on Java Interact with regular expressions via the built-in re module in Python Python code that depends on C bindings will not work in OpenRefine, which uses Java / Jython only. Since Jython is essentially Java, you can also import Java libraries and utilize those. Expressions must have a return statement import re return re.sub("\s+", " ", value) https://docs.openrefine.org/manual/jythonclojure https://docs.openrefine.org/manual/expressions#jythonsupported-regex Other functions listed here: https://www.pythontutorial.net/python-regex/python-regular-expressions/ How-To https://docs.python.org/3/howto/regex.html Same command in GREL value.replace(/\s+/, " ") Clojure-supported regex Clojure is a dialect of the Lisp programming language on the Java platform Clojure treats code as data and has a Lisp macro system Clojure regexes are host language regexes. On the Java Virtual Machine (including Openrefine) you're using Java regexes. In ClojureScript, it's Javascript regexes. Regex patterns can be compiled at read-time via the #"pattern" reader macro, or at run time with re-pattern (clojure.string/replace value #"\s+" " ") https://docs.openrefine.org/manual/jythonclojure https://docs.openrefine.org/manual/expressions#clojure-supported-regex Same command in GREL value.replace(/\s+/, " ") Same command in Jython import re return re.sub("\s+", " ", value) Regex in OpenRefine https://github.com/OpenRefine/OpenRefine/wiki/Recipes This page collects OpenRefine recipes, small workflows and code fragments that show you how to achieve specific things with OpenRefine. Regex examples on your cheat sheet Here is 2 cheat sheets with regex: https://code4libtoronto.github.io/2018-10-12-access/GoogleRefineCheatSheets.pdf https://datenschule.de/files/downloads/workshops/CheatSheet-Open-Refine.pdf If this is your first time working with regex, I recommend https://regexr.com/ this testing and learning tool (Supports JavaScript & PHP/PCRE RegEx) Another testing tool https://regex101.com/ TASK: Extracting dates of publication Work only with records, where “Year of Publication” is blank “Place of Publication”→ Edit column → add a column based on this column function Use “match” function with a regular expression to find where the “Place of Publication” ends with four digits Tips: [-1] use last part of array / / regex is inside these .*(\d{4}).* Regex. .* Any text string at all (any character repeated any number of times () group these d digits {4} match 4 of preceding token Solution: Extracting dates of publication Work only with records, where “Year of Publication” is blank Edit cells → Common transforms → to numbers Facet → Numeric facet → tick Blanks only “Place of Publication”→ Edit column → add a column based on this column function Use “match” function with a regular expression to find where the “Place of Publication” ends with four digits Option 1 value.split(",")[-1] return value.split(",")[-1] Keeps in [] Option 2 value.match(/.*(\d{4}).*/).join("") import re return "".join(re.findall("(\d{4})", value)) Tips: [-1] use last part of array / / regex is inside these .*(\d{4}).* Regex. .* Any text string at all (any character repeated any number of times () group these d digits {4} match 4 of preceding token Python TASK: Extracting dates of publication Move “new Date of Publication” year into “Year of Publication column” Remove the “new DoP” column Solution: Extracting dates of publication Move “new Date of Publication” year into “Year of Publication column” Year of publication → Edit columns→ Join columns Choose your 2 columns to be wedded Tick: Write results in selected columns NB! It writes into the column which drop-down menu you selected from the start. So if you chose new date column, it will just rewrite contents into new column. Remove the “new DoP” column New Date of Publication → Edit column → Remove this column Additional features Extensions Extensions have been created by OpenRefine community to add functionality or provide convenient shortcuts for common uses of OpenRefine. They might be out of date - look at the latest compatible version! List of extensions: https://openrefine.org/extensions Selection of useful extensions: GeoJSON Export Adds a Graphical User Interface (GUI) that allows you to export OpenRefine data to the GeoJSON format. Supports latitude/longitude coordinates. FAIR metadata Supports FAIR metadata by integrating with FAIR Data Point to store your data and export to FAIR. Stats extension for Google Refine 2.5+ Computes elementary statistics on column data. Reconciling Reconciliation is matching your dataset with that of an external source External dataset must offer a web service that conforms to the Reconciliation Service API standards Reconciliation is semi-automated: OpenRefine matches your cell values as best it can Human judgment is required to review and approve the results Typos, whitespace, and extraneous characters will have an effect on the results Clean and cluster your data before reconciliation https://docs.openrefine.org/manual/reconciling Reconciling You may wish to reconcile in order to: Fix spelling or variations in proper names Clean up manually-entered subject headings against authorities Link your data to an existing dataset Add to an editable platform such as Wikidata See whether entities in your project appear in some specific list, such as the Panama Papers. https://docs.openrefine.org/manual/reconciling Reconciliation sources Current list of reconcilable authorities https://reconciliation-api.github.io/testbench/#/ Further list of sources on the wiki https://github.com/OpenRefine/OpenRefine/wiki/Reconcilable-Data-Sources Ways that you can reconcile against a local dataset https://github.com/OpenRefine/OpenRefine/wiki/Reconcilable-Data-Sources#localservices You can reconcile against the entire dataset or only the contributions from certain institutions https://refine.codefork.com/ Reconciling with Wikibase https://docs.openrefine.org/manual/wikibase/reconciling Extensions can add reconciliation services, and can also add enhanced reconciliation capacities. How-to reconcile Chosen columns dropdown menu → Reconcile → Start reconciling If you want to reconcile only some cells in that column, first use filters and facets to isolate them Reconciliation window Wikidata as a default service To add another service, click Add Standard Service... and paste in the URL of a service You should see the name of the service appear in the list of Services if the URL is correct https://docs.openrefine.org/manual/reconciling#getting-started How-to reconcile Choose “types” (categories) You can reconcile batches against different types Time-consuming process, especially with large datasets. Start with a small test batch If the cell was successfully matched, it displays text as a single dark blue link. You should not have to check it manually. If there is no clear match, one or more candidates are displayed, together with their reconciliation score, with the text in light blue links. You will need to select the correct one. https://docs.openrefine.org/manual/reconciling#getting-started How-to reconcile For each matching decision you make, you have two options: Match this cell only (one checkmark) Use the same identifier for all other cells containing the same original string (two checkmarks). “preview entities” feature For matched values the underlying cell value has not been altered - the cell is storing both the original string and the matched entity link at the same time. https://docs.openrefine.org/manual/reconciling#getting-started Automatic reconciliation facets Reconcile → Facets Automatically creates two facets when you reconcile some cells Numeric facet for “best candidate's score” Approve them all in bulk by using Reconcile → Actions → Match each cell to its best candidate Judgment facet Lets you filter for the cells that haven't been matched https://docs.openrefine.org/manual/reconciling#reconciliation-facets Reconciliation facets Useful for doing successive reconciliation attempts The information is held in the cells themselves https://docs.openrefine.org/manual/reconciling#reconciliation-facets Task: Rodent dataset Reconcile scientific names with the Encyclopedia of Life (EOL) Firstly it gets a standard form of the name or label for the entity. Secondly it gets an ID for the entity - in this case a page and numeric id for the scientific name in EOL. This is hidden in the default view, but can be extracted: In the scientificName column use the dropdown menu to choose Reconcile > Add entity identifiers column... Give the column the name “EOL-ID” This will create a new column that contains the EOL ID for the matched entity Reconcile country, state, and counties against Wikidata https://datacarpentry.github.io/OpenRefine-ecology-lesson/06-reconciliation.html BONUS Tasks Open dataset Ask_a_manager_salary_survey.csv https://oscarbaruffa.com/messy/ Give good names to variables Using Facets find outliers Example: young but significant work experience (more than their age) Harmonise country,county and industry Replace blanks and 0-s in Additional monetary compensation Reformat Timestamp Split race into multiple columns Fix anything else you see is messy Take away message Excel can do data cleaning, but it will take extra steps and the workflow will not be recorded. Excel has 477 functions, but it pales in comparison with all the Python and Java libraries you can import with Jython. In Excel you can use macros written in Visual Basic for Applications (VBA) and write custom functions Microsoft considers macros a security risk Subjective graph depicting relation between ease of use and variety of problems a tool can solve. Poll Will you use OpenRefine for messy data cleaning? Take away message OpenRefine was made for dealing with messy data Is open source and free to use Workflows are reproducible Keeps your data private Common transformations allow you to do advanced data cleaning without writing code Custom transformations can be written in expression editor You can combine regexes, functions and controls Openrefine supports many extensions that enhance its functionality You can reconcile data with external sources Feedback https://forms.gle/SeZqpXQYB6sG8JG7A References: ELIXIR and OpenRefine ELIXIR-Estonia https://elixir.ut.ee/ Subscription list: News about courses and events organised by ELIXIR Estonia https://lists.ut.ee/wws/subscribe/elixir.news?previous_action=edit_list_request OpenRefine User Manual https://docs.openrefine.org/ OpenRefine cheatsheets https://github.com/OpenRefine/OpenRefine/wiki/Recipes Introduction to OpenRefine by Owen Stephens ([email protected]) on behalf of the British Library in July 2014 http://www.meanboyfriend.com/overdue_ideas/wpcontent/uploads/2014/11/Introduction-to-OpenRefine-handout-CC-BY.pdf List tutorials and resources developed outside the OpenRefine user manual https://github.com/OpenRefine/OpenRefine/wiki/External-Resources https://openrefine.org/external_resources Tutorial on reconciliation in OpenRefine https://www.youtube.com/watch? v=q8ffvdeyuNQ References: Excel limitations and Regex Excel limitations https://support.microsoft.com/en-us/office/excel-specifications-and-limits1672b34d-7043-467e-8e27-269d656771c3 Regex tutorials https://www.regular-expressions.info/ https://www.codeproject.com/Articles/939/An-Introduction-to-Regular-Expressions Regex cheat sheets https://datenschule.de/files/downloads/workshops/CheatSheet-Open-Refine.pdf https://code4libtoronto.github.io/2018-10-12-access/GoogleRefineCheatSheets.pdf Regex testing tools https://regexr.com/ https://regex101.com/ ‹#› References: Regex Python regex https://www.pythontutorial.net/python-regex/python-regular-expressions/ https://docs.python.org/3/howto/regex.html Jython regex https://www.jython.org/jython-old-sites/docs/library/re.html Learning Jython https://wiki.python.org/jython/LearningJython Clojure regex tutorial https://ericnormand.me/mini-guide/clojure-regex Thank you!