I have a dataframe with messages sent and received. I want to calculate the time it took for someone to reply to the message.
import numpy as np import pandas as pd from datetime import datetime df = pd.DataFrame({'sent':[78,18,94,55,68,57,78,8], 'received':[18,78,35,14,57,68,57,17], 'time':['2017-01-01T12','2017-01-01T13', '2017-01-02T12','2017-02-01T13', '2017-01-01T14','2017-01-01T15', '2017-01-01T16','2017-01-01T17']}) df['time'] = pd.to_datetime(pd.Series(df['time']))
The method I thought of using was identifying pairs, so if sent =A and received =B, then there should be another entry with sent=B and received =A.
df["pairs"] = df.apply(lambda x: not df[(df["sent"] == x["received"]) & (df["received"] == x["sent"]) & (df.index != x.name)].empty, axis=1)
Then once I identify the pairs, I can calculate the time it took to respond with
sent_time = datetime.strptime('2017-01-01 12:00:00', fmt) recieved_time = datetime.strptime('2017-01-01 13:00:00', fmt) if sent_time > recieved_time: td = sent_time - recieved_time else: td = recieved_time - sent_time time = int(round(td.total_seconds() / 60))
I feel like I can do these separately, but I can’t seem to put them together.
EDIT
So as for the output, I guess I would need a separate dataframe that would list the senders and the time it took for someone to reply to the e-mail.
so with the example,
the message was sent by 78 and it took 60 min to respond. then a message was sent by 68 and it took 60 min to respond
Sender | time_to_respond |
---|---|
78 | 60 |
68 | 60 |
Advertisement
Answer
#Sort row values to create unique group df[['s','t']] = np.sort(df[['sent','received']], axis=1) #Subset duplicated groups s = df[df.duplicated(subset=['s','t'], keep=False)] #Compute time difference between duplicated groups, drop duplicated rows and unwanted columns s=s.assign(time_to_respond=s.groupby(['s','t'])['time'].transform(lambda x:x.diff().bfill().dt.total_seconds()/60)).drop_duplicates(subset=['s','t'])[['sent','time_to_respond']] sent time_to_respond 0 78 60.0 4 68 60.0