I have a dataset in Pandas consisting of phone call logs, from several caller numbers into several destination numbers. Calls are logged as soon as they are completed.

Main features:

• unix_timestamp (index)
• caller_number
• dest_number (US Toll Free)
• real_duration

Sample df

I need to identify those destiantion numbers that have limited the call duration. Especially those where at least the last five calls have the same duration (flat line).

The pseudo code to identify the flat line condition is as follows:

• Split stage: groupby dest_number, without calculations
• Apply stage: for each individual frame after the split, add 5 new columns with shifted values of real_duration, calculate the mean and the std of the last 5 calls real_duration, column wise. Then add a column with the ratio_pre = std / mean.
• Combine stage: output is a df with index dest_number and one column with the last_ratio_pre for each frame

After these transformations, the resulting dataframe should look like this:

After extensive research, the code with which I implemented the solution is as follows:

# create a pandas groupby class, without applying any calculation
df_dest_temp = df.groupby(['dest_number'], sort=True)

# add column last_ratio_pre and fill it with ones as placeholders
df_dest['last_ratio_pre'] = 1

# define how many previous values
control = 5

# iterate over groupby components, and assign to df_dest one by one *** SUPER SLOW!! ***
for num, frame in df_dest_temp:
    
    # create a temp df
    df_temp = frame[['dest_number', 'real_duration']]
    
    # create 5 new columns that hold the shifted values of real_duration
    for i in range(control):
        df_temp['pre_'+str(i+1)] = df_temp.real_duration.shift(i+1)

    # select only the columns related to real_duration
    cols = ['real_duration'] + [col for col in df_temp.columns if col.startswith('pre_')]

    # calculate mean of last 5 real_durations, by columns
    mean_pre  = df_temp[cols].mean(axis=1)
    
    # calculate std of last 5 real_durations, by columns
    std_pre = df_temp[cols].std(axis=1)

    # assign results to new columns
    df_temp['mean_pre'] = mean_pre
    df_temp['std_pre']  = std_pre
    
    # calculate the ratio between std and mean
    df_temp['ratio_pre']  = std_pre / mean_pre

    # take each fram and groupby dest_number and choose last value for ratio_pre
    df_temp_grouped = df_temp.groupby('dest_number').ratio_pre.last()

    # assign result to df_dest
    df_dest.loc[num, 'ratio_pre'] = float(df_temp_grouped.values)

As it can be seen, the code above solves the problem but in a VERY slow way. I did not find a way to include all the functionality in a lambda function, or in a vectorized way.

I will certainly welcome comments, remarks and suggestions to make the code more efficient.