Parsing A Csv File And Aggregate Values In Python
I'm looking to parse a csv file and aggregate 2 columns. Data in csv file: 'IP Address', Severity 10.0.0.1, High 10.0.0.1, High 10.0.0.1, Low 10.0.0.1, Medium 10.0.0.2, Medium 10.0
Solution 1:
import csv
from collections import defaultdict
withopen('text.txt') as f, open('ofile.csv','w+') as g:
reader,writer = csv.reader(f), csv.writer(g)
results = defaultdict(list)
next(reader) #skip header linefor ip,severity in reader:
results[ip].append(severity)
writer.writerow(["'IP Adress'"," High"," Medium"," Low"]) #Write headersfor ip,severities insorted(results.iteritems()):
writer.writerow([ip]+[severities.count(t) for t in [" High"," Medium"," Low"]])
Produces:
'IP Adress', High, Medium, Low
10.0.0.1,2,1,1
10.0.0.2,1,1,1
10.0.0.3,1,2,0
Solution 2:
Here is my solution, ag.py:
import collections
import csv
import sys
output = collections.defaultdict(collections.Counter)
withopen(sys.argv[1]) as infile:
reader = csv.reader(infile)
reader.next() # Skip header linefor ip,level in reader:
level = level.strip() # Remove surrounding spaces
output[ip][level] += 1print"'IP Address',High,Medium,Low"for ip, count in output.items():
print'{0},{1[High]},{1[Medium]},{1[Low]}'.format(ip, count)
To run the solution, issue the following command:
python ag.py data.csv
Discussion
output
is a dictionary whose keys are the IP, and values arecollections.Counter
objects.- Each counter object counts 'High', 'Medium', and 'Low' for a particular IP
- My solution prints to the stdout, you can modify it to print to file
Post a Comment for "Parsing A Csv File And Aggregate Values In Python"