OpenRefine 3.3 allows to combine information from multiple columns. This method allows you to combine the contents of two columns, add a specific string or characters to a column's values, or a combination of both. There are several ways to combine contents from multiple columns. Here, introduce two basic functions to combine information:
OpenRefine 3.3. allows to join the contents of multiple columns. It can be done in a few simple steps.
NOTE: This function requires the use of regular expressions. The relevant expressions are provided here, but for more information, please refer to the section on Google Refine Expression Language (GREL) and the Resources page.
Go to the column you would like alter and click the arrow button on the column header.
Select the “Edit columns” and then choose “Add Column based on this column.” OR
Select the "Edit cells" and then chose "Transform."
If selecting 2, you can create a new column for merging data. See an example of the first picture below.
If selecting 3, you can overwrite merging data in the column you chose. See an example of the second picture below.
A pop-up window will appear with several sections, including a GREL expression input box and a preview panel. For more information on this window, refer to the Google Refine Expression Language (GREL) section.
The expression for combining column information is:
value + cells[‘Column’].value OR
cells[‘Column1’].value + cells[‘Colmn2’].value
"Value" indicates the values in the current column.
"Column" should match the name of the column whose values you would like to combine with the current column.
If a data type in a column is number, the result will return to sum of two values in each row. To avoid this, add string as following step 5.
To combine column information AND additional strings, use a “+.” For example:
value + “-” + cells[‘Column’].value OR
cells[‘Column1’].value + “-“ + cells[‘Colmn2’].value
Adds a dash between the values of the current column and the values of the column to be combined with the current column.
Once you have typed your expression into the expression box, check the preview window to confirm that your transformation looks the way it should.There is also a section to the right of the expression box which will tell you if there are any syntax errors in your expression.
When you are satisfied with the preview, select “OK.”
NOTE: If some sells contain null values, null cells will return when merging columns using these techniques. To avoid null values, follow a few simple steps: