如何对比两个Pandas分组聚合结果并检查申报工时差异？

Got it, let's work through this problem step by step. You've got two aggregated DataFrames (df1 and df2) from groupby operations, and you want to join them to spot differences in reported hours. First, let's make sure we're using complete, meaningful data (your df2 example looks truncated), then walk through the solution.

Step 1: Ensure Your Data Has Matching Keys #

To align the data correctly, you'll need shared columns to join on. Since these are grouped results, the logical keys are Week and Empl—this lets you compare the same employee's hours for the same week across both datasets.

First, let's define complete sample data (I'll fill in a realistic df2 with intentional discrepancies to demonstrate):

import pandas as pd

# Your original aggregated df1
df1 = pd.DataFrame({
    "Week": ["3/30/2018", "3/30/2018", "3/30/2018", "3/23/2018", "3/23/2018","3/16/2018", "3/16/2018", "3/9/2018", "3/9/2018"], 
    "Empl": ["Sam", "John", "Mike", "Sam", "Mike","Sam", "John", "Mike", "Sam"], 
    "Hrs": [11, 12, 2, 13, 5, 14, 15, 16, 7]
})

# Simulated df2 (another source's aggregated hours, with differences/missing entries)
df2 = pd.DataFrame({
    "Week": ["3/30/2018", "3/30/2018", "3/23/2018", "3/23/2018","3/16/2018", "3/9/2018", "3/9/2018", "3/2/2018"], 
    "Empl": ["Sam", "Mike", "Sam", "Mike","John", "Mike", "Sam", "Sam"], 
    "Hrs": [11, 3, 13, 5, 15, 16, 8, 10]
})

Step 2: Join the DataFrames and Calculate Differences #

Use pd.merge() with an outer join to preserve all records from both datasets—this way you won't miss cases where an entry exists in one DataFrame but not the other. We'll also rename the hours columns to keep them distinct, then add columns to flag discrepancies.

# Join on Week and Empl, add suffixes to distinguish hours columns
merged_df = pd.merge(
    df1, 
    df2, 
    on=["Week", "Empl"], 
    how="outer", 
    suffixes=("_df1", "_df2")
)

# Calculate the hour difference and flag discrepancies
merged_df["Hrs_Diff"] = merged_df["Hrs_df1"] - merged_df["Hrs_df2"]
merged_df["Has_Discrepancy"] = merged_df["Hrs_Diff"].notna() & (merged_df["Hrs_Diff"] != 0)

# View the results
print(merged_df)

What the Output Shows: #

• Hrs_Diff: Positive values mean df1 has more hours than df2; negative values mean the opposite. NaN indicates the entry exists in only one DataFrame.
• Has_Discrepancy: A True value flags rows where hours don't match, or where one dataset is missing the entry entirely.

If You're Comparing Weekly Total Hours #

If your df2 is aggregated at the weekly level (not employee-level), adjust the approach by first summing hours per week in df1, then joining on Week:

# Aggregate df1 to weekly totals
df1_weekly = df1.groupby("Week")["Hrs"].sum().reset_index(name="Total_Hrs_df1")

# Simulated df2 with weekly totals
df2_weekly = pd.DataFrame({
    "Week": ["3/30/2018", "3/23/2018", "3/16/2018", "3/9/2018"],
    "Total_Hrs_df2": [25, 18, 29, 23]
})

# Join and check discrepancies
weekly_merged = pd.merge(df1_weekly, df2_weekly, on="Week", how="outer")
weekly_merged["Hrs_Diff"] = weekly_merged["Total_Hrs_df1"] - weekly_merged["Total_Hrs_df2"]
weekly_merged["Has_Discrepancy"] = weekly_merged["Hrs_Diff"].notna() & (weekly_merged["Hrs_Diff"] != 0)

print(weekly_merged)

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