Pandas create a set of parameters and average data

Question

For each date, dateI want to get the average value of the sum from each combination of Brands.

For example, I have a dataframe:

df1 = 
Company Brand Date        Amount
A       1     01/01/2015  3     
A       1     01/02/2015  4
A       1     01/03/2015  2   
A       2     01/01/2015  7     
A       2     01/02/2015  2
A       2     01/03/2015  1   
A       3     01/01/2015  6     
A       3     01/02/2015  3
A       3     01/03/2015  1   

and I want the result to be the following df, where Sum is the average of the combined groups:

result = 
Company Brand Date        Amount
A       1     01/01/2015  3     
A       1     01/02/2015  4
A       1     01/03/2015  2  
A       2     01/01/2015  7     
A       2     01/02/2015  2
A       2     01/03/2015  1   
A       3     01/01/2015  6     
A       3     01/02/2015  3
A       3     01/03/2015  1
A       1_2   01/01/2015  5    
A       1_2   01/02/2015  3
A       1_2   01/03/2015  1.5 
A       2_3   01/01/2015  6.5 
A       2_3   01/02/2015  2.5
A       2_3   01/03/2015  1
A       1_3   01/01/2015  4.5    
A       1_3   01/02/2015  3.5
A       1_3   01/03/2015  1.5
A       1_2_3 01/01/2015  5.33  
A       1_2_3 01/02/2015  3
A       1_2_3 01/03/2015  1.33

I am currently using a loop with groupby for this, but it is very slow.

d = pd.DataFrame()
comb = ['1_2','1_3','2_3','1_2_3']
for c in comb:
    new = df1.loc[(df1.Brand.isin(map(int,c.split('_')))].groupby(['Company','Date'])['Amount'].mean().reset_index()
    new.insert(1,'Group',c)
    d = d.append(new)

    df = df.append(d)

However, I work with thousands of unique companies and several million lines, so this is very slow. Is there any way to speed this up?

Answer 1

import pandas as pd
from itertools import chain, combinations

def powerset(iterable):
    "powerset([1,2,3]) --> () (1,) (2,) (3,) (1,2) (1,3) (2,3) (1,2,3)"
    s = list(iterable)
    return chain.from_iterable(combinations(s, r) for r in range(1, len(s)+1))

a = [['A', 'b1', '01/01/2015', 3], 
['A', 'b1', '01/02/2015', 4],
['A', 'b1', '01/03/2015', 2], 
['A', 'b2', '01/01/2015', 7], 
['A', 'b2', '01/02/2015', 2],
['A', 'b2', '01/03/2015', 1], 
['A', 'b3', '01/01/2015', 6], 
['A', 'b3', '01/02/2015', 3],
['A', 'b3', '01/03/2015', 1]]

df = pd.DataFrame(a, columns=['Company', 'Brand', 'Date', 'Amount'])

ps = powerset(['b1', 'b2', 'b3'])
# create new dataframe to append to
new_df = pd.DataFrame()
for s in ps:
    view = df[df.Brand.isin(s)].groupby(['Company', 'Date']).mean()
    view['Brand'] = '_'.join(s)
    new_df = new_df.append(view)

The result is as follows:

                      Amount     Brand
Company Date                          
A       01/01/2015  3.000000        b1
        01/02/2015  4.000000        b1
        01/03/2015  2.000000        b1
        01/01/2015  7.000000        b2
        01/02/2015  2.000000        b2
        01/03/2015  1.000000        b2
        01/01/2015  6.000000        b3
        01/02/2015  3.000000        b3
        01/03/2015  1.000000        b3
        01/01/2015  5.000000     b1_b2
        01/02/2015  3.000000     b1_b2
        01/03/2015  1.500000     b1_b2
        01/01/2015  4.500000     b1_b3
        01/02/2015  3.500000     b1_b3
        01/03/2015  1.500000     b1_b3
        01/01/2015  6.500000     b2_b3
        01/02/2015  2.500000     b2_b3
        01/03/2015  1.000000     b2_b3
        01/01/2015  5.333333  b1_b2_b3
        01/02/2015  3.000000  b1_b2_b3
        01/03/2015  1.333333  b1_b2_b3

Answer 2

from itertools import combinations

# define a generator to use combinations
# (iterate through combinations of a specific length)
# and iterate through all combinations
def combo(iterable):
    for r in range(1, len(iterable) + 1):
        for c in combinations(iterable, r):
            yield c

df.groupby(['Company', 'Date']).Brand.unique().apply(
    lambda x: pd.Series(
        {'_'.join(map(str, blist)): df.query('Brand in @blist').Amount.sum() for blist in combo(x)},
    )
).rename_axis('Combo', 1).stack().reset_index(name='Amount')