Group by Date find average distinct customers-CodePudding

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.

CodePudding user response：

IIUC,

new_df = \
df.groupby(df['initiated_date'].dt.day_name())['specialist_id']\
.value_counts()\
.mean(level='initiated_date')\ #.groupby(level=0).mean() if you need instead
.rename_axis('Day').reset_index(name='specialist_id')

if you want get nunique by day:

new_df = \
df.groupby(df['initiated_date'].dt.day_name())['specialist_id']\
  .nunique()\
  .rename_axis('Day').reset_index(name='specialist_id')

If ceil is needed:

new_df = \
np.ceil(
    df.groupby(df['initiated_date'].dt.day_name())['specialist_id']
      .value_counts()
      .mean(level='initiated_date')#.groupby(level=0).mean() if you need instead
)\
.rename_axis('Day').reset_index(name='specialist_id')

CodePudding user response：

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()