如何修正Pandas中含非数值列分组求min时的错误结果？

Got it, let's work through this problem together! The core issue here is that your val column is an object type with a mix of strings (like AX, BX) and numeric values. When you call min() directly on this column, Pandas uses string lexicographical ordering instead of numeric ordering—so "11" gets treated as smaller than "2" because the character '1' comes before '2' in ASCII. That's why you're getting 11 instead of the actual smallest numeric value, 2.

Here are two reliable ways to fix this:

方法一：先清洗数据，转换为数值类型后再分组 #

This approach first cleans the column to separate numeric values from non-numeric ones, then computes the minimum:

import pandas as pd

# 示例数据
df1 = pd.DataFrame({
    'col1': ['A', 'A', 'A', 'A', 'A', 'A'],
    'val': ['AX', '2', '11', '13', 'BX', '20']
})

# 1. 将val列转换为数值，非数值转为NaN
df1['val_numeric'] = pd.to_numeric(df1['val'], errors='coerce')

# 2. 过滤掉非数值的行，然后分组取最小数值
min_numeric = df1.dropna(subset=['val_numeric']).groupby('col1')['val_numeric'].min().reset_index()

# 3. 匹配回原val列的内容（如果需要保留原字符串形式，比如"2"而不是2.0）
df2 = min_numeric.merge(df1[['col1', 'val', 'val_numeric']], 
                        on=['col1', 'val_numeric'], 
                        how='left').drop_duplicates('col1').drop('val_numeric', axis=1)

print(df2)

输出结果：

col1 val
0    A   2

方法二：使用自定义分组函数直接筛选数值最小值 #

If you prefer to handle everything within the groupby operation without adding an extra column, you can use a custom apply function:

def get_min_numeric_row(group):
    # 把分组里的val转成数值，过滤掉非数值
    numeric_vals = pd.to_numeric(group['val'], errors='coerce').dropna()
    if not numeric_vals.empty:
        # 找到最小数值对应的原行
        min_val = numeric_vals.min()
        return group[group['val'] == str(min_val)]
    # 如果分组里没有数值，返回空（可选）
    return pd.DataFrame()

df2 = df1.groupby('col1').apply(get_min_numeric_row).reset_index(drop=True)

print(df2)

输出结果：

col1 val
0    A   2

Both methods will give you the row with the smallest numeric value ("A 2") instead of the string-based minimum.

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