OpenRefine – Cleaning and Transforming Messy Data

OpenRefine Cleaning and Transforming Messy Data Data Days Niedersachsen 2025 - Virtual Theme Day 26.11.2025 Instructor: Hoang Phuong Tu eResearch Alliance Georg-August University of Göttingen Material available at: https://datacarpentry.github.io/openrefine-socialsci/ Teaching materials based on Data Carpentry - OpenRefine Lesson, DOI: https:/doi.org/10.5281/zenodo.7916232 Presentation created by the eResearch Alliance, Georg-August University of Göttingen What you need today: •OpenRefine installed (we recommend using the latest stable version 3.8.7) https://openrefine.org/download.html •A Web browser (Firefox or Chrome recommended –NOT Internet Explorer) •The Dataset SAFI_openrefine.csv •https://ndownloader.figshare.com/files/11502815 Starting OpenRefine: •Windows: double-click on openrefine.exe •MacOS: launch OpenRefine from Applications folder •Linux: run ./refine in the OpenRefine directory If you are using a different browser, or OpenRefine does not automatically open for you, point your browser at http://127.0.0.1:3333/ or http://localhost:3333 to launch the program. Data Cleaning with OpenRefine Dataset •Available at: https://ndownloader.figshare.com/files/11502815 •Subset of data of the Studying African Farmer-Led Irrigation (SAFI) database •Dataset from interviews of farmers in Mozambique and Tanzania •Interviews conducted between November 2016 and June 2017 •Data contains information on household features, agricultural practices and assets •Intentionally messed up data Data Cleaning with OpenRefine Why OpenRefine? •Tools to identify and amend messy data •Documentation of data cleaning steps •Leave raw data untouched •Undo/redo of steps, also on other files •Easy use of complex algorithms •Open source •Large community •Works with datasets up to 100.000 rows, extendable Data Cleaning with OpenRefine Getting started with Open Refine Data Cleaning with OpenRefine 1. Launch OpenRefine (see Getting Started with OpenRefine). 2. Click Create Project and select Get data from This Computer. 3. Click Choose Files and select the file SAFI_openrefine.csv. Click Open or double-click on the filename. 4. Click Next>> under the browse button to upload the data into OpenRefine. 5. OpenRefine gives you a preview –check it. If this is the wrong file, click <<Start Over (upper left). 6. Please uncheck the option Trim leading & trailing whitespace from strings 7. If all looks well, click Create Project>> (upper right). Faceting with Open Refine Data Cleaning with OpenRefine •to get an overview of the data in a project •to bring more consistency to the data A ‘Facet’ groups the values that appear in a column according to some criterion, then allows you to filter the groups and edit values across many records at the same time. Exercise 1: 1. Scroll over to the village column. 2. Click the down arrow and choose Facet > Text facet. 3. Try sorting this facet by name and by count. Do you notice any problems with the data? What are they? 4. Hover the mouse over one of the names in the Facet list. You should see that you have an edit function available. Faceting with Open Refine Data Cleaning with OpenRefine Exercise 2: 1. Using faceting, find out how many different interview_date values are represented in the survey results. 2. Is the column formatted as Number, Date, or Text? How does changing the format change the faceting display? 3. Use faceting to produce a timeline display for interview_date. You will need to use Edit cells > Common transforms > To date to convert this column to dates. 4. During what period were most of the interviews collected? •to get an overview of the data in a project •to bring more consistency to the data A ‘Facet’ groups the values that appear in a column according to some criterion, and then allows you to filter the groups and edit values across many records at the same time. Clustering data with Open Refine Data Cleaning with OpenRefine • “finding groups of different values that might be alternative representations of the same thing” •very powerful tool for cleaning datasets which contain misspelled or mistyped entries Exercise 3: 1. In the village Text Facet we created in the step above, click the Cluster button. 2. In the resulting pop-up window, you can change the Method and the Keying Function. Try different combinations to see what different mergers of values are suggested. 3. Select the key collision method and metaphone3 keying function. It should identify three clusters. 4. Click the Merge? box beside each, then click Merge Selected and Recluster to apply the corrections to the dataset. 5. Try selecting different Methods and Keying Functions again, to see what new merges are suggested. You may find there are still improvements that can be made, but don’t Merge again; just Close when you’re done. We’ll now see other operations that will help us detect and correct the remaining problems, and that have other, more general uses. 6. You should find that using the default settings, no more clusters are found, for example to merge RuacaNhamuenda with Ruaca or Chirdozo with Chirodzo. (Note that the nearest neighbor method with ppm distance, radius ≥ 4, and block chars ≤ 4 will find these clusters, as well as other settings with levenshtein distance) 7. To merge these values we will hover over them in the village text facet, select edit, and manually change the names. Change Chirdozo to Chirodzo and Ruaca-Nhamuenda to Ruaca. You should now have four clusters: Chirodzo, God, Ruaca and 49. Transforming data Data Cleaning with OpenRefine •Data in the items_owned column is a set of items in a list •Before splitting the list into individual items, remove the brackets and the quotes Exercise 4: 1. Click the down arrow at the top of the items_owned column. Choose Edit Cells > Transform… 2. In the pop-up, first remove all left square brackets ( [ ). In the Expression box type value.replace(“[“, “”) and click OK. 3. What the expression means is this: Take the value In each cell in the selected column and replace all of the “[“ with “” (i.e. nothing – delete). 4. Click OK. You’ll get see in the items_owned column that there are no longer any left square brackets.. 5. Use the same strategy to remove the single quote marks ( ‘), the right square brackets( ] ), and spaces from the items_owned column. Sorting data with Open Refine (2) Data Cleaning with OpenRefine •sort by multiple columns by performing sort on additional columns •to restart the sorting process with the current column, check the Sort by this column alone box Exercise 9: We discovered in an earlier lesson that the value for one of the village entries was given as 49. This is clearly wrong. By looking at the GPS coordinates for the entries of the other villages can we decide what village the data in that column was collected from. 1. Sort on gps_Latitude as a number with the smallest first? 2. Add a sort on gps_Longiitude as a number with the smallest first. 3. Using the drop down arrow on the village column, select Edit column > Move column to end. This will allow you to compare cillage names with GPS coordinates. 4. Scroll through the entries untill you find village 49. Can you tell from it‘s GPS coordinates which village it belongs to? 5. Now sort only by interview_date as date. Move the village column to the start of the table. Does the row where village is 49 group with one articular village? Is it the same village as when comparing GPS coordinates? 6. Perform a text facet on the village column and change 49 to the village name that was determined in the previous exercise. Examining numbers in Open Refine (1) Data Cleaning with OpenRefine •using data types can ease identifying errors in data or cleaning up •Click Edit cells > Common transforms to change the data type for one column Exercise 10: 1. Remove any text filter facets from the left panel. 2. Transform the cells in the column years_farm to numbers by using Edit cells > Common transforms > To number. What can you observe in the display of that column? 3. Transform three more columns to numbers: no_members,years_liv and buildings_in_compound. Can all columns be transformed to numbers? Examining numbers in Open Refine (2) Data Cleaning with OpenRefine •non-number values or blanks in a numerical column may represent errors in data •we can find them with a Numeric facet Exercise 11: 1. For a column you transformed to numbers, edit one or two cells, replacing the numbers with text (such as abc) or blank (no number or text). Change the Data type to Text. 2. Use the pulldown menu to apply a numeric facet to the column you edited. The facet will appear in the left panel. 3. Notice that there are several checkboxes in this facet: Numeric, Non-numeric, Blank, and Error. Below these are counts of the number of cells in each category. You should see checks for Non-numeric and Blank if you changed some values. 4. Experiment with checking or unchecking these boxes to select subsets of your data. 5. When you are done, undo your changes and remove the facet. Scripts from Open Refine Data Cleaning with OpenRefine •OpenRefine saves every change you make to the dataset •changes are saved in a script file format known as JSON (JavaScript Object Notation) •you can export this script and apply it to other data files ➢quick way to clean all of your related data, for many files with equal structure and similar errors Exercise 14: 1. In the Undo / Redo section, click Extract..., and select the steps that you want to apply to other datasets by clicking the checkboxes. 2. Copy the code from the right hand panel and paste it into a text editor. Make sure it saves as a plain text file. 3. Start a new project in OpenRefine with the original raw data file and name it something different from your existing project. 4. Click the Undo / Redo tab > Apply and paste in the contents of txt file with the JSON code 5. Click Perform operations. The dataset should now be the same as your other cleaned dataset. Exporting and Saving Data from Open Refine Data Cleaning with OpenRefine •OpenRefine automatically saves your project with all data and all performed operations •exporting projects can be useful to exchange not only data, but also supplementary information •exported projects can also be imported to OpenRefine (e.g. to continue work on another computer) Exercise 15: 1. Click the Export button in the top right and select OpenRefine project archive to file. 2. A tar.gz file will download to your default Download directory. The tar.gz extension tells you that this is a compressed file, which means that this file contains multiple files. 1. On Mac and Linux, you can double-click on the tar.gz file and it will expand into a directory. A folder icon will now appear. 2. On Windows, opening tar.gz files requires additional software such as 7-zip or WinZip. 3. Look at the files that appear in this folder. What files are here? What information do you think these files contain? 4. Export only your cleaned data by clicking Export in the top right, and then selecting a file format. Which file formats would be a good choice? Other resources in OpenRefine Data Cleaning with OpenRefine •You can find out a lot more about OpenRefine at http://openrefine.org and check out some great introductory videos or the manual . •There is a Google Group that can answer a lot of beginner questions and problems. •OpenRefine recipes, scripts, projects, and extensions are available on github, where you can find and copy them into your OpenRefine instance to run on your dataset. •The OpenRefine GitHub wiki page has a reference of the General Refine Expression Language (GREL). •Grateful Data is a fun site with many resources devoted to OpenRefine, including a nice tutorial. •Margaret Heller shows how she uses OpenRefine for Measuring and Counting Impact in Repositories. •Intersect Course Resources has Jared Berghold’s Cleaning & Exploring your data with Open Refine.