Python Forum
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Aggregating CSV Data
#1
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.
.csv   Data Python (version 1).csv (Size: 292 bytes / Downloads: 290)
Reply
#2
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
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Basic one: Aggregating from a dictionary Mustey 1 1,879 Aug-12-2019, 07:33 PM
Last Post: buran

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020