+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Aggregating CSV Data (/thread-33931.html)
Aggregating CSV Data - nb1214 - Jun-10-2021
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]
RE: Aggregating CSV Data - Axel_Erfurt - Jun-10-2021
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