Open CSV, sort a specific column and overwrite existing CSV

Question

I'm been stuck on this one for a while. I'm trying to open a csv, sort by severity (Critical, High, Medium, Low) then overwrite the existing file. I'd also like to ignore the first wrote or add a header row.

Original CSV

IP Address  Severity    Score
10.0.0.1    High        2302
172.65.0.1  Low         310
192.168.0.1 Critical    5402
127.0.0.1   Medium      1672`

Modified/Sorted CSV

IP Address  Severity    Score
192.168.0.1 Critical    5402
10.0.0.1    High        2302
127.0.0.1   Medium      1672 
172.65.0.1  Low         310

Code

import csv
crit_sev = "Critical"
high_sev = "High"
med_sev = "Medium"
low_sev = "Low"
reader = csv.reader(open('sample.csv', 'r'))
row=0
my_list = []
for row in reader:
    if row[1] == crit_sev:
        my_list.append(row)
    elif row[1] == high_sev:
        my_list.append(row)
    elif row[1] == med_sev:
        my_list.append(row)
    elif row[1] == low_sev:
        my_list.append(row)

writer = csv.writer(open("sample.csv", 'w'))
header = ['IP Address', 'Severity', 'Score']
writer.writerow([header])
for word in my_list:
    writer.writerow([word])

Any help would be appreciated.

Why not just open the CSV in Excel or something and sort it there? — TigerhawkT3
– TigerhawkT3, Commented Jan 23, 2017 at 0:26
CSV == Comma Separated Values. I don't see any commas in your file, so that's probably the first issue. Is it perhaps tab separated or fixed format? Fixed seems unlikely, because when the IP address 192.168.0.254 comes along, you're not going to have enough room. The general idea is to read each record in, classify it according to severity, and store it away in a data structure. Then when done, write the new data structure in order of severity. — Ian McGowan
– Ian McGowan, Commented Jan 23, 2017 at 0:42

DYZ · Accepted Answer · 2017-01-23 00:49:58Z

2

Here's a pandas solution:

import pandas as pd
# Read the CSV file
data = pd.read_csv('sample.csv')

# Configure the levels of severity
levels = pd.Series({"Critical" : 0, "High" : 1, "Medium" : 2, "Low" : 3})
levels.name='Severity'

# Add numeric severity data to the table
augmented = data.join(levels,on='Severity',rsuffix='_')

# Sort and select the original columns
sorted_df = augmented.sort_values('Severity_')[['IP Address', 'Severity','Score']]

# Overwrite the original file
sorted_df.to_csv('sample.csv',index=False)

edited Jan 23, 2017 at 0:49

answered Jan 23, 2017 at 0:26

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

TigerhawkT3 Over a year ago

Would you need to define the various levels, as they're words rather than numbers?

DYZ Over a year ago

@TigerhawkT3 Theoretically, yes. But in this very case the order of severity happily matches the alphabetic order ('C' < 'H' < 'M' < 'L').

TigerhawkT3 Over a year ago

'M' < 'L'? Really?

DYZ Over a year ago

@TigerhawkT3 Oops... You've got me.

TigerhawkT3 Over a year ago

:) It'd probably be good to show how to define the levels regardless, as a match of that sort would just be an arbitrary coincidence anyway.

Martin Evans · Accepted Answer · 2017-01-23 08:35:34Z

1

You could use Python's csv library to do this as follows:

import socket     
import csv

severity = {"Critical" : 0, "High" : 1, "Medium" : 2, "Low" : 3}     

with open('sample.csv', 'rb') as f_input:
    csv_input = csv.reader(f_input)
    header = next(csv_input)
    data = sorted(csv_input, key=lambda x: (severity[x[1]], socket.inet_aton(x[0])))

with open('sample.csv', 'wb') as f_output:
    csv_output = csv.writer(f_output)
    csv_output.writerow(header)
    csv_output.writerows(data)

This preserves the existing header and sorts the entries based on the severity column. Next it also (optionally) sorts by IP address (which may or may not be useful to you) using socket.inet_aton() to convert the IP address into a sortable number.

For example:

IP Address,Severity,Score
10.168.0.1,Critical,5402
192.168.0.1,Critical,5402
10.0.0.1,High,2302
127.0.0.1,Medium,1672
172.65.0.1,Low,310

answered Jan 23, 2017 at 8:35

Martin Evans

46.9k17 gold badges88 silver badges104 bronze badges

1 Comment

cyber_raven Over a year ago

Thank you so much!

Collectives™ on Stack Overflow

Open CSV, sort a specific column and overwrite existing CSV

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related