Python Forum

Full Version: Aggregating CSV Data
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
I have a CSV file that I will be working with, and it contains a lot of data, and two of the columns are "Company Name" and "Sales". I would like to add a "Sales Group" column, which divides all of the companies on the sheet in thirds based on their "Sales". The top 1/3 of companies based on sales would have "High Sales" in the "Sales Group" column, while the middle 1/3 and lower 1/3 would have "Medium Sales" and "Low Sales", respectively. I would like to keep the rest of the data the same, and just add this new column.

I have attached a sample CSV file so it is more clear what I am after. Thank you in advance for any assistance.[attachment=1133]
something like this?

import csv

out_text = "Company,Sales,Sales Group\n"

with open('Data Python (version 1).csv', 'r', newline='') as f:
    reader = csv.reader(f, delimiter=',')
    header = next(reader)
    sortedChanels = sorted(reader, key=lambda row: int(row[1]), reverse=True)
    rows = sortedChanels

for row in rows:
    out_text += ",".join(row)
    out_text += "\n"
    

        
print(out_text)

with open("data_new.csv", "w") as f:
    f.write(out_text)
Output:
Company,Sales,Sales Group Company 3,300000,High Sales Company 5,215000,High Sales Company 2,200000,High Sales Company 8,190000,Medium Sales Company 7,178000,Medium Sales Company 1,150000,Medium Sales Company 10,135000,Low Sales Company 9,125000,Low Sales Company 4,110000,Low Sales