Generating Rows between two dates - Pandas

user_19897368 0
Viewed 42

I'm going to split this between the problem I want to solve and how I think would be the best way to get there, but open to anything.

Problem:

I have leads coming in and then the next step down the funnel is trying to contact them. Both have a time stamp and can vary in length between the two drastically among leads. I want to track throughout the day how many leads are between the two steps at any given minute.

Dataset:

ID Created DateTime Call DateTime Time Gap
123 11:58 12:02 4
124 11:58 12:00 2

Maybe Solution:

def generate_rows(df,x):
    sample = {'Full_ID':df.iloc[x,0]}
    sample['Date'] = pd.date_range(df.iloc[x,1], freq='Min', 
                                   periods=df.iloc[x,3], name='Date')
    return pd.DataFrame(sample)

pd.concat([generate_rows(df,x) for x in range(num_rows)])

Giving us this output:

ID Date
123 11:58
123 11:59
123 12:00
123 12:01
124 11:58
124 11:59

Then I can group by Date and count the IDs to see how many leads are waiting to be called.

pandas dataframe python
1 Answers
out = df.loc[df.index.repeat(df['Time Gap']), ['ID', 'Created DateTime']]
out.columns = ['ID', 'Date']
out['Date'] += out.groupby(level=0).cumcount() * pd.Timedelta('1 min')

# out.reset_index(drop=True, inplace=True)  # reset index if you want

Output :

    ID                Date
0  123 2022-09-01 11:58:00
0  123 2022-09-01 11:59:00
0  123 2022-09-01 12:00:00
0  123 2022-09-01 12:01:00
1  124 2022-09-01 11:58:00
1  124 2022-09-01 11:59:00
user_146275050
Related