Itertools Groupby Looping Over Different Columns

'm trying to do a conditional sum-product in Python. The simplified idea is as follows: A = [1 1 2 3 3 3] B = [0.50 0.25 0.99 0.80 0.70 0.20] I would like to have as output Total1

Solution 1:

It appears you are asking two questions:

1. Calculate sums of products based on groups from a separate list
2. Write these results to an Excel file

As these are quite different problems, I will address the first and refer to references on the second.

import operator as op
import itertools as it
import functools as ft


A = [1, 1, 2, 3, 3, 3]
B = [0.5, 0.25, 0.99, 0.8, 0.7, 0.2]

groups = [list(g) for k, g in it.groupby(zip(A, B), op.itemgetter(0))]
groups
# [[(1, 0.5), (1, 0.25)], [(2, 0.99)], [(3, 0.8), (3, 0.7), (3, 0.2)]]

Here we zip the columns of data and group them according to list A. Now we are able to apply the appropriate operators to each iterable in groups.

[sum(ft.reduce(op.mul, i) for i in sub) for sub ingroups]
# [0.75, 1.98, 5.1]

As for writing data to Excel, there are several Python libraries available, each with documentation on how to do so.