21.7.11

merge 2 columns that have both blank cells

New - March 2020 Update:

  • OpenRefine 3.0, we have the coalesce() function:  which natively handles the null correctly. 
  • OpenRefine 3.3 introduced a user interface that offers tons of flexibility. See tutorial


The basic function to merge or concatenate data from two different column is the following:
cells["col1"].value + " " + cells["col2"].value




    In the case the Refine return null or blank value because one of the two columns have blank cells you can:
    1. Facet column 1 and column 2 on blank
    2. In facet menu (on the left) select column 1 = false and column 2 = false, to get only rows with data in it.
    3. Run the following expression in column 2: cells["column 1"].value + " " + cells["column 2"].value
    4. In facet menu (on the left) select column 1 = false and column 2 = true, to get only rows with data in the column1
    5. Run the following expression in column 2: cells["column 1"].value as column 2 received some content and you faceted it on true, no rows will be displayed on screen, so ...
    6. Remove your 2 facets to see all your content.
    In case you want to see your edit, and verify manually that the step 5 operate properly. You can star all your row at step 2, and then facet on star rows after step 6. This will display the rows you just edited.

    0 comments:

    Post a Comment