如何在Pandas DataFrame中删除零值占比≥50%的行？

测试用Pandas数据如下：

import pandas as pd

data = {'col1': [0, 1, 2, 3, 4],
        'col2': [1, 177, 62876, 83036, 108504],
        'col3': [7765, 177, 62876, 83036, 108504],
        'col4': ['7,765', 'Non-Bitter', 'Non-Bitter', 'Non-Bitter', 'Non-Bitter'],
        'col5': ['NaN', '2_Fnb', '3_Fnb', '4_Fnb', '5_Fnb'],
        'col6': ['1_Fnb', '2_Fnb', '3_Fnb', '4_Fnb', '5_Fnb'],
        'col7': ['O(C([H])([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]',
                 'O=C([H])C([H])([H])[H]',
                 'O(C([H])(C([H])([H])[H])OC([H])([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]',
                 'O(C([H])(C([H])([H])[H])OC([H])([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]',
                 'O(C([H])([H])C([H])([H])C([H])=C([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]'],
        'col8': [1.0, 1.0, 1.0, 1.0, 1.0],
        'col9': ['CCOC(C)OCC', 'CC=O', 'CCCCOC(C)OCCc1ccccc1', 'CC(C)CCOC(C)OCCC(C)C', 'CCOC(C)OCCC=CCC'],
        'col10': ['NaN', 'NaN', 'StandardizeStereo\nStandardizeStereo_ChiralFlag', 'NaN', 'StandardizeStereo\nStandardizeStereo_ChiralFlag'],
        'col11': [5.0645, 1.67, 8.31033, 8.24664, 8.7538],
        'col12': [5.0, 0.0, 7.05817, 11.0, 9.0,0,0,0],
        'col13': [4.92, 0.0, 6.36993, 10.92, 8.65999],
        'col14': [5.01386, 1.4863, 6.85916, 8.19952, 8.50578],
        'col15': [8.0, 3.0, 16.0, 14.0, 12.0],
        'col16': [7.0, 2.0, 16.0, 13.0, 11.0],
        'col17': [7.0, 1.0]
}

需求是删除**零值占比≥50%**的行，但使用以下代码后删除了所有行：

df2.drop(df2[df2.astype(bool).sum(axis=1) >= df2.shape[1]*0.5].index, inplace=True)

尝试改用以下代码后结果依旧：

df4.drop(df4[(df4 == 0) | df4.isnull()].astype(int).sum(axis=1) >= df4.shape[1]*0.5].index, inplace=True)

1. 逻辑完全反转：你写的代码是删除**非零/非空值占比≥50%的行，而需求是删除零值/NaN占比≥50%**的行，方向搞反了。
2. 未区分数值列与非数值列：测试数据中存在大量字符串列（col4-col10），这些列的非空字符串在astype(bool)中会被判定为True，导致每行的非零/非空计数远超过50%，最终触发全删。
3. 数据长度不一致：col12、col17的长度与其他列不匹配，创建DataFrame时会自动补NaN，额外增加了无效值的数量，干扰占比计算。

步骤1：修正数据格式 #

先统一所有列的长度，避免自动生成额外的NaN：

# 修正后的测试数据
data = {'col1': [0, 1, 2, 3, 4],
        'col2': [1, 177, 62876, 83036, 108504],
        'col3': [7765, 177, 62876, 83036, 108504],
        'col4': ['7,765', 'Non-Bitter', 'Non-Bitter', 'Non-Bitter', 'Non-Bitter'],
        'col5': ['NaN', '2_Fnb', '3_Fnb', '4_Fnb', '5_Fnb'],
        'col6': ['1_Fnb', '2_Fnb', '3_Fnb', '4_Fnb', '5_Fnb'],
        'col7': ['O(C([H])([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]',
                 'O=C([H])C([H])([H])[H]',
                 'O(C([H])(C([H])([H])[H])OC([H])([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]',
                 'O(C([H])(C([H])([H])[H])OC([H])([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]',
                 'O(C([H])([H])C([H])([H])C([H])=C([H])C([H])([H])[H])C([H])(C([H])([H])[H])C([H])(C([H])([H])[H])[H]'],
        'col8': [1.0, 1.0, 1.0, 1.0, 1.0],
        'col9': ['CCOC(C)OCC', 'CC=O', 'CCCCOC(C)OCCc1ccccc1', 'CC(C)CCOC(C)OCCC(C)C', 'CCOC(C)OCCC=CCC'],
        'col10': ['NaN', 'NaN', 'StandardizeStereo\nStandardizeStereo_ChiralFlag', 'NaN', 'StandardizeStereo\nStandardizeStereo_ChiralFlag'],
        'col11': [5.0645, 1.67, 8.31033, 8.24664, 8.7538],
        'col12': [5.0, 0.0, 7.05817, 11.0, 9.0],  # 统一长度为5
        'col13': [4.92, 0.0, 6.36993, 10.92, 8.65999],
        'col14': [5.01386, 1.4863, 6.85916, 8.19952, 8.50578],
        'col15': [8.0, 3.0, 16.0, 14.0, 12.0],
        'col16': [7.0, 2.0, 16.0, 13.0, 11.0],
        'col17': [7.0, 1.0, None, None, None]  # 补全长度为5
}

步骤2：正确筛选并删除目标行 #

只针对数值列统计零值和NaN的占比，逻辑上保留占比<50%的行（即删除占比≥50%的行）：

import pandas as pd

df = pd.DataFrame(data)

# 1. 筛选所有数值类型的列
numeric_cols = df.select_dtypes(include=['number']).columns

# 2. 计算每行中零值 + NaN 的数量
zero_nan_count = (df[numeric_cols] == 0).sum(axis=1) + df[numeric_cols].isnull().sum(axis=1)

# 3. 设置阈值：数值列数量的50%
threshold = len(numeric_cols) * 0.5

# 4. 保留零值+NaN占比<50%的行
df_filtered = df[zero_nan_count < threshold]

print(df_filtered)

代码说明 #

• 只针对数值列计算，避免非数值列的字符串干扰统计结果
• 明确统计零值+NaN的数量，而非非零值数量，符合需求逻辑
• 修正数据长度问题，避免额外NaN影响占比计算

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