Group by, aggregate, include separate column

Question

Here's my data:

foo = pd.DataFrame({
    'accnt' : [101, 102, 103, 104, 105, 101, 102, 103, 104, 105],
    'gender' : [0, 1 , 0, 1, 0, 0, 1 , 0, 1, 0],
    'date' : pd.to_datetime(["2019-01-01 00:10:21", "2019-01-05 00:09:18", "2019-01-05 00:09:30", "2019-02-05 00:05:12", "2019-04-01 00:08:46",
             "2019-04-01 00:11:31", "2019-02-06 00:01:39", "2019-01-26 00:15:14", "2019-01-21 00:12:36", "2019-03-01 00:09:31"]),
    'value' : [10, 20, 30, 40, 50, 5, 2, 6, 48, 96]
                })

Which is:

   accnt    date                gender  value
0   101  2019-01-01 00:10:21       0    10
1   102  2019-01-05 00:09:18       1    20
2   103  2019-01-05 00:09:30       0    30
3   104  2019-02-05 00:05:12       1    40
4   105  2019-04-01 00:08:46       0    50
5   101  2019-04-01 00:11:31       0    5
6   102  2019-02-06 00:01:39       1    2
7   103  2019-01-26 00:15:14       0    6
8   104  2019-01-21 00:12:36       1    48
9   105  2019-03-01 00:09:31       0    96

I want to do the following: - Group by accnt, include gender, take latest date as latest_date, count number of transactions as txn_count; resulting in:

  accnt  gender         latest_date        txn_count
   101      0       2019-04-01 00:11:31         2
   102      1       2019-02-06 00:01:39         2
   103      0       2019-01-26 00:15:14         2
   104      1       2019-02-05 00:05:12         2
   105      0       2019-04-01 00:08:46         2

In R, I can do this using group_by and summarise from dplyr:

foo %>% group_by(accnt) %>% 
summarise(gender = last(gender), most_recent_order_date = max(date), order_count = n()) %>% data.frame()

I'm taking last(gender) to include it, since gender is the same throughout for any accnt, I can take min, max or mean instead also.

How can I do the same in python using pandas?

I've tried:

foo.groupby('accnt').agg({'gender' : ['mean'],
                          'date': ['max'],
                          'value': ['count']}).rename(columns = {'gender' : "gender",
                                                                 'date' : "most_recent_order_date",
                                                                 'value' : "order_count"})

But this leads to "extra" column names. I'd also like to know what is the best way to include a non-aggregation column like gender in the result.

BENY · Accepted Answer · 2019-04-07T03:35:26.713

In R summarise will equal to agg , mutate equal to transform

The reason why you have multiple index in columns : Since you pass the function call with list , which means you can do something like {'date':['mean','sum']}

foo.groupby('accnt').agg({'gender' : 'first',
                          'date': 'max',
                          'value': 'count'}).rename(columns = {'date' : "most_recent_order_date",
                                                                 'value' : "order_count"}).reset_index()
Out[727]: 
   accnt most_recent_order_date  order_count  gender
0    101    2019-04-01 00:11:31            2       0
1    102    2019-02-06 00:01:39            2       1
2    103    2019-01-26 00:15:14            2       0
3    104    2019-02-05 00:05:12            2       1
4    105    2019-04-01 00:08:46            2       0

Some example : Here I called two function same time for one columns , which means there should be have two level of index to make sure the out columns names do not have duplicated

foo.groupby('accnt').agg({'gender' : ['first','mean']})
Out[728]: 
      gender     
       first mean
accnt            
101        0    0
102        1    1
103        0    0
104        1    1
105        0    0

score 0 · Answer 2 · answered Apr 07 '19 at 03:18

Sorry for the late response. Here's a solution I found.

# Pandas Operations
foo = foo.groupby('accnt').agg({'gender' : ['mean'],
                                'date': ['max'],
                                'value': ['count']})

# Drop additionally created column names from Pandas Operations
foo.columns = foo.columns.droplevel(1)

# Rename original column names
foo.rename( columns = { 'date':'latest_date',
                        'value':'txn_count'}, 
            inplace=True)

If you'd like to include an additional non aggregate column, you can simply append a new column to the grouped foo dataframe.

Group by, aggregate, include separate column

2 Answers2