Search code examples
pythonpandasdataframepandas-groupby

Group by Date find average distinct customers

I have a DataFrame with one month's data:

initiated_date      | specialist_id
21/10/2020 05:00:01 |   ab12
21/10/2020 12:20:01 |   gc35
22/10/2020 04:30:01 |   ad32
22/10/2020 03:40:01 |   fe45
22/10/2020 01:50:01 |   ad32
23/10/2020 02:10:01 |   iu99
23/10/2020 11:30:01 |   iu99

I want to find out the average number of distinct specialist_id each dayname(Monday,Tues..etc.) I want to replicate SQL's subquery:

SELECT 
    initiated_day, CEILING(AVG(specialist_id)) AS specialist_id
FROM
    (SELECT 
        DATE(initiated_date),
            DAYNAME(initiated_date) AS initiated_day,
            COUNT(DISTINCT specialist_id) specialist_id
    FROM
        nts.contacts
    GROUP BY 1 , 2) x
GROUP BY 1

What I am looking for is:

Day    |  specialist_id
Mon    |   42 
Tue    |   48
Wed    |   51
Thu    |   47
Fri    |   38
Sat    |   31
Sun    |   22

This is what I am trying to do

df.groupby([df['initiated_date'].dt.date,df['initiated_date'].dt.weekday_name])['specialist_id'].nunique().reset_index()

I am not sure how to go further.

Solution

  • You can add the 2nd groupby

    st1 = dt.groupby([dt['initiated_date'].dt.date,dt['initiated_date']. day_name()])['specialist_id'].nunique()
out = st1.groupby(level=1).apply(lambda x : np.ceil(x.mean())).reset_index()