I have some data in the following format:

Visitor_id    rownum    contenttype
abc             1          PageA
abc             2          PageB
abc             3          PageC
def             1          PageB
def             2          PageD

This data is for about 40 Million+ visitor_id. I want the result in the following format:


I wrote the following code to achieve this:

import pandas as pd
import numpy as np
from tqdm import tqdm
d = d.sort_values('visitor_id')
flow_new = []
visitors = set(d['visitor_id'])
for visitor_id in tqdm(set(d['visitor_id'])):
    flow_session = []
    temp = d[d.visitor_id==visitor_id]
    for count in range(0,len(temp)):
        flow_session.append(str(temp.iloc[count,2]) + temp.iloc[count,3])

Although this code runs properly and gives me the required results on a smaller dataset, it takes insanely long on the whole dataset. Here's the expected time from tqdm:

  0%|            | 42/12009071 [01:47<7473:36:18,  2.24s/it]

This is obviously extremely long and there has to be another way to do this much quicker.

  • 2
    \$\begingroup\$ You should add your imports. I have no idea what tqdm is for example. \$\endgroup\$ – Graipher Jun 16 '17 at 17:00
  • \$\begingroup\$ @Graipher: Added the imports \$\endgroup\$ – Patthebug Jun 19 '17 at 15:41
  • \$\begingroup\$ This sounds like a job for your database (or sqlite), not for Python. You're iterating through every page visit by every user that's ever visited your site. You couldn't have expected this to be fast? \$\endgroup\$ – I wrestled a bear once. Jun 19 '17 at 18:16

First thing first

Don't use tqdm just to show some fancy progress bar. On their website, they say the overhead is low (which it might be the case), but you can just print something like: Processing data. Please wait... until the data is processed.

Or, you can use timeit module to see how long your code takes.

Second, fix the bug:

This line:

flow_session.append(str(temp.iloc[count, 2]) + temp.iloc[count, 3])

Will throw an IndexError: single positional indexer is out-of-bounds because of temp.iloc[count, 3] which makes your code off-topic for this site's purpose.

Perhaps you wanted:

flow_session.append(str(temp.iloc[count, 1]) + temp.iloc[count, 2])

Instead of:

visitors = set(d['visitor_id'])

You can use:

visitors = d.visitor_id.unique()

In your for loop, you already have the unique visitors so why calculating them again ? Just do:

for visitor_id in visitors

Use len(df.index) instead of len(df). Due to one additional function call it is a bit slower than calling len(df.index) directly, but this should not play any role in most use cases.

You're not using numpy so you can remove it.

After I spent quite some time on this, I came up with a different approach which is probably better (and shorter):

Suppose you have the following dataframe:

d = pd.DataFrame(columns=['visitor_id', 'row_num', 'contenttype'], data=[
    ['abc', 1, 'PageA'], ['abc', 2, 'PageB'], ['abc', 3, 'PageC'],
    ['def', 1, 'PageB'], ['def', 2, 'PageD'], ['crf', 7, 'PageE']

You can create a new column which is the joint between rownum and content:

d["joint"] = d.row_num.astype(str) + d.contenttype

Then, you just want to group your data by visitor_id and create a list of lists with that and the joint. That can be easily done using a list comprehension and groupby:

result = [list(group.joint.values) for name, group in d.groupby("visitor_id")]

So, the final code looks like this:

import pandas as pd

def process_data(_df):
    _df["joint"] = _df.row_num.astype(str) + _df.contenttype

    return [list(group.joint.values)
            for name, group in d.groupby("visitor_id")]

if __name__ == '__main__':
    d = pd.DataFrame(columns=['visitor_id', 'row_num', 'contenttype'], data=[
        ['abc', 1, 'PageA'], ['abc', 2, 'PageB'], ['abc', 3, 'PageC'],
        ['def', 1, 'PageB'], ['def', 2, 'PageD'], ['crf', 7, 'PageE']

I don't think you can get any better than that.

  • \$\begingroup\$ Thanks so much. I updated my code as per your recommendation. It is still going on for an hour or so. Guess I will just have to wait for it to finish, but I'm sure it's better than my method. \$\endgroup\$ – Patthebug Jun 19 '17 at 21:13

Maybe run the counts in off hours and save to a table? It would then provide your counts on demand at other times. You could also just add a new visit to the table during the in between time. UPDATE yourTempTable set count = count+1 visitorID= thisVisitorID


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.