Transpose survey response dataset with Open Refine (previously Google Refine)

Question

I’m looking for some help to reshape a survey response dataset, exported as a csv, using Open Refine (previously Google Refine).

Some context on the survey

1. Collector and responder ID are collected in the background - ID1 ID2
2. Users select tasks from a long list - T{n}
3. Users enter a custom task - OT
4. Users rate the importance of the each selected task - R1
5. Users rate the satisfaction of the each selected task - R2

We have a total of 20 tasks atm but this might change.

Current dataset as follows:

ID1 | ID2 | T1 | » | T20 | OT | T1 R1 | » | T20 R1 | OT R1 | T1 R2 | » | T20 R2 | OT R2
123 | 789 | 

I’m trying to reshape the dataset to the following format:

ID1 | ID2 | Task | Importance | Satisfaction

Here’s a gist of original and reshaped data sets

Also, i’ve tried to articulate how I want to reshape the data in a drawing, which might help

Answer 1

This can't be done by clicking a single button. You have to perform three "transpose cells across columns into rows" (one for tasks, one for their importance, one for their satisfaction), then three "join multivalued cells", then three "split multivalued cells", and finally use fill down to fill the blanks in the ID columns. A screencast will probably be clearer than my explanations.

You'll find the Json operations in a comment on your Gist. If your columns have exactly the same name as the example provided, you can apply it on your project by copying and pasting the file into "Undo/Redo -> Apply"

Answer 2

Try the following:

• Concatenate all your content for each task using cells['Task1'].value+"|Importance: "+cells['Task Importance 1'].value+"|Satisfaction:"+cells['Task Satisfaction 1'].value You will need to do that 20 times (one for each group of task)
• Transpose all column after Response ID (not included). You can reuse this Operation
• split cells based on the pipe |
• finish renaming and cleaning up value with value.replace()