CODEWITHSUNDEEP
openrefine
amustafa
amustafa

Reputation: 858

How to transpose cell data by section in Open Refine?

I have a data table that looks like this:

Name | Date-Freq | Date-Amount | Date-Freq | Date-Amount 
A    |      4    |    3000     |     8     |   9000
B    |      5    |    4000     |     9     |   7000
C    |      6    |    5000     |     10    |   8000

and I want it to look like this:

Name |     Date    |  Freq | Amount 
A    |  July 2014  |   4   | 3000
A    |  Aug 2014   |   8   | 9000
B    |  July 2014  |   5   | 4000
B    |  Aug 2014   |   9   | 7000
C    |  July 2014  |   6   | 5000
C    |  Aug 2014   |   10  | 3000

What is the best way to do something like this? Should I just create two new columns?

Upvotes: 1

Views: 572

Answers (1)

Thad Guidry
Thad Guidry

Reputation: 608

What you want to accomplish will require lots of steps and faceting and adding new columns, etc. But you can accomplish this with OpenRefine, YES.

You will need to use a combination of:

  1. Always work in Records mode (not row mode) for any kind of Merging work.
  2. Transpose Cells across columns into rows (into one MERGE column with prepend)
  3. Moving your Name column to beginning column.
  4. Fill down on your Name column (because it is now blank on some cells after the Transpose. and later you might need to fill down again after any particular Transpose or Merging.)
  5. Use Custom Text Faceting with value.startsWith("Amount") etc.
  6. Use Add new column based on to create new columns based on the MERGE column
  7. Move Columns as necessary to do Step 2 again.
  8. Repeat steps as necessary.

Here is an example OpenRefine project showing the beginning of what happens after the 1st set of the above steps (Use Undo/Redo to see, doesn't show Facets though):

OpenRefine Project with Transpose across cells into one column

Upvotes: 3

Related Questions