Dataframe:Create new dataframe keeping duplicate on basis of multiple columns which are coming more then 2 times(Time constraint)

user_13796935 0
Viewed 70

This ia sample code:

data = {'Col1':  [0,1,2,3,4,5,6,7,8,9,10,11,12],
            'Col2': ['A1','A1','A1','A2','A2','A3','A4','A4','A4','A1','a9','a9','A2'],
            'Col3': ['B1','B1','B1','B2','B3','B4','B5','B5','B5','B1','b9','b9','B2'],
            'Col4': ['ab','bc','cd','da','da','da','df','fd','vf','sd','asd','sda','sdf'],
            }

    df2 = pd.DataFrame (data)

    counts_col2 = df2.groupby("Col2")["Col2"].transform(len)
    counts_col3 = df2.groupby("Col3")["Col3"].transform(len)
    
    mask = (counts_col2 > 2) & (counts_col3 > 2)

    df2[mask]

output

    Col1 Col2 Col3 Col4
0   0     A1    B1  ab
1   1     A1    B1  bc
2   2     A1    B1  cd
6   6     A4    B5  df
7   7     A4    B5  fd
8   8     A4    B5  vf
9   9     A1    B1  sd
  1. Everything is right in it,But Since my data is huge is taking long time to execute.
  2. So if any better soultion can be used for reducing time
  3. Any help would be apreciated
pandas dataframe group-by python
3 Answers

Do not use agg(len)

You should aggregate with size, which is implemented quickly. That will give you a huge performance gain, and after that you can squeeze out a little bit more specifying sort=False in the groupby, So change your lines to:

counts_col2 = df.groupby("Col2", sort=False)["Col2"].transform('size')
counts_col3 = df.groupby("Col3", sort=False)["Col3"].transform('size')

Example of timings/equivalence:

import perfplot
import pandas as pd
import numpy


def agg_len(df):
    counts_col2 = df.groupby("Col2")["Col2"].transform(len)
    counts_col3 = df.groupby("Col3")["Col3"].transform(len)
    mask = (counts_col2 > 2) & (counts_col3 > 2)
    return df[mask]

def agg_size(df):
    counts_col2 = df.groupby("Col2")["Col2"].transform('size')
    counts_col3 = df.groupby("Col3")["Col3"].transform('size')
    mask = (counts_col2 > 2) & (counts_col3 > 2)
    return df[mask]

def agg_size_nosort(df):
    counts_col2 = df.groupby("Col2", sort=False)["Col2"].transform('size')
    counts_col3 = df.groupby("Col3", sort=False)["Col3"].transform('size')
    mask = (counts_col2 > 2) & (counts_col3 > 2)
    return df[mask]

#@ansev's solution
def map_value_counts(df)
    return df[df['Col2'].map(df['Col2'].value_counts()>2) &
              df['Col3'].map(df['Col3'].value_counts()>2)]


perfplot.show(
    setup=lambda N: pd.DataFrame({'Col1': range(N),
                       'Col2': np.random.choice(np.arange(N), N),
                       'Col3': np.random.choice(np.arange(N), N),
                       'Col4': np.random.choice(np.arange(N), N)}),
    kernels=[
        lambda df: agg_len(df),
        lambda df: agg_size(df),
        lambda df: agg_size_nosort(df),
        lambda df: map_value_counts(df)
    ],
    labels=['Agg len', 'Agg size', 'Agg size No Sort', 'Map Value Counts'],
    n_range=[2 ** k for k in range(16)],
    equality_check=np.allclose,
    xlabel="~ Number of Groups"
)

enter image description here

user_43333590

Other good approach is Series.value_counts and Series.map.

df2[df2['Col2'].map(df2['Col2'].value_counts()>2) &
    df2['Col3'].map(df2['Col3'].value_counts()>2)]

Taking data from @aLollz answer

%%timeit
df2[df2['Col2'].map(df2['Col2'].value_counts()>2) &
    df2['Col3'].map(df2['Col3'].value_counts()>2)]
31.2 ms ± 8.79 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)


%%timeit
df2[(df2.groupby("Col2")["Col2"].transform('size')>2) &
    (df2.groupby("Col3")["Col3"].transform('size')>2)]
40.5 ms ± 981 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)
user_118842370

Change

(counts_col2 > 2)

To

(counts_col2.to_numpy() > 2)
user_66928980
Related