Odtnhj

I have found other posts very closely related to this, but they are not helping.

I have a Master CSV file, and I need to find specific 'string' from the third column. Shown below:

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

The ID column is unique, and so I was trying to find 'KTD684'(for expample). Once found, I need to export the values of "Date", "Prj1_Assigned", "Prj1_closed", "Prj2_assigned" and "Prj2_solved".

The export would be to a file 'KTD684.csv'(same as ID) where there is already headers available 'Date,Prj1_Assigned,Prj1_closed,Prj2_assigned,Prj2_solved'

So far (as I am a non-programmer) I have not been able to draft this, but can one please be kind to guide me in:

1. Finding the row with the element 'KTD684'.


2. Selecting the values of the below from that row:
   ['Date,Prj1_Assigned,Prj1_closed,Prj2_assigned,Prj2_solved']


3. Appending the file with the ID name itself please('KTD684.csv')

I need to perform this for 45 userIDs, and now with hiring in company, its 195. I tried to write excel macro(didn't work either), but I feel python is most reliable.

I know I need to at least show the basic progress, but after over 2 months of trying to learn from someone, I'm still unable to find the element in this csv.

This is an ideal use-case for pandas:

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"])

This will open the CSV, only select rows that are in your list (id_list), group by the values in the ID column and save individual CSV files for each unique ID. You just need to expand id_list to have the ids you are interested in.

Extended example:

Reading in the CSV results in a DataFrame object like this:

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

If you just select KTD684 and 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

The groupby operation groups on ID and export each unique ID to a CSV file resulting in:

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

If I understand your problem correctly; You need to read from 2 input files:

• 1 containing the users IDs you are looking for




• 2 containing the project data related to users

In that fashion something like this would find all the users you specify in 1 in file 2 and write them out to result.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()

For example, search_for.csv looks like this

Here's a pure python approach which reads the master .csv file with csv.DictReader, matches the ids, and appends the file data into a new or existing .csv file with csv.DictWriter():

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)

Which can be called like this:

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

And produces the following KTD684.csv:

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