Search code examples
csvopenrefine

Add every other pair of cells in column in Google Refine

I've got a data set with the number of men and women in each country in the world.

"country","sex","amount"
"Afghanistan","men","number"
"Afghanistan","women","number"
"Albania","men","number"
"Albania","women","number"...

I want to add the merge every pair of country rows, and erase the sex column, so that I'd end up with:

"country","amount"
"Afghanistan","number"
"Albania","number"

How do I add rows together in this way in Google/Open Refine?

Solution
    • If the list isn't sorted, sort it on Country and make the source permanent
    • Blank down on the Country column
    • Remove the Sex column
    • Join multivalued cells on the Amount column
    • transform Amount column with an expression along the lines of forEach(value.split(','),a,a.toNumber()).sum()