Pandas: join on grouping keys after aggregation

Question

What I have

I have a pandas frame like this:

df1 = pd.DataFrame({
    'date': ['31-05-2017', '31-05-2017', '31-05-2017', '31-05-2017', '01-06-2017', '01-06-2017'],
    'tag':     ['A', 'B', 'B', 'B', 'A', 'A'],
    'metric1': [0, 0, 0, 1, 1, 1],
    'metric2': [0, 1, 1, 0, 1, 0]
})


df2 = pd.DataFrame({
    'date': ['31-05-2017', '31-05-2017', '01-06-2017'],
    'tag':     ['A', 'B', 'A'],
    'metric3': [25, 3, 7,]
})

What I want

1) I want to sum metricand metric_2 per each combination of date and tag

2) compute the percentage of entries being 1 in metric_2

3) merge grouped df1 with df2 so that I have metric_3 for each dateand tag

date       | tag | metric1_sum | metric2_sum | metric2_percentage| metric 3
-----------|-----|-------------|-------------|-------------------|---------
31-05-2017 | A   | 0           | 0           | 0                 | 25
31-05-2017 | B   | 1           | 2           | 0.667             | 3
01-06-2017 | A   | 1           | 0           | 0.5               | 7

Attempts

(1) group and sum works

>>> g = df1.groupby(['date', 'tag']).agg(sum)
>>> g
                metric1  metric2
date       tag                  
01-06-2017 A          2        1
31-05-2017 A          0        0
           B          1        2

(2) calculating the percentage works but adding it as a column does not

I used the method posted here to calculate the percentage.

>>> g2 = df1.groupby(['date', 'tag']).agg({'metric2': 'sum'})
>>> g2.groupby(level=0).apply(lambda x: x/float(x.sum()))
                metric2
date       tag         
01-06-2017 A        1.0
31-05-2017 A        0.0
           B        1.0

But, how can I now assign this grouped metric2 to a column metric2_percentage in my groups g or my df1?

(3) unsuccessful merge

Merging with the group apparently does not work:

>>> pd.merge(g, df2, how='left', on=['date', 'tag'])
KeyError: 'date'

How can I then reduce df1 to one row per group so that I can merge it with df2?

Can you explain the problems between calculating the % and merging? Are these separate problems? — Andrew L, Jul 24 '17 at 18:58

Psidom · Accepted Answer · 2017-07-24T19:11:38.037

g has date, tag as index, while merge is expecting columns, you'll need to reset_index on g:

pd.merge(g.reset_index(), df2, how='left', on=['date', 'tag'])

Or specify left_index = True:

pd.merge(g, df2, how='left', left_index=True, right_on=['date', 'tag'])

Both give results as (with columns order slightly differ):

#         date  tag  metric1  metric2   metric3
#0  01-06-2017    A        2        1         7
#1  31-05-2017    A        0        0        25
#2  31-05-2017    B        1        2         3

Here is an alternative that does your job with one less join:

(df1.groupby(['date', 'tag']).apply(
        lambda g: pd.Series({'metric1_sum': g.metric1.sum(), 
                             'metric2_sum': g.metric2.sum(), 
                             'metric2_percentage': g.metric2.mean()})   
# assumed here you have only 1 and 0 in metric 2 column if not use your own lambda function
    ).reset_index().merge(df2, how='left', on=['date', 'tag']))

#         date  tag  metric1_sum    metric2_percentage  metric2_sum metric3
#0  01-06-2017    A          2.0              0.500000         1.0        7
#1  31-05-2017    A          0.0              0.000000         0.0       25
#2  31-05-2017    B          1.0              0.666667         2.0        3

Great, I was not aware that I can flatten the group with reset_index() — Willi Müller, Jul 25 '17 at 08:27

piRSquared · Answer 2 · 2017-07-24T21:03:26.447

Use agg. mean of ones and zeros will be the same as percentage.

cols = ['date', 'tag']
d1 = df1.groupby(cols).agg(
    dict(metric1='sum', metric2=['sum', 'mean'])
)

d1.columns = d1.columns.map('_'.join)

d1.join(df2.set_index(cols))

         date tag  metric1_sum  metric2_sum  metric2_mean  metric3
0  01-06-2017   A            2            1      0.500000        7
1  31-05-2017   A            0            0      0.000000       25
2  31-05-2017   B            1            2      0.666667        3

Over-engineering for the sake of a one-liner

from collections import OrderedDict

df1.groupby(['date', 'tag']).agg(
    dict(metric1='sum', metric2=['sum', 'mean'])
).pipe(
    lambda d: pd.DataFrame(OrderedDict({'_'.join(k): v for k, v in d.iteritems()}))
).join(df2.set_index(['date', 'tag'])).reset_index()

         date tag  metric1_sum  metric2_sum  metric2_mean  metric3
0  01-06-2017   A            2            1      0.500000        7
1  31-05-2017   A            0            0      0.000000       25
2  31-05-2017   B            1            2      0.666667        3