python - 如何验证 .csv 文件中是否引用了所有值

Question

我有数千个 .csv 文件，我需要检查所有文件的值是否都被引用。

我尝试将它们全部放入数据帧列表中，并尝试了一个非常糟糕的代码。我需要这方面的帮助!

def csv_list(folder):
    path = r'C:\\'+folder+'' # use your path
    all_files = glob.glob(path + "/*.csv")
    li = []
    for filename in all_files:
        df = pd.read_csv(filename, index_col=None, header=0)
        li.append(df)
    return li

def check_doublequotes(csvfile):
    if (csvfile.QUOTE_ALL == True):
        print("csv are double quoted")

我收到以下错误

AttributeError:“DataFrame”对象没有属性“QUOTE_ALL”

Answer 1

如果您想检查您的文件是否被一致引用，您可以通过两种方式进行。首先是全部加载到内存中，然后检查一致性。另一种是使用转换器。如果您想节省内存，这可能是一个选择。

将所有内容加载到内存中

第一种可能性的工作原理如下:

import pandas as pd
import csv

# 1. read the file without removing the quotes (all colums will be string)
df= pd.read_csv('yourfile.csv', sep=';', dtype='str', skipinitialspace=True, quoting= csv.QUOTE_NONE)

# 2. now check that all fields are doublequoted:
#    the .str.replace below is called to remove
#    trailing spaces from the fields (behind the quotes)
#    the spaces at the beginning are removed by pandas (because of skipinitialspace=True)
df.apply(lambda ser: ser.str.startswith('"') 
                     & ser.str.replace(r'\s+$', '').str.endswith('"')
        ).all().all()

测试代码:

import io

raw_csv='''""; "Col1"; "Col2" ; "Col3"; "C12"; "index"
"0"; "Bob"; "Joe"; "0.218111"; "BobJoe"; "1"
"1"; "Joe"; "Steve"; "0.849890"; "JoeSteve"; "2"
"2"; "Bill"; "Bob"; "0.316259"; "BillBob"; "0"
"3"; "Mary"; "Bob"; "0.179488"; "MaryBob"; "3"
"4"; "Joe"; "Steve"; "0.129853"; "JoeSteve"; "2"
"5"; "Anne"; "NaN"; "0.752859" ; "NaN"; "-1"
"6"; "NaN"; "Bill"; "0.414644"; "NaN"; "-1"
"7"; "NaN"; "NaN"; "0.026471"; "NaN"; "-1"'''

df= pd.read_csv(
        io.StringIO(raw_csv), 
        sep=';', index_col=[0], 
        dtype='str', 
        skipinitialspace=True, 
        quoting= csv.QUOTE_NONE)

print(df.apply(lambda ser: ser.str.startswith('"') 
                           & ser.str.replace(r'\s+$', '').str.endswith('"')
              ).all().all())
--> True

如果您愿意，您还可以使输出更详细一些。例如。如果您根据得到的 id "2" 删除 Bob 周围的引号，则总体结果 False (当然)并且:

df.apply(lambda ser: ser.str.startswith('"') 
                     & ser.str.replace(r'\s+$', '').str.endswith('"')
        ).all(axis='index')
-->
"Col1"      True
"Col2"     False
"Col3"      True
"C12"       True
"index"     True
 dtype: bool

df.apply(lambda ser: ser.str.startswith('"') 
                     & ser.str.replace(r'\s+$', '').str.endswith('"')
        ).all(axis='column')
-->
"0"     True
"1"     True
"2"    False
"3"     True
"4"     True
"5"     True
"6"     True
"7"     True

使用转换器

带转换器的版本工作原理如下:

# define a check function (a converter from string to bool):
def check_quotes(val):
    stripped= val.strip()
    return stripped.startswith('"') & stripped.endswith('"')

# create a converter dict (just use a dict comprehension 
# if you don't know the column names, just make sure you
# chose a range at least as large as you have columns in
# your files (if your range is larger, it doesn't hurt)
conv_dict= {i: check_quotes for i in range(100)}
df= pd.read_csv('yourfile.csv', sep=';', index_col=[0], converters=conv_dict, quoting= csv.QUOTE_NONE)

# if the file is consistently quoted, the following line prints True
df.any().any()