最近项目进入数据分析阶段,用python pandas包实现的,总是学了忘忘了学的,自己记录一下代码~
用了swmm-api的包,提取出来转成dataframe格式。主要操作类型包括行索引,列索引,插入行,列,修改值,将dataframe读出写入文件。接下来看代码!
from swmm_api import read_out_file, read_rpt_file
import pandas as pd
import os
os.chdir('E:\HANproject\SWMMproject')#更改工作路径
#从rpt文件中提取数据
tss = read_rpt_file('new_inputfile.rpt').subcatchment_washoff_summary
#提取出来已经是dataframe类型了,不知道数据结构用print(tss.columns)方法看看
#在矩阵末尾列插入全0行
tss.insert(tss.shape[1],'level',0)
#把索引提取出来放第一行方便后续写入文件查看
tss.insert(0,'subcatch',tss.index)
#对数据进行自然断点分析
import jenkspy
NB_ss = jenkspy.jenks_breaks(tss['TSS_kg'], n_classes=5)
#根据自然断点划分给数据分级
for i in range(0, tss.shape[0]):
if tss['TSS_kg'][i]>=NB_ss[-2]:
tss.loc[tss.index == tss.index[i],'level'] = 5
elif tss['TSS_kg'][i]<=NB_ss[-2] and tss['TSS_kg'][i]>=NB_ss[-3]:
tss.loc[tss.index == tss.index[i],'level'] = 4
elif tss['TSS_kg'][i]<=NB_ss[-3] and tss['TSS_kg'][i]>=NB_ss[-4]:
tss.loc[tss.index == tss.index[i],'level'] = 3
elif tss['TSS_kg'][i]<=NB_ss[-4] and tss['TSS_kg'][i]>=NB_ss[-5]:
tss.loc[tss.index == tss.index[i],'level'] = 2
elif tss['TSS_kg'][i]<=NB_ss[-5]:
tss.loc[tss.index == tss.index[i],'level'] = 1
#写入文件
import openpyxl
with pd.ExcelWriter('LIDlocatorOutput.xlsx', mode = 'a') as writer:
#mode = 'a'的意思是不覆盖原有sheet
#没有mode a就是将原有内容全部清空再进行写入
# mode a条件下,如果sheet_name与已有sheet重复则会报错
tss.to_excel(writer, sheet_name='tss', index=False)