Python - pandas - find most frequent combination with tie-resolution - performance

user_2079925 0
Viewed 180

Data
I have a data set that looks something like this:

| id    | string_col_A | string_col_B | creation_date |
|-------|--------------|--------------|---------------|
| x12ga | STR_X1       | STR_Y1       | 2020-11-01    |
| x12ga | STR_X1       | STR_Y1       | 2020-10-10    |
| x12ga | STR_X2       | STR_Y2       | 2020-11-06    |
| x21ab | STR_X4       | STR_Y4       | 2020-11-06    |
| x21ab | STR_X5       | STR_Y5       | 2020-11-02    |
| x11aa | STR_X3       | STR_Y3       | None          |

Goal

  1. I want to find the most frequent combination of values for each id.
  2. Further, in case of tie I want to extract the combination that is most recent.

i.e The result for the above table would be:

| id    | string_col_A | string_col_B |
|-------|--------------|--------------|
| x12ga | STR_X1       | STR_Y1       |
| x21ab | STR_X4       | STR_Y4       |
| x11aa | STR_X3       | STR_Y3       |

Explanation

  1. For x12ga, the explanation is straightforward. STR_X1, STR_Y1 occurs twice and STR_X2, STR_Y2 occurs only once (i,e no tie resolution)
  2. x11aa is straightforward as well, there is only one row
  3. For x21ab, both combination has 1 row, but STR_X4, STR_Y4 is most recent.

Code
Here is what I have so far:


def reducer(id_group):
    id_with_sizes = id_group.groupby(
            ["id", "string_col_A", "string_col_B"], dropna=False).agg({
            'creation_date': [len, max]
            }).reset_index()
    id_with_sizes.columns = [
            "id", "string_col_A", "string_col_B", "row_count",
            "recent_date"
            ]
    id_with_sizes.sort_values(by=["row_count", "recent_date"],
                           ascending=[False, False],
                           inplace=True)
    return id_with_sizes.head(1).drop(["recent_date", "row_count"], axis=1)

I call the above methods like so:

assignment =  all_data.groupby("id").apply(inventor_reduce)

The Problem
The above code when testing with data works fine, but the actual dataset that I am working with has more than 10M rows, with ~3M ids. Consequently, to process 10K IDS it takes 5 minutes and overall it would take 25 hours to process. I would like to improve the performance.

The solution
I have seen questions on stackoverflow (and elsewhere) about getting frequent combinations (albeit without tie-resolution) and about vectorizing the process to improve performance. I am not quite sure how to achieve both with my problem above.

Ideally, the solution would still be pandas based (code looks and reads better with pandas)

pandas-groupby pandas python performance
3 Answers
  1. You could create a series s that combines both columns
  2. Return the index of the max count
  3. Filter by that index. NOTE: If you are on an earlier version of pandas, then take out , sort=False from the .groupby code and sort at the end.

--

s = df['string_col_A'] + df['string_col_B']
df['max'] = df.groupby(['id',s])['id'].transform('count')
df = df.iloc[df.groupby('id', sort=False)['max'].idxmax().values].drop(['max', 'creation_date'], axis=1)
df
Out[1]: 
      id string_col_A string_col_B
0  x12ga       STR_X1       STR_Y1
3  x21ab       STR_X4       STR_Y4
5  x11aa       STR_X3       STR_Y3
user_63667700

You need to groupby only by the id column and find the most-frequent data (mode) based on this.

To make things easier you can create another column combined_str :

df['combined_str'] = df['string_col_A'] + df['string_col_B']

group by id and reduce using the pd.Series.mode function:

df = df.sort_values(by=['creation_date'])
df = df.groupby(['id'])['combined_str'].agg(most_common = ('combined_str', pd.Series.mode))
user_102084810

Let us try groupby with transform , then get the count of most common value, then sort_values with drop_duplicates

df['help'] = df.groupby(['id','string_col_A','string_col_B'])['string_col_A'].transform('count')
out = df.sort_values(['help','creation_date'],na_position='first').drop_duplicates('id',keep='last').drop(['help','creation_date'],1)
out
Out[122]: 
      id string_col_A string_col_B
3  x21ab       STR_X4       STR_Y4
5  x11aa       STR_X3       STR_Y3
0  x12ga       STR_X1       STR_Y1
user_79645270
Related