using columnName to bulk fill down columns

Thanks to this StackOverflow question, I finally found a great use case to introduce the columnName variable in OpenRefine. 

The columnName variable has been poorly documented (see previous discussions in SO and on the OpenRefine mailing list). The feature got really interesting with the All > Transform option available in OpenRefine 2.7 back in 2017 (yes, this blog post is long overdue!)

Read More

How to use Columnize by Key/Value

Based on the Wordpress export file in XML format shared by Adam K on the user mailing list the column item - wp:postmeta - wp:meta_key and item - wp:postmeta - wp:meta_value store data in a key:value format. The column meta_key indicates the value type and the column meta_value store the field value.

Read More

How to check if value within a record are unique in OpenRefine

When working in record mode, it is possible to compare all the value within one record and see if those are unique. To do that we will introduce the function forEach() that let turn the record into an array and count unique value.

Read More

Limitation when splitting and joining multi-valued cells

Split multi-valued cells function helps to transpose data stored in one cells into multiple rows, while keeping the relationship with the other columns in the data set. In this article we will see some of the limitation of the function when splitting and joining back a data set and how you can work around it.

Read More

Creating row and record index

Google Refine provide the row index as information in the third column. Unfortunately GREL expression cannot call value in this column, you need to use one of the following expression to generate the value.

Read More

Sort by multiple criteria

Google Refine sort function allow a combination of several columns to sort by field A and field B. 

In my case, I used this method as a work extract the most recent title posted from a records in a list of radio show (using a timestamp field). As I am not aware for a way to select a specific row within a record, I used the sort function to present the record I wanted to extract at the top my the record group.

Read More

Create records in Google Refine

This short tutorial describe how to create records in Google Refine. For the difference between a row can present a data set in row or record mode (see the difference between the two).

Read More

Difference between a record and a row

Google refine make a clear distinction between a row and a record. We will see what's the difference between the two and advantages to works in records mode.

Read More

Fill down the right and secure way

The fill down function consists of taking the content of cells and copying down following blank cells. This is done based on the rows number. When you perform this action using the fill down function, Google refine does not take into account if rows belong to different records or not, if the following rows are blank, it will fill it down with the content of the previous row.

If you do not use this function with extra care you can easily corrupt the integrity of your data set. In a nutshell use  row.record.cells[columnName].value[0]   to fill down data within the same record. 

Here is why, and how to avoid that.

Read More

Merge records spread on multiple rows

This article will go through steps to gather on the content of the same row spread on two rows. This method can be extended when a single record is hosted on multiple rows.

Read More