Python CSV：如果一行满足一个条件，如何忽略编写类似的行？

Question

我目前正在跟踪大规模的录像带数字化，需要帮助从多个CSV中提取数据。大多数磁带都有多个拷贝，但我们只对一组磁带进行数字化。我想创建一个新的CSV，其中只包含尚未数字化的节目磁带。以下是我的原始CSV的模型：

Date Digitized |  Series  | Episode Number | Title | Format
---------------|----------|----------------|-------|--------
01-01-2016     | Series A |       101      |       |  VHS
               | Series A |       101      |       |  Beta
               | Series A |       101      |       |  U-Matic
               | Series B |       101      |       |  VHS

从这里开始，我想忽略所有包含“Series A”和“101”的字段，因为这个显示在“Date digitalized”单元格中有一个值。我试图隔离这些条件，但似乎无法获得未数字化内容的完整列表。这是我的密码：

import csv, glob

names = glob.glob("*.csv")
names = [os.path.splitext(each)[0] for each in names]

for name in names:
    with open("%s_.csv" % name, "rb") as source:
        reader = csv.reader( source )
        with open("%s_edit.csv" % name,"wb") as result:
            writer = csv.writer( result )
            for row in reader:
                if row[0]:
                    series = row[1]
                    epnum = row[2]
                if row[1] != series and row[2] != epnum:
                    writer.writerow(row)

我要补充的是，这是我的第一个问题，我对Python非常陌生，所以任何建议都将不胜感激！你知道吗

Answer 1

我不能百分之百肯定我了解你的需要。不过，这可能会让你走上正轨。我正在使用^{}模块：

data = """
Date Digitized |  Series  | Episode Number | Title | Format
       -|     |        |   -|    
01-01-2016     | Series A |       101      |       |  VHS
               | Series A |       101      |       |  Beta
               | Series A |       101      |       |  U-Matic
               | Series B |       101      |       |  VHS"""
# useful module for treating csv files (and many other)
import pandas as pd
# module to handle data as it was a csv file
import io
# read the csv into pandas DataFrame
# use the 0 row as a header
# fields are separated by |
df = pd.read_csv(
    io.StringIO(data),
    header=0,
    sep="|"
)
# there is a bit problem with white spaces
# remove white space from the column names
df.columns = [x.strip() for x in df.columns]
# remove white space from all string fields
df = df.applymap(lambda x: x.strip() if type(x) == str else x)
# finally choose the subset we want
# for some reason pandas guessed the type of Episode Number wrong
# it should be integer, this probably won't be a problem when loading 
# directly from file
df = df[~((df["Series"] == "Series A") & (df["Episode Number"] == "101"))]
# print the result
print(df)
#     Date Digitized      Series    Episode Number    Title    Format
# 0         -                      -      
# 4                     Series B               101                VHS

请随意询问，希望我能够根据您的实际需要更改代码或以任何其他方式提供帮助。你知道吗

Answer 2

最简单的方法是对CSV文件集进行两次读取：一次读取所有数字化磁带的列表，第二次读取不在数字化列表中的所有磁带的唯一列表：

# build list of digitized tapes
digitized = []
for name in names:
    with open("%s_.csv" % name, "rb") as source:
        reader = csv.reader(source)
        next(reader) # skip header
        for row in reader:
            if row[0] and ((row[1], row[2]) not in digitized):
                digitized.append((row[1], row[2]))

# build list of non-digitized tapes
digitize_me = []
for name in names:
    with open("%s_.csv" % name, "rb") as source:
        reader = csv.reader(source)
        header = next(reader)[1:3] # skip / save header
        for row in reader:
            if not row[0] and ((row[1], row[2]) not in digitized + digitize_me):
                digitize_me.append((row[1], row[2]))

# write non-digitized tapes to 'digitize.csv`
with open("digitize.csv","wb") as result:
    writer = csv.writer(result)
    writer.writerow(header)
    for tape in digitize_me:
        writer.writerow(tape)

输入文件1:

Date Digitized,Series,Episode Number,Title,Format
01-01-2016,Series A,101,,VHS
,Series A,101,,Beta
,Series C,101,,Beta
,Series D,102,,VHS
,Series B,101,,U-Matic

输入文件2:

Date Digitized,Series,Episode Number,Title,Format
,Series B,101,,VHS
,Series D,101,,Beta
01-01-2016,Series C,101,,VHS

输出：

Series,Episode Number
Series D,102
Series B,101
Series D,101

---

根据OP评论

header = next(reader)[1:3] # skip / save header

有两个目的：

1. 假设每个csv文件都以一个头文件开头，我们不希望 读取标题行，就好像它包含了关于我们磁带的数据，所以我们 在这种意义上需要“跳过”标题行
2. 但我们还想保存标题的相关部分，以备将来使用 我们编写输出csv文件。我们希望那个文件有一个头 也。因为我们只写series和episode number，它们是row字段1和2，所以我们只分配那个片， i、 例如，[1:3]，将头行的

让一行代码服务于两个完全不相关的目的并不是真正的标准，这就是为什么我对它进行了注释。当header只需要分配一次时，它还会多次分配给header（假设有多个输入文件）。也许写这一节的更简洁的方法是：

# build list of non-digitized tapes
digitize_me = []
header = None
for name in names:
    with open("%s_.csv" % name, "rb") as source:
        reader = csv.reader(source)
        if header:
            next(reader) # skip header
        else:
            header = next(reader)[1:3] # read header
        for row in reader:
            ...

问题是哪种形式更具可读性。无论哪种方法都很接近，但我认为将5行合并为一行，可以将重点放在代码中更突出的部分。下次我可能会用另一种方式。你知道吗