Efficient way of calculating amount of concurrent calls by one user to a distinct phone number using python pandas?

user_13161972 0
Viewed 77

I have a large dataframe of user calls to different phone numbers

calls = {
    'user': ['a', 'b', 'b', 'b', 'c', 'c'],
    'number': ['+1 11', '+2 22', '+2 22', '+1 11', '+4 44', '+1 11'],
    'start_time': ['00:00:00', '00:02:00', '00:03:00', '00:00:00', '00:00:00', '00:00:00'],
    'end_time': ['00:05:00', '00:03:01', '00:05:00', '00:05:00', '00:02:00', '00:02:00']
}

df = pd.DataFrame(calls)
user number time_start time_end
0 a 1 11 00:00:00 00:05:00
1 b 2 22 00:02:00 00:03:01
2 b 2 22 00:03:00 00:05:00
3 b 1 11 00:00:00 00:05:00
4 c 4 44 00:00:00 00:02:00
5 c 1 11 00:00:00 00:02:00

And I am trying to calculate max number of concurrent (parallel) calls from one user to a distinct number:

res = pd.DataFrame([])
grouped_by_user = df.groupby(['user'])
user_dict = defaultdict(lambda: {'number_dict': None})
    
    for user in grouped_by_user.groups:
    user_group = grouped_by_user.get_group(user)
    grouped_by_number = user_group.groupby(['number'])
    number_dict = defaultdict(lambda: {'max_calls': None})
    
    for number in grouped_by_number.groups:
        number_group = grouped_by_number.get_group(number)
        calls = []
        for i in number_group.index:
            calls.append(len(number_group[(number_group["start_time"] <= number_group.loc[i, "start_time"]) & (number_group["end_time"] > number_group.loc[i, "start_time"])]))
        number_dict[number]['max_calls'] = max(calls)
    user_dict[user]['number_dict'] = number_dict
    
    tmp_list = []
    for num, calls in number_dict.items():
        tmp_list.append([user, num, calls['max_calls']])
    res = res.append(tmp_list, ignore_index=True)

with a resulting dataframe which looks like that:

user number max
0 a 1 11 1
1 b 1 11 1
2 b 2 22 2
3 c 1 11 1
4 c 4 44 1

But this code is very slow for large dataframes. Is there a better way of doing it? Or how can improve the time efficiency of this code?

pandas dataframe python-3.x group-by python
1 Answers

Try:

df["start_time"] = pd.to_datetime(df["start_time"])
df["end_time"] = pd.to_datetime(df["end_time"])

def fn(x):
    x["tmp1"] = x.apply(
        lambda y: pd.date_range(y["start_time"], y["end_time"], freq="1s"),
        axis=1,
    )

    x = x.explode("tmp1")

    return (
        x.loc[x.duplicated(subset=["tmp1"], keep=False), "tmp1"]
        .value_counts()
        .max()
    )


print(
    df.groupby(["user", "number"])
    .apply(fn)
    .to_frame(name="max")
    .reset_index()
    .fillna(1)
)

Prints:

  user number  max
0    a  +1 11  1.0
1    b  +1 11  1.0
2    b  +2 22  2.0
3    c  +1 11  1.0
4    c  +4 44  1.0

With

calls = {
    "user": ["a", "b", "b", "b", "c"],
    "number": ["+1 11", "+1 11", "+1 11", "+1 11", "+1 11"],
    "start_time": ["00:00:00", "00:04:00", "00:00:00", "00:03:00", "00:00:00"],
    "end_time": ["00:05:00", "00:08:00", "00:05:00", "00:05:30", "00:02:00"],
}

Prints:

  user number  max
0    a  +1 11  1.0
1    b  +1 11  3.0
2    c  +1 11  1.0
user_100359850
Related