How can I obtain a single aligned outlier flag column using groupby with np.select in Pandas?

You can use groupby with transform instead of apply. The transform method returns a series aligned with the original DataFrame. Here's how you can modify your code:

PYTHONCopied
import pandas as pd
import numpy as np

# Create sample data
df = pd.DataFrame({
    'datecol': pd.date_range('2024-1-1', '2024-12-31'),
    'val': np.random.randint(low=100, high=5000, size=366)
})

# Define function to detect outliers in a Series using the IQR method
def is_outlier(x):
    q1 = x.quantile(0.25)
    q3 = x.quantile(0.75)
    iqr = q3 - q1
    lower_bound = q1 - 1.5 * iqr
    upper_bound = q3 + 1.5 * iqr
    return ((x <= lower_bound) | (x >= upper_bound)).astype(int)

# Use groupby with transform to calculate outlier flag aligned to original index
df['flag'] = df.groupby(df['datecol'].dt.weekday)['val'].transform(is_outlier)

Explanation

1. Function is_outlier:

   • Computes the first (q1) and third (q3) quartiles.
   • Calculates the inter-quartile range (IQR).
   • Sets the lower and upper bounds.
   • Flags the numbers outside these boundaries as outliers (1) and the rest as 0.

2. Using transform:

   • df.groupby(df['datecol'].dt.weekday)['val'].transform(is_outlier) applies the function to each weekday group.
   • Transform ensures that the output is aligned with the original DataFrame so that you can directly add the flag column.

This approach will give you a single series that can be added as a flag column to your original DataFrame.