Convert your rows of data into multi-row records

[Jan]

Hey there,
I have a csv with list of persons identified by a id column. Each person has a operation_id which is basically a foreign key. To represent a one-to-many relation, a same person can be present in several rows:

id,first_name,last_name,date_of_birth,gender, operation_id
111111,John,Doe,01/01/2000,Male, 1
111111,John,Doe,01/01/2000,Male, 2
111112,Jane,Smith,01/05/2001,Female, 3
111113,Sarah,Thomas,01/21/2002,Male, 4

In the above example, the person 111111 had two operations.

I imported this file in OpenRefine and the data shows as 4 rows but also as 4 records. How can I tell OpenRefine to use the id colum to actually end up with 3 records?

I read the records vs rows docs. The transforming data chapter mentions

• convert your rows of data into multi-row records.

How can I do that?

thanks in advance

and as always .. many thanks for maintaining this open source project

[antonin_d]

Hi @Jan,

In rows mode, use the "Blank down" operation on the "id" column to replace consecutive identical values by a value followed by blank cells. That will create the appropriate record structure.

[Jan]

hey Antonin,
I forgot to mention that I tried that already. If I blank down the rows, it says that 0 rows were blanked.

Does the format of the column (numeric vs string) play a role here?

[Jan]

My bad, it actually works ..

I had the records tab selected and there it was telling me Blank down 0 cells in column id. If I select the rows tab, it works as expected

[fabiolinus]

Hi Jan. I know you solved your problem. I usually use:

Edit column > Move column to beginnin

to make records creation more simple.

Fabio