3

I have a set of data that is contained in a .csv spreadsheet arranged like this:

name | 2006 | 2007 | 2008 | 2009
--------------------------------
foo  | 28   | 48   | 395  | 3829
bar  | 83   | 475  | 350  | 46

I need to rearrange this data so it looks like this:

name | date | value
-------------------
foo  | 2006 | 28
foo  | 2007 | 48
foo  | 2008 | 395
foo  | 2009 | 3829
bar  | 2006 | 83
bar  | 2007 | 475
bar  | 2008 | 350
bar  | 2009 | 46

I was thinking I could do this with a spreadsheet formula, something like row.valueEntry = inputSheet[row.index/4 + 1][row.index % 4 + 1], but I have not yet figured out how to implement it in spreadsheet-formula-language.

Is there a better way to accomplish this sort of rearranging?

(BTW, I do not have access to MS Excel, as I am on Ubuntu, but answers that need things specific to excel might still be useful to other users.)

  • 1
    I think the thing for this in Excel is a "pivot table". I'll think about a simple spreadsheet code... – apnorton Jan 2 '14 at 22:16
  • It looks like LibreOffice also has a pivot table, but I don't know how good it is. – apnorton Jan 2 '14 at 22:23
  • @anorton Well I just finished doing it by hand, 27,000 rows of it, with some fairly repetitive but fairly simple block copying. I would copy a column over to a second page, where I had the other data rows to the left, and a column to the right set up so all cells equaled the pasted column's header, then copied all the resulting rows to a third sheet using 'paste values only'. – AJMansfield Jan 2 '14 at 22:28
0

Check out the following link for steps how to pivot table in OpenOffice that is available for Linux.

The following link is for handling same result in MS Excel.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.