Hey there, let's break down why your Gross sum for abc@gmail.com is coming out as that messy dash-connected string instead of a proper numerical total—this is a super common gotcha with Pandas!

The Root Cause #

Your Gross column is stored as a string (object) type, not a numerical type (int/float). When you call .sum() on a string column, Pandas doesn't do math—it concatenates all the strings together. Since each of your Gross entries for that email starts with a minus sign (like "-10", "-49"), you end up with all those values strung together with dashes.

Step-by-Step Fix #

1. Verify the Data Type First #

First, confirm that Gross is indeed a string:

print(df['Gross'].dtype)
print(df['Gross'].sample(5))  # Check a few sample values

You’ll almost certainly see object in the dtype output.

2. Clean and Convert to Numeric #

We need to strip out any non-numeric characters (like commas in "1,500.00") and convert the column to a numerical type. Use pd.to_numeric() to handle this:

# Remove commas from values like "1,500.00"
df['Gross'] = df['Gross'].str.replace(',', '')

# Convert to float; coerce any unconvertible values to NaN
df['Gross'] = pd.to_numeric(df['Gross'], errors='coerce')

If there are any values that can’t be converted (like random text), errors='coerce' turns them into NaN. You can fill these with 0 if that makes sense for your data:

df['Gross'] = df['Gross'].fillna(0)

3. Re-Run Your GroupBy Sum #

Now that Gross is a numerical column, your original code will work as expected:

# Your original grouped aggregation
sum_df = df.groupby(['From Email Address'], as_index=False).agg(
    {'Name':'first', 
     'From Email Address':'first', 
     'Country':'first', 
     'Subject':'first',
     'Gross': 'sum'
    }
)

# Or the simplified version
sum_df2 = df.groupby('From Email Address', as_index=False)['Gross'].sum()

Extra Checks #

• If you have other non-numeric characters (like $ for currency), add another str.replace() step to remove them before conversion.
• Use df[df['Gross'].isna()] to check which rows failed conversion—this can help you spot any unexpected formatting in your raw data.

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