Vfrdtyky

I have a table like this (email is simplified to just one letter here):

timestamp                  | email

2018-10-17 13:00:00+00:00  | m

2018-10-17 13:00:00+00:00  | m

2018-10-17 13:00:10+00:00  | 

2018-10-17 13:00:10+00:00  | v

2018-10-17 13:00:30+00:00  |  

2018-10-17 13:00:30+00:00  | c

2018-10-17 13:00:50+00:00  | p

2018-10-17 13:01:00+00:00  |  

2018-10-17 13:01:00+00:00  | m

2018-10-17 13:01:00+00:00  | s

2018-10-17 13:01:00+00:00  | b

Now, I would like to make a new column, that would count the number of times that the email repeated in the last 30 seconds before the entry, for example.

timestamp                  | email | count | comment

2018-10-17 13:00:00+00:00  | m     |   1   |

2018-10-17 13:00:00+00:00  | m     |   2   | (there were 2 entries in the last 30s)

2018-10-17 13:00:10+00:00  |       |   1   | (empty we count as well)

2018-10-17 13:00:10+00:00  | v     |   1   |

2018-10-17 13:00:30+00:00  |       |   2   | (counting the empty like emails)

2018-10-17 13:00:30+00:00  | c     |   1   | 

2018-10-17 13:00:50+00:00  | p     |   1   |

2018-10-17 13:01:00+00:00  |       |   2   | (in the last 30s from this ts, we have 2)

2018-10-17 13:01:00+00:00  | m     |   1   | (the first 2 m happened before the last 30s)

2018-10-17 13:01:00+00:00  | s     |   1   |

2018-10-17 13:01:00+00:00  | b     |   1   |

The timestamp is a dateTime object

timestamp          datetime64[ns, UTC]

Also, it's the index and it's sorted.
I first tried, this command:

df['email'].groupby(df.email).rolling('120s').count().values

But it doesn't work with a string, so I converted it to a unique number, using:

full_df['email'].factorize()

But the results doesn't appear to be right:

timestamp                  | email | count | comment

2018-10-17 13:00:00+00:00  | m     |   1   |  

2018-10-17 13:00:00+00:00  | m     |   2   | 

2018-10-17 13:00:10+00:00  |       |   1   | 

2018-10-17 13:00:10+00:00  | v     |   2   |  (No ideia about this result)

2018-10-17 13:00:30+00:00  |       |   3   | (Appears to just keeping count)

2018-10-17 13:00:30+00:00  | c     |   1   |  (Then just go back to 1 again... )

2018-10-17 13:00:50+00:00  | p     |   2   |

2018-10-17 13:01:00+00:00  |       |   3   | 

2018-10-17 13:01:00+00:00  | m     |   4   | 

2018-10-17 13:01:00+00:00  | s     |   1   |

2018-10-17 13:01:00+00:00  | b     |   1   |

Any ideia what I'm doing wrong and how can I get what I would like to get?

Many Thanks,
Joao

You can use apply after rolling to count the number of times the last element of the window shows up in the window like this:

df['count'] = df['email'].astype('category').cat.codes.rolling('30s').apply(lambda x: sum(x==x[-1]))