Skip to content

Instantly share code, notes, and snippets.

@benjaminparry

benjaminparry/data set example - original.csv

Created September 1, 2017 10:22
Show Gist options
  • Save benjaminparry/a5c1227c29e18150c6d5cfd124031e83 to your computer and use it in GitHub Desktop.
Save benjaminparry/a5c1227c29e18150c6d5cfd124031e83 to your computer and use it in GitHub Desktop.
Download ZIP
Transpose survey response dataset with Open Refine (previously Google Refine)
Raw
data set example - original.csv
Collector ID Response ID Task1 Task2 Task3 Task4 Task5 Task6 Task7 Task8 Task9 Task10 Task11 Task12 Task13 Task14 Task15 Task16 Task17 Task18 Task19 Task20 Other Task Task Importance 1 Task Importance 2 Task Importance 3 Task Importance 4 Task Importance 5 Task Importance 6 Task Importance 7 Task Importance 8 Task Importance 9 Task Importance 10 Task Importance 11 Task Importance 12 Task Importance 13 Task Importance 14 Task Importance 15 Task Importance 16 Task Importance 17 Task Importance 18 Task Importance 19 Task Importance 20 Custom Task Importance Task Satisfaction 1 Task Satisfaction 2 Task Satisfaction 3 Task Satisfaction 4 Task Satisfaction 5 Task Satisfaction 6 Task Satisfaction 7 Task Satisfaction 8 Task Satisfaction 9 Task Satisfaction 10 Task Satisfaction 11 Task Satisfaction 12 Task Satisfaction 13 Task Satisfaction 14 Task Satisfaction 15 Task Satisfaction 16 Task Satisfaction 17 Task Satisfaction 18 Task Satisfaction 19 Task Satisfaction 20 Custom Task Satisfaction
11111 54321 Task1 Task5 Task8 Task16 Download a document 3 5 4 3 4 3 5 2 4 2
22222 12345 Task3 Task10 Task12 Task19 Report a fault 4 3 2 4 3 4 3 4 5 4
Raw
data set example - reshaped.csv
Collector ID Response ID Task Importance Satisfaction
11111 54321 Task1 3 3
11111 54321 Task5 5 5
11111 54321 Task8 4 2
11111 54321 Task16 3 4
11111 54321 Download a document 4 2
22222 12345 Task3 4 4
22222 12345 Task10 3 3
22222 12345 Task12 2 4
22222 12345 Task19 4 5
22222 12345 Report a fault 3 4
@ettorerizza
Copy link

ettorerizza commented Sep 1, 2017 •
edited
Loading

And here is my version, so I can delete my Gist ;)

[
  {
    "op": "core/transpose-columns-into-rows",
    "description": "Transpose cells in 21 column(s) starting with Task1 into rows in one new column named tasks",
    "startColumnName": "Task1",
    "columnCount": 21,
    "ignoreBlankCells": true,
    "fillDown": false,
    "combinedColumnName": "tasks",
    "prependColumnName": false,
    "separator": ":"
  },
  {
    "op": "core/transpose-columns-into-rows",
    "description": "Transpose cells in 21 column(s) starting with Task Importance 1 into rows in one new column named importance",
    "startColumnName": "Task Importance 1",
    "columnCount": 21,
    "ignoreBlankCells": true,
    "fillDown": false,
    "combinedColumnName": "importance",
    "prependColumnName": false,
    "separator": ":"
  },
  {
    "op": "core/transpose-columns-into-rows",
    "description": "Transpose cells in columns starting with Task Satisfaction 1 into rows in one new column named satisfaction",
    "startColumnName": "Task Satisfaction 1",
    "columnCount": -1,
    "ignoreBlankCells": true,
    "fillDown": false,
    "combinedColumnName": "satisfaction",
    "prependColumnName": false,
    "separator": ":"
  },
  {
    "op": "core/multivalued-cell-join",
    "description": "Join multi-valued cells in column tasks",
    "columnName": "tasks",
    "keyColumnName": "Collector ID",
    "separator": ", "
  },
  {
    "op": "core/multivalued-cell-join",
    "description": "Join multi-valued cells in column importance",
    "columnName": "importance",
    "keyColumnName": "Collector ID",
    "separator": ", "
  },
  {
    "op": "core/multivalued-cell-join",
    "description": "Join multi-valued cells in column satisfaction",
    "columnName": "satisfaction",
    "keyColumnName": "Collector ID",
    "separator": ", "
  },
  {
    "op": "core/multivalued-cell-split",
    "description": "Split multi-valued cells in column tasks",
    "columnName": "tasks",
    "keyColumnName": "Collector ID",
    "separator": ",",
    "mode": "plain"
  },
  {
    "op": "core/multivalued-cell-split",
    "description": "Split multi-valued cells in column importance",
    "columnName": "importance",
    "keyColumnName": "Collector ID",
    "separator": ",",
    "mode": "plain"
  },
  {
    "op": "core/multivalued-cell-split",
    "description": "Split multi-valued cells in column satisfaction",
    "columnName": "satisfaction",
    "keyColumnName": "Collector ID",
    "separator": ",",
    "mode": "plain"
  },
  {
    "op": "core/fill-down",
    "description": "Fill down cells in column Response ID",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "Response ID"
  },
  {
    "op": "core/fill-down",
    "description": "Fill down cells in column Collector ID",
    "engineConfig": {
      "mode": "row-based",
      "facets": []
    },
    "columnName": "Collector ID"
  }
]

@benjaminparry
Copy link
Author

@magdmartin @ettorerizza thanks very much for your input.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment