Microsoft Excel CSV file titled \"testInput\" \"Location\" \"Feature\" \"User\"
ID: 3872780 • Letter: M
Question
Microsoft Excel CSV file titled "testInput"
"Location" "Feature" "User" "NumUser" "HoursUsed" (and 20 more column headers...)
HOST: 1231 test Bob 1 0.5
HOST::1231 test Billy 1 1
HOST: 12315 test Bob 1 1
HOST: 1231 test2 Bob 1 2.5
HOST: 123 Jill 1 1
Microsoft Excel CSV file titled "testOutput"
"User" "Location" "Feature" "HoursUsed"
Bob 1231 test 3
Bob 12315 test2 1
Jill 123 test2 1
------------------------------------------
Edit the code below in Python so that it reads the file: 'testInput" and outputs the data into a microsoft excel csv file titled "testOutput"
Also edit the code so that in the "Location" column, it deletes "HOST:" but leaves everything else. (example: "HOST: 1231" should be "1231")
Python code to edit:
#importing the csv package
import csv
# declaring empty dictionary
d={}
# opening the csv file
with open('testInput.csv') as f:
reader=csv.DictReader(f)
# iterating through every row
for row in reader:
# reading the values by column name
location=row['Location']
feature=row['Feature']
user=row['User']
hoursused=float(row['HoursUsed'])
# excluding the user 'Billy'
if user=='Billy':
continue
else:
# including the user in the dictionary
if user not in d:
d[user]=[location[-3:],feature,hoursused]
else:
# including the feature if not found in the user list
if feature not in d[user]:
d[user].extend([location[-3:],feature,hoursused])
# adding the hoursused to the already included data
else:
d[user][2]+=hoursused
# displaying the collected data
print('"User" "Location" "Feature" "HoursUsed"')
for k,v in d.items():
if len(v) > 3:
while v:
print(k,end=' ')
for j in v[:3]:
print(j,end=' ')
v=v[3:]
print()
else:
print(k,end=' ')
for i in v:
print(i,end=' ')
input("Press Enter to continue...")
Explanation / Answer
#importing the csv package
import csv
# declaring empty dictionary
d={}
# opening the csv file
with open('testInput.csv') as f:
reader=csv.DictReader(f)
# iterating through every row
for row in reader:
# reading the values by column name
# this will give back only the numerical part of the location column
location= filter(str.isdigit, row['Location'])
feature=row['Feature']
user=row['User']
hoursused=float(row['HoursUsed'])
# excluding the user 'Billy'
if user=='Billy':
continue
else:
# including the user in the dictionary
if user not in d:
d[user]=[location,feature,hoursused]
else:
# including the feature if not found in the user list
if feature not in d[user]:
d[user].extend([location,feature,hoursused])
# adding the hoursused to the already included data
else:
d[user][2]+=hoursused
# saving the collected data to ‘testoutput’ csv
with open(' testoutput.csv', 'wb') as csv_file:
writer = csv.writer(csv_file)
for key, value in mydict.items():
writer.writerow([key, value])