使用pandas read_cs检测头分隔符导入csv文件

Question

又来了。

嗨，我正在检测CSV文件中的错误。

文件应该如下所示

    goodfile.csv

    "COL_A","COL_B","COL_C","COL_D"
    "ROW1COLA","ROW1COLB","ROW1COLC","ROW1COLD"
    "ROW2COLA","ROW2COLB","ROW2COLC","ROW2COLD"
    "ROW3COLA","ROW3COLB","ROW3COLC","ROW3COLD"
    "ROW4COLA","ROW4COLB","ROW4COLC","ROW4COLD"
    "ROW5COLA","ROW5COLB","ROW5COLC","ROW5COLD"
    "ROW6COLA","ROW6COLB","ROW6COLC","ROW6COLD"
    "ROW7COLA","ROW7COLB","ROW7COLC","ROW7COLD"

但我的档案实际上是

^{pr2}$

当我用pandas导入这两个文件时

    data = pd.read_csv('goodfile.csv')
    data = pd.read_csv('brokenfile.csv')

我得到同样的结果

    data

          COL_A     COL_B     COL_C     COL_D
    0  ROW1COLA  ROW1COLB  ROW1COLC  ROW1COLD
    1  ROW2COLA  ROW2COLB  ROW2COLC  ROW2COLD
    2  ROW3COLA  ROW3COLB  ROW3COLC  ROW3COLD
    3  ROW4COLA  ROW4COLB  ROW4COLC  ROW4COLD
    4  ROW5COLA  ROW5COLB  ROW5COLC  ROW5COLD
    5  ROW6COLA  ROW6COLB  ROW6COLC  ROW6COLD
    6  ROW7COLA  ROW7COLB  ROW7COLC  ROW7COLD

不管怎样，我想检测第二个文件中的错误“brokenfile.csv文件标题列之间当前缺少“”的

Answer 1

Pandas试图在读取数据时聪明地识别数据类型。这正是您所描述的情况，COL_C和{}都被解析为字符串。在

总之，没有错误可以检测！至少熊猫在这种情况下不会出错。在

你能做的是，如果你想在标题中发现遗漏的引号，你可以试着用一种更“传统”的python方式来阅读第一行，然后从中得出你自己的结论：

>>> with open('filename') as f:
        lines = f.readlines()
        ....

Answer 2

我认为您可以通过~在DataFrame的列中检测到缺失的"，用~反转{}的DataFrame：

import pandas as pd
import io

temp=u'''"COL_A","COL_B",COL C,"COL_D"
"ROW1COLA","ROW1COLB","ROW1COLC","ROW1COLD"
"ROW2COLA","ROW2COLB","ROW2COLC","ROW2COLD"
"ROW3COLA","ROW3COLB","ROW3COLC","ROW3COLD"
"ROW4COLA","ROW4COLB","ROW4COLC","ROW4COLD"
"ROW5COLA","ROW5COLB","ROW5COLC","ROW5COLD"
"ROW6COLA","ROW6COLB","ROW6COLC","ROW6COLD"
"ROW7COLA","ROW7COLB","ROW7COLC","ROW7COLD"'''
#after testing replace io.StringIO(temp) to filename
df = pd.read_csv(io.StringIO(temp), quoting = 3)
print df
      "COL_A"     "COL_B"       COL C     "COL_D"
0  "ROW1COLA"  "ROW1COLB"  "ROW1COLC"  "ROW1COLD"
1  "ROW2COLA"  "ROW2COLB"  "ROW2COLC"  "ROW2COLD"
2  "ROW3COLA"  "ROW3COLB"  "ROW3COLC"  "ROW3COLD"
3  "ROW4COLA"  "ROW4COLB"  "ROW4COLC"  "ROW4COLD"
4  "ROW5COLA"  "ROW5COLB"  "ROW5COLC"  "ROW5COLD"
5  "ROW6COLA"  "ROW6COLB"  "ROW6COLC"  "ROW6COLD"
6  "ROW7COLA"  "ROW7COLB"  "ROW7COLC"  "ROW7COLD"

print df.columns
Index([u'"COL_A"', u'"COL_B"', u'COL C', u'"COL_D"'], dtype='object')

print df.columns.str.contains('"')
[ True  True False  True]

print ~df.columns.str.contains('"')
[False False  True False]

print df.columns[~df.columns.str.contains('"')]
Index([u'COL C'], dtype='object')