Pandas aggregate count distinct

Question

Let's say I have a log of user activity and I want to generate a report of the total duration and the number of unique users per day.

import numpy as np
import pandas as pd
df = pd.DataFrame({'date': ['2013-04-01','2013-04-01','2013-04-01','2013-04-02', '2013-04-02'],
    'user_id': ['0001', '0001', '0002', '0002', '0002'],
    'duration': [30, 15, 20, 15, 30]})

Aggregating duration is pretty straightforward:

group = df.groupby('date')
agg = group.aggregate({'duration': np.sum})
agg
            duration
date
2013-04-01        65
2013-04-02        45

What I'd like to do is sum the duration and count distincts at the same time, but I can't seem to find an equivalent for count_distinct:

agg = group.aggregate({ 'duration': np.sum, 'user_id': count_distinct})

This works, but surely there's a better way, no?

group = df.groupby('date')
agg = group.aggregate({'duration': np.sum})
agg['uv'] = df.groupby('date').user_id.nunique()
agg
            duration  uv
date
2013-04-01        65   2
2013-04-02        45   1

I'm thinking I just need to provide a function that returns the count of distinct items of a Series object to the aggregate function, but I don't have a lot of exposure to the various libraries at my disposal. Also, it seems that the groupby object already knows this information, so wouldn't I just be duplicating the effort?

DSM · Accepted Answer · 2013-09-01 03:31:03Z

231

How about either of:

>>> df
         date  duration user_id
0  2013-04-01        30    0001
1  2013-04-01        15    0001
2  2013-04-01        20    0002
3  2013-04-02        15    0002
4  2013-04-02        30    0002
>>> df.groupby("date").agg({"duration": np.sum, "user_id": pd.Series.nunique})
            duration  user_id
date                         
2013-04-01        65        2
2013-04-02        45        1
>>> df.groupby("date").agg({"duration": np.sum, "user_id": lambda x: x.nunique()})
            duration  user_id
date                         
2013-04-01        65        2
2013-04-02        45        1

answered Sep 1, 2013 at 3:31

DSM

355k67 gold badges606 silver badges504 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dave Over a year ago

That's it. pd.Series.nunique is what I couldn't find, well, couldn't get to work correctly. Pretty obvious in hindsight. Thanks!

Ted Petrou Over a year ago

This answer is outdated. You can now use nunique directly. See @Blodwyn Pig's solution below

haneulkim Over a year ago

Hey do you know how to get non-duplicate count?

Asclepius · Accepted Answer · 2023-12-16 00:24:26Z

130

'nunique' is an option for .agg() since pandas 0.20.0, so:

df.groupby('date').agg({'duration': 'sum', 'user_id': 'nunique'})

edited Dec 16, 2023 at 0:24

Asclepius

64.6k20 gold badges188 silver badges164 bronze badges

answered Jul 11, 2017 at 21:27

Ricky McMaster

4,6772 gold badges26 silver badges25 bronze badges

3 Comments

guy Over a year ago

Is it possible to agg and get the unique values? something like duration: np.unique

BallpointBen Over a year ago

@guy Try df.groupby('date').agg({'user_id': lambda s: s.unique().reset_index(drop=True)})

user9366862 Over a year ago

How do we get the output?

thorbjornwolf · Accepted Answer · 2019-10-08 13:55:17Z

38

Just adding to the answers already given, the solution using the string "nunique" seems much faster, tested here on ~21M rows dataframe, then grouped to ~2M

%time _=g.agg({"id": lambda x: x.nunique()})
CPU times: user 3min 3s, sys: 2.94 s, total: 3min 6s
Wall time: 3min 20s

%time _=g.agg({"id": pd.Series.nunique})
CPU times: user 3min 2s, sys: 2.44 s, total: 3min 4s
Wall time: 3min 18s

%time _=g.agg({"id": "nunique"})
CPU times: user 14 s, sys: 4.76 s, total: 18.8 s
Wall time: 24.4 s

edited Oct 8, 2019 at 13:55

thorbjornwolf

1,88820 silver badges19 bronze badges

answered Sep 27, 2017 at 15:53

user6903745

5,5274 gold badges23 silver badges38 bronze badges

4 Comments

Ufos Over a year ago

Nice catch! I guess it's b/c in a "lambda"/"other function" case it is applied sequentially, while "known" functions are applied to the whole column in a vectorized fashion.

Chogg Over a year ago

which solution is from @Blodwyn Pig?

m-dz Over a year ago

@Chogg, the fastest one!

Ricky McMaster Over a year ago

@Chogg - sorry I changed my username. It was me.

Mykola Zotko · Accepted Answer · 2022-05-04 07:12:55Z

0

If you want to get only a number of distinct values per group you can use the method nunique directly with the DataFrameGroupBy object:

df.groupby('date')['user_id'].nunique()

answered May 4, 2022 at 7:12

Mykola Zotko

18.2k6 gold badges88 silver badges90 bronze badges

Comments

HT. · Accepted Answer · 2023-01-05 20:01:34Z

0

You can find it for all columns at once with the aggregate method,

df.aggregate(func=pd.Series.nunique, axis=0)
# or
df.aggregate(func='nunique', axis=0)

See aggregate | Pandas Docs

answered Jan 5, 2023 at 20:01

HT.

2412 silver badges8 bronze badges

Collectives™ on Stack Overflow

Pandas aggregate count distinct

5 Answers 5

3 Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

3 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related