0

I have a dataframe of messages, their sender and time. It looks similar to this:

Time           Person    Message
8:00           A         '1'
8:01           B         '2'
8:02           B         '3'
8:05           A         '4'
9:04           B         '5'

I want to calculate how long it took a person to respond to the other person's last message. i.e get the following:

Time           Person    Message     TimeDiff(in min)
8:00           A         '1'         0
8:01           B         '2'         1
8:02           B         '3'         2
8:05           A         '4'         3
9:04           B         '5'         59

How can I reach this goal?

Edit: Note this isn't a simple diff with the last element in the Time col as it's conditioned on the Person column. See example.

matanc1
  • 6,525
  • 6
  • 37
  • 57

1 Answers1

1

you can use a datetime series to calculate the difference.

First get the serie using pd.to_datetime:

date_time_serie=pd.to_datetime(df['Time'] ,format = '%H:%M')

Then calculate the difference using diff and dt.second:

df['TimeDiff(in min)']=date_time_serie.diff().fillna(pd.Timedelta(0)).dt.seconds/60
df['TimeDiff(in min)']=df['TimeDiff(in min)'].astype(int)
print(df)

   Time Person Message  TimeDiff(in min)
0  8:00      A     '1'                 0
1  8:01      B     '2'                 1
2  8:02      B     '3'                 1
3  8:05      A     '4'                 3
4  9:04      B     '5'                59
ansev
  • 30,322
  • 5
  • 17
  • 31