GroupBy语句未按相似字符串分组及按security_type1聚合需求

Got it, let's fix this grouping issue you're dealing with. The problem is that your state column has variations like "Tied Done" and "Done" which are being treated as separate groups—we just need to normalize those statuses first so related entries get grouped together.

Step 1: Normalize the State Column #

First, we'll create a new column that collapses the similar statuses into two clear categories: Done (covers both "Done" and "Tied Done") and Traded Away (covers "Traded Away" and "Tied Traded Away"). You can use either of these two simple methods:

Method 1: Remove the "Tied " prefix directly

# Strip the "Tied " prefix from state values
dfRFQ_Breakdown_By_Done_Traded_Away_Grp['normalized_state'] = dfRFQ_Breakdown_By_Done_Traded_Away_Grp['state'].str.replace('Tied ', '', regex=False)

Method 2: Check for keyword presence (more flexible if statuses vary)

# Classify based on whether "Done" is in the state string
dfRFQ_Breakdown_By_Done_Traded_Away_Grp['normalized_state'] = dfRFQ_Breakdown_By_Done_Traded_Away_Grp['state'].apply(
    lambda x: 'Done' if 'Done' in x else 'Traded Away'
)

Step 2: Group and Aggregate #

Now we can group by the normalized state and security_type1, then sum up the count values:

# Group by normalized state and security type, sum the counts
grouped_result = dfRFQ_Breakdown_By_Done_Traded_Away_Grp.groupby(
    ['normalized_state', 'security_type1'], 
    as_index=False
)['count'].sum()

# Sort and display the final result
display(grouped_result.sort_values(['security_type1', 'normalized_state'], ascending=[True, True]))

Expected Output #

You'll get a cleaned-up dataframe where each security_type1 has two rows: one for aggregated Done counts (including Tied Done) and one for aggregated Traded Away counts (including Tied Traded Away). For example:

...

This should exactly match what you're looking for—combining all related status rows per security type.

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