如何返回csv中包含特定列中特定单词或短语的所有行？

Question

我有一个包含数据集的csv文件（在本例中是地址）。我想制作第二个csv文件，其中只包含在特定列中有一组短语的条目。例如，我想返回所有的人谁目前住在“绿”，但不是那些谁以前住在那里或从来没有住在那里。你知道吗

示例数据为：

First Name,Second Name,ID,Home Town,County,Current Town,Street
Sam,Smith,1234,Pallet,North,Orange,Lemon
Jenny,Walton,1456,Viridian,West,York,High View
Alan,Kirk,2378,Orange,West,Viridian,High street
Reese,Small,9840,Minsk,East,Viridian,Ocean Avenue
Audry,Owen,7865,York,South,Blackmarsh,8th Street
Marco,Jefferson,1580,Amsterdam,Central,Oxford,Church Road
Jim,Lowe,5218,Windy City,East,Windy City,Oak
Gillian,Pope,3217,Rome,Central,Rome,Low road

我以前使用过以下代码：

town = ["Viridian", "Rome"]

with open("addresses.csv",) as oldfile, open("Filtered addresses.csv", "w") as newfile:
    for line in oldfile:
        if any(town in line.strip().lower() for town in town):
            newfile.write(line)

但是，这将返回所有列中具有指定城市的行—我只希望在“当前城镇”列中具有指定城市的行。你知道吗

我试过这个：

import csv

town = ["Viridian", "Rome"]

with open("Filtered addresses.csv", "w", encoding="Latin-1") as newfile:

    reader = csv.reader(open("addresses.csv", 'r', encoding="Latin-1"))

    for data in reader:
        if any(town in data[6] for town in town):
            newfile.write(data)

但这会导致一个错误：

TypeError: write() argument must be str, not list

将代码改为：

newfile.write(str(data))

返回一些条目，但它们被格式化为一个长行而不是行。你知道吗

实现我的目标最好的方法是什么？我想保留每种情况下的整行数据。你知道吗

谢谢！你知道吗

Answer 1

熊猫会让它变得非常简单：

import pandas as pd

town = ["Viridian", "Rome"]
# Read csv as pandas dataframe
original = pd.read_csv("addresses.csv", index_col=False)
# Select rows where `Current Town` column's value is in `town`
filtered = original[original['Current Town'].isin(town)]
# Save the filtered dataframe to a file
filtered.to_csv("Filtered addresses.csv")

如果没有安装pandas，则可以通过以下方式轻松安装：

pip install pandas

在命令行中

Answer 2

import csv

town = ["Viridian", "Rome"]

with open("Filtered addresses.csv", "w", encoding="Latin-1") as newfile:

    reader = csv.reader(open("addresses.csv", 'r', encoding="Latin-1"))
    csvwriter = csv.writer(newfile)

    for data in reader:
        if data[6] in town:
            csvwriter.writerow(data)