I have a List of Lists which looks like

[[1, 'Category A Test', 3, '250'], [2, 'Test 2', 44, '250'], [2, 'Test 2', 12, '250']]

How would I go about merging the items in the list according to the ID (Col 0) and add their values together (Col 2) so the end result looks like this:

[[1, 'Category A Test', 3, '250'], [2, 'Test 2', 56, '250']]

I have attempted to alter code I have found found online but have been unsuccessful

totals = {}
for id, description, quantity, price in original_list:
    totals[id, description, price] = totals.get(id, 0)   price

I am new to python and any help would be greatly appreciated

CodePudding user response：

vals = [[1, 'Category A Test', 3, '250'], [2, 'Test 2', 44, '250'], [2, 'Test 2', 12, '250']]
d = {}
for x in vals:
    if x[0] in d.keys():
        d[x[0]] = (x[1], d[x[0]][1] x[2], x[3])    
    else:
        d[x[0]] = (x[1], x[2], x[3])
ans = []
for k,v in d.items():
    ans.append([k,v[0],v[1], v[2]])
print(ans)

Output:

[[1, 'Category A Test', 3, '250'], [2, 'Test 2', 56, '250']]

Using a dictionary to keep track of ID.

CodePudding user response：

Just iterate through the list and check everytime whether it contains another list with the same id. If so, add a new List with the same elements as the two lists at index 0 and 1, and with the sum of the elements of the two lists at index 2 and 3. Than remove both of the old lists.

CodePudding user response：

original_list = [[1, 'Category A Test', 3, '250'], 
                 [2, 'Test 2', 44, '250'], 
                 [2, 'Test 2', 12, '250']
                ]

totals = {}

for item in original_list:
    # Unpack the 4 items of the list
    id_, name, value, unknown = item

    # If the id is not yet in the totals dict, then copy the whole thing, otherwise add the value to the existing value (stored in totals[id_][2])
    if id_ not in totals:
        totals[id_] = item
    else:
        totals[id_][2]  = value

# Use a list comprehension to transform the dict to a list, so it's the same as the result in your question
totals_as_list = [*totals.values()]

print(totals_as_list)

Result:

[[1, 'Category A Test', 3, '250'], [2, 'Test 2', 56, '250']]

CodePudding user response：

These operations are often done with a library called Pandas.

Here is how you would do it. But I suggest you to check the library out and understand how to work with it. It is really ubiquitous with tabular data.

import pandas as pd

start_list = [[1, 'Category A Test', 3, '250'], [2, 'Test 2', 44, '250'], [2, 'Test 2', 12, '250']]

# Create a Pandas Dataframe.
df = pd.DataFrame(start_list, columns=['ID', 'description', 'quantity', 'price'])

# Get the result
result_df = df.groupby('ID').agg({'description':'first', 'quantity':'sum', 'price':'first'}).reset_index()

# If you want a list back:
result_list = result_df.to_numpy().tolist()

Here, result_list is:

[[1, 'Category A Test', 3, '250'], [2, 'Test 2', 56, '250']]

CodePudding user response：

Your approach could be slightly modified and would work:

totals = {}
for ID, description, quantity, price in original_list:
    totals[ID, description, price] = totals.get((ID, description, price), 0)   quantity

new_list = [
    [ID, description, quantity, price]
    for (ID, description, price), quantity in totals.items()
]

• Build totals by using (ID, description, price) as key and sum over quantity (the value).
• To get the aggregated list: Iterate over totals items and put the new quantity back into its place.

Hint: Don't use id as variable name - it's a built-in function.