7
\$\begingroup\$

I have a Pandas DataFrame that contains a row per member per day, expressing member interaction with a website. Members interact only on some days, each member is identified with an ID. Here is a simulated example:

import pandas as pd
import numpy as np

# Generate data.

ids = np.repeat(np.arange(100), np.random.randint(100, size = 100))

test = (
    pd.Series(
        ids,
        index = pd.Series(pd.date_range('2020-01-01', '2020-02-01').values).sample(ids.shape[0], replace = True)
    )
    .sort_index()
)

print(test.head())

Gives:

2020-01-01     4
2020-01-01    65
2020-01-01    95
2020-01-01    40
2020-01-01    88
dtype: int32

I'd like to calculate a unique count of members within a 7 day rolling window. After some experimentation and research on Stack Overflow (including Pandas rolling / groupby function), I arrived at an explicit loop and slicing.

# Calculate rolling 7 day unique count.

unique_count = {}

for k in test.index.unique():

    unique_count[k] = test.loc[k - pd.Timedelta('7 days'):k].nunique()

# Store as a dataframe and truncate to a minimum period.

unique_count = pd.DataFrame.from_dict(unique_count, orient = 'index', columns = ['7_day_unique_count']).iloc[7:]

print(unique_count.tail())

Gives:

            7_day_unique_count
2020-01-28                  98
2020-01-29                  98
2020-01-30                  98
2020-01-31                  97
2020-02-01                  97

This seems to work correctly and performs OK. But is it possible to do this without the explicit loop, such as with resample / groupby / rolling functions? If so, is that more efficient?

\$\endgroup\$
  • \$\begingroup\$ Welcome to CodeReview@SE. This seems at the very border of what's on- and off-topic here: looking for information and opinions on one implementation alternative not written (yet) rather than a review of the code presented. \$\endgroup\$ – greybeard Jul 2 at 4:49
  • 2
    \$\begingroup\$ @greybeard This is on-topic. However I'm interested, what train of thought is making you think that this is potentially off-topic? \$\endgroup\$ – Peilonrayz Jul 2 at 7:48
  • \$\begingroup\$ (@Peilonrayz his is the night before an 8-dayish holiday trip; in a pinch for time and not likely to respond a gain any time soon. Pretty much what I commented before: 1) got the impression that feedback about any or all facets of the code is not wanted 2) the alternative explicitly asked about/for is a not presented b) not coded (yet).) \$\endgroup\$ – greybeard Jul 2 at 21:49
  • \$\begingroup\$ @greybeard thanks for taking the time to reply. I hope you have a nice holiday! \$\endgroup\$ – Peilonrayz Jul 2 at 22:08
  • \$\begingroup\$ I don't know if you've seen the discussion and issues on Github, but you might like this one and the discussion around it. \$\endgroup\$ – Juho Jul 30 at 8:15
1
\$\begingroup\$

How about something like this:

test.rolling('7d').apply(lambda s:s.nunique()).groupby(level=0).max()

rolling('7d') is the rolling window. The window is determined for each row. So the first window starts from the row "2020-01-01 4" and extends 7 days in the past. The second window starts from the row "2020-01-01 65" and extends 7 days in the past.

.apply(lambda s:s.nunique()) determines the number of unique items in the window. But, because of the way rolling works, we get multiple results for the same day.

.groupby(level=0) groups the results by the date.

.max() takes the maximum nunique value for that date.

The above approach seemed rather slow, so here's a different approach. Basically use a Counter as a multiset and use a deque as a FIFO queue. For each day update the Counter with the id's for that day and subtract the ones for the day at the beginning of the window. The len of the Counter is then the number of unique ids.

from collections import Counter, deque

WINDOW = 7

fifo = deque(maxlen=WINDOW)
uniq = Counter()

def unique_in_window(x):
    if len(fifo) == WINDOW:
        uniq.subtract(fifo.popleft())
        
    uniq.update(x)
    fifo.append(x)
    
    return len(uniq)

test.groupby(level=0).apply(unique_in_window)
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.