Python/Numpy（CSV）：查找值，附加另一个cs

Question

我发现其他帖子与此非常密切相关，但它们没有帮助。你知道吗

我有一个主CSV文件，我需要从第三列中找到特定的“字符串”。如下所示：

Name,ID,Title,Date,Prj1_Assigned,Prj1_closed,Prj2_assigned,Prj2_solved
Joshua Morales,MF6B9X,Tech_Rep, 08-Nov-2016,948,740,8,8
Betty García,ERTW77,SME, 08-Nov-2016,965,854,15,12
Kathleen Marrero,KTD684,Probation, 08-Nov-2016,946,948,na,na
Mark León,GSL89D,Tech_Rep, 08-Nov-2016,951,844,6,4

ID列是唯一的，所以我试图找到“KTD684”（对于expsample）。一旦找到，我需要导出“Date”、“Prj1\u Assigned”、“Prj1\u closed”、“Prj2\u Assigned”和“Prj2\u solved”的值。你知道吗

导出到文件“KTD684.csv”（与ID相同），其中已有可用的标题“Date，Prj1\u Assigned，Prj1\u closed，Prj2\u Assigned，Prj2\u solved”

到目前为止（因为我不是程序员），我还不能起草这篇文章，但能否请一位好心的人来指导我：

1. 正在查找包含元素“KTD684”的行。你知道吗
2. 从该行中选择以下值： ['日期，Prj1\U分配，Prj1\U关闭，Prj2\U分配，Prj2\U解决']
3. 请在文件后面附加ID名称（'KTD684.csv'）

我需要为45个用户ID执行此操作，现在公司招聘了195个用户ID。我试着写excel宏（也没用），但我觉得python是最可靠的。你知道吗

我知道我需要至少显示基本的进展，但在2个多月的努力向别人学习，我仍然无法找到这个csv的元素。你知道吗

Answer 1

这是^{}的理想用例：

import pandas as pd

id_list = ['KTD684']

df = pd.read_csv('input.csv')
# Only keep values that are in 'id_list'
df = df[df['ID'].isin(id_list)]

gb = df.groupby('ID')
for name, group in gb:
    with open('{}.csv'.format(name), 'a') as f:
        group.to_csv(f, header=False, index=False,
                     columns=["Date", "Prj1_Assigned", "Prj1_closed",
                             "Prj2_assigned", "Prj2_solved"])

这将打开CSV，只选择列表中的行（id_list），按ID列中的值分组，并为每个唯一的ID保存单个CSV文件。您只需要展开id_list就可以得到您感兴趣的id。你知道吗

---

扩展示例：

读取CSV会产生如下DataFrame对象：

df = pd.read_csv('input.csv')
               Name      ID      Title          Date  Prj1_Assigned  \
0    Joshua Morales  MF6B9X   Tech_Rep   08-Nov-2016            948
1      Betty García  ERTW77        SME   08-Nov-2016            965
2  Kathleen Marrero  KTD684  Probation   08-Nov-2016            946
3         Mark León  GSL89D   Tech_Rep   08-Nov-2016            951

   Prj1_closed Prj2_assigned Prj2_solved
0          740             8           8
1          854            15          12
2          948            na          na
3          844             6           4

如果您只选择KTD684和GSL89D：

id_list = ['KTD684', 'GSL89D']
df = df[df['ID'].isin(id_list)]
               Name      ID      Title          Date  Prj1_Assigned  \
2  Kathleen Marrero  KTD684  Probation   08-Nov-2016            946
3         Mark León  GSL89D   Tech_Rep   08-Nov-2016            951

   Prj1_closed Prj2_assigned Prj2_solved
2          948            na          na
3          844             6           4

groupby操作对ID进行分组，并将每个唯一的ID导出到CSV文件，结果是：

KTD684.csv
Date,Prj1_Assigned,Prj1_closed,Prj2_assigned,Prj2_solved
08-Nov-2016,946,948,na,na

GSL89D.csv
Date,Prj1_Assigned,Prj1_closed,Prj2_assigned,Prj2_solved
08-Nov-2016,951,844,6,4

Answer 2

下面是一种纯python方法，它用^{}读取主.csv文件，匹配ID，并用^{}将文件数据附加到新的或现有的.csv文件中：

from csv import DictReader
from csv import DictWriter

from os.path import isfile

def export_csv(user_id, master_csv, fieldnames, key_id, extension=".csv"):
    filename = user_id + extension
    file_exists = isfile(filename)

    with open(file=master_csv) as in_file, open(
        file=filename, mode="a", newline=""
    ) as out_file:

        # Create reading and writing objects
        csv_reader = DictReader(in_file)
        csv_writer = DictWriter(out_file, fieldnames=fieldnames)

        # Only write header once
        if not file_exists:
            csv_writer.writeheader()

        # Go through lines and match ids
        for line in csv_reader:
            if line[key_id] == user_id:

                 # Modify line and append to file
                line = {k: v.strip() for k, v in line.items() if k in fieldnames}
                csv_writer.writerow(line)

可以这样称呼：

export_csv(
    user_id="KTD684",
    master_csv="master.csv",
    fieldnames=["Date", "Prj1_Assigned", "Prj1_closed", "Prj2_assigned", "Prj2_solved"],
    key_id="ID",
)

并生成以下KTD684.csv：

Date,Prj1_Assigned,Prj1_closed,Prj2_assigned,Prj2_solved
08-Nov-2016,946,948,na,na

Answer 3

如果我正确理解您的问题，您需要阅读2个输入文件：

• 1包含您要查找的用户ID

• 2包含与用户相关的项目数据

以这种方式，类似这样的东西会在文件2中找到您在1中指定的所有用户，并将它们写出来结果.csv你知道吗

Sepicify your search IDs in search_for.csv. Keep in mind that this will revrite your result.csv every time you run it.

import csv
import sys
import os


inputPatterns = open(os.curdir + '/search_for.csv', 'rt')

# Reader for the IDs (users) you are looking to find (key)
reader = csv.reader(inputPatterns)

ids = []

# reading the IDs you are looking for from search_for.csv
for row in reader:
    ids.append(row[0])
inputPatterns.close()

# Let's see if any of the user IDs we are looking for has any project related info
# if so write them to your output CSV
for userID in ids:
    # Organization list with names and Company ID and reader
    userList = open(os.curdir + '/users.csv', 'rt')
    reader = csv.reader(userList)

    # This will be the output file
    result_f = open(os.curdir + "/" + userID + ".csv", 'w')
    w = csv.writer(result_f)
    # Writing header information
    w.writerow(['Date', 'Prj1_Assigned', 'Prj1_closed', 'Prj2_assigned', 'Prj2_solved'])

    # Scanning for projects for user and appending them
    for row in reader:
        if userID == row[1]:
            w.writerow([row[3], row[4], row[5], row[6], row[7]])
    result_f.close()
    userList.close()

例如，搜索_对于.csv看起来像这样