使用Python合并CSV文件
我的交叉表CSV文件看起来是这样的：
Country,Age,All,M,F
UK,Under65,30987,15000,15987
UK,65andOver,12345,6345,6000
Germany,Under65,32646,15642,17004
Germany,65andOver,14747,7192,7555
France,Under65,31587,16286,15301
France,65andOver,13741,6187,7554
我想把它改成这样：
Country,Under65_All,Under65_M,Under65_F,65andOver_All,65andOver_M,65andOver_F
UK,30987,15000,15987,12345,6345,6000
Germany,32646,15642,17004,14747,7192,7555
France,31587,16286,15301,13741,6187,7554
现在每个国家都在一行上，列的数量也增加了（没有交叉表）。
我想用Python 3来实现这个功能。因为一些较大的CSV文件让我在Excel VBA中遇到了行数限制，所以我不打算使用它。
我想做的其实是一个“聚合”操作，并且还要加一个“分组”的步骤。我已经完成了读取CSV文件和计算一些可能有用的值，比如：独特国家的数量（3个）、独特年龄组的数量（2个）、最终输出文件所需的列名和数量（7列）。
我希望代码尽可能灵活，这样它就可以读取一个有x个独特国家、y个独特年龄组和z个列变量的文件。最终的文件会包含一个标题行，列数是y*z+1，下面会有x行数据。
希望这样说清楚了，如果有任何帮助或建议，我会非常感激。
2 个回答
4
最简单的方法可以分为两个步骤：
- 从输入的CSV文件中收集数据，放到一个方便的数据结构里。
- 利用收集到的数据来写出输出的CSV文件。
我觉得最方便的数据结构是一个defaultdict，里面存放的是dict对象。我们可以用主要的键（在这里是“国家”）作为最上层的键，然后把组合键（在这里是“年龄”）和其他键拼接起来，作为第二层字典的键：
{
'France': {
'65andOver_All': '13741',
'65andOver_F': '7554',
'65andOver_M': '6187',
'Under65_All': '31587',
'Under65_F': '15301',
'Under65_M': '16286'
},
'Germany': {
'65andOver_All': '14747',
# ...
},
# ...
}
我们还需要记录使用的表头，使用一个set可能是最好的选择。
用这些数据结构，代码大概会是这样的：
from collections import defaultdict
from csv import DictReader, DictWriter
def aggregate(infile, outfile, p_key, c_key):
"""Group 'infile' on 'p_key', combining additional keys with 'c_key'."""
data = defaultdict(dict)
headers = set()
with open(infile) as f:
for row in DictReader(f):
p_value = row.pop(p_key)
c_value = row.pop(c_key)
for key, value in row.items():
header = "_".join([c_value, key])
headers.add(header)
data[p_value][header] = value
field_names = [p_key] + sorted(headers)
with open(outfile, "w") as f:
writer = DictWriter(f, field_names)
writer.writeheader()
for p_value, row in data.items():
row[p_key] = p_value
writer.writerow(row)
示例用法：
>>> aggregate("in.csv", "out.csv", "Country", "Age")
生成的out.csv文件：
Country,65andOver_All,65andOver_F,65andOver_M,Under65_All,Under65_F,Under65_M
France,13741,7554,6187,31587,15301,16286
UK,12345,6000,6345,30987,15987,15000
Germany,14747,7555,7192,32646,17004,15642
4
我想推荐一个 pandas 的解决方案，因为不这样做就像是在重复造轮子。不过，刚开始用的时候确实需要适应一下。好处是，一旦你掌握了这些操作，就会变得相对简单。
import pandas as pd
df = pd.read_csv("c.dat")
df = pd.melt(df, id_vars=["Country", "Age"], var_name="Other")
df["Column"] = df.pop("Age") + "_" + df.pop("Other")
df = df.pivot(index="Country", columns="Column")
df.columns = df.columns.droplevel(0)
df.to_csv("out.csv")
这样就能得到结果
>>> !cat out.csv
Country,65andOver_All,65andOver_F,65andOver_M,Under65_All,Under65_F,Under65_M
France,13741,7554,6187,31587,15301,16286
Germany,14747,7555,7192,32646,17004,15642
UK,12345,6000,6345,30987,15987,15000
(如果我们真的想的话，还可以对列进行排序。)
这里没必要复制整个教程——不过你可以在 这里 阅读关于数据重塑的教程——但我可以简单介绍一下这个过程是怎么工作的。
一步一步来。首先，我们把csv文件读入一个 DataFrame（有点像Excel表格）：
>>> df = pd.read_csv("c.dat")
>>> df
Country Age All M F
0 UK Under65 30987 15000 15987
1 UK 65andOver 12345 6345 6000
2 Germany Under65 32646 15642 17004
3 Germany 65andOver 14747 7192 7555
4 France Under65 31587 16286 15301
5 France 65andOver 13741 6187 7554
在这里你可以按行、按列等方式访问数据。为了你的需求，我们可以把这些数据“融化”（unpivot）：
>>> df = pd.melt(df, id_vars=["Country", "Age"], var_name="Other")
>>> df
Country Age Other value
0 UK Under65 All 30987
1 UK 65andOver All 12345
2 Germany Under65 All 32646
3 Germany 65andOver All 14747
4 France Under65 All 31587
5 France 65andOver All 13741
6 UK Under65 M 15000
7 UK 65andOver M 6345
8 Germany Under65 M 15642
9 Germany 65andOver M 7192
10 France Under65 M 16286
11 France 65andOver M 6187
12 UK Under65 F 15987
13 UK 65andOver F 6000
14 Germany Under65 F 17004
15 Germany 65andOver F 7555
16 France Under65 F 15301
17 France 65andOver F 7554
这样我们就得到了想要的行标签（国家）和其他列的信息，不管它们是什么，还有数值。你想把“年龄”和“其他”结合起来，所以：
>>> df["Column"] = df.pop("Age") + "_" + df.pop("Other")
>>> df
Country value Column
0 UK 30987 Under65_All
1 UK 12345 65andOver_All
2 Germany 32646 Under65_All
3 Germany 14747 65andOver_All
4 France 31587 Under65_All
5 France 13741 65andOver_All
6 UK 15000 Under65_M
7 UK 6345 65andOver_M
8 Germany 15642 Under65_M
9 Germany 7192 65andOver_M
10 France 16286 Under65_M
11 France 6187 65andOver_M
12 UK 15987 Under65_F
13 UK 6000 65andOver_F
14 Germany 17004 Under65_F
15 Germany 7555 65andOver_F
16 France 15301 Under65_F
17 France 7554 65andOver_F
现在所有的繁琐工作都完成了。我们只需要调用 pivot 来转换数据：
>>> df = df.pivot(index="Country", columns="Column")
>>> df
value \
Column 65andOver_All 65andOver_F 65andOver_M Under65_All Under65_F
Country
France 13741 7554 6187 31587 15301
Germany 14747 7555 7192 32646 17004
UK 12345 6000 6345 30987 15987
Column Under65_M
Country
France 16286
Germany 15642
UK 15000
（在屏幕上看起来更好。）这给了我们一个额外的“值”层级，但你不需要这个，所以我们把它去掉：
>>> df.columns = df.columns.droplevel(0)
>>> df
Column 65andOver_All 65andOver_F 65andOver_M Under65_All Under65_F \
Country
France 13741 7554 6187 31587 15301
Germany 14747 7555 7192 32646 17004
UK 12345 6000 6345 30987 15987
Column Under65_M
Country
France 16286
Germany 15642
UK 15000
然后我们把结果写入csv文件：
>>> df.to_csv("out.csv")