Pandas DataFrame执行校验时实现首个校验跳过含任意NaN值行的方案咨询

需求说明 #

现有两个Pandas DataFrame，需分别应用两套独立校验逻辑，最终将所有校验差异合并为统一的结果列表，校验规则如下：

• 第一套校验：仅对行内除Fruits外所有数值列均无NaN的行执行校验，存在任意NaN的行直接跳过该校验
• 第二套校验：无行过滤限制，所有行都参与校验

现有实现代码 #

import pandas as pd
import numpy as np

df1 = {'Fruits': ["Banana","Blueberry","Apple","Cherry","Mango","Pineapple","Watermelon","Papaya","Pear","Coconut"],
        'Price': [2,1.5,np.nan,2.5,3,4,np.nan,3.5,1.5,2],'Amount':[40,19,np.nan,np.nan,60,70,80,np.nan,45,102],
        'Quantity Frozen':[3,4,np.nan,15,np.nan,9,12,8,np.nan,80],
        'Quantity Fresh':[37,12,np.nan,45,np.nan,61,np.nan,24,14,20],
        'Multiple':[74,17,np.nan,112.5,np.nan,244,np.nan,84,21,40]}
df1 = pd.DataFrame(df1, columns = ['Fruits', 'Price','Amount','Quantity Frozen','Quantity Fresh','Multiple'])

df2 = {'Fruits': ["Banana","Blueberry","Apple","Cherry","Mango","Pineapple","Watermelon","Papaya","Pear","Coconut"],
        'Price': [2,1.5,np.nan,2.6,3,4,np.nan,3.5,1.5,2],'Amount':[40,16,np.nan,np.nan,60,72,80,np.nan,45,100],
        'Quantity Frozen':[3,4,np.nan,np.nan,np.nan,9,12,8,np.nan,80],
        'Quantity Fresh':[np.nan,12,np.nan,45,np.nan,61,np.nan,24,15,20],
        'Multiple':[74,17,np.nan,112.5,np.nan,244,np.nan,84,20,40]}

df2 = pd.DataFrame(df2, columns = ['Fruits', 'Price','Amount','Quantity Frozen','Quantity Fresh','Multiple'])

#Validation Check 1:

for name, dataset in {'Fruit Dataset1':df1,'Fruit Dataset2':df2}.items():
        dataset['dif_Stock on Hand'] = dataset['Quantity Fresh']+dataset['Quantity Frozen']
        for varname,var in {'Stock on Hand vs. Quantity Fresh + Quantity Frozen':'dif_Stock on Hand'}.items():
                print('{} differences in {}:'.format(name, varname))
                print(dataset[var].value_counts())
                print('\n')

#Validation Check 2:

for name, dataset in {'Fruit Dataset1':df1,'Fruit Dataset2':df2}.items():
        dataset['dif_Multiple'] = dataset['Price'] * dataset['Quantity Fresh']
        for varname,var in {'Multiple vs. Price x Quantity Fresh':'dif_Multiple'}.items():
                print('{} differences in {}:'.format(name, varname))
                print(dataset[var].value_counts())
                print('\n')

# #Wrangling internal inconsistency data frames to be in correct format
inconsistency_vars = ['dif_Stock on Hand','dif_Multiple']
inconsistency_var_betternames = {'dif_Stock on Hand':'Stock on Hand = Quantity Fresh + Quantity Frozen','dif_Multiple':'Multiple = Price x Quantity on Hand'}

# #Rollup1
idvars1=['Fruits']
df1 = df1[idvars1 + inconsistency_vars]
df2 = df2[idvars1 + inconsistency_vars]
df1 = df1.melt(id_vars = idvars1, value_vars = inconsistency_vars, value_name = 'Difference Magnitude')
df2 = df2.melt(id_vars = idvars1, value_vars = inconsistency_vars, value_name = 'Difference Magnitude')
df1['dataset'] = 'Fruit Dataset1'
df2['dataset'] = 'Fruit Dataset2'

# #First table in Internal Inconsistencies Sheet (Table 5)
inconsistent = pd.concat([df1,df2])
inconsistent = inconsistent[['variable','Difference Magnitude','dataset','Fruits']]
inconsistent['variable'] = inconsistent['variable'].map(inconsistency_var_betternames)
inconsistent = inconsistent[inconsistent['Difference Magnitude'] != 0]

预期输出 #

inconsistent_true = {'variable': ["Stock on Hand = Quantity Fresh + Quantity Frozen","Stock on Hand = Quantity Fresh + Quantity Frozen","Multiple = Price x Quantity on Hand",
"Multiple = Price x Quantity on Hand","Multiple = Price x Quantity on Hand"],
        'Difference Magnitude': [1,2,1,4.5,2.5],
        'dataset':["Fruit Dataset1","Fruit Dataset1","Fruit Dataset2","Fruit Dataset2","Fruit Datset2"],
        'Fruits':["Blueberry","Coconut","Blueberry","Cherry","Pear"]}
inconsistent_true = pd.DataFrame(inconsistent_true, columns = ['variable', 'Difference Magnitude','dataset','Fruits'])

注：预期输出中Fruit Datset2为拼写失误，正确输出应为Fruit Dataset2

修改方案 #

核心修改点 #

1. 第一套校验逻辑新增行过滤：先判断行内除Fruits外所有列是否都无NaN，仅对符合条件的行计算库存差异，不符合条件的行差异字段赋值为NaN
2. 补全差异计算逻辑：原有代码仅做了数值相加/相乘，未减去校验基准值（Amount/Multiple）
3. 最终结果过滤时新增排除NaN的规则，避免未参与第一套校验的行进入结果

修改后完整代码 #

import pandas as pd
import numpy as np

df1 = {'Fruits': ["Banana","Blueberry","Apple","Cherry","Mango","Pineapple","Watermelon","Papaya","Pear","Coconut"],
        'Price': [2,1.5,np.nan,2.5,3,4,np.nan,3.5,1.5,2],'Amount':[40,19,np.nan,np.nan,60,70,80,np.nan,45,102],
        'Quantity Frozen':[3,4,np.nan,15,np.nan,9,12,8,np.nan,80],
        'Quantity Fresh':[37,12,np.nan,45,np.nan,61,np.nan,24,14,20],
        'Multiple':[74,17,np.nan,112.5,np.nan,244,np.nan,84,21,40]}
df1 = pd.DataFrame(df1, columns = ['Fruits', 'Price','Amount','Quantity Frozen','Quantity Fresh','Multiple'])

df2 = {'Fruits': ["Banana","Blueberry","Apple","Cherry","Mango","Pineapple","Watermelon","Papaya","Pear","Coconut"],
        'Price': [2,1.5,np.nan,2.6,3,4,np.nan,3.5,1.5,2],'Amount':[40,16,np.nan,np.nan,60,72,80,np.nan,45,100],
        'Quantity Frozen':[3,4,np.nan,np.nan,np.nan,9,12,8,np.nan,80],
        'Quantity Fresh':[np.nan,12,np.nan,45,np.nan,61,np.nan,24,15,20],
        'Multiple':[74,17,np.nan,112.5,np.nan,244,np.nan,84,20,40]}

df2 = pd.DataFrame(df2, columns = ['Fruits', 'Price','Amount','Quantity Frozen','Quantity Fresh','Multiple'])

# 校验1：仅对全数值列无NaN的行执行
for name, dataset in {'Fruit Dataset1':df1,'Fruit Dataset2':df2}.items():
    # 筛选除Fruits外所有列无NaN的行
    num_cols = dataset.columns.drop('Fruits')
    valid_mask = dataset[num_cols].notna().all(axis=1)
    # 仅对有效行计算差异，无效行赋值为NaN自动跳过后续统计
    dataset.loc[valid_mask, 'dif_Stock on Hand'] = dataset.loc[valid_mask, 'Quantity Fresh'] + dataset.loc[valid_mask, 'Quantity Frozen'] - dataset.loc[valid_mask, 'Amount']
    dataset.loc[~valid_mask, 'dif_Stock on Hand'] = np.nan
    for varname,var in {'Stock on Hand vs. Quantity Fresh + Quantity Frozen':'dif_Stock on Hand'}.items():
        print('{} differences in {}:'.format(name, varname))
        print(dataset[var].value_counts())
        print('\n')

# 校验2：无行过滤限制
for name, dataset in {'Fruit Dataset1':df1,'Fruit Dataset2':df2}.items():
    dataset['dif_Multiple'] = dataset['Price'] * dataset['Quantity Fresh'] - dataset['Multiple']
    for varname,var in {'Multiple vs. Price x Quantity Fresh':'dif_Multiple'}.items():
        print('{} differences in {}:'.format(name, varname))
        print(dataset[var].value_counts())
        print('\n')

# 结果整理
inconsistency_vars = ['dif_Stock on Hand','dif_Multiple']
inconsistency_var_betternames = {'dif_Stock on Hand':'Stock on Hand = Quantity Fresh + Quantity Frozen','dif_Multiple':'Multiple = Price x Quantity on Hand'}

idvars1=['Fruits']
df1 = df1[idvars1 + inconsistency_vars]
df2 = df2[idvars1 + inconsistency_vars]
df1 = df1.melt(id_vars = idvars1, value_vars = inconsistency_vars, value_name = 'Difference Magnitude')
df2 = df2.melt(id_vars = idvars1, value_vars = inconsistency_vars, value_name = 'Difference Magnitude')
df1['dataset'] = 'Fruit Dataset1'
df2['dataset'] = 'Fruit Dataset2'

# 合并结果，过滤差异为0或NaN的记录
inconsistent = pd.concat([df1,df2])
inconsistent = inconsistent[['variable','Difference Magnitude','dataset','Fruits']]
inconsistent['variable'] = inconsistent['variable'].map(inconsistency_var_betternames)
inconsistent = inconsistent[(inconsistent['Difference Magnitude'] != 0) & (inconsistent['Difference Magnitude'].notna())]
inconsistent = inconsistent.reset_index(drop=True)
print(inconsistent)

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