Pandas read_csv(): 保留0为0（不转换为NaN）
我正在尝试读取一个csv文件，下面是一个示例：
datetime,check,lat,lon,co_alpha,atn,status,bc
2012-10-27 15:00:59,2,0,0,2.427,,,
2012-10-27 15:01:00,2,0,0,2.407,,,
2012-10-27 15:02:49,2,0,0,2.207,-17.358,0,-16162
2012-10-27 15:02:50,2,0,0,2.207,-17.354,0,8192
2012-10-27 15:02:51,1,0,0,2.207,-17.358,0,-8152
2012-10-27 15:02:52,1,0,0,2.207,-17.358,0,648
2012-10-27 15:06:03,0,51.195076,4.444407,2.349,-17.289,0,4909
2012-10-27 15:06:04,0,51.195182,4.44427,2.344,-17.289,0,587
2012-12-05 09:21:34,,,,,42.960,1,16430
2012-12-05 09:21:35,,,,,42.962,1,3597
我遇到的问题是，在只有整数的列中，0被转换成了NaN（比如'check'和'status'这两列，它们本来都是整数，但因为有真实的缺失值，所以这一列被读取成了浮点数）。但我只希望空值被转换成NaN，而不是0。
这是我得到的结果：
>>> pd.read_clipboard(sep=',', parse_dates=True, index_col=0)
check lat lon co_alpha atn status bc
datetime
2012-10-27 15:00:59 2 0.000000 0.000000 2.427 NaN NaN NaN
2012-10-27 15:01:00 2 0.000000 0.000000 2.407 NaN NaN NaN
2012-10-27 15:02:49 2 0.000000 0.000000 2.207 -17.358 NaN -16162
2012-10-27 15:02:50 2 0.000000 0.000000 2.207 -17.354 NaN 8192
2012-10-27 15:02:51 1 0.000000 0.000000 2.207 -17.358 NaN -8152
2012-10-27 15:02:52 1 0.000000 0.000000 2.207 -17.358 NaN 648
2012-10-27 15:06:03 NaN 51.195076 4.444407 2.349 -17.289 NaN 4909
2012-10-27 15:06:04 NaN 51.195182 4.444270 2.344 -17.289 NaN 587
2012-12-05 09:21:34 NaN NaN NaN NaN 42.960 1 16430
2012-12-05 09:21:35 NaN NaN NaN NaN 42.962 1 3597
所以，在'check'和'status'这两列中，有太多的NaN。而在'lat'和'lon'这两列中，0并没有被转换成NaN。
使用
na_values=''和keep_default_na=False并没有帮助。有没有办法指定不把整数0转换成NaN？还是说这是个bug？我可以用
dtype关键字指定特定列的数据类型为整数，这样0就会保持为0，但问题是这些列也包含真实的NaN（空值）。所以在这种情况下，这些值也会被转换成0，因为在整数列中不能有NaN。因此，我必须把所有列都保持为浮点数。
编辑：在升级到pandas 0.10.1后，即使不指定 keep_default_na 和 na_values，它也能按预期工作：
>>> pd.read_clipboard(sep=',', parse_dates=True, index_col=0)
check lat lon co_alpha atn status bc
datetime
2012-10-27 15:00:59 2 0.000000 0.000000 2.427 NaN NaN NaN
2012-10-27 15:01:00 2 0.000000 0.000000 2.407 NaN NaN NaN
2012-10-27 15:02:49 2 0.000000 0.000000 2.207 -17.358 0 -16162
2012-10-27 15:02:50 2 0.000000 0.000000 2.207 -17.354 0 8192
2012-10-27 15:02:51 1 0.000000 0.000000 2.207 -17.358 0 -8152
2012-10-27 15:02:52 1 0.000000 0.000000 2.207 -17.358 0 648
2012-10-27 15:06:03 0 51.195076 4.444407 2.349 -17.289 0 4909
2012-10-27 15:06:04 0 51.195182 4.444270 2.344 -17.289 0 587
2012-12-05 09:21:34 NaN NaN NaN NaN 42.960 1 16430
2012-12-05 09:21:35 NaN NaN NaN NaN 42.962 1 3597
1 个回答
5
你首先需要把 keep_default_na 设置为 False：
df = pd.read_clipboard(sep=',', index_col=0, keep_default_na=False, na_values='')
In [2]: df
Out[2]:
check lat lon co_alpha atn status bc
datetime
2012-10-27 15:00:59 2 0.000000 0.000000 2.427 NaN NaN NaN
2012-10-27 15:01:00 2 0.000000 0.000000 2.407 NaN NaN NaN
2012-10-27 15:02:49 2 0.000000 0.000000 2.207 -17.358 0 -16162
2012-10-27 15:02:50 2 0.000000 0.000000 2.207 -17.354 0 8192
2012-10-27 15:02:51 1 0.000000 0.000000 2.207 -17.358 0 -8152
2012-10-27 15:02:52 1 0.000000 0.000000 2.207 -17.358 0 648
2012-10-27 15:06:03 0 51.195076 4.444407 2.349 -17.289 0 4909
2012-10-27 15:06:04 0 51.195182 4.444270 2.344 -17.289 0 587
2012-12-05 09:21:34 NaN NaN NaN NaN 42.960 1 16430
2012-12-05 09:21:35 NaN NaN NaN NaN 42.962 1 3597
这是来自 read_tables 的文档说明：
keep_default_na: 布尔值，默认是 True
如果你指定了na_values并且把keep_default_na设置为False，那么默认的NaN
值会被覆盖，否则会被添加到列表中
na_values: 类似列表或字典，默认是None
额外的 字符串，用来识别为 NA/NaN。如果传入字典，可以为每一列指定 NA 值