get value of one column by another column in csv file python

Question

I have my csv file like this:

ID        Value      Amount 
----      -------    -------
A           3          2
A           4          4
B           3          6
C           5          5
A           3          2
B           10         1

I want sum of column "Value" or "Amount" by the column "ID". I want the output that for 'A' it should give me sum of all values which is related to A means [3+4+3].

My Code:

import csv
file = open(datafile.csv)
rows=csv.DictReader(file)
summ=0.0
count=0
for r in rows:
  summ=summ+int(r['Value'])
  count=count+1
print "Mean for column Value is: ",(summ/count)
file.close()

mhawke · Accepted Answer · 2015-10-19 22:45:30Z

1

You can use a defaultdict of list to group the data by the ID column. Then use sum() to produce the totals.

from collections import defaultdict

with open('datafile.csv') as f:
    d = defaultdict(list)
    next(f)    # skip first header line
    next(f)    # skip second header line
    for line in f:
        id_, value, amount = line.split()
        d[id_].append((int(value), int(amount)))

# sum and average of column Value by ID
for id_ in d:
    total = sum(t[0] for t in d[id_])
    average = total / float(len(d[id_]))
    print('{}: sum = {}, avg = {:.2f}'.format(id_, total, average))

Output for your input data:

A: sum = 10, avg = 3.33
C: sum = 5, avg = 5.00
B: sum = 13, avg = 6.50

It can also be done with a standard Python dictionary. The solution is very similar:

with open('datafile.csv') as f:
    d = {}
    next(f)    # skip first header line
    next(f)    # skip second header line
    for line in f:
        id_, value, amount = line.split()
        d[id_] = d.get(id_, []) + [(int(value), int(amount))]

# sum and average of column Value by ID
for id_ in d:
    total = sum(t[0] for t in d[id_])
    average = total / float(len(d[id_]))
    print('{}: sum = {}, avg = {:.2f}'.format(id_, total, average))

edited Oct 19, 2015 at 22:45

answered Oct 12, 2015 at 3:45

mhawke

87.5k10 gold badges122 silver badges142 bronze badges

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

6 Comments

mohammad shahbaz Khan Over a year ago

I don't want to use collection.

mhawke Over a year ago

@mohammadshahbazKhan: really? Why not?

mohammad shahbaz Khan Over a year ago

I am clearing my basic skills on python. I am trying to find out the ways without using collection.

mhawke Over a year ago

Does a dictionary count as a "collection"? What about a list? It's pretty difficult to do this without some kind of collection like data structure.

mohammad shahbaz Khan Over a year ago

you can use dictionary or List.

|

Collectives™ on Stack Overflow

get value of one column by another column in csv file python

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related