4
\$\begingroup\$

I'm wondering if there is a more efficient way of filtering a dataframe down based on certain unique values in several columns. Once I have it filtered down, I then want to extract keep one the largest value and I do this by dropping all indexes from the original dataframe. However, this process is slow and as the iterations and number of items in the dataframe increase the speed becomes more of an issue.

import pandas as pd
import numpy as np
import timeit


data_len = 1000
half = int(data_len/2)
fifth = int(data_len/5)
ans = ['Cat'] * data_len
cols = ['White'] * half + ['Black'] * half
age = np.random.randint(1, 10, data_len)
breed = ['A'] * fifth + ['B'] * fifth + ['C'] * fifth + ['D'] * fifth + ['E'] * fifth
weights = np.random.uniform(1, 100, data_len)
heights = np.random.uniform(1, 100, data_len)

df = pd.DataFrame(columns=['Animal', 'Color', 'Age', 'Breed', 'Weight', 'Height'])

df['Animal'] = ans
df['Color'] = cols
df['Age'] = age
df['Breed'] = breed
df['Weight'] = weights
df['Height'] = heights


def get_largest(df):
    drop_id = []
    sort_cols = ['Weight', 'Height']
    for Animal in pd.unique(df['Animal']):
        animals_df = df.loc[np.in1d(df['Animal'], Animal)]
        for Color in pd.unique(animals_df['Color']):
            color_df = animals_df.loc[np.in1d(animals_df['Color'], Color)]
            for uid in pd.unique(color_df['Age']):
                age_df = color_df.loc[np.in1d(color_df['Age'], uid)]
                for vic in pd.unique(age_df['Breed']):
                    breed_df = age_df.loc[np.in1d(age_df['Breed'], vic)]
                    drop_id.extend(
                        breed_df.sort_values(by=sort_cols, ascending=[False] * len(sort_cols)).index[1:].values)
    df = df.drop(df.index[drop_id])
    return df

func = lambda: get_largest(df)
print(timeit.timeit(func, number=100))
\$\endgroup\$
0
0
\$\begingroup\$

I may have misunderstood what your are trying to achieve. However it sounds like you want to filter your dataframe for a unique list animal, color, age and breed. and from that take that largest, weight and height.

you could achieve this by sorting your data frame by weight then height this will sort from lowest to highest.

you can then drop duplicates based on the animal, color, age and breed and specify to the drop method to keep the last row(This will be the biggest since the table is ordered from low to high).

We can write this in a general function that will accept three parameters, a df, a list to sort by and a list to filter by.

def my_df_filter(df: pd.DataFrame, sort_by: List[str], drop_by: List[str]) -> pd.DataFrame:
    sorted_df = df.sort_values(by=sort_by)
    dropped_df = sorted_df.drop_duplicates(subset=drop_by, keep='last')
    return dropped_df

This could actually all be returned in a single line by chaining them together. However I have broken it down for more readability. But you could write as

def my_df_filter1(df: pd.DataFrame, sort_by: List[str], drop_by: List[str]) -> pd.DataFrame:
    return df.sort_values(by=sort_by).drop_duplicates(subset=drop_by, keep='last')

using your original code and a dataset of 100,000 rows. it reduced it from 100k rows to 54 rows and running it 100 times via timeit on my laptop took around 18 seconds.

import pandas as pd
import numpy as np
import timeit
from typing import List

def my_df_filter(df: pd.DataFrame, sort_by: List[str], drop_by: List[str]) -> pd.DataFrame:
    return df.sort_values(by=sort_by).drop_duplicates(subset=drop_by, keep='last')


data_len = 100000
half = int(data_len / 2)
fifth = int(data_len / 5)
data = {
    'Animal': ['Cat'] * data_len,
    'Color': ['White'] * half + ['Black'] * half,
    'Age': np.random.randint(1, 10, data_len),
    'Breed': ['A'] * fifth + ['B'] * fifth + ['C'] * fifth + ['D'] * fifth + ['E'] * fifth,
    'Weight': np.random.uniform(1, 100, data_len),
    'Height': np.random.uniform(1, 100, data_len)
}
df = pd.DataFrame(data)

print(df.shape)
print(my_df_filter(df, sort_by=['Weight', 'Height'], drop_by=['Animal', 'Color', 'Age', 'Breed']).shape)

func = lambda: my_df_filter(df, sort_by=['Weight', 'Height'], drop_by=['Animal', 'Color', 'Age', 'Breed'])
print(timeit.timeit(func, number=100))

OUTPUT

(100000, 6)
(54, 6)
18.2784114

Having a general function means that its much easier to increase or reduce the number of columns to filter or sort by without having to change much code.

\$\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.