How to efficiently count the number of larger elements for every elements in another column?

user_7357673 0
Viewed 49

I have the following df

    name        created_utc
0   t1_cqug90j  1430438400
1   t1_cqug90k  1430438400
2   t1_cqug90z  1430438400
3   t1_cqug91c  1430438401
4   t1_cqug91e  1430438401
... ...         ...

in which all values in column name are unique. I would like to create a dictionary whose keys are the same elements as in column name. The value for each such a key is the number of elements in column created_utc greater than that of the key. My expected result is something like

{'t1_cqug90j': 6, 't1_cqug90k': 0, 't1_cqug90z': 3, ...}

In this case, there are 6 elements in column created_utc greater than 1430438400, which is the corresponding value of t1_cqug90j. I can do the loop to generate such dictionary. However, the loop is not efficient in my case with more than 3 millions rows.

Could you please elaborate on a more efficient way?

import pandas as pd
import numpy as np
df = pd.read_csv('https://raw.githubusercontent.com/leanhdung1994/WebMining/main/df1.csv', header = 0)[['name', 'created_utc']]
df
pandas count python-3.x dictionary python
1 Answers

This is a possible approach. Let's first assume that your name column is unique-valued. Then we can count the created_utc like this:

count_utc = df.groupby('created_utc').size()
cumulative_counts = count_utc.shift(fill_value=0).cumsum()

output = dict(zip(df['name'], df['created_utc'].map(cumulative_counts)) )

Then the first few output would look like this:

{'t1_cqug90j': 0,
 't1_cqug90k': 0,
 't1_cqug90z': 0,
 't1_cqug91c': 3,
 't1_cqug91e': 3,
 't1_cqug920': 5
 ...
}

Now if the name's are not unique (which is unlikely due to your output expectation), but in which case, we can certainly just take the largest values of the cumulative_counts substract the size of the name count (?), something like this:

output = dict(zip(df['name'],
                  df['created_utc'].map(cumulative_counts)
                      .sub(df.groupby('name')['name'].transform('size'))
                      .add(1)                  
                 ) )
user_42384080
Related