Sum Values If First Values Match

Question

Is there any way i can compare both lists and sum the second value ONLY if the the first value matches? I queried two tables and gave me the results below but not sure if python has some sort of group function i can use to group the account number(first value) and sum the second values if the first value matches.

import psycopg2

def connect():
    conn = psycopg2.connect(host="test", port=5432, database="test", user="test", 
    password="test123")
    cursor = conn.cursor()
    return cursor

def allocation(cursor,cashdt, sumbmaster):
    alloclist = []
    print("Allocation: ")
    cursor.execute("""select bill_acct,sum(debit_amount) from test.a where 
    cash_date ='"""
               + cashdt + "'" + "and master_bill='" + sumbmaster +"' group by bill_acct")
    for row in cursor.fetchall():
         a = row[0],"{:.2f}".format(row[1])
         alloclist.append(a)
     print(alloclist)

 def statement(cursor, billingdt,sumbmaster):
     statementlist = []
     cursor.execute(
         """
         select bill_account,total_due_amount from test.b where billing_date ='""" 
     + billingdt + "'" + "and master_bill='" + sumbmaster + "'")
     print("Statement:")
    for row in cursor.fetchall():
         a = row[0], "{:.2f}".format(row[1])
         statementlist.append(a)
     print(statementlist)

def main():
    connect()
    allocation(connect(),'2020-02-25','123')
    statement(connect(), '2020-2-14', '345')

example

list 1 =[('123',1),('345', 2)]
list2 = [('123',1),('345', 2)]

output

('123',2), ('345',4)

are those lists or tuples? Lists usually have [] brackets and tuples have () — Moo10000, Mar 03 '20 at 19:19
I don’t understand what the example has to do with the rest of the code above. — mkrieger1, Mar 03 '20 at 19:19
what will happen if `list1 =[('123',1),('345', 2)]` AND `list2 = [('123',1)]` — mad_, Mar 03 '20 at 19:20
@Marcos Only display matching ones and do the addition of their second value. — Ben21, Mar 03 '20 at 19:22
Okay now its a list of tuples (the way it looks]. Do you mean this? [1,2,3,4] and [1,3,4,5] — Moo10000, Mar 03 '20 at 19:23
Are matches sensitive or insensitive to position? What output do you expect for ``list1 =[('123',1),('345', 2)]; list2 = [('345', 2), ('123',1)]``? What about additional elements, e.g. ``list2 =[('123',1), ('999', 3), ('345', 2)]``? — MisterMiyagi, Mar 03 '20 at 19:35

score 1 · Answer 1 · answered Mar 03 '20 at 19:23

1

Is there any way i can compare both lists and sum the second value ONLY if the the first value matches?

Yes:

if list1[0] == list2[0]:
    result = list1[1] + list2[1]

answered Mar 03 '20 at 19:23

mkrieger1

19,194
5
54
65

Thanks, would i have to do a for loop? I see other comments saying to use zip in the loop but that only works if both list are the same length...in this case they are but what if they arent? – Ben21 Mar 03 '20 at 21:25
I don’t know exactly what you mean. Anyway, if you want to do something for every item in a list, you can use a `for` loop. And if you want to iterate over two lists in parallel, you could use `zip`. – mkrieger1 Mar 03 '20 at 21:31
what happens if the lists are different lengths? – Ben21 Mar 03 '20 at 21:32
It iterates until the shortest list is exhausted. – mkrieger1 Mar 03 '20 at 21:34
Hmm.. i would need it to keep looking for matches between both lists regardless of length. – Ben21 Mar 03 '20 at 21:40
See https://stackoverflow.com/questions/1277278/python-zip-like-function-that-pads-to-longest-length – mkrieger1 Mar 03 '20 at 21:42

score 1 · Accepted Answer · answered Mar 03 '20 at 19:29

1

Using list comprehension

[(i[0],i[1]+j[1]) for i,j in zip(list1,list2) if i[0]==j[0]]

answered Mar 03 '20 at 19:29

mad_

8,121
2
25
40

The thing is that both list are not the same length. Doesn't ZIP iterate simultaneously? – Ben21 Mar 03 '20 at 20:42

Sum Values If First Values Match

2 Answers2