Pandas分组遍历过慢及apply函数执行异常问题求助

Hey there! Let's tackle your two main issues here: the duplicate rows after switching to groupby.apply() and the slow performance with 800 rows.

1. Fixing the Duplicate Rows Problem #

The root cause of your duplicate groups is a small misstep in how you're using groupby.apply(): you're initializing an empty final_df and then calling groupby on that empty DataFrame, which doesn't make sense. Instead, you should apply your function directly to the original df that contains your data. Also, after applying the function, you'll want to reset the index to clean up the grouped keys that stick around.

Corrected apply() Code #

First, tweak your calculate_attendance function to avoid modifying the input DataFrame in-place (which can cause warnings):

def calculate_attendance(temp_df):
    temp_df = temp_df.sort_values(by=['Checkin At Time'], ascending=True)
    num_of_rows = len(temp_df)
    
    first_checkin_time = temp_df.iloc[0]['Checkin At Time']
    last_checkin_time = temp_df.iloc[-1]['Checkin At Time']
    temp_df['FIRST_BODY_TEMP'] = temp_df.iloc[0]['Body Temp']
    temp_df['LAST_BODY_TEMP'] = temp_df.iloc[-1]['Body Temp']
    
    if num_of_rows <= 1:
        temp_df['TOTAL_TIME'] = 0
        temp_df['LAST_OUT_TIME'] = None
    else:
        temp_df['TOTAL_TIME'] = last_checkin_time - first_checkin_time
        temp_df['LAST_OUT_TIME'] = last_checkin_time.dt.time
        temp_df['TOTAL_TIME'] = pd.to_timedelta(temp_df['TOTAL_TIME'], unit='s')
        temp_df['TOTAL_TIME'] = temp_df['TOTAL_TIME'].apply(lambda x: strfdelta(x, '{hours} Hrs {minutes} Min'))
    
    return temp_df.iloc[0]

Then call apply() on your original data and reset the index:

final_df = df.groupby(["Mobile Number", "Checkin At Date"]).apply(calculate_attendance).reset_index(drop=True)

This will give you a clean DataFrame with no duplicate groups.

2. Speeding Up Performance (Big Improvement!) #

Your original loop and even the apply() approach are slow because they're doing row-by-row operations. Pandas shines with vectorized operations, so let's replace the loop/apply with groupby.agg()—this will process your 800 rows in milliseconds instead of seconds.

Optimized Vectorized Approach #

First, do your initial data prep (and let's combine date and time into a single datetime column for easier handling):

import pandas as pd

# Initial data prep
df["Code"] = df["[People]Employee Code"]
df["Checkin At DateTime"] = pd.to_datetime(df["Checkin At Date"] + " " + df["Checkin At Time"])
df["Checkin At Time"] = pd.to_datetime(df["Checkin At Time"])

Then use agg() to compute all your required metrics in one go:

# Define what we want to aggregate for each group
aggregation = {
    "Checkin At Time": ["min", "max"],
    "Body Temp": ["first", "last"],
    "Code": "first",  # Assume Code is consistent per group; adjust if needed
    # Add any other columns you need to retain here with "first" or appropriate agg
}

# Run the aggregation
grouped_df = df.groupby(["Mobile Number", "Checkin At Date"]).agg(aggregation)

# Flatten the multi-level columns for readability
grouped_df.columns = [
    "FIRST_CHECKIN_TIME", 
    "LAST_CHECKIN_TIME", 
    "FIRST_BODY_TEMP", 
    "LAST_BODY_TEMP", 
    "Code"
]
grouped_df = grouped_df.reset_index()

# Calculate total time and last out time
grouped_df["TOTAL_TIME"] = grouped_df["LAST_CHECKIN_TIME"] - grouped_df["FIRST_CHECKIN_TIME"]

# Handle single-entry groups
single_entry_mask = grouped_df["TOTAL_TIME"] == pd.Timedelta(0)
grouped_df.loc[single_entry_mask, "TOTAL_TIME"] = "0 Hrs 0 Min"
grouped_df.loc[single_entry_mask, "LAST_OUT_TIME"] = None

# Handle multi-entry groups
multi_entry_mask = ~single_entry_mask
grouped_df.loc[multi_entry_mask, "LAST_OUT_TIME"] = grouped_df.loc[multi_entry_mask, "LAST_CHECKIN_TIME"].dt.time
grouped_df.loc[multi_entry_mask, "TOTAL_TIME"] = grouped_df.loc[multi_entry_mask, "TOTAL_TIME"].apply(
    lambda x: strfdelta(x, "{hours} Hrs {minutes} Min")
)

Why this is faster: groupby.agg() uses Pandas' optimized C-backed operations instead of Python-level loops, so it's drastically more efficient. You'll notice the runtime drop from 18-20 seconds to almost instant.

Final Notes #

• Make sure your strfdelta function is defined (it's a custom function to format timedeltas, which Pandas doesn't have built-in).
• If you need to retain more columns from the original data, just add them to the aggregation dict with an appropriate method (like "first" if the value is consistent per group).

内容的提问来源于stack exchange，提问作者Nikhil Parmar