如何将改变结构的csv读入数据帧

Question

我试图通过read_csv导入的csv给我带来了麻烦，因为中的某些行缺少值，这会导致尾随逗号，从而搞乱了如何将其读取到数据帧中

来自Kaggle: Facebook Ad Campaign的数据

CSV示例行

1121593,26/08/2017,26/08/2017,1178,144622,45-49,M,10,16,16,1177535,221,365.6600009,15,3
1121594,26/08/2017,26/08/2017,45-49,M,10,14,14,426500,72,128.2799988,4,1,,

df = pd.read_csv('test.csv', header=None)

# result
        0           1           2      3       4      5   6   7       8   9            10   11           12  13 14
0  1121593  26/08/2017  26/08/2017   1178  144622  45-49   M  10      16  16      1177535  221  365.6600009  15  3
1  1121594  26/08/2017  26/08/2017  45-49       M     10  14  14  426500  72  128.2799988    4            1       

所需

        0           1           2       3         4      5  6   7   8   9        10   11          12  13  14
0  1121593  26/08/2017  26/08/2017  1178.0  144622.0  45-49  M  10  16  16  1177535  221  365.660001  15   3
1  1121594  26/08/2017  26/08/2017     NaN       NaN  45-49  M  10  14  14   426500   72  128.279999   4   1

对如何处理这些数据有什么建议吗？理想情况下，缺失的值（第二行中45-49之前）将显示为NaN

Answer 1

当您得到有已知故障的坏数据集时，一个好的解决方案是清除数据并将好的数据写回磁盘。该代码只需在下载后运行，而您的其他代码不会因修复程序的错误而负担过重。这最好使用csv模块来完成，我们可以逐行修复

kaggle_campaign_data_fixer.py

import sys
import csv
from pathlib import Path

filename = Path(sys.argv[1])
newname = filename.parent/f"{filename.stem}-fixed{filename.suffix}"

BADCOLS = ['', '']

with open(filename, newline='') as infile, open(newname, 'w', newline='') as outfile:
    writer = csv.writer(outfile)
    for row in csv.reader(infile):
        if row[-2:] == BADCOLS:
            row[3:3] = BADCOLS
            del row[-2:]
        writer.writerow(row)

# test it
import pandas as pd

df = pd.read_csv(filename, header=None)
print(df)
print("""
============== FIXED ==================
""")
df = pd.read_csv(newname, header=None)
print(df)