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
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()
Use:
# STEP 1:
df['created_at'] = pd.to_datetime(df['created_at'])
df['end_at'] = df['created_at'] + pd.to_timedelta(df['duration (second)'], unit='s')
# STEP 2:
df1 = df.melt(id_vars='user_id', value_vars=['created_at', 'end_at'], value_name='ts')
# STEP 3:
df1 = (
df1[['user_id', 'ts']].groupby('user_id', group_keys=False)
.apply(lambda s: s.drop_duplicates().set_index('ts').resample('1min').ffill().bfill())
)
# STEP 4
df1 = df1.groupby(level=0).nunique().set_axis(['count'], 1)
# STEP 5:
df1 = df1.assign(date=df1.index.date, time=df1.index.time).reset_index(drop=True)
Steps:
# STEP 1: df
created_at user_id duration (second) end_at
0 2019-02-21 11:32:57 A 50 2019-02-21 11:33:47
1 2019-02-21 11:32:57 A 50 2019-02-21 11:33:47
2 2019-02-21 11:32:57 B 100 2019-02-21 11:34:37
3 2019-02-21 11:35:00 B 70 2019-02-21 11:36:10
# STEP 2: df1
user_id variable ts
0 A created_at 2019-02-21 11:32:57
1 A created_at 2019-02-21 11:32:57
2 B created_at 2019-02-21 11:32:57
3 B created_at 2019-02-21 11:35:00
4 A end_at 2019-02-21 11:33:47
5 A end_at 2019-02-21 11:33:47
6 B end_at 2019-02-21 11:34:37
7 B end_at 2019-02-21 11:36:10
# STEP 3: df1
user_id
ts
2019-02-21 11:32:00 A
2019-02-21 11:33:00 A
2019-02-21 11:32:00 B
2019-02-21 11:33:00 B
2019-02-21 11:34:00 B
2019-02-21 11:35:00 B
2019-02-21 11:36:00 B
# STEP 4: df1
count
ts
2019-02-21 11:32:00 2
2019-02-21 11:33:00 2
2019-02-21 11:34:00 1
2019-02-21 11:35:00 1
2019-02-21 11:36:00 1
# STEP 5 (RESULT): df1
count date time
0 2 2019-02-21 11:32:00
1 2 2019-02-21 11:33:00
2 1 2019-02-21 11:34:00
3 1 2019-02-21 11:35:00
4 1 2019-02-21 11:36:00