I need to convert a .csv file's contents from:
我需要转换.csv文件的内容:
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
1 个解决方案
#1
1
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.
不太清楚你的意思这些词在技术上还不存在所以我在第一年专栏前移动了国家专栏,暂时忽略了国家左边的任何事情。这里详细描述了'Unpivot',选择了去年列的国家列,并且只填充了多少行。
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.
生成的表应为三列,每个国家/地区包含25行。第一列(“行” - 国家/地区名称)可能用于查找我上面忽略的详细信息。
#1
1
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.
不太清楚你的意思这些词在技术上还不存在所以我在第一年专栏前移动了国家专栏,暂时忽略了国家左边的任何事情。这里详细描述了'Unpivot',选择了去年列的国家列,并且只填充了多少行。
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.
生成的表应为三列,每个国家/地区包含25行。第一列(“行” - 国家/地区名称)可能用于查找我上面忽略的详细信息。