OpenRefine: cleaning messy survey and MIS data

Create a project from a CSV

An OpenRefine project is a copy of your data plus the history of everything you do to it. The original file on disk is never changed.

The practice file

The course uses households.csv: 240 households in ten districts. Illustrative data, invented for teaching The district names are real places; every number is made up. That file is clean, so the cell below makes a messy copy of it, the kind a field team actually sends: every third household gets a variant spelling of its district (wrong case, extra spaces, a full stop, an older spelling, a transliteration variant, a colonial-era name), some expenditure figures arrive as 2,580 with a comma, and a few say not asked. A location column holds district and state together.

The first block printed is the top eight rows and the size, 240 rows and 6 columns. Below the line is the whole file as CSV text.

Load it into OpenRefine

1. Run the cell above. Select the CSV text below the line "Copy everything below this line", from hh_id,district,... to the last row, and copy it.
2. In OpenRefine, on the Create project screen, choose Clipboard on the left, paste the text into the box and click Next ».
3. You now see the preview. Check that OpenRefine has read it as comma-separated values and that the first row became the column names. If a name such as Kozhikode showed odd characters, you would change the character encoding here (UTF-8 is the usual choice).
4. Type a project name, for example households messy, and click Create project ».

For your own files, choose This Computer and click Browse… instead of Clipboard. The manual lists CSV, TSV, Excel (XLS and XLSX), ODS, JSON and XML among the formats it reads, and it can open a ZIP or GZ archive directly.

See the problem: text, numeric and timeline facets

A facet summarises one column in the left-hand panel and lets you filter rows by clicking a value. It is the fastest way to see what a column really contains.

Text facet: every distinct spelling

1. Click the small triangle at the top of the district column.
2. Choose Facet → Text facet.
3. The facet panel lists every value with its count. Use the sort option at the top of the facet to sort by count instead of alphabetically. Click a value to show only those rows; click exclude beside it to show every row except those.

The cell below does the same count with pandas.

There are 29 distinct values for 10 districts. Look closely: Barmer appears twice (16 rows and 4 rows) because one of them carries a trailing space you cannot see, and the same is true of Gaya and Patna. Your OpenRefine facet should list the same 29 choices with the same counts. The "29 choices" link at the top of the facet copies the list as tab-separated text, which is a quick way to paste it into a report.

Numeric facet: which cells are not numbers

1. OpenRefine imported monthly_pc_exp as text. Convert it first with the data-type transform for numbers under Edit cells → Common transforms. A yellow bar at the top tells you how many cells were converted; cells that cannot be converted keep their original value. (For your own files, you can instead tick "Attempt to parse cell text into numbers" in the import preview.)
2. Then Facet → Numeric facet on the same column.
3. The facet draws a histogram and offers checkboxes for non-numeric, blank and error values. Tick only non-numeric to see the cells that did not convert.

Here 222 cells parse as numbers and 18 do not: twelve written with a thousands comma (such as 2,580) and six that say not asked. The banded counts below them are the histogram the facet draws; most households sit between Rs 1,500 and Rs 3,500. Module 5 fixes the commas.

Timeline facet: dates

A timeline facet works like a numeric facet for dates, and only on cells of the date type. The practice file has no date column, so try it on your own data, for example the submission time in a KoboToolbox or ODK export: convert the column with the data-type transform for dates under Edit cells → Common transforms (or the GREL expression value.toDate()), then choose Facet → Timeline facet. The facet also counts blank cells and cells that failed to convert, which is how you spot interviews logged on impossible dates.

Cluster spellings of the same place

Clustering finds values that are probably the same thing written differently and offers to merge them. Open it from the column menu: Edit cells → Cluster and edit…, or press Cluster in a text facet. The window offers two families of methods, and the OpenRefine manual recommends using them in the order shown, strictest first.

• Key collision: each value is turned into a key, and values with the same key form a cluster. The keying functions are Fingerprint, N-gram fingerprint, and four phonetic ones: Metaphone3, Cologne Phonetic, Daitch-Mokotoff and Beider-Morse. Fast, even on millions of cells.
• Nearest neighbour: values are compared in pairs and clustered when their distance is within a radius you set. The distance functions are Levenshtein (edit distance) and PPM. Slower, and looser.

Fingerprint first

The clustering documentation lists the fingerprint steps: trim spaces, lowercase, remove punctuation, turn accented Western letters into plain ASCII (gödel becomes godel), split into words, sort and de-duplicate the words, and join them again. The cell below follows those steps. It is our reimplementation, so OpenRefine's own keys may differ in small details.

Fingerprint finds six clusters: Barmer, Gaya, Kozhikode, Patna, Purnia and Udaipur, which between them hold the case, space and punctuation variants (GAYA, patna, Udaipur., the trailing spaces). It leaves nine values alone: Badmer, Baitul, Calicut, Indor, Kozhikkode, Purnea, Reewa, Wayanadu, Waynad. Those differ in their letters, and fingerprint never changes letters, which is why it produces so few false matches.

Merging in the window

1. Each row of the clustering window is one cluster, with its values, their row counts and a text box for the new value.
2. Pick one of the listed values to apply to every cell in the cluster, or type the official spelling into the text box, and mark the cluster for merging. Leave a cluster unmarked if its values are different places.
3. Click Merge selected & re-cluster. OpenRefine applies the merge and runs the method again on what is left.
4. Change the method and keying function, and repeat. Close the window when nothing sensible is left.

N-gram fingerprint and transliteration variants

N-gram fingerprint lowercases the value, strips spaces and punctuation, cuts it into overlapping pieces of n characters, and sorts the unique pieces. The cell compares four pairs with n = 2.

All four pairs get different keys. Kozhikkode has a doubled k, which adds the piece kk; Purnea has ea where Purnia has ia. Key collision only clusters exact key matches, so a single changed letter is enough to escape it.

Nearest neighbour for one-letter differences

Levenshtein distance counts single-character edits. OpenRefine's manual gives the example that New York and newyork are 3 apart, so a change of case counts as an edit. The cell compares every messy value against the ten official names in districts.csv with a radius of 2.

Radius 2 matches Purnea, Kozhikkode, Badmer, Wayanadu, Waynad, Reewa, Indor and most of the space, case and punctuation variants at distance 1, and Baitul to Betul and Patna with two trailing spaces at distance 2. It misses GAYA and PURNIA, because four or five capital letters are four or five edits; fingerprint had already caught those, which is the reason to run fingerprint first. It cannot match Calicut to Kozhikode at any sensible radius.

What clustering can and cannot do with Indian place names

• Case, spaces and punctuation (GAYA, Patna  , Udaipur.): fingerprint handles them safely.
• One-letter transliteration differences (Purnea/Purnia, Badmer/Barmer, Kozhikkode/Kozhikode, Baitul/Betul): these come from writing the same Hindi, Malayalam or Bengali sound in Roman letters in different ways. Levenshtein at radius 1 or 2 finds most of them. Check each one: at radius 2, two different villages can also fall together, and the manual's own example is M. Makeba matching B. Makeba, another person.
• Phonetic keys: the manual describes Metaphone3 as an English-language algorithm and Cologne Phonetic as built for German. They may catch some vowel variants, but they were not designed for Indian names, so read each cluster before you accept it.
• Different names for one place (Calicut/Kozhikode, Gurgaon/Gurugram, Allahabad/Prayagraj): no string method links them. Use a lookup table, or better, match on the Census or LGD code instead of the name.
• Names in two scripts (पूर्णिया in one row, Purnia in another): the ASCII folding in fingerprint covers accented Western letters only, and none of the methods transliterates Devanagari or any other Indian script into Roman letters. Split such a column by script and map each against the official list.

The cell below writes down what you would decide in the clustering window as a mapping, applies it, and checks the result against the official list.

After the merges, one value remains outside districts.csv: Calicut, 2 rows. That is the colonial-era name for Kozhikode, and the cell in module 8 maps it with an explicit lookup.

Transform cells with GREL

GREL (General Refine Expression Language) is OpenRefine's formula language. In an expression, value is the current cell. Open Edit cells → Transform… on a column, type an expression, and the window shows a preview of the first rows before you apply it. The functions below are from the GREL functions reference.

value.trim()
value.toTitlecase()
value.trim().toTitlecase()

trim() removes leading and trailing spaces and toTitlecase() capitalises the first letter of each word. You can chain them. The same two fixes are also on the menu, under Edit cells → Common transforms (Trim leading and trailing whitespace; Case transforms).

value.replace(",", "").toNumber()

replace(find, replacement) removes the thousands comma; toNumber() converts the result to a number. A cell that still cannot be converted, such as not asked, stays as it was, which the numeric facet will show as non-numeric.

Trim and title case take the district column from 29 distinct values to 20. Removing commas takes the non-numeric expenditure cells from 18 to 6, and the six left all say not asked. Those are missing values, and they should stay missing: do not type a zero.

value.fingerprint()
value.split(",")[0]
if(value == null, "missing", value)
cells["district"].value + ", " + cells["area"].value

• value.fingerprint() in Edit column → Add column based on this column… puts each cell's fingerprint key in a new column, so you can see why two values clustered.
• value.split(",")[0] keeps the part before the first comma.
• if(value == null, "missing", value) labels empty cells.
• cells["district"].value reads another column in the same row, so the last line builds a combined label.

Split and join columns

MIS exports often pack two facts into one column ("Gaya, Bihar") or spread one fact across several. OpenRefine's column menu handles both, as described in the column editing manual.

Split one column into several

1. On the location column, choose Edit column → Split into several columns…
2. Choose by separator and type a comma followed by a space.
3. Choose whether to remove the original column, and click OK.

Join several columns into one

1. Choose Edit column → Join columns… from any column.
2. Tick the columns to join (here area and caste) and drag them into order.
3. Type a separator, for example | , and choose whether the result goes into the selected column or a new one.

The split gives a second column holding the state, with 72 rows each for Madhya Pradesh and Bihar and 48 each for Rajasthan and Kerala. The joined area | caste column is a quick cross-tab: Rural | OBC is the largest group with 71 households.

Multi-valued cells are a different operation

If a cell holds a list, such as the schemes a household receives ("PDS; MGNREGA; PM-KISAN"), use Edit cells → Split multi-valued cells…. That puts each item on its own row under the same record, so you can facet by scheme. Edit cells → Join multi-valued cells… puts them back.

Reconciliation, in outline

Clustering makes a column consistent with itself. Reconciliation matches it against an outside list and attaches that list's identifier to each cell. For a district column, the useful identifier is an official code that does not change when the spelling does.

1. On the column, choose Reconcile → Start reconciling…
2. The window offers Wikidata as a built-in service. To use another, click Add Standard Service… and paste the service's address.
3. Pick a type if the service offers types (for a district column, a type meaning "district of India" narrows the candidates), and start. Each cell gets a best match, a list of candidates, or nothing.
4. Facet on the reconciliation results to review the uncertain ones, and match them by hand.

The reconciling manual also mentions reconcile-csv, a small tool that builds a reconciliation service from a CSV file. That suits the usual MIS case: reconcile your messy village column against the official list your programme already holds, with its codes. If you already have identifiers in a column, Reconcile → Use values as identifiers links them directly.

Without a service, the same idea is a join on a lookup table. The Python cell in the next module maps Calicut to Kozhikode that way and then refuses to finish if any district is still unknown.

Undo, replay the cleaning, and export

Undo / Redo

Click the Undo / Redo tab in the left panel. It lists every change in order, starting with step 0, Create project, which cannot be undone. Click any earlier step to go back to it; the later steps stay in the list, greyed out, and you can click forward again. If you make a new change while stepped back, the greyed steps are erased for good, so step forward again before carrying on.

Extract the operation history as JSON

1. In the Undo / Redo tab, click Extract…
2. A box lists every operation up to the current state. Tick the ones you want; they appear as JSON on the right.
3. Copy the JSON and save it as a text file next to your data, for example clean_households.json. That file is your cleaning recipe.
4. Next month, create a project from the new export, open Undo / Redo, click Apply…, paste the JSON, and every step runs again.

The manual notes that not every operation can be extracted: edits to a single cell cannot be replayed. Do one-off corrections with a GREL expression or a cluster merge, so they go into the recipe. Read the JSON before you rely on it: you should find your column names and your GREL expressions written out in it.

The same idea in Python

A cleaning recipe written as code does the same job and can be checked. This cell puts all the steps from this course into one function and stops with an error if a district is still unknown.

The result has 240 rows and 6 columns, exactly 24 households in each of the ten districts, and 6 missing expenditure values (the not asked cells), and it reads back from the saved file with the same shape.

Export

The Export button at the top right offers, among others: comma- or tab-separated values, an HTML table, Excel (XLS or XLSX), ODS, a custom tabular exporter, an SQL statement exporter and a templating exporter for JSON. The exporting manual warns that many of these export only the rows in the current view, with your facets applied, so clear facets before exporting the full file. Export → OpenRefine project archive to file saves the whole project with its history as a .tar.gz file that another OpenRefine can import.

Where next