I need to convert a .csv file's contents from:
continent, region, country, 1990, 1991, 1992, 1993, ...
Africa, East Africa, Ethiopia, 12, 14, 15, 9, ...
to:
continent, region, country, year, value
Africa, East Africa, Ethiopia, 1990, 12
Africa, East Africa, Ethiopia, 1991, 14
Africa, East Africa, Ethiopia, 1992, 15
Africa, East Africa, Ethiopia, 1993, 9
...
There are a bunch of rows, so this is impossible to do by hand. I know there has got to be a way to do it, I really am just having issues even explaining what I am doing. It is essentially transposing part of the table but not all of it.
EDIT: here is a sample of a full row of data:
continent, region, country, country_abbr, economy, lat, long, 1990/91, 1991/92, 1992/93, 1993/94, 1994/95, 1995/96, 1996/97, 1997/98, 1998/99, 1999/00, 2000/01, 2001/02, 2002/03, 2003/04, 2004/05, 2005/06, 2006/07, 2007/08, 2008/09, 2009/10, 2010/11, 2011/12, 2012/13, 2013/14, 2014/15
Africa, Middle Africa, Angola, AO, 7. Least developed region, -12.5, 18.5, 50, 79, 78, 33, 77, 62, 71, 64, 85, 55, 50, 21, 57, 38, 15, 25, 35, 36, 38, 13, 35, 29, 33, 35, 39
Not quite sure what you mean by Those words technically don't exist yet so I have moved the country column just before the first year column and am ignoring, for the moment, anything to the left of country. 'Unpivot' as described in detail here with selection of country column to the last year column and only as many rows as are populated.
The resulting Table should be of three columns with a set of 25 rows for each country. The first column ("Row" - country name) might be used to lookup the details I was ignoring above.