3
1
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.)
AJMansfield
Posted 2014-01-02T21:11:45.247
Reputation: 173
1I think the thing for this in Excel is a "pivot table". I'll think about a simple spreadsheet code... – apnorton – 2014-01-02T22:16:44.247
It looks like LibreOffice also has a pivot table, but I don't know how good it is. – apnorton – 2014-01-02T22:23:12.977
@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 – 2014-01-02T22:28:49.233