Python Sum excel file

Question

I have an excel file that has 3 columns.
Please example below

Name      Produce     Number
Adam      oranges     6
bob       Apples      5
Adam      Apples      4
steve     Peppers     7
bob       Peppers     16
Adam      oranges     5

I need to generate the sum in python in such a way

Name      Produce     Number
Adam      oranges     11
bob       apples      5
steve     peppers     7
etc

I am new to python and trying to figure out how to write the output of each persons totals? is there an easy way to gather this data from excel?

This is quite easy if you know where in the Python library to look. Have you already managed to open the Excel file in Python? — Fred Foo, Jun 12 '12 at 21:49
yes I can open the file in excel, but do I have to open the excel file first, to be able to write it to python? — Trying_hard, Jun 12 '12 at 21:56

tabchas · Accepted Answer · 2012-06-13T02:28:46.273

4

Here is the code for it:

Be sure to look at the comments and post back here if you have any questions (or if I made any mistakes)

import csv

file  = open('names.csv', "rb") #Open CSV File in Read Mode
reader = csv.reader(file)      #Create reader object which iterates over lines

class Object:                   #Object to store unique data
    def __init__(self, name, produce, amount):
        self.name = name
        self.produce = produce
        self.amount = amount

rownum = 0 #Row Number currently iterating over
list = []  #List to store objects

def checkList(name, produce, amount):

    for object in list:  #Iterate through list        
        if object.name == name and object.produce == produce:  #Check if name and produce combination exists
            object.amount += int(amount) #If it does add to amount variable and break out
            return

    newObject = Object(name, produce, int(amount)) #Create a new object with new name, produce, and amount
    list.append(newObject)  #Add to list and break out


for row in reader:  #Iterate through all the rows
    if rownum == 0:  #Store header row seperately to not get confused
        header = row
    else:
        name = row[0]  #Store name
        produce = row[1]  #Store produce
        amount = row[2]  #Store amount

        if len(list) == 0:  #Default case if list = 0
            newObject = Object(name, produce, int(amount))
            list.append(newObject)
        else:  #If not...
            checkList(name, produce, amount)


rownum += 1

for each in list: #Print out result
    print each.name, each.produce, each.amount

file.close() #Close file

edited Jun 13 '12 at 02:28

answered Jun 13 '12 at 01:24

tabchas

1,374
2
18
37

1

I run it, and I am getting the output of just - - - Adam Oranges 5 It does look up the other names and sum up the total for Adam and oranges, I am trying to work through it..... let me see what I can get. Will be a chance to try to leran how to fix a problem, but any tips would be helpful :) – Trying_hard Jun 13 '12 at 01:47
2

Yea i see a problem... one second im trying to fix it.... sorry i just send what I had without really testing it. – tabchas Jun 13 '12 at 01:56
1

no problem. I am trying to see what I can get but just messing it up more. I am new and very very slow, but this helps alot of seeing how someone that knows what they are doing would set it up. – Trying_hard Jun 13 '12 at 01:57
2

Take a look at it again. I fixed up the code. Also try making sure all the indentations are exactly like it is typed here. – tabchas Jun 13 '12 at 02:23
1

ok got this ..... Traceback (most recent call last): File "C:/Python27/test", line 22, in newObject = Object(name, produce, int(amount)) #Create a new object with new name, produce, and amount NameError: name 'name' is not defined – Trying_hard Jun 13 '12 at 02:27
2

Sorry, missed that indentation while copying the code to StackOverflow... look at my edits on the code again.. – tabchas Jun 13 '12 at 02:29
2

let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/12471/discussion-between-tabchas-and-adam-g) – tabchas Jun 13 '12 at 02:33
1

I actually got that one, but now I run it and get no errors but no data generated in python? – Trying_hard Jun 13 '12 at 02:33
2

Click continue discussion in chat. – tabchas Jun 13 '12 at 02:43

score 3 · Answer 2 · answered Jun 12 '12 at 21:55

3

Should be very straightforward once you have the Excel file open. If it is saved as a .csv file, use this documentation: http://docs.python.org/library/csv.html

Use this link to then iterate over the records and get the sum of each of the Name and Type: http://www.linuxjournal.com/content/handling-csv-files-python

answered Jun 12 '12 at 21:55

tabchas

1,374
2
18
37

2

+1 for `csv` -- way easier to use than `xlrd` -- especially if you don't care about formatting – Jeff Tratner Jun 12 '12 at 22:27
2

Yea usually people dont leave it in excel as you can easily save it as .csv. – tabchas Jun 12 '12 at 23:57
1

how would I sum the number column together, I am having problem with that. I want all of lines with "bobs" "apples" to be a total sum of all lines that contain those two? Sorry probably really easy – Trying_hard Jun 13 '12 at 00:03
2

Yea no problem... sorry i was busy ill do it now. – tabchas Jun 13 '12 at 00:37

score 2 · Answer 3 · edited May 23 '17 at 11:59

2

1) Read Excel file with http://pypi.python.org/pypi/xlrd

2) Iterate over the records, and use a composite key (Name, Produce) in dictionary ( Object of custom type as dictionary key ) to accumulate sum

edited May 23 '17 at 11:59

Community

1
1

answered Jun 12 '12 at 21:50

dbf

6,399
2
38
65

Python Sum excel file

3 Answers3

Linked