Concatenate column x with n in OpenRefine
I have a table with an unknown number of columns and I need to concatenate all columns after a certain point. Consider the following:
| A | B | C | D | E |
|----|----|---|---|---|
| 24 | 25 | 7 | | |
| 12 | 3 | 4 | | |
| 5 | 5 | 5 | 5 | |
The AC columns are known and the information is correct. But column D to N (unknown number of columns starting with D) needs to be concatenated since all parts are the same row. How can I concatenate an unknown number of columns in OpenRefine?
Since some columns may have empty cells (the row may have different lengths), I also need to ignore empty cells.
source to share
There are two approaches to this that should work for you.
In the first column you want to concatenate (Col D in this case) select Transpose-> Transpose Cells Columns to Rows
You will be prompted to set some parameters. You need to select "From Column" D and "To Column" N. Then select transpose to one column, assign a name to this column, make sure the option "Ignore blank cells" is checked (should be checked by default. Then click Submit.
You will get the values that were previously in the DN columns appearing in rows. eg.
| A | B | C | D | E | F |
|----|----|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | 6 |
Transmits:
| A | B | C | new |
|----|----|---|-----|
| 1 | 2 | 3 | 4 |
| | | | 5 |
| | | | 6 |
Then you can use the dropdown menu from the "new" column chapter to select Modify Cells-> Append Multi-valued Cells
You will be asked what character you want to use to separate characters in the merged cell. Probably in your use case, you can remove the join character and merge cells without any join characters. This will give you:
| A | B | C | new |
|----|----|---|-----|
| 1 | 2 | 3 | 456 |
source to share