用Python+Excel搞定湖泊水质评价：手把手教你实现TSI指数自动计算（附完整代码）

用Python+Excel实现湖泊水质TSI指数自动化分析全流程指南

湖泊水质监测是环境科学领域的核心工作之一，而TSI（Trophic State Index）指数作为评估水体富营养化程度的重要指标，其计算过程往往涉及大量重复性公式运算。传统手工计算不仅效率低下，还容易因人为失误导致结果偏差。本文将带你用两种技术路线——Python编程和Excel公式，构建一套完整的自动化分析工作流。

1. 理解TSI指数计算的核心逻辑

TSI指数通过总氮（TN）、总磷（TP）、叶绿素a（Chl-a）、化学需氧量（COD）和透明度（SD）五个参数的综合计算，反映水体的营养状态。其独特之处在于采用加权平均算法，各参数的权重由它们与叶绿素a的相关系数决定。

中国典型湖泊的参数相关系数经验值如下：

注意：当拥有本地湖泊的历史监测数据时，建议重新计算相关系数以获得更准确的权重分配

各参数TSI分项计算公式为：

# Python函数形式表达 import math def calculate_tsi_tn(tn): return 10 * (5.453 + 1.694 * math.log(tn)) def calculate_tsi_tp(tp): return 10 * (9.436 + 1.624 * math.log(tp)) def calculate_tsi_chla(chla): return 10 * (2.5 + 1.086 * math.log(chla)) def calculate_tsi_cod(cod): return 10 * (0.109 + 2.66 * math.log(cod)) def calculate_tsi_sd(sd): return 10 * (5.118 - 1.94 * math.log(sd))

2. Excel自动化方案：高级公式与条件格式

对于不熟悉编程的用户，Excel仍能实现高效的自动化计算。我们设计的工作表包含三个核心区域：数据输入区、计算区和结果可视化区。

2.1 数据输入表结构设计

创建如下结构的输入表格（示例为前5行）：

2.2 关键计算公式实现

在计算区设置以下公式（以第2行为例）：

TSI_TN: =10*(5.453 + 1.694*LN(D2)) TSI_TP: =10*(9.436 + 1.624*LN(E2)) TSI_Chla:=10*(2.5 + 1.086*LN(F2)) TSI_COD: =10*(0.109 + 2.66*LN(G2)) TSI_SD: =10*(5.118 - 1.94*LN(H2)) 加权TSI: =I2*0.2663 + J2*0.1879 + K2*0.179 + L2*0.1834 + M2*0.1834

2.3 智能结果可视化技巧

利用条件格式实现自动颜色标注：

1. 选择加权TSI结果列
2. 新建条件格式规则：
   • TSI<30：蓝色填充（寡营养）
   • 30≤TSI<50：绿色填充（中营养）
   • 50≤TSI<60：黄色填充（轻度富营养）
   • 60≤TSI<70：橙色填充（中度富营养）
   • TSI≥70：红色填充（高度富营养）

添加数据条格式可以直观显示富营养化程度梯度变化。

3. Python自动化方案：Pandas全流程实现

对于大批量数据处理，Python方案展现出明显优势。我们构建的脚本包含数据清洗、计算、分析和可视化完整功能。

3.1 数据预处理模块

import pandas as pd import numpy as np def load_and_clean_data(filepath): """加载并清洗原始监测数据""" df = pd.read_excel(filepath) # 处理缺失值 for col in ['TN', 'TP', 'Chla', 'COD', 'SD']: df[col] = df[col].replace(0, np.nan).fillna(df[col].mean()) # 单位统一化 df['TN'] = df['TN'] * 1000 # 转为μg/L return df

3.2 核心计算引擎

class TSI_Calculator: WEIGHTS = { 'Chla': 0.2663, 'TP': 0.1879, 'TN': 0.1790, 'SD': 0.1834, 'COD': 0.1834 } @staticmethod def calculate_tsi(df): """计算各分项TSI及加权综合TSI""" df['TSI_TN'] = 10 * (5.453 + 1.694 * np.log(df['TN'])) df['TSI_TP'] = 10 * (9.436 + 1.624 * np.log(df['TP'])) df['TSI_Chla'] = 10 * (2.5 + 1.086 * np.log(df['Chla'])) df['TSI_COD'] = 10 * (0.109 + 2.66 * np.log(df['COD'])) df['TSI_SD'] = 10 * (5.118 - 1.94 * np.log(df['SD'])) df['TSI_Weighted'] = (df['TSI_Chla'] * WEIGHTS['Chla'] + df['TSI_TP'] * WEIGHTS['TP'] + df['TSI_TN'] * WEIGHTS['TN'] + df['TSI_SD'] * WEIGHTS['SD'] + df['TSI_COD'] * WEIGHTS['COD']) return df

3.3 智能分析与可视化输出

import matplotlib.pyplot as plt def visualize_results(df): """生成专业级分析图表""" plt.figure(figsize=(12, 6)) # 各采样点TSI对比 plt.subplot(1, 2, 1) df.sort_values('TSI_Weighted').plot.bar( x='采样点', y='TSI_Weighted', color=df['TSI_Weighted'].apply( lambda x: '#1f77b4' if x<30 else '#2ca02c' if x<50 else '#ffd700' if x<60 else '#ff7f0e' if x<70 else '#d62728'), ax=plt.gca() ) plt.title('各采样点TSI指数对比') # 参数贡献度分析 plt.subplot(1, 2, 2) contributions = df[['TSI_TN','TSI_TP','TSI_Chla','TSI_COD','TSI_SD']].mean() contributions.plot.pie(autopct='%1.1f%%') plt.title('各参数对富营养化的贡献比例') plt.tight_layout() return plt.gcf()

4. 进阶应用：构建自动化报告系统

将上述技术整合为完整解决方案：

4.1 批处理脚本设计

def generate_report(input_path, output_dir): """全自动报告生成流程""" # 数据加载与计算 df = load_and_clean_data(input_path) df = TSI_Calculator.calculate_tsi(df) # 生成可视化图表 fig = visualize_results(df) fig.savefig(f'{output_dir}/tsi_analysis.png') # 输出Excel报告 with pd.ExcelWriter(f'{output_dir}/TSI_Report.xlsx') as writer: df.to_excel(writer, sheet_name='原始数据') # 添加分析结果页 summary = df.describe().loc[['mean','min','max']] summary.to_excel(writer, sheet_name='统计分析') print(f"报告已生成至 {output_dir} 目录")

4.2 典型应用场景示例

# 实际调用示例 if __name__ == '__main__': # 处理单个湖泊数据 generate_report('input/东湖监测数据.xlsx', 'output') # 批量处理多个湖泊 for lake in ['洞庭湖','鄱阳湖','太湖']: generate_report(f'input/{lake}_data.xlsx', f'output/{lake}')

提示：将此脚本部署为定期任务，可实现监测数据的自动周报/月报生成

5. 方案对比与选择建议

两种技术路线的适用场景对比：

选择建议：

• 科研人员：推荐Python方案，便于方法迭代和复杂分析
• 环保机构：可开发基于Python的Web应用供非技术人员使用
• 学生作业：Excel方案更易上手，适合课程小作业
• 长期监测项目：建议采用Python+Airflow构建自动化流水线