Assign a common ID to a group (facet)

[Franco Giovannini]

Hi, I have a set of records where the field DESC can be the same in different entries.

I would like to add a new column GROUP_ID which regroupes those records, i.e. 

from:

ID DESC

01 aaaa

02 bbbb

03 cccc

04 bbbb

05 bbbb

06 aaaa

07 cccc

to:

ID DESC GROUP_ID

01 aaaa 1

02 bbbb 2

03 cccc 3

04 bbbb 2

05 bbbb 2

06 aaaa 1

07 cccc 3

I know I can easily do it outside Openrefine, but I would like to use if possible a GREL script to perform this operation. Any suggestion on how to do it?

Thanks

Franco

[Owen Stephens]

Hi Franco,

You can do this by moving to 'records' and then back again to 'rows':

1. Move the DESC column to the be the first column in the project

2. Sort by DESC, and apply Sort permanently (from the 'Sort' dropdown menu that appears after you have sorted a column)

3. On the DESC column choose 'Edit Cells->Blank down'

4. Make sure you are in Records mode (click on 'Records' at the top left if OpenRefine hasn't switched automatically)

4. On the DESC column choose 'Edit Column->Add Column based on this column'. Call it GROUP_ID

5. In the GREL expression box use

row.record.index+1

This will number the groups 1-n

6. On the DESC column use 'Edit Cells->Fill down' to put the DESC values back on all rows

7. Switch back to Row mode

8. Re-order the columns into the desired order

Let me know if you have any questions

Owen

[Franco Giovannini]

Owen, this is absolutely brillant and works perfectly! 

Thanks a lot!!

Ciao,

Franco