How to calculate average value of different pairs of rows and delete N-1 rows from dataframe?

user_1199499 0
Viewed 136

I have a dataframe like

id group person company time timestamp
345 2020-04-01 user1 A 10:04:05
346 2020-04-01 user1 A 10:14:05
347 2020-04-01 user2 B 10:24:05
348 2020-04-01 user1 A 11:04:05
349 2020-04-01 user2 B 11:06:05
... ... ... ... ...
1000 2020-04-20 user1 AA 11:04:05
1034 2020-04-20 user1 AA 12:04:05
1078 2020-04-21 user2 BB 12:34:05
1200 2020-04-22 user1 AA 12:40:05

This is list of messages where user1 is consultant and userN are clients from different companies. I also added group column where I added the date when this message was sent.

I need to calculate the average time between different type of users, i.e.: in 2020-04-01 **user1** sent the 1st message in 10:04:05 and **user2** answered in 10:24:05, diff 20 min and in this day user1 sent the 2nd message in 11:04:05 and user2 answered in 11:06:05, diff is 2 min. Knowing several diff periods I can calculate mean() and if I have only messages from 1 type of user my average would be 'no answered'

My code is here

fin = fin.reset_index() # reset indexes
# here I wanna leave only the first message of each type of users, convert [user1, user1, user2] to [user1, user2]
test = fin.loc[fin['sender_full_name'].shift() != fin['sender_full_name']]
g = test.groupby('group') # got the series of group
for i in g.groups: # iterate over every group element
    id = g.get_group(i).index # got the index of this group
    f = test.loc[id] # new dataframe by index
    ds = pd.Series(f['timestamp']).reset_index(drop=True) # got all timestamps by date 
    
    avg_idx = pd.Series(f['id'])
    s1 = pd.Series([])
    s2 = pd.Series([])
    for j in range(ds.size):
        s1 = s1.append([pd.Series(ds[j])], ignore_index=True) if j % 2 == 0 else s1
        s2 = s2.append([pd.Series(ds[j])], ignore_index=True) if j % 2 != 0 else s2
        s3 = s2.subtract(s1) if len(s2) > 0 else 'без ответа'
        s3 = s3.loc[~s3.isna()].mean() if len(s2) > 0 else s3
        fin.loc[fin['id'].isin(avg_idx), 'avg'] = s3 # write new value of average
fin

But I got not expected values, also after that I want to drop other rows in group instead of the 1st by, i.e.

from

id group person company timestamp
1000 2020-04-20 user1 AA 11:04:05
1034 2020-04-20 user1 AA 12:04:05
1078 2020-04-21 user2 BB 12:34:05
1200 2020-04-22 user1 AA 12:40:05

to

id group person company timestamp
1000 2020-04-20 user1 AA 11:04:05
1078 2020-04-21 user2 BB 12:34:05
1200 2020-04-22 user1 AA 12:40:05
data-science pandas dataframe numpy python
1 Answers

Let's define a monologue as a sequence of messages from the same person. Below is how you get the time difference between the start of each monologue from a client and the start of the last monologue from the consultant before it.

import pandas as pd

df = pd.DataFrame({
    "group": [
        "2020-04-01", "2020-04-01", "2020-04-01", "2020-04-01",
        "2020-04-01", "2020-04-20", "2020-04-20", "2020-04-21",
        "2020-04-22"
    ],
    "person": [
        "user1", "user1", "user2", "user1", "user2", "user1",
        "user1", "user2", "user1"
    ],
    "time": [
        "10:04:05", "10:14:05", "10:24:05", "11:04:05", "11:06:05",
        "11:04:05", "12:04:05", "12:34:05", "12:40:05"
    ],
    "company": ["A", "A", "B", "A", "B", "AA", "AA", "BB", "AA"],
})

# Only keep first message for each monologue
df = df[df["person"] != df["person"].shift()]

# Add a timestamp column for time difference computations
df["timestamp"] = pd.to_datetime(df["group"] + " " + df["time"])

# Keep time when user is user1, NaN otherwise
person_is_user1 = df["person"] == "user1"
user1_time = df["timestamp"].where(person_is_user1)

# Then replace NaNs with the closest earlier non-NaN value
last_user1_time = user1_time.fillna(method="ffill")

# Then exclude rows where user is user1
last_user1_time = last_user1_time.where(~person_is_user1)
df["diff"] = df["timestamp"] - last_user1_time

Result:

        group person      time company           timestamp            diff
0  2020-04-01  user1  10:04:05       A 2020-04-01 10:04:05             NaT
2  2020-04-01  user2  10:24:05       B 2020-04-01 10:24:05 0 days 00:20:00
3  2020-04-01  user1  11:04:05       A 2020-04-01 11:04:05             NaT
4  2020-04-01  user2  11:06:05       B 2020-04-01 11:06:05 0 days 00:02:00
5  2020-04-20  user1  11:04:05      AA 2020-04-20 11:04:05             NaT
7  2020-04-21  user2  12:34:05      BB 2020-04-21 12:34:05 1 days 01:30:00
8  2020-04-22  user1  12:40:05      AA 2020-04-22 12:40:05             NaT

You can then call df["diff"].mean() to get the average difference:

>>> df["diff"].mean()
Timedelta('0 days 08:37:20')
user_96810810
Related