CODEWITHSUNDEEP
pythonpython-3.xtextinputoutput
Seruza
Seruza

Reputation: 47

How to find the sum of values in a column from a text file if matching certain criteria

I'm having some trouble trying add certain values in a column from a text file together. My text file looks like:

e320,2/3/5,6661,c120,A,6661
e420,6/5/3,16916,c849,A,24323
e432,6/5/3,6962,c8429,A,4324
e430,6/5/3,4322,c8491,A,4322
e32042,2/3/5,13220,c1120,A,13220
e4202,6/5/3,4232,c8419,E,4232

I would like to find the sum of the last column's values, provided in the array the third column (final total) is equal to the last column. (amount paid.). The total of all the last column's values should only be found if the fifth column's (status) equals 'E' and finaltotal == amountpaid.

My code so far for this is:

data = open("paintingJobs.txt", "r")
info=data.readlines()
data.close
totalrev=0
for li in info:
    status=li.split(",")[4]
    finaltotal=int(li.split(",")[2])
    amountpaid=int(li.split(",")[5])
    if amountpaid == finaltotal:
        revenue=True
        if status == "A" and revenue == True:
            totalamountpaid = li.split(",")[5]
            total = (sum(totalamountpaid))
            print("The total revenue is")
            print(total)

My desired output would be:

The total revenue is
28435

The total should equal 28435 as 6661+4322+13220+4232=28435 (the sum of the total revenues where status equals 'A' and finaltotal=amountpaid.)

I keep receiving a "TypeError: unsupported operand type(s) for +: 'int' and 'str'". I'm using Python 3.4.3 and a complete newbie to Python. Any help would be much appreciated.

Upvotes: 3

Views: 1284

Answers (5)

Enrique Bruzual
Enrique Bruzual

Reputation: 493

Just need to make use of the 'totalrev' variable and add up 'amountpaid' every time the 'for loop' executed, and only adding the numbers determined by your criteria. At the end you just call it in your print statement. I removed two lines of codes you didn't need after the small change.

data = open("paintingJobs.txt", "r")
info=data.readlines()
data.close()

totalrev=0
for li in info:
    status=(li.split(",")[4])
    finaltotal=int(li.split(",")[2])
    amountpaid=int(li.split(",")[5])
    if amountpaid == finaltotal:
        totalrev += amountpaid
        revenue=True
        if status == "E" and revenue == True:
            print("The total revenue is: " + str(totalrev))

This works with the data you provided, I get 28435 which is what you were looking for

Upvotes: 1

Alvaro Silvino
Alvaro Silvino

Reputation: 9743

Try this. 

total = (sum(totalamountpaid))

to 

total = (sum(map(int,totalamountpaid.split(','))))

Split every number from the string map converting the string to int. Then sum them up.

Upvotes: 2

corinna
corinna

Reputation: 649

...assuming that the third column should be equal to 'E':

data = open("test.txt", "r")
info=data.readlines()
s = sum([int(li.split(',')[5]) for li in info if li.split(",")[4]=="E" and int(li.split(",")[2])==int(li.split(",")[5])])
print("The total revenue is")
print(s)

Tested. Returns 24113, i.e. 6661+13220+4232.

Upvotes: 1

Anthony Kong
Anthony Kong

Reputation: 40664

It is because at this line, 

total = (sum(totalamountpaid))

the sum function is applied to a string

So using your example data, you are effective asking python to execute this

sum("4322")

which is equivalent to 

0 + "4" + "3" + "2" + "2"

Of course you cannot add string to a numeric value 0. Hence the error message.

Actually there are a few many issues with your code. I think you need to make these changes to make it work. See comments (words after #) for explanation. Not tested.

data = open("paintingJobs.txt", "r")
info=data.readlines()
data.close()    ## Need the '()' to call the function
totalrev=0
for li in info:
    status=li.split(",")[4]
    finaltotal=int(li.split(",")[2])
    amountpaid=int(li.split(",")[5])
    if amountpaid == finaltotal:
        revenue=True
        if status == "A" and revenue == True:
            totalamountpaid = li.split(",")[5]
            ### Assuming you actually want to accumulate the sum in variable `totalrev`
            totalrev += int(totalamountpaid)  ### you need to convert totalamountpaid to a numeric value, and add to the running total `totalrev` 
            print("The total revenue is")
            print(totalrev)

Upvotes: 0

Dut A.
Dut A.

Reputation: 1168

You are fetching strings from your text file. That means you first need to cast the values to appropriate data type (from strings) before adding them up.

Try changing this line total = (sum(totalamountpaid)) to total = (sum(Decimal(totalamountpaid))) or total = (sum(float(totalamountpaid)))

Upvotes: 1

Related Questions