import pandas as pd
import numpy as np
# 这里写所有表格的判断规则 按顺序每个表格一个函数 方便后续修改
pd.set_option('display.max_columns', 1000)
pd.set_option('display.width', 1000)
pd.set_option('display.max_colwidth', 1000)
# 表1 土壤容重机械组成数据 为了方便做具体修改
def soil_bulk_density(arr): #arr为计算过的数组
    # （1）土壤容重不在[0.8, 1.6]范围以内的，存疑
    shenHeList=[] # 定义一个数组存放容重存疑的数据
    shenHeTarget = [] # 存放每条数据 有问题的指标
    try:
        for i in arr['土壤容重平均值(g/cm3)(计算)']:
            if i > 1.6 or i < 0.8:
                shenHeList.append('土壤容重:超阈值。')
                shenHeTarget.append('土壤容重平均值。')
            else:
                shenHeList.append('')
                shenHeTarget.append('')
    except Exception as err:
        print('土壤容重判断出错！请检查soil_bulk_density中判断土壤容重内容',err)
    # （2）土壤利用类型→耕地、园地→相对极差＞15 %，存疑；土壤利用类型→林地、草地→相对极差＞20 %，存疑
    tRTypeList = [] # 定义一个数组存放土壤利用类型存疑的数据
    tRTypeTarget= [] # 定义一个数组存放土壤利用类型存疑的数据指标名称
    try:
        for i in arr['相对极差(%)']:
            # TODO 此处土地利用类型应从原样品编码提取7-8位两位数组，01代表耕地，02代表园地，03代表林地，04代表草地
            if i > 15 and arr.loc[arr['相对极差(%)'] == i, '土地利用类型'].iloc[0] == '耕地园地':
                tRTypeList.append('存疑：耕地园地相对极差＞15%。')
                tRTypeTarget.append('耕地园地极差。')
            elif i > 20 and arr.loc[arr['相对极差(%)'] == i, '土地利用类型'].iloc[0] == '林地草地':
                tRTypeList.append('存疑：林地草地相对极差＞20%。')
                tRTypeTarget.append('林地草地极差。')
            else:
                tRTypeList.append('')
                tRTypeTarget.append('')
    except Exception as err:
        print('相对极差判断、土壤利用类型判断出错！请检查soil_bulk_density中判断相对极差判断、土壤利用类型内容',err)
    # （3）加和不在[99.98, 100.02]范围内的，存疑
    plusShenHeList = [] # 定义一个数组存放加和存疑的数据
    plusShenHeTarget = [] # 保存土壤颗粒加和存疑的指标
    try:
        for i in arr['加和%']:
            if float(i) > 100.02 or float(i) < 99.98:
                plusShenHeList.append('土壤颗粒加和:超阈值。')
                plusShenHeTarget.append('土壤颗粒含量加和。')
            else:
                plusShenHeList.append('')
                plusShenHeTarget.append('')
    except Exception as err:
        print('颗粒含量加和判断出错！请检查soil_bulk_density中判断颗粒含量加和内容',err)
    # 根据国际土壤质地类型三角形编程实现对质地的分类→判断质地分类和质地名称是否正确
    # 判断土壤类型逻辑：
    soilList = []  # 定义一个数组存放土地类型的数据
    soilContent = []
    soilContentTarget = [] # 存放土壤质地异常的指标名称
    xSLErr = []  # 存放ph>7 洗失量为空的异常数据
    xSLTarget = []  # 存放异常数据 指标名称
    try:
        # 按行循环读取所有数据
        for index, row in arr.iterrows():
            # 1.将0.02-0.2，0.2-2两列加起来
            plusSoil = row['0.2-0.02mm颗粒含量%'] + row['2-0.2mm颗粒含量%']
            small_002_list = row['0.02-0.002mm颗粒含量%']
            small_0002_list = row['0.002mm以下颗粒含量%']
            if np.isnan(plusSoil) or np.isnan(small_002_list) or np.isnan(small_0002_list):
                soilList.append('')
            # 具体判断 这里为了方便看 减少了嵌套逻辑
            elif small_0002_list >=65 and  small_0002_list <100: # 2. <0.002含量 65-100 ->重黏土
                soilList.append('重黏土')
            elif small_0002_list >= 45 and small_0002_list <65: # 3.<0.002含量 45-65 ->黏土
                soilList.append('黏土')
            elif small_0002_list >= 25 and small_0002_list <45 and small_002_list >= 45 and small_002_list <75: # 4. <0.002含量 25-45 and 0.002-0.02含量 45-75 -> 粉(砂)质黏土
                soilList.append('粉(砂)质黏土')
            elif small_0002_list >= 25 and small_0002_list<45 and  small_002_list>=0 and small_002_list<45 and plusSoil>=10 and plusSoil <55: # 5. <0.002含量 25-45 and 0.002-0.02含量 0-45 and 0.02-2含量 10-55-> 壤质黏土
                soilList.append('壤质黏土')
            elif small_0002_list >= 25 and small_0002_list<45 and  small_002_list>=0 and small_002_list<20 and plusSoil>=55 and plusSoil <75:# 6. <0.002含量 25-45 and 0.002-0.02含量 0-20 and 0.02-2含量 55-75-> 砂质黏土
                soilList.append('砂质黏土')
            elif small_0002_list >= 15 and small_0002_list<25 and  small_002_list>=45 and small_002_list<85: # 7.<0.002含量 15-25 and 0.002-0.02含量 45-85 -> 粉(砂)质黏壤土
                soilList.append('粉(砂)质黏壤土')
            elif small_0002_list >= 15 and small_0002_list<25 and  small_002_list>=20 and small_002_list<45 and plusSoil>=30 and plusSoil <55:# 8.<0.002含量 15-25 and 0.002-0.02含量 20-45 and 0.02-2含量 30-55-> 黏壤土
                soilList.append('黏壤土')
            elif small_0002_list >= 15 and small_0002_list<25 and  small_002_list>=0 and small_002_list<30 and plusSoil>=55 and plusSoil <85:# 9.<0.002含量 15-25 and 0.002-0.02含量 0-30 and 0.02-2含量 55-85-> 砂质黏壤土
                soilList.append('砂质黏壤土')
            elif small_0002_list >= 0 and small_0002_list<15 and  small_002_list>=45 and small_002_list<100:#10.<0.002含量 0-15 and 0.002-0.02含量 45-100 ->粉(砂)质壤土
                soilList.append('粉(砂)质壤土')
            elif  small_0002_list >= 0 and small_0002_list<15 and  small_002_list>=30 and small_002_list<45 and plusSoil>=40 and plusSoil <55: # 11.<0.002含量 0-15 and 0.002-0.02含量 30-45 and 0.02-2含量 40-55-> 壤土
                soilList.append('壤土')
            elif small_0002_list >= 0 and small_0002_list<15 and  small_002_list>=0 and small_002_list<45 and plusSoil>=55 and plusSoil <85: # 12.<0.002含量 0-15 and 0.002-0.02含量 0-45 and 0.02-2含量 55-85-> 砂质壤土
                soilList.append('砂质壤土')
            elif small_0002_list >= 0 and small_0002_list<15 and  small_002_list>=0 and small_002_list<15 and plusSoil>=85 and plusSoil <100: # 13.<0.002含量 0-15 and 0.002-0.02含量 0-15 and 0.02-2含量 85-100-> 砂土及壤质砂土
                soilList.append('砂土及壤质砂土')
            else:
                soilList.append('') # 除所有情况外 还有空值

        # 比较和原有数据是否一致
        arr['土壤质地(判断)'] = soilList
        for index, row in arr.iterrows():
            if (row['土壤质地(判断)'] != row['土壤质地']) and (not pd.isna(row['土壤质地'])):
                soilContent.append('存疑：土壤质地填报与判断不一致')
                soilContentTarget.append('土壤质地。')
            else:
                soilContent.append('')
                soilContentTarget.append('')
            # 如果pH＞7,则洗失量数据不能为空；
            if (not pd.isna(row['pH']) and row['pH'] > 7 and pd.isna(row['洗失量(吸管法需填)%'])):
                xSLErr.append('洗失量：ph>7但洗失量未检测。')
                xSLTarget.append('洗失量。')
            else:
                xSLErr.append('')
                xSLTarget.append('')
    except Exception as err:
        print('土壤类型判断出错！请检查soil_bulk_density中判断土壤类型内容', err)
    # 把存疑数据组合并返回
    # print('shenHeList--',shenHeList,len(shenHeList))
    # print('plusShenHeList--', plusShenHeList, len(plusShenHeList))
    # print('tRTypeList--', tRTypeList, len(tRTypeList))
    # print('soilContent--', soilContent, len(soilContent))
    # print('soilList--', soilList, len(soilList))
    pdData = pd.DataFrame({
        '审核结果': pd.Series(shenHeList) + pd.Series(tRTypeList) + pd.Series(plusShenHeList) + pd.Series(soilContent) + pd.Series(xSLErr),
        '土壤质地(判断)': soilList,
        '异常指标': pd.Series(shenHeTarget) + pd.Series(tRTypeTarget) + pd.Series(plusShenHeTarget) + pd.Series(soilContentTarget) + pd.Series(xSLTarget),
    })
    return pdData
# 这是一个判断范围的函数 如果需要修改范围 修改start end值就行

def is_not_in_range(value):
    return value <30 or value > 90


# 表3 水稳性大团聚体规则判断函数
def water_stable(arr):
    # （1）不在[30, 90]范围以内的，存疑
    shenHeList = []  # 定义一个数组存放团聚体存疑的数据
    shenHeTar = [] # 存放水稳异常指标
    # （2）总和超过90，存疑；耕地和园地＞80，提示关注；林地和草地＞90，提示关注
    plusList = []
    plusTar = [] # 水稳总和异常指标名称
    soilType = []
    # （3）＞5mm指标占比超过10 %，存疑，应回溯
    rateList = []
    rateTar = [] # >5mm占比异常指标
    try:
        for index, row in arr.iterrows():
            # 规则1判断 先判断值是否存在
            # if (not pd.isna(row['＞5mm%']) and is_not_in_range(row['＞5mm%'])) or (
            #         not pd.isna(row['3-5mm%']) and is_not_in_range(row['3-5mm%'])) or (
            #         not pd.isna(row['2-3mm%']) and is_not_in_range(row['2-3mm%'])) or (
            #         not pd.isna(row['1-2mm%']) and is_not_in_range(row['1-2mm%'])) or (
            #         not pd.isna(row['0.5-1mm%']) and is_not_in_range(row['0.5-1mm%'])) or (
            #         not pd.isna(row['0.25-0.5mm%']) and is_not_in_range(row['0.25-0.5mm%'])):
            #     shenHeList.append('存疑:团聚体百分比不在范围以内。')
            #     shenHeTar.append('水稳分项指标。')
            # else:
            #     shenHeList.append('')
            #     shenHeTar.append('')
            # 规则2判断
            if row['总和(%)'] > 60 or row['总和(%)'] < 1:
                plusList.append('水稳性大团聚体：总和超阈值。')
                plusTar.append('水稳总和。')
            else:
                plusList.append('')
                plusTar.append('')
            if (row['土地利用类型'] == '耕地园地' and row['总和(%)'] > 80) or (row['土地利用类型'] == '林地草地' and row['总和(%)'] > 90):
                soilType.append('关注：耕地园地团聚体总和大于80或林地草地团聚体总和大于90。')
            else:
                soilType.append('')
            if row['＞5mm%'] > 10:
                rateList.append('存疑：>5mm占比超过10%应回溯。')
                rateTar.append('水稳>5mm。')
            else:
                rateList.append('')
                rateTar.append('')
        resData = pd.DataFrame({
                '审核结果': pd.Series(plusList) + pd.Series(soilType) + pd.Series(rateList),
                '异常指标':  pd.Series(plusTar) + pd.Series(rateTar),
                })
        return resData
    except Exception as err:
        print('大团聚体判断出错！请检查water_stable中判断大团聚体内容', err)

# 表5  pH、阳离子交换量、交换性盐基基础数据判断
# 判断土壤类型和阳离子交换量 盐基饱和度的范围
def soilTypeValue(row): # 传入一行数据
    strValue = row['土壤类型']
    soilType = ''

    if isinstance(strValue, str):
        # print('type---', strValue.split('_'))
        # print('strValue',strValue)
        if len(strValue.split('_')) > 1:
            soilType = strValue.split('_')[1] # 获取到土壤类型

    cationChange = row['阳离子交换量Cmol(+)/kg'] # 获取到阳离子交换量
    bHValue = (row['交换性盐总量Cmol(+)/kg']/row['阳离子交换量Cmol(+)/kg'] )*100# 计算出的盐基饱和度
    # 判断三者范围是否合理
    res = ''
    if soilType == '黄红壤':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange<24) and (not pd.isna(bHValue)) and (bHValue > 30 and bHValue<50):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '棕红壤':
        if (not pd.isna(cationChange)) and (cationChange > 6 and cationChange < 15) and (not pd.isna(bHValue)) and (
                bHValue > 25 and bHValue < 70):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '红壤性土':
        if (not pd.isna(cationChange)) and (cationChange > 5 and cationChange < 15) and (not pd.isna(bHValue)) and (
                bHValue > 10 and bHValue < 50):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型黄壤':
        if (not pd.isna(cationChange)) and (cationChange > 5 and cationChange < 15) and not(pd.isna(bHValue)) and (
                 bHValue < 30):
            res = ''
        else:
            res = '该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '黄壤性土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 18) and (not pd.isna(bHValue)) and (
                 bHValue < 45):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型黄棕壤' or soilType == '暗黄棕壤' or soilType == '黄棕壤性土':
        if (not pd.isna(cationChange)) and (cationChange > 8 and cationChange < 22) and (not pd.isna(bHValue)) and (
                bHValue > 30 and bHValue < 60):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型黄褐土' or soilType == '黏盘黄褐土' or soilType == '粘盘黄褐土' or soilType == '粘盘黄褐土' or soilType=='白浆化黄褐土' or soilType=='黄褐土性土':
        if (not pd.isna(cationChange)) and (cationChange > 15 and cationChange < 25) and (not pd.isna(bHValue)) and (
                bHValue > 60 and bHValue < 85):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '粘盘黄褐土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 30) and (not pd.isna(bHValue)) and (
                bHValue > 75 and bHValue < 95):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型棕壤' or soilType == '白浆化棕壤' or soilType == '潮棕壤' or soilType == '棕壤性土' or soilType == '棕壤性土':
        if (not pd.isna(cationChange)) and (cationChange > 5 and cationChange < 20) and (not pd.isna(bHValue)) and (
                cationChange > 25 and cationChange < 65):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型山地草甸土' or soilType == '山地草原草甸土' or soilType == '山地灌丛草甸土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 20) and (not pd.isna(bHValue)) and (
                bHValue > 15 and bHValue < 30):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '酸性紫色土' or soilType == '中性紫色土' or soilType == '石灰性紫色土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 20) and (not pd.isna(bHValue)) and (
                bHValue > 50 and bHValue < 70):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '红色石灰土' or soilType == '黑色石灰土' or soilType == '棕色石灰土' or soilType == '黄色石灰土' :
        if (not pd.isna(cationChange)) and (cationChange > 15 and cationChange < 30) and (not pd.isna(bHValue)) and (
                bHValue > 70 and bHValue < 100):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '酸性石质土' or soilType == '中性石质土' or soilType == '钙质石质土' or soilType == '含盐石质土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 15) and (not pd.isna(bHValue)) and (
                bHValue > 45 and bHValue < 65):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '酸性粗骨土' or soilType == '中性粗骨土' or soilType == '钙质粗骨土' or soilType == '硅质盐粗骨土':
        if (not pd.isna(cationChange)) and (cationChange > 5 and cationChange < 15) and (not pd.isna(bHValue)) and (
                bHValue > 20 and bHValue < 50):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型潮土' or soilType == '灰潮土' or soilType == '脱潮土' or soilType == '湿潮土' or soilType == '盐化潮土' or soilType == '碱化潮土' or soilType == '灌於潮土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 30) and (not pd.isna(bHValue)) and (
                bHValue > 70 and bHValue < 100):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '典型砂姜黑土' or soilType == '石灰性砂姜黑土' or soilType == '盐化砂姜黑土' or soilType == '碱化砂姜黑土' or soilType == '黑粘土':
        if (not pd.isna(cationChange)) and (cationChange > 18 and cationChange < 35) and (not pd.isna(bHValue)) and (
                bHValue > 90 and bHValue < 100):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '淹育水稻土':
        if (not pd.isna(cationChange)) and (cationChange > 20 and cationChange < 30) and (not pd.isna(bHValue)) and (
                bHValue > 85 and bHValue < 90):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '潴育水稻土':
        if (not pd.isna(cationChange)) and (cationChange > 12 and cationChange < 20) and (not pd.isna(bHValue)) and (
                bHValue > 60 and bHValue < 80):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '潜育水稻土':
        if (not pd.isna(cationChange)) and (cationChange > 15 and cationChange < 25) and (not pd.isna(bHValue)) and (
                bHValue > 75 and bHValue < 90):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    elif soilType == '漂洗水稻土':
        if (not pd.isna(cationChange)) and (cationChange > 10 and cationChange < 20) and (not pd.isna(bHValue)) and (
                bHValue > 65 and bHValue < 80):
            res = ''
        else:
            res = '存疑：该土壤类型的阳离子交换量或盐基饱和度范围存疑。'
    return res
def cation_value(arr):
    phList = [] # 保存ph存疑数据
    cationList = [] # 保存阳离子存疑数据
    exchangeableSalt = [] # 保存交换性盐基总量存疑数据
    exchangeableCa = [] # 保存交换性钙总量存疑数据
    exchangeableMg = []  # 保存交换性镁总量存疑数据
    exchangeableK = []  # 保存交换性钾总量存疑数据
    exchangeableNa = []  # 保存交换性钠总量存疑数据
    summaryList = [] # 保存ph 离子和 盐基饱和度范围存疑数据
    soilTypeList = [] # 保存土壤类型 阳离子交换量cmol(+)/kg 和盐基饱和度范围存疑数据
    waterMount = [] # 保存含水量存疑数据 含水量应小于10%

    phTar = [] # 保存ph异常指标
    cationTar = [] # 保存阳离子异常指标
    exchangeableSaltTar = []  # 保存交换性盐基总量异常指标
    exchangeableCaTar = []  # 保存交换性钙异常指标
    exchangeableMgTar = []  # 保存交换性镁异常指标
    exchangeableKTar = []  # 保存交换性钾异常指标
    exchangeableNaTar = []  # 保存交换性钠异常指标
    summaryListTar = []  # 保存ph 离子和 盐基饱和度异常指标
    soilTypeListTar = []  # 保存土壤类型 阳离子交换量cmol(+)/kg 和盐基饱和度异常数据
    waterMountTar = []  # 保存含水量异常指标

    try:
        for index, row in arr.iterrows():
            # 风干样含水量 0.5-5，存疑
            if pd.isna(row['含水量']) or row['含水量'] > 5 or row['含水量'] < 0.5:
                waterMount.append('风干试样含水量(分析基)：超阈值。')
                waterMountTar.append('风干试样含水量(分析基)。')
            else:
                waterMount.append('')
                waterMountTar.append('')
            # （1）pH在[4, 9]范围之外的，存疑；
            if pd.isna(row['pH']) or row['pH'] < 4 or row['pH'] > 9:
                phList.append('pH：超阈值。')
                phTar.append('pH。')
            else:
                phList.append('')
                phTar.append('')
            # （2）阳离子交换量在[6, 38]范围之外的，存疑；
            if row['阳离子交换量Cmol(+)/kg'] < 6 or row['阳离子交换量Cmol(+)/kg'] > 38:
                cationList.append('阳离子交换量：超阈值。')
                cationTar.append('阳离子交换量。')
            else:
                cationList.append('')
                cationTar.append('')
            # （3）交换性盐基总量在[3, 30]范围之外的，存疑；
            if row['交换性盐总量Cmol(+)/kg'] <3 or row['交换性盐总量Cmol(+)/kg'] > 30:
                exchangeableSalt.append('交换性盐基总量：超阈值。')
                exchangeableSaltTar.append('交换性盐总量。')
            else:
                exchangeableSalt.append('')
                exchangeableSaltTar.append('')
            # （4）交换性钙在[1, 25]范围之外的，存疑；
            if row['交换性钙Cmol(1/2Ca2+)/kg'] < 1 or row['交换性钙Cmol(1/2Ca2+)/kg'] > 25:
                exchangeableCa.append('交换性钙：交换性钙超阈值。')
                exchangeableCaTar.append('交换性钙。')
            else:
                exchangeableCa.append('')
                exchangeableCaTar.append('')
            # （5）交换性镁在[0.5, 12.5]范围之外的，存疑；
            if row['交换性镁cmol(1/2Mg2+)/kg'] < 0.5 or row['交换性镁cmol(1/2Mg2+)/kg'] > 12.8:
                exchangeableMg.append('交换性镁：超阈值。')
                exchangeableMgTar.append('交换性镁。')
            else:
                exchangeableMg.append('')
                exchangeableMgTar.append('')
            # （6）交换性钾在[0.1, 1.5]范围之外的，存疑；
            if row['交换性钾Cmol(+)/kg'] < 0.1 or row['交换性钾Cmol(+)/kg'] > 1.5:
                exchangeableK.append('交换性钾：超阈值。')
                exchangeableKTar.append('交换性钾。')
            else:
                exchangeableK.append('')
                exchangeableKTar.append('')
            # （7）交换性钠在[0.3, 1.9]范围之外的，存疑；
            if row['交换性钠cmol(+)/kg'] < 0.3 or row['交换性钠cmol(+)/kg'] > 1.9:
                exchangeableNa.append('交换性钠：超阈值。')
                exchangeableNaTar.append('交换性钠。')
            else:
                exchangeableNa.append('')
                exchangeableNaTar.append('')
            # （8）pH＜7.5，阳离子交换量＞交换性盐总量＞四大离子之和，且盐基饱和度小于100 %；违反则存疑；pH≥7.5，交换性盐总量 = 四大离子之和，盐基饱和度范围在80~120 %；违反则存疑；
            if ((not pd.isna(row['pH']) and row['pH']<7.5 and (row['阳离子交换量Cmol(+)/kg']>row['交换性盐总量Cmol(+)/kg']) and (row['交换性盐总量Cmol(+)/kg']>row['四大离子之和']) and row['盐基饱和度%']*100 <100 ) or
                ((not pd.isna(row['pH']) and row['pH']>=7.5 and row['交换性盐总量Cmol(+)/kg']==row['四大离子之和'] and (row['盐基饱和度%']*100 <120 and row['盐基饱和度%']*100 >80 )))or
                ((not pd.isna(row['pH']) and row['pH']<6 and row['盐基饱和度%'] >80))
            ):
                summaryList.append('')
                summaryListTar.append('')
            else:
                summaryList.append('存疑：ph值、阳离子交换量、交换性盐总量、离子总和、盐基饱和度之间关系存疑。')
                summaryListTar.append('盐基饱和度。')
            soilRes = soilTypeValue(row)
            soilTypeList.append(soilRes)
        # print('pd.Series(phList)',pd.Series(phList) + pd.Series(cationList)+pd.Series(exchangeableSalt))
        # print('pd.Series(cationList)', pd.Series(exchangeableSalt))
        # print('res---',  pd.Series(phList)+pd.Series(cationList)+pd.Series(exchangeableSalt)+pd.Series(exchangeableCa)+pd.Series(exchangeableMg)+pd.Series(exchangeableK)+pd.Series(exchangeableNa)+pd.Series(summaryList))
        checkData = pd.DataFrame({
            '审核结果': pd.Series(phList)+pd.Series(cationList)+pd.Series(exchangeableSalt)+pd.Series(exchangeableCa)+pd.Series(exchangeableMg)+pd.Series(exchangeableK)+pd.Series(exchangeableNa)+pd.Series(summaryList)+pd.Series(soilTypeList)+pd.Series(waterMount),
            '异常指标': pd.Series(phTar)+pd.Series(cationTar)+pd.Series(exchangeableSaltTar)+pd.Series(exchangeableCaTar)+pd.Series(exchangeableMgTar)+pd.Series(exchangeableKTar)+pd.Series(exchangeableNaTar)+pd.Series(summaryListTar)+pd.Series(waterMountTar)
        })
        return checkData
    except Exception as err:
        print('阳离子量判断出错！请检查cation_value中判断阳离子量内容', err)

# 表8  8大离子基础数据判断
def eight_ion_coun(arr, summary):
    try:
        allArr = [] # 存储水溶性盐总量存疑数据
        conductivity = [] # 存储电导率存疑数据
        naArr = [] # 存储钠离子存疑数据
        kArr = []  # 存储钾离子存疑数据
        caArr = []  # 存储钙离子存疑数据
        mgArr = []  # 存储镁离子存疑数据
        coArr = []  # 存储碳酸根离子存疑数据
        cohArr = []  # 存储碳酸氢根离子存疑数据
        soArr = [] # 存储硫酸根离子存疑数据
        clArr = [] # 氯离子存疑数据
        totalCom = [] # 全盐量小于八大离子和存疑数据
        phCoArr = [] # ph 碳酸根存疑数据
        changeComArr = [] #交换性离子高于水溶性离子存疑数据
        rateArr = [] # (水溶性全盐量-八大离子加和)/八大离子加和×100 存疑数据
        subtractionArr=[] #阳离子-阴离子 不在范围内
        # 存放异常指标
        allArrTar = []  # 存储水溶性盐总量异常指标
        conductivityTar = []  # 存储电导率异常指标
        naArrTar = []  # 存储钠离子异常指标
        kArrTar = []  # 存储钾离子异常指标
        caArrTar = []  # 存储钙离子异常指标
        mgArrTar = []  # 存储镁离子异常指标
        coArrTar = []  # 存储碳酸根离子异常指标
        cohArrTar = []  # 存储碳酸氢根离子异常指标
        soArrTar = []  # 存储硫酸根离子异常指标
        clArrTar = []  # 氯离子异常指标
        totalComTar = []  # 全盐量小于八大离子和异常指标
        phCoArrTar = []  # ph 碳酸根异常指标
        changeComArrTar = []  # 交换性离子高于水溶性离子异常指标
        rateArrTar = []  # (水溶性全盐量-八大离子加和)/八大离子加和×100 异常指标
        subtractionArrTar = []  # 阳离子-阴离子 异常指标
        #（2）水溶性盐总量在[0.1, 2]范围之外的，存疑；
        for index, row in arr.iterrows():
            if (not pd.isna(row['水溶性全盐量g/kg']) and row['水溶性全盐量g/kg'] < 0.1) or (not pd.isna(row['水溶性全盐量g/kg']) and row['水溶性全盐量g/kg'] > 2):
                allArr.append('全盐量：超阈值。')
                allArrTar.append('全盐量。')
            else:
                allArr.append('')
                allArrTar.append('')
            #（3）电导率在[0.01, 2]范围之外的，存疑；
            if ( not pd.isna(row['电导率ms/cm']) and row['电导率ms/cm'] < 0.01) or (not pd.isna(row['电导率ms/cm']) and row['电导率ms/cm'] > 2):
                conductivity.append('电导率：超阈值。')
                conductivityTar.append('电导率。')
            else:
                conductivity.append('')
                conductivityTar.append('')
            #（4）水溶性钠在[0.05, 0.5]范围之外的，存疑；
            if (not pd.isna(row['水溶性钠离子含量Cmol(Na+)/kg']) and row['水溶性钠离子含量Cmol(Na+)/kg'] <0.05) or (pd.isna(row['水溶性钠离子含量Cmol(Na+)/kg']) and row['水溶性钠离子含量Cmol(Na+)/kg'] > 0.5):
                naArr.append('水溶性钠离子：水溶性钠离子超阈值。')
                naArrTar.append('水溶性钠离子。')
            else:
                naArr.append('')
                naArrTar.append('')
            #（5）水溶性钾在[0.01, 0.5]范围之外的，存疑；
            if (not pd.isna(row['水溶性钾离子含量Cmol(K+)/kg']) and row['水溶性钾离子含量Cmol(K+)/kg'] <0.01) or ( not pd.isna(row['水溶性钾离子含量Cmol(K+)/kg']) and row['水溶性钾离子含量Cmol(K+)/kg'] > 0.5):
                kArr.append('水溶性钾离子：超阈值。')
                kArrTar.append('水溶性钾离子。')
            else:
                kArr.append('')
                kArrTar.append('')
            #（6）水溶性钙在[0.25, 5]范围之外的，存疑；
            if (not pd.isna(row['水溶性钙离子含量cmol(1/2Ca2+)/kg']) and row['水溶性钙离子含量cmol(1/2Ca2+)/kg'] <0.25) or (not pd.isna(row['水溶性钙离子含量cmol(1/2Ca2+)/kg']) and row['水溶性钙离子含量cmol(1/2Ca2+)/kg'] > 0.5):
                caArr.append('水溶性钙离子：超阈值。')
                caArrTar.append('水溶性钙离子。')
            else:
                caArr.append('')
                caArrTar.append('')
            #（7）水溶性镁在[0.125, 2.5]范围之外的，存疑；
            if (not pd.isna(row['水溶性镁离子Cmol(1/2Mg2+)/kg']) and row['水溶性镁离子Cmol(1/2Mg2+)/kg'] <0.125) or  (not pd.isna(row['水溶性镁离子Cmol(1/2Mg2+)/kg']) and row['水溶性镁离子Cmol(1/2Mg2+)/kg'] > 2.5):
                mgArr.append('水溶性镁离子：超阈值。')
                mgArrTar.append('水溶性镁离子。')
            else:
                mgArr.append('')
                mgArrTar.append('')
            #（8）水溶性碳酸根在[0.01, 2.5]范围之外的，存疑；
            if (not pd.isna(row['水溶性碳酸根离子含量cmol(1/2CO32+)/kg']) and row['水溶性碳酸根离子含量cmol(1/2CO32+)/kg'] <0.01) or (not pd.isna(row['水溶性碳酸根离子含量cmol(1/2CO32+)/kg']) and row['水溶性碳酸根离子含量cmol(1/2CO32+)/kg'] > 2.5):
                coArr.append('水溶性碳酸根：超阈值。')
                coArrTar.append('水溶性碳酸根。')
            else:
                coArr.append('')
                coArrTar.append('')
            #（9）水溶性碳酸氢根在[0.05, 5]范围之外的，存疑；
            if (not pd.isna(row['水溶性碳酸氢离子含量cmol(1/2HCO3-)/kg']) and row['水溶性碳酸氢离子含量cmol(1/2HCO3-)/kg'] <0.05) or (not pd.isna(row['水溶性碳酸氢离子含量cmol(1/2HCO3-)/kg']) and row['水溶性碳酸氢离子含量cmol(1/2HCO3-)/kg'] > 5):
                cohArr.append('水溶性碳酸氢根：超阈值。')
                cohArrTar.append('水溶性碳酸氢根。')
            else:
                cohArr.append('')
                cohArrTar.append('')
            #（10）水溶性硫酸根在[0.25, 2.5]范围之外的，存疑；
            if (not pd.isna(row['水溶性硫酸根离子含量cmol(1/2SO42-)/kg']) and row['水溶性硫酸根离子含量cmol(1/2SO42-)/kg'] <0.25) or (not pd.isna(row['水溶性硫酸根离子含量cmol(1/2SO42-)/kg']) and row['水溶性硫酸根离子含量cmol(1/2SO42-)/kg'] > 2.5):
                soArr.append('水溶性硫酸根：超阈值。')
                soArrTar.append('水溶性硫酸根。')
            else:
                soArr.append('')
                soArrTar.append('')
            #（11）水溶性氯根在[0.5, 5]范围之外的，存疑；
            if (not pd.isna(row['水溶性氯离子含量cmol(Cl-)/kg']) and row['水溶性氯离子含量cmol(Cl-)/kg'] <0.5) or  (not pd.isna(row['水溶性氯离子含量cmol(Cl-)/kg']) and row['水溶性氯离子含量cmol(Cl-)/kg'] > 5):
                clArr.append('水溶性氯根：超阈值。')
                clArrTar.append('水溶性氯根。')
            else:
                clArr.append('')
                clArrTar.append('')
            #（12）水溶性盐总量大于等于八大离子之和，违背则存疑；土地利用类型为菜地的，可能不符合这个规律；
            if (not pd.isna( row['水溶性全盐量g/kg']) and not pd.isna(row['八大离子加和g/kg']) and row['水溶性全盐量g/kg'] < row['八大离子加和g/kg']):
                totalCom.append('存疑：水溶性全盐量小于八大离子之和。')
                totalComTar.append('水溶性全盐量。')
            else:
                totalCom.append('')
                totalComTar.append('')

            #（13）水溶性八大离子换算为g / kg，如水溶性钠离子g / kg = 水溶性钠离子cmol(Na +) / kg×23g / mol×10 - 2； 这里在计算离子和时已转换
            #（14）pH＜8，碳酸根基本为0
            if row['pH'] <8 and not pd.isna(row['水溶性碳酸根离子含量cmol(1/2CO32+)/kg']) and row['水溶性碳酸根离子含量cmol(1/2CO32+)/kg'] != 0:
                phCoArr.append('水溶性碳酸根：pH＜8水溶性碳酸根不为0。')
                phCoArrTar.append('水溶性碳酸根。')
            else:
                phCoArr.append('')
                phCoArrTar.append('')
            #（15）交换性四大盐离子均要高于水溶性四大盐离子（钙镁钾钠）
            naBool = not pd.isna(row['水溶性钠离子含量Cmol(Na+)/kg'])
            kBool = not pd.isna(row['水溶性钾离子含量Cmol(K+)/kg'])
            caBool = not pd.isna(row['水溶性钙离子含量cmol(1/2Ca2+)/kg'])
            mgBool = not pd.isna(row['水溶性镁离子Cmol(1/2Mg2+)/kg'])
            sumNa = not pd.isna(summary.loc[index,'交换性钠'])
            sumK = not pd.isna(summary.loc[index, '交换性钾'])
            sumCa = not pd.isna(summary.loc[index, '交换性钙'])
            sumMg = not pd.isna(summary.loc[index, '交换性镁'])
            if (naBool and sumNa and row['水溶性钠离子含量Cmol(Na+)/kg']>summary.loc[index,'交换性钠']) or (
                    kBool and sumK and row['水溶性钾离子含量Cmol(K+)/kg']>summary.loc[index,'交换性钾']) or (
                    caBool and sumCa and row['水溶性钙离子含量cmol(1/2Ca2+)/kg']>summary.loc[index,'交换性钙']) or (
                   mgBool and sumMg and row['水溶性镁离子Cmol(1/2Mg2+)/kg']>summary.loc[index,'交换性镁']):
                changeComArr.append('存疑：交换性盐基总量低于于水溶性盐离子。')
            else:
                changeComArr.append('')
            #（16）(水溶性全盐量 - 八大离子加和) / 八大离子加和 * 100，不超过±20 %
            if not pd.isna(row['(水溶性全盐量-八大离子加和)/水溶性全盐量×100']) and (row['(水溶性全盐量-八大离子加和)/水溶性全盐量×100'] < -0.2 or row['(水溶性全盐量-八大离子加和)/水溶性全盐量×100'] > 0.2) :
                rateArr.append('存疑：(水溶性全盐量-八大离子加和)/水溶性全盐量×100超阈值。')
            else:
                rateArr.append('')
            #（17）阳离子总量 - 阴离子总量应基本相等，超过±1则提示异常
            if not pd.isna(row['阳离子总量-阴离子总量']) and (row['阳离子总量-阴离子总量'] < -1 or row['阳离子总量-阴离子总量'] > 1) :
                subtractionArr.append('存疑：阳离子总量 - 阴离子总量超阈值。')
            else:
                subtractionArr.append('')
        resData = pd.DataFrame({
            '审核结果': pd.Series(allArr) + pd.Series(conductivity) + pd.Series(naArr) +
                pd.Series(kArr) + pd.Series(caArr) + pd.Series(mgArr) + pd.Series(coArr) + pd.Series(
                    cohArr) + pd.Series(soArr) + pd.Series(clArr) + pd.Series(totalCom) + pd.Series(
                    phCoArr) + pd.Series(changeComArr) + pd.Series(rateArr) + pd.Series(subtractionArr),
            '异常指标': pd.Series(allArrTar) + pd.Series(conductivityTar) + pd.Series(naArrTar) +
                pd.Series(kArrTar) + pd.Series(caArrTar) + pd.Series(mgArrTar) + pd.Series(coArrTar) + pd.Series(
                    cohArrTar) + pd.Series(soArrTar) + pd.Series(clArrTar) + pd.Series(totalComTar) + pd.Series(
                    phCoArrTar)
        })
        return resData
    except Exception as err:
        print('八大离子判断出错！请检查eight_ion_coun中判断离子内容', err)

# 表10 有机质、全氮、全磷、全钾数据
def nutrient_data(arr):
    try:
        organicMatter = [] # 有机质存疑数据
        NArr = [] # 全氮存疑数据
        PArr = [] # 全磷存疑数据
        KArr = [] # 全钾存疑数据
        availableP = [] # 有效磷存疑数据
        availablek = [] #速效钾存疑数据
        slowlyK= [] #缓效钾存疑数据
        organicRate = [] #有机质 / 全氮比值存疑数据
        availablePCom = [] #有效磷＜3和大于60，提示异常；
        availableTxt = [] # 速效钾＜50提示异常
        availablekCom = [] # 速效钾＞缓效钾 存疑数据
        sKErr = []  # 保存交换性钾不等于速效钾
        # 异常指标
        organicMatterTar = []  # 有机质异常指标
        NArrTar = []  # 全氮异常指标
        PArrTar = []  # 全磷异常指标
        KArrTar = []  # 全钾异常指标
        availablePTar = []  # 有效磷异常指标
        availablekTar = []  # 速效钾异常指标
        slowlyKTar = []  # 缓效钾异常指标
        sKErrTar = []  # 保存交换性钾不等于速效钾
        for index, row in arr.iterrows():
            # 交换性钾 == 速效钾
            if not pd.isna(row['速效钾mg/kg']) and not pd.isna(row['交换性钾']) and ((row['速效钾mg/kg'] - row['交换性钾']*391)/ (row['交换性钾']*391) > 0.2 or (row['速效钾mg/kg'] - row['交换性钾']*391)/ (row['交换性钾']*391) < -0.2):
                sKErr.append('存疑：交换性钾和速效钾误差超20%。')
                sKErrTar.append('交换性钾、速效钾。')
            else:
                sKErr.append('')
                sKErrTar.append('')
            #（2）有机质在[2, 50]范围之外的，存疑；有机质＜5提示异常；
            if row['有机质g/kg'] < 2 or row['有机质g/kg'] >50:
                organicMatter.append('有机质：超阈值。')
                organicMatterTar.append('有机质。')
            else:
                organicMatter.append('')
                organicMatterTar.append('')
            #（3）全氮在[0.1, 2.5]范围之外的；存疑；
            if row['全氮g/kg'] < 0.1 or row['全氮g/kg'] > 2.5:
                NArr.append('全氮：超阈值。')
                NArrTar.append('全氮。')
            else:
                NArr.append('')
                NArrTar.append('')
            #（4）全磷在[0.18, 1.5]范围之外的；存疑；
            if row['全磷g/kg'] < 0.18 or row['全磷g/kg'] > 1.5:
                PArr.append('全磷：超阈值。')
                PArrTar.append('全磷。')
            else:
                PArr.append('')
                PArrTar.append('')
            #（5）全钾在[10, 29]范围之外的；存疑；
            if row['全钾g/kg'] < 10 or row['全钾g/kg'] > 29:
                KArr.append('全钾：超阈值。')
                KArrTar.append('全钾。')
            else:
                KArr.append('')
                KArrTar.append('')
            #（6）有效磷在[1, 80]范围之外的；存疑； 耕地 超过80存疑
            if (row['pH'] >= 6.5 and (row['有效磷g/kg'] < 3 or row['有效磷g/kg'] > 60)) or (row['pH'] < 6.5 and (row['有效磷g/kg'] <1 or row['有效磷g/kg'] > 80) or (row['编号'][6:10] == '0101' or row['编号'][6:10] == '0102' or row['编号'][6:10] == '0103') and row['有效磷g/kg'] > 60) :
                availableP.append('有效磷：超阈值。')
                availablePTar.append('有效磷。')
            else:
                availableP.append('')
                availablePTar.append('')
            # if row['有效磷g/kg'] < 1 or row['有效磷g/kg'] > 60 or ((row['编号'][6:11] == '0101' or row['编号'][6:11] == '0102' or row['编号'][6:11] == '0103') and row['有效磷g/kg'] > 60):
            #     availableP.append('存疑：有效磷超阈值。')
            # else:
            #     availableP.append('')
            #（7）速效钾在[30, 300] 范围之外的；存疑； 耕地超过300存疑
            if row['速效钾mg/kg'] < 30 or row['速效钾mg/kg'] > 300 or ((row['编号'][6:10] == '0101' or row['编号'][6:10] == '0102' or row['编号'][6:10] == '0103') and row['速效钾mg/kg'] > 300):
                availablek.append('速效钾：超阈值。')
                availablekTar.append('速效钾。')
            else:
                availablek.append('')
                availablekTar.append('')
            #（8）缓效钾在[100, 2000]范围之外的；存疑；
            if row['缓效钾mg/kg'] < 100 or row['缓效钾mg/kg'] > 2000:
                slowlyK.append('缓效钾：超阈值。')
                slowlyKTar.append('缓效钾。')
            else:
                slowlyK.append('')
                slowlyKTar.append('')
            #（9）有机质 / 全氮比值≥20和≤13，提示存疑
            if row['有机质g/kg']/row['全氮g/kg'] >=20 or row['有机质g/kg']/row['全氮g/kg'] <=13 :
                organicRate.append('存疑：有机质/全氮比值超阈值。')
            else:
                organicRate.append('')
            #（10）有机质、全氮含量异常高，但速效养分特低，提示异常   无法量化不处理
            #（11）母岩为片麻岩，但全钾、速效缓效钾含量低，提示异常   无法量化不处理
            #（12）有效磷＜3和大于60，提示异常；速效钾＜50提示异常
            if row['有效磷g/kg'] < 3 or row['有效磷g/kg'] > 60:
                availablePCom.append('有效磷：超阈值。')
            else:
                availablePCom.append('')
            if row['速效钾mg/kg'] < 50:
                availableTxt.append('速效钾：超阈值。')
            else:
                availableTxt.append('')
            #（13）速效钾＞缓效钾，提示异常
            if row['速效钾mg/kg'] > row['缓效钾mg/kg']:
                availablekCom.append('异常：速效钾大于缓效钾。')
            else:
                availablekCom.append('')
        resData = pd.DataFrame({
            '审核结果': pd.Series(organicMatter) + pd.Series(NArr) + pd.Series(PArr) +
                pd.Series(KArr) + pd.Series(availableP) + pd.Series(availablek) + pd.Series(slowlyK) + pd.Series(
                    organicRate) + pd.Series(availablePCom) + pd.Series(availablekCom) + pd.Series(availableTxt) + pd.Series(availablePCom) + pd.Series(sKErr),
            '异常指标': pd.Series(organicMatterTar) + pd.Series(NArrTar) + pd.Series(PArrTar) +
                pd.Series(KArrTar) + pd.Series(availablePTar) + pd.Series(availablekTar) + pd.Series(slowlyKTar) + pd.Series(sKErrTar)
        })
        return resData
    except Exception as err:
        print('有机质、全氮、全磷、全钾数据判断出错！请检查nutrient_data中判断内容', err)


# 表12 土壤指标判断规则
def soil_metal(arr):
    try:
        effectiveL = []  # 有效硫存疑数据
        effectiveG = []  # 有效硅存疑数据
        effectiveT = []  # 有效铁存疑数据
        effectiveM = []  # 有效锰存疑数据
        effectiveCu = []  # 有效铜存疑数据
        effectiveX = []  # 有效锌存疑数据
        effectiveP = []  # 有效硼存疑数据
        effectiveMu = []  # 有效钼存疑数据
        # 存疑指标
        effectiveLTar = []  # 有效硫
        effectiveGTar = []  # 有效硅
        effectiveTTar = []  # 有效铁
        effectiveMTar = []  # 有效锰
        effectiveCuTar = []  # 有效铜
        effectiveXTar = []  # 有效锌
        effectivePTar = []  # 有效硼
        effectiveMutar = []  # 有效钼
        for index, row in arr.iterrows():
            #（1）有效硫在[2, 60]范围之外的，存疑；
            if (not pd.isna(row['有效硫mg/kg']) and row['有效硫mg/kg'] <2) or (not pd.isna(row['有效硫mg/kg']) and row['有效硫mg/kg'] >60):
                effectiveL.append('有效硫：超阈值。')
                effectiveLTar.append('有效硫。')
            else:
                effectiveL.append('')
                effectiveLTar.append('')
            #（2）有效硅在[10, 500]范围之外的，存疑；
            if ( not pd.isna(row['有效硅mg/kg']) and row['有效硅mg/kg'] <10) or (not pd.isna(row['有效硅mg/kg']) and row['有效硅mg/kg'] >500):
                effectiveG.append('有效硅：超阈值。')
                effectiveGTar.append('有效硅。')
            else:
                effectiveG.append('')
                effectiveGTar.append('')
            #（3）有效铁在[5, 300]范围之外的，存疑；
            if ( not pd.isna(row['有效铁mg/kg']) and row['有效铁mg/kg'] <5) or (not pd.isna(row['有效铁mg/kg']) and row['有效铁mg/kg'] >300):
                effectiveT.append('有效铁：超阈值。')
                effectiveTTar.append('有效铁。')
            else:
                effectiveT.append('')
                effectiveTTar.append('')
            #（4）有效锰在[5, 200]范围之外的，存疑；
            if (not pd.isna(row['有效锰mg/kg']) and row['有效锰mg/kg'] <5) or (not pd.isna(row['有效锰mg/kg']) and row['有效锰mg/kg'] >200) :
                effectiveM.append('有效锰：超阈值。')
                effectiveMTar.append('有效锰。')
            else:
                effectiveM.append('')
                effectiveMTar.append('')
            #（5）有效铜在[0.1, 8]范围之外的，存疑；
            if ( not pd.isna(row['有效铜mg/kg']) and row['有效铜mg/kg'] <0.1) or (not pd.isna(row['有效铜mg/kg']) and row['有效铜mg/kg'] >8):
                effectiveCu.append('有效铜：超阈值。')
                effectiveCuTar.append('有效铜。')
            else:
                effectiveCu.append('')
                effectiveCuTar.append('')

            #（6）有效锌在[0.1, 10] 范围之外的，存疑；
            if (not pd.isna(row['有效锌mg/kg']) and row['有效锌mg/kg'] <0.1) or (not pd.isna(row['有效锌mg/kg']) and row['有效锌mg/kg'] >10):
                effectiveX.append('有效锌：超阈值。')
                effectiveXTar.append('有效锌。')
            else:
                effectiveX.append('')
                effectiveXTar.append('')
            #（7）有效硼在[0.1, 2] 范围之外的，存疑；
            if (not pd.isna(row['有效硼mg/kg']) and row['有效硼mg/kg'] <0.1) or (not pd.isna(row['有效硼mg/kg']) and row['有效硼mg/kg'] >2):
                effectiveP.append('有效硼：超阈值。')
                effectivePTar.append('有效硼。')
            else:
                effectiveP.append('')
                effectivePTar.append('')
            #（8）有效钼在[0.03, 1]范围之外的，存疑。
            if (not pd.isna(row['有效钼mg/kg']) and row['有效钼mg/kg'] <0.03) or (not pd.isna(row['有效钼mg/kg']) and row['有效钼mg/kg'] >1):
                effectiveMu.append('有效钼：超阈值。')
                effectiveMutar.append('有效钼。')
            else:
                effectiveMu.append('')
                effectiveMutar.append('')
        resData = pd.DataFrame({
            '审核结果': pd.Series(effectiveL) + pd.Series(effectiveT) + pd.Series(effectiveG) +
                pd.Series(effectiveM) + pd.Series(effectiveCu) + pd.Series(effectiveX) + pd.Series(effectiveP) + pd.Series(
                    effectiveMu),
            '异常指标': pd.Series(effectiveLTar) + pd.Series(effectiveTTar) + pd.Series(effectiveGTar) +
                pd.Series(effectiveMTar) + pd.Series(effectiveCuTar) + pd.Series(effectiveXTar) + pd.Series(effectivePTar) + pd.Series(
                    effectiveMutar)
        })
        return resData
    except Exception as err:
        print('土壤指标数据判断出错！请检查soil_metal中判断内容', err)

# 表14  土壤重金属判断
# 这里风险值和管控值判断 单独写两个函数
# 风险值
def risk_value(arr):
    unnormalValue = []

    for index, row in arr.iterrows():
        str = ''
        if row['编号'][6:10] == '0101': # 水田
            # 镉
            if (row['pH'] <= 5.5 and row['镉mg/kg'] > 0.3) or (
                    row['pH'] > 5.5 and row['pH']<= 6.5 and row['镉mg/kg'] > 0.4) or (
                    row['pH'] >6.5 and row['pH'] <=7.5 and row['镉mg/kg'] > 0.6) or (
                    row['pH'] > 7.5 and row['镉mg/kg'] > 0.8):
                str += '镉超污染风险值筛选值。'
            # 汞
            if (row['pH'] <= 5.5 and row['汞mg/kg'] > 0.5) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['汞mg/kg'] > 0.5) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['汞mg/kg'] > 0.6) or (
                    row['pH'] > 7.5 and row['汞mg/kg'] > 1):
                str += '汞超污染风险值筛选值。'
            # 砷
            if (row['pH'] <= 5.5 and row['砷mg/kg'] >30) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['砷mg/kg'] > 30) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['砷mg/kg'] > 25) or (
                    row['pH'] > 7.5 and row['砷mg/kg'] > 20):
                str += '砷超污染风险值筛选值。'
            # 铅
            if (row['pH'] <= 5.5 and row['铅mg/kg'] > 80) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['铅mg/kg'] > 100) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['铅mg/kg'] > 140) or (
                    row['pH'] > 7.5 and row['铅mg/kg'] > 240):
                str += '铅超污染风险值筛选值。'
            # 铬
            if (row['pH'] <= 5.5 and row['铬mg/kg'] > 250) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['铬mg/kg'] > 250) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['铬mg/kg'] > 300) or (
                    row['pH'] > 7.5 and row['铬mg/kg'] >350):
                str += '铬超污染风险值筛选值。'
        else:
            # 镉
            if (row['pH'] <= 5.5 and row['镉mg/kg'] > 0.3) or (
                    row['pH'] > 5.5 and row['pH']<= 6.5 and row['镉mg/kg'] > 0.3) or (
                    row['pH'] >6.5 and row['pH'] <=7.5 and row['镉mg/kg'] > 0.3) or (
                    row['pH'] > 7.5 and row['镉mg/kg'] > 0.6):
                str += '镉超污染风险值筛选值。'
            # 汞
            if (row['pH'] <= 5.5 and row['汞mg/kg'] > 1.3) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['汞mg/kg'] > 1.8) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['汞mg/kg'] > 2.4) or (
                    row['pH'] > 7.5 and row['汞mg/kg'] > 3.4):
                str += '汞超污染风险值筛选值。'
            # 砷
            if (row['pH'] <= 5.5 and row['砷mg/kg'] > 40) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['砷mg/kg'] > 40) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['砷mg/kg'] > 30) or (
                    row['pH'] > 7.5 and row['砷mg/kg'] > 25):
                str += '砷超污染风险值筛选值。'
            # 铅
            if (row['pH'] <= 5.5 and row['铅mg/kg'] > 70) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['铅mg/kg'] > 90) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['铅mg/kg'] > 120) or (
                    row['pH'] > 7.5 and row['铅mg/kg'] > 170):
                str += '铅超污染风险值筛选值。'
            # 铬
            if (row['pH'] <= 5.5 and row['铬mg/kg'] > 150) or (
                    row['pH'] > 5.5 and row['pH'] <= 6.5 and row['铬mg/kg'] > 150) or (
                    row['pH'] > 6.5 and row['pH'] <= 7.5 and row['铬mg/kg'] > 200) or (
                    row['pH'] > 7.5 and row['铬mg/kg'] > 250):
                str += '铬超污染风险值筛选值。'

        if (row['pH'] <= 5.5 and row['镍mg/kg'] > 60) or (
                row['pH'] > 5.5 and row['pH'] <= 6.5 and row['镍mg/kg'] > 70) or (
                row['pH'] > 6.5 and row['pH'] <= 7.5 and row['镍mg/kg'] > 100) or (
                row['pH'] > 7.5 and row['镍mg/kg'] > 190):
            str += '镍超污染风险值筛选值。'
        unnormalValue.append(str)
    return unnormalValue

# 管制值
def control_value(arr):
    unnormalValue = []
    for index, row in arr.iterrows():
        str = ''
        # 镉
        if (row['pH'] <= 5.5 and row['镉mg/kg'] > 1.5) or (
                row['pH'] > 5.5 and row['pH'] <= 6.5 and row['镉mg/kg'] > 2) or (
                row['pH'] > 6.5 and row['pH'] <= 7.5 and row['镉mg/kg'] > 3) or (
                row['pH'] > 7.5 and row['镉mg/kg'] > 4):
            str += '镉超污染风险值管制值。'
        # 汞
        if (row['pH'] <= 5.5 and row['汞mg/kg'] > 2) or (
                row['pH'] > 5.5 and row['pH'] <= 6.5 and row['汞mg/kg'] > 2.5) or (
                row['pH'] > 6.5 and row['pH'] <= 7.5 and row['汞mg/kg'] > 4) or (
                row['pH'] > 7.5 and row['汞mg/kg'] > 6):
            str += '汞超污染风险值管制值。'
        # 砷
        if (row['pH'] <= 5.5 and row['砷mg/kg'] > 200) or (
                row['pH'] > 5.5 and row['pH'] <= 6.5 and row['砷mg/kg'] > 150) or (
                row['pH'] > 6.5 and row['pH'] <= 7.5 and row['砷mg/kg'] > 120) or (
                row['pH'] > 7.5 and row['砷mg/kg'] > 100):
            str += '砷超污染风险值管制值。'
        # 铅
        if (row['pH'] <= 5.5 and row['铅mg/kg'] >400) or (
                row['pH'] > 5.5 and row['pH'] <= 6.5 and row['铅mg/kg'] > 500) or (
                row['pH'] > 6.5 and row['pH'] <= 7.5 and row['铅mg/kg'] > 700) or (
                row['pH'] > 7.5 and row['铅mg/kg'] > 1000):
            str += '铅超污染风险值管制值。'
        # 铬
        if (row['pH'] <= 5.5 and row['铬mg/kg'] > 800) or (
                row['pH'] > 5.5 and row['pH'] <= 6.5 and row['铬mg/kg'] > 850) or (
                row['pH'] > 6.5 and row['pH'] <= 7.5 and row['铬mg/kg'] > 1000) or (
                row['pH'] > 7.5 and row['铬mg/kg'] > 1300):
            str += '铬超污染风险值管制值。'
        unnormalValue.append(str)
    return unnormalValue
def last_metal(arr):
    try:
        totalGe = []  # 总镉在范围之外
        totalGo = []  # 总汞在范围之外
        totalShen = []  # 总砷在范围之外
        totalPb = []  # 总铅在范围之外
        totalG = []  # 总铬在范围之外
        totalN = []  # 总镍在范围之外
        # 异常指标
        totalGeTar = []  # 总镉
        totalGoTar = []  # 总汞
        totalShenTar = []  # 总砷
        totalPbTar = []  # 总铅
        totalGTar = []  # 总铬
        totalNTar = []  # 总镍
        # （1）超过风险筛选值，提示关注
        overValue = risk_value(arr) # 超过风险筛选值
        # （2）超过风险管控值，提示重点关注
        overLimit = control_value(arr)  # 超过风险管控值
        for index, row in arr.iterrows():
            # （3）总镉在[0.03, 0.3]范围之外的，存疑
            if row['镉mg/kg'] < 0.03 or row['镉mg/kg'] > 0.3:
                totalGe.append('总镉：超阈值。')
                totalGeTar.append('总镉。')
            else:
                totalGe.append('')
                totalGeTar.append('')
            # （4）总汞在[0.01, 0.3]范围之外的，存疑
            if row['汞mg/kg'] < 0.01 or row['汞mg/kg'] > 0.3:
                totalGo.append('总汞：超阈值。')
                totalGoTar.append('总汞。')
            else:
                totalGo.append('')
                totalGoTar.append('')
            # （5）总砷在[0.5, 30]范围之外的，存疑
            if row['砷mg/kg'] < 0.5 or row['砷mg/kg'] > 30:
                totalShen.append('总砷：超阈值。')
                totalShenTar.append('总砷。')
            else:
                totalShen.append('')
                totalShenTar.append('')
            # （6）总铅在[2, 100]范围之外的，存疑
            if row['铅mg/kg'] < 2 or row['铅mg/kg'] > 100:
                totalPb.append('总铅：超阈值。')
                totalPbTar.append('总铅。')
            else:
                totalPb.append('')
                totalPbTar.append('')
            # （7）总铬在[0.4, 200]范围之外的，存疑
            if row['铬mg/kg'] < 0.4 or row['铬mg/kg'] > 200:
                totalG.append('总铬：超阈值。')
                totalGTar.append('总铬。')
            else:
                totalG.append('')
                totalGTar.append('')
            # （8）总镍在[0.3, 100]范围之外的，存疑
            if row['镍mg/kg'] < 0.3 or row['镍mg/kg'] > 100:
                totalN.append('总镍：超阈值。')
                totalNTar.append('总镍。')
            else:
                totalN.append('')
                totalNTar.append('')
        resData = pd.DataFrame({
            '审核结果': pd.Series(overValue) + pd.Series(overLimit) + pd.Series(totalGe) +
                pd.Series(totalGo) + pd.Series(totalShen) + pd.Series(totalPb) + pd.Series(
                    totalG) + pd.Series(totalN),
            '异常指标': pd.Series(totalGeTar) + pd.Series(totalGoTar) + pd.Series(totalShenTar) + pd.Series(totalPbTar) + pd.Series(
                    totalGTar) + pd.Series(totalNTar)
        })
        return resData
    except Exception as err:
        print('土壤重金属指标数据判断出错！请检查last_metal中判断内容', err)