DS161 Introduction to Data Science & Artificial Intelligence
Self-Study 7
Data Cleaning with OpenRefine
Krishnendu Ghosh
Installing Openrefine
Setup Steps
How to Access
Download OpenRefine free from openrefine.org — available for Windows, Mac, and Linux. No internet needed after install.
Download & install from openrefine.org for your OS
Run openrefine.exe (Win) or ./refine (Mac/Linux) — opens in browser at localhost:3333
1
2
Core Openrefine Features
Faceting & Filtering
Key Capabilities:
GREL Transformations
Undo/Redo History
Clustering for Typos
Hands-On Workflow
Create a new project & import your CSV dataset
Explore data using text facets to spot dirty values
Apply GREL transformations to clean and standardize messy entries
1
2
3
GREL Expressions
GREL (General Refine Expression Language) lets you transform messy cell values. These expressions run inside OpenRefine — no install needed beyond the tool itself.
Examples
Description
value.trim() — removes leading/trailing spaces
value.toTitlecase() — fixes "john doe" → "John Doe"
value.replace("N/A","") — clears placeholder text
Case Study:
Student Records Dataset
You have a CSV of 500 student records with messy department names ("CSE", "cse", "C.S.E") and mixed date formats.
GREL Fix
Scenario
Cluster dept column → Key Collision → merge all to "CSE"
Standardize dates: value.toDate("MM/dd/yyyy").toString("yyyy-MM-dd")
Advanced Cleaning
Split multi-value cells using value.split(",") in GREL, and reconcile data against external databases for validation.
Key Features
Description
Split cells: value.split(",") breaks multi-value fields into rows
Reconcile columns against Wikidata to validate and enrich entries
Homework
Task 2
Task 1
Download messy_products.csv, open it in OpenRefine, and remove duplicate product entries using the Cluster & Edit feature under a text facet.
In OpenRefine, use GREL to extract numeric values from a mixed text column:
value.match(/\d+/)[0]
Conclusion
Install & Launch
Faceting & Filtering
Clustering Algorithms
Use GREL expressions like value.trim() to transform cells.
GREL Transformations
Export cleaned dataset as CSV for further CSE project use.
Export Clean Data
Download OpenRefine & run locally via browser at localhost:3333.
Use facets and filters to explore and isolate dirty data quickly.
Apply key-collision & nearest-neighbor clustering to fix typos.
THANK YOU &
Happy Data Cleaning!