OpenRefine Foundation

Topic outline

• General
  

  General

  • 

    Foundation

    Created September 2015 - Updated April 2019

    
    This course covers the foundation of OpenRefine and its scripting language GREL. You will learn how to:
    

    • Use the facet/filter;
    • Leverage OpenRefine point and click transformation and fuzzy matching function for quick but powerful data cleaning ;
    • Write complex transformation in GREL, OpenRefine script language ;
    • Call API and parse results in OpenRefine.

    
    

    We developed the course in 2015 using OpenRefine 2.6-beta. We do our best to keep the content current with the latest version of OpenRefine. It is possible that some third-party services referenced in the course are not available any longer or have changed their interfaces. Please let us know if there is out of date information.
    

    This course was developed by RefinePro in partnership with BigData University and Cognitive Class.  Since 2011, RefinePro has developed training programs for OpenRefine, including free online courses, in-person or remote courses, and individual coaching sessions.
    
    Learn more about RefinePro’s training here.

    Welcome!

    
    

    Course Developed By

    
    

    General information

    ---

    1. This course is free
    2. It is self-paced.
       
    3. It can be taken at any time.
    4. It can be taken as many times as you wish.
    5. Labs can be performed using a Linux, mac or windows machine. More details are provided in the lesson 1.5 Installing OpenRefine and Getting Started.
    6. You will need to create a free account on the tranzform platform to post on the forum or take the final quiz (optional)

    
    

    Recommended skills prior to taking this course

    ---

    • Familiarity with spreadsheet software and different data type (text, date, number)

    
    

    

    
    

  • 
    

    Support

    ---

  • Course forum
  • OpenRefine Support Mailing List URL
  • OpenRefine tag on StackOverflow URL
• Lesson 1 Introduction to OpenRefine
  

  Lesson 1 Introduction to OpenRefine

  
  

  Learning objectives

  ---

  • Understand the principles of data preparation
  • Getting familiar with the OpenRefine community and the software interface
  • Installing OpenRefine

  
  

  Instructions

  ---

  • Review all the videos provided
  • Complete the lab

  
  

  Videos

  ---

  • Introduction to Data Quality and Integration (9:56) Page
  • Moving toward an Agile Data process (9.08) Page
  • Openrefine History and Community (8:59) Page
  • OpenRefine Interface Tour (5:56) Page
  • Installing OpenRefine and Getting Started (8:47) Page
  • 
    Hands-on lab
    

    ---

  • Lab Instruction Lesson 1 Page
  • clearedpermits2014.xls File
• Lesson 2 Data Mining and Discovery
  

  Lesson 2 Data Mining and Discovery

  
  

  Learning objectives

  ---

  • Learn the different facet type
  • Learn to combine facet to create complex filtering
  • Learn how to sort data in OpenRefine

  
  

  Instructions

  ---

  • Review all the videos provided
  • Complete the lab

  
  

  Videos

  ---

  • Data Mining and Discovery Text Based Facet (9:34) Page
  • Data Mining and Discovery Date and Number Based Facet (6:19) Page
  • Data Mining and Discovery Text Combining Facet (6:17) Page
  • Data Mining and Discovery Sorting Data (7:26) Page
  • 
    Hands-on lab
    

    ---

  • Lesson 2 Lab Questions Page
  • Lesson 2 Lab Answers Page
• Lesson 3 Data Preparation and Normalization
  

  Lesson 3 Data Preparation and Normalization

  
  

  Learning objectives

  ---

  • Learn point and click data normalization (clustering, removing duplicate, splitting cells)
  • Understand how the history and undo / redo works
  • Start using GREL to concatenate two fields

  
  

  Instructions

  ---

  • Review all the videos provided
  • Complete the lab Mapping Toronto Condo Boom Part 1: Data Preparation
  • The Twitter Stream Analysis lab is optional and for practice purpose only.

  
  

  Videos

  ---

  Mapping Toronto Condo Boom Part 1: Data Preparation

  • Data Preparation and Normalization Clustering (7:58) Page
  • Data Preparation and Normalization Remove Duplicate (4:23) Page
  • Data Preparation and Normalization Split Multi Valued Cells (6:57) Page
  • Data Preparation and Normalization Concatenate (6:42) Page
  • Data Preparation and Normalization Using OpenRefine History Do/Undo (8:28) Page
  • 
    Hands-on lab: Mapping Toronto Condo Boom Part 1: Data Preparation
    

    ---

    This lab is the first part in a series of two labs to map all closed projects in 2014 in Toronto that have created more than 10 dwelling units.

     

     

    Mapping Toronto Condo Boom Part 1: Data Preparation

  • Instruction for Mapping Toronto Condo Boom Part 1: Data Preparation Page
  • Lesson 3 Lab 1 Answer Page
  • Lesson 3 Lab 1 Answer, JSON file
  • 
    

    Hands-on lab: Twitter Stream Analysis

    ---

  • Instruction for Twitter Stream Analysis Lab Page
  • OpenRefine Twitter Archive - Archive.tsv File
  • Answer to Twitter Stream Analysis - Part 3: Extracting URLs Page
• Lesson 4 General Refine Expression Language
  

  Lesson 4 General Refine Expression Language

  
  

  Learning objectives

  ---

  • Understand and master GREL syntax
  • Learn basic GREL expression to replace, split and compare string

  
  

  Instructions

  ---

  • Review all the videos provided
  • Complete the lab Wiki Page Data Extraction
    
  • The Lab Combining GREL together is optional.

  
  

  Videos

  ---

  • General Refine Expression Language Introduction (7:19) Page
  • Toronto Place of Interest.xls File
  • General Refine Expression Language Replace Function(7:36) Page
  • General Refine Expression Language Split and work with Array (7:25) Page
  • General Refine Expression Language String Comparison and If Condition (7:59) Page
  • General Refine Expression Language Calculate with Refine (4:33) Page
  • 
    Hands-on lab: Wiki Page Data Extraction

    ---

  • Lesson 4 Lab 1: Wiki Page Data Extraction
  • List_of_M_postal_codes_of_Canada.txt File
  • General Refine Expression Language Calculate Lab 4.1 Correction (10:20) Page
  • Correction: postalcode.xls File
  • Correction: OpenRefine project archive File
  • 
    Hands-on lab: Combining GREL together

    ---

  • Lesson 4 Lab 2: Combining GREL together Page
  • General Refine Expression Language Lab 4.2 Correction (2:30) Page
• Lesson 5 Data Enrichment
  

  Lesson 5 Data Enrichment

  
  

  Learning objectives
  

  ---

  • Join OpenRefine project together
  • Call an API to enrich your project
  • Parse a JSON answer from an API

  
  

  Instructions

  ---

  • Review all the videos provided
  • Complete the lab Mapping Toronto Condo Boom Part2: Data Enrichment and mapping
  • The lab Translation and Sentiment Analysis is optional

  
  

  Videos

  ---

  • Data Enrichment (2:57) Page
  • Data Enrichment Joining two OpenRefine Projects(4:44) Page
  • Data Enrichment Working with API Introduction (4:32) Page
  • Data Enrichment Calling an API with Refine (6:14) Page
  • Data Enrichment Parsing API Results (4:00) Page
  • 
    Hands-on lab: Mapping Toronto Condo Boom Part2: Data Enrichment and mapping

    ---

  • Lesson 5 Lab 1: Mapping Toronto Condo Boom Part2: Data Enrichment and Mapping Page
  • Correction Lesson 5 Lab 1: Mapping Toronto Condo Boom Part2: Data Enrichment and Mapping Page
  • 
    Hands-on lab: Translation and Sentiment Analysis
    

    ---

  • Lesson 5 Lab 2: Translation and Sentiment Analysis Page
  • Correction: Lesson 5 Lab 2: Translation and Sentiment Analysis Page
• Test your knowledge
  

  Test your knowledge

  • Take the test! Quiz
• Going Further
  

  Going Further

  Those 5 lessons provided a solid overview of what OpenRefine can do and you should now be able to prepare and clean most of your data. We only scratched the surface of GREL and left some topics out of the scope of this course including:

  • Working in record mode
  • Using reconciliation service
  • Using OpenRefine extensions