python csv list compare and addition

Question

Parsing & Adding Client Data

Data:
('Client 1', '13.2')
('Client 1', '22.4')
('Client 1', '1.2')
('Client 2', '3.4')
('Client 3', '12.3')
('Client 3', '3.221')
('Client 4', '234.44')

Trying to write the proper loop and adding feature to get the right output.

Goal Result:

Client 1: 36.8
Client 2: 3.4
Client 3: 15.521
Client 4: 234.44

This is the code I finally got to list the data correctly. Where do I go from here to get the result. I have tried a number of different loops with no success.

import csv

with open('clientdata.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    numbers = []
    for row in reader:
       print(row['Client Name'], row['Earnings'])

or use dictionary `numbers[ row['Client Name'] ] += row['Earnings']` to sum values — furas, Jan 19 '16 at 22:25
Do you actually have parenthesis in your data file? i.e., is it `('Client 1', '13.2')` or `'Client 1', '13.2'` in the file? — dawg, Jan 19 '16 at 22:40

mattsap · Accepted Answer · 2016-01-20T00:56:37.473

2

You can solve it by using a format string (%.2f will have 2 decimal places) and dictionary keeping track of who makes how much.

clients = {}
with open('clientdata.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    numbers = []
    for row in reader:
       name = row['Client Name']
       earnings = float(row['Earnings'])
       if name in clients:
           clients[name] += earnings
       else:
           clients[name] = earnings
    for client in sorted(clients):
          print("%s:%.2f" % (client, clients[client]))

edited Jan 20 '16 at 00:56

answered Jan 19 '16 at 22:20

mattsap

3,790
1
15
36

This works great! The order of the clients gets trashed in the process. How do you list this clients A-Z? – Troy Wilson Jan 20 '16 at 00:46
The issue is that dictionaries do not maintain order. Therefore, we need to sort the clients names. I've adjusted the solution to incorporate the clients being sorted. – mattsap Jan 20 '16 at 00:48

deltashade · Answer 2 · 2016-01-19T22:43:40.977

You should create a dictionary with all the clients, afterwards check if the client already is in the directory. If the client is not then add them with their earning. If they are in it, then just add to their existing earning the next earning.

Here is a code of how I would go about doing this. Hope this helps:

clientDirectory = {}
for row in reader:
    if row['Client Name'] not in clientDirectory:
        clientDirectory[row['Client Name']] = float(row['Earnings'])
    else:
        [row['Client Name']] += float(row['Earnings'])

for key in clientDirectory.keys():
    print("%s %f" %(key, clientDirectory[key]))

I think the clientDirectory[key] is a float, you should use %f rather than %s when printing. — mattsap, Jan 19 '16 at 22:35

score 1 · Answer 3 · answered Jan 19 '16 at 22:29

1

You can use dictionary do add values

with open('clientdata.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    results = {}

    # adding

    for row in reader:

       # default value for new client
       if row['Client Name'] not in results:
          results[row['Client Name']] = 0

       # add value
       results[row['Client Name']] += float(row['Earnings'])

    # showing

    for name, value in results.items():
        print("%s: %s" % (name,value))

But it can be one problem - dictionary doesn't have to keep order and you can see results in different order.

answered Jan 19 '16 at 22:29

furas

134,197
12
106
148

Won't this cause a problem in your print statement since value will be a float and your format string is type string? – mattsap Jan 19 '16 at 22:34
python should convert float to string - but using for example `%.3f` I could get rounded results if I need them. – furas Jan 19 '16 at 22:37
1

And of course you can use an Ordered Dictionary (from collections) to keep the order. – rotten Jan 19 '16 at 22:52

score 0 · Answer 4 · edited May 23 '17 at 12:30

Assuming you have a datafile like so:

$ cat /tmp/data.csv
'Client 1', '13.2'
'Client 1', '22.4'
'Client 1', '1.2'
'Client 2', '3.4'
'Client 3', '12.3'
'Client 3', '3.221'
'Client 4', '234.44'

You can use defaultdict to add up the float values in the file:

from collections import defaultdict
import csv

dd=defaultdict(float)

with open('/tmp/data.csv') as data:
    for row in csv.reader(data, quotechar="'", skipinitialspace=True):
        dd[row[0]]+=float(row[1])

print '\n'.join(["{}: {}".format(k, dd[k]) for k in sorted(dd)])

Prints:

Client 1: 36.8
Client 2: 3.4
Client 3: 15.521
Client 4: 234.44

(The sorting I used only works for Client 1 - Client 9; after that you need a natural sort method...)

score 0 · Answer 5 · answered Jan 19 '16 at 23:12

$ cat test.csv
Client 1, 13.2
Client 1, 22.4
Client 1, 1.2
Client 2, 3.4
Client 3, 12.3
Client 3, 3.221
Client 4, 234.44

And then in Python:

from odo import odo
from pandas import DataFrame

clientDF = odo('test.csv', DataFrame)
print clientDF.groupby(['0']).sum()

python csv list compare and addition

Goal Result:

5 Answers5