Distinct users per time from duration and datetime using pandas python

Question

I have this dataset

created_at                  user_id    duration (second)
2019-02-21 11:32:57.000     A          50
2019-02-21 11:32:57.000     B          100
2019-02-21 11:35:00.000     B          70

My goal is to know distinct user_id per minute that still open our app. for example

date           time       count
2019-02-21     11:32      2
2019-02-21     11:33      2
2019-02-21     11:34      1
2019-02-21     11:35      1
2019-02-21     11:36      1

Answer 1

Self-contained example followed by the steps

df = pd.DataFrame({'created_at': ['2019-02-21 11:32:57.000', 
                                  '2019-02-21 11:32:57.000', 
                                  '2019-02-21 11:35:00.000'], 
                   'user_id': ['A', 'B', 'B'], 
                   'duration':[50, 100, 70]})


df['session_index'] = df.index
df['created_at'] = pd.to_datetime(df['created_at'])
df['dtm_start'] = df['created_at'].dt.floor(freq='min')
df['dtm_end'] = (df['created_at'] + pd.to_timedelta(df['duration'], unit='seconds')).dt.floor(freq='min')



df_melt = pd.melt(df, 
                  id_vars=['user_id', 'session_index'], 
                  value_vars=['dtm_start', 'dtm_end'], 
                  value_name='active_min').sort_values(by=['session_index', 
                                                           'user_id',
                                                           'active_min'])

df_melt['col_1'] = 1
df_melt = df_melt.set_index('active_min')

all_active_mins = df_melt.groupby(['user_id', 'session_index'])['col_1'].resample('60S').sum().reset_index()

active_users_by_min = all_active_mins.active_min.value_counts()

active_users_by_min

---

Step-wise outputs

df['session_index'] = df.index
df['created_at'] = pd.to_datetime(df['created_at'])
df['dtm_start'] = df['created_at'].dt.floor(freq='min')
df['dtm_end'] = (df['created_at'] + pd.to_timedelta(df['duration'], unit='seconds')).dt.floor(freq='min')
df

df_melt = pd.melt(df, 
                  id_vars=['user_id', 'session_index'], 
                  value_vars=['dtm_start', 'dtm_end'], 
                  value_name='active_min').sort_values(by=['session_index', 
                                                           'user_id',
                                                           'active_min'])

df_melt['col_1'] = 1
df_melt = df_melt.set_index('active_min')
df_melt

all_active_mins = df_melt.groupby(['user_id', 'session_index'])['col_1'].resample('60S').sum().reset_index()
all_active_mins

all_active_mins.active_min.value_counts()