pandas实践之表格整理

试验数据整理

目的

一个试验有不同的处理，每个处理又有不同小区，每个小区不同指标有不同重复
类似下图

这个利用Excel的透视表功能和vlookup函数结合就能实现对小区的统计分析，不过稍显麻烦
这里pandas涉及的主要操作有

• 数据的分组统计（涉及多元索引）

• 行列的计算

• 空值填充（需要注意的是python计算均值的时候会忽略空值）

• 分组后索引更改

• 透视表的制作（包括透视表的制作与逆向生成）

• 不同表之间的融合（类似VLOOKUP）

• 日期类型的转换

• 数据框多因素排序

• 多sheet表格生成

接下来上代码

"""
author: Shuai-jie Shen 沈帅杰
CSDN: https://blog.csdn.net/weixin_45452300
公众号: AgBioIT
"""
import pandas as pd


# 读入测产数据，计算实际密度，python3.7之后可以用中文做变量
测产 = pd.read_excel(r'E:\2020玉米整理.xlsx', sheet_name='测产')
株数 = 测产.groupby(by=['处理', '小区']).mean()  # 这里不要选日期
株数['密度'] = 株数['株数']/6  # 计算密度为10000/ha
# 读入考苗数据
考苗 = pd.read_excel(r'E:\2020玉米整理.xlsx', sheet_name='考苗')
考苗数据 = 考苗.groupby(by=['日期', '处理', '小区']).mean()  # 根据小区分组平均, 对于三株一块称的，后面空的两行的平均后还需要除以3
考苗整理 = pd.merge(考苗数据, 株数['密度'], how='outer', left_index=True, right_index=True) # 加入密度
# 计算指标
考苗整理['LAI'] = 考苗整理['叶面积']*考苗整理['密度']/10000
考苗整理['茎干重t/ha'] = 考苗整理['茎干重']/3*考苗整理['密度']/100
考苗整理['叶干重t/ha'] = 考苗整理['叶干重']/3*考苗整理['密度']/100
考苗整理['穗干重t/ha'] = 考苗整理['穗干重']/3*考苗整理['密度']/100
考苗整理['穗干重t/ha'].fillna(0, inplace=True)
考苗整理['籽粒干重t/ha'] = 考苗整理['籽粒干重']/3*考苗整理['密度']/100
考苗整理['籽粒干重t/ha'].fillna(0, inplace=True)
考苗整理['苞叶干重t/ha'] = 考苗整理['苞叶干重']/3*考苗整理['密度']/100
考苗整理['苞叶干重t/ha'].fillna(0, inplace=True)
考苗整理['穗轴干重t/ha'] = 考苗整理['穗轴干重']/3*考苗整理['密度']/100
考苗整理['穗轴干重t/ha'].fillna(0, inplace=True)
考苗整理['干物质总量t/ha'] = 考苗整理['茎干重t/ha']+考苗整理['叶干重t/ha']+考苗整理['穗干重t/ha']
# 分样的所有指标
分样整理 = 考苗整理[['密度','株高', 'LAI', '茎干重t/ha', '叶干重t/ha', '穗干重t/ha', '籽粒干重t/ha', '苞叶干重t/ha', '穗轴干重t/ha', '干物质总量t/ha']]
分样整理.reset_index(inplace=True)
# 读入全氮数据
全氮 = pd.read_excel(r'E:\2020年玉米全氮.xlsx', sheet_name='氮含量整理')
全氮表 = 全氮.groupby(by=['日期', '处理', '部位']).mean()
全氮表.reset_index(inplace=True)
# 将各个部位转换为列
全氮透视表 = 全氮表.pivot(index=['日期', '处理'], columns='部位', values='全氮g/kg')
全氮透视表.reset_index(inplace=True)
# melted=pd.melt(全氮透视表,['日期', '处理'])  # 恢复长表
合并氮 = pd.merge(分样整理, 全氮透视表, how='outer', left_on=['日期', '处理'], right_on=['日期', '处理'])
合并氮.fillna(0, inplace=True)
合并氮['茎氮积累kg/ha'] = 合并氮['茎干重t/ha']*合并氮['茎']
合并氮['叶氮积累kg/ha'] = 合并氮['叶干重t/ha']*合并氮['叶']
合并氮['穗氮积累kg/ha'] = 合并氮['穗干重t/ha']*合并氮['穗']+合并氮['穗轴干重t/ha']*合并氮['穗轴']+合并氮['籽粒干重t/ha']*合并氮['籽粒']+合并氮['苞叶干重t/ha']*合并氮['苞叶']
合并氮['总氮积累kg/ha'] = 合并氮['茎氮积累kg/ha']+合并氮['叶氮积累kg/ha']+合并氮['穗氮积累kg/ha']
考苗结果汇总 = 合并氮[['日期', '处理', '小区', '密度', '株高', 'LAI', '茎干重t/ha', '叶干重t/ha', '穗干重t/ha', '籽粒干重t/ha',
             '苞叶干重t/ha', '穗轴干重t/ha', '干物质总量t/ha', '茎氮积累kg/ha', '叶氮积累kg/ha', '穗氮积累kg/ha', '总氮积累kg/ha']]
考苗结果汇总.loc[:, ['日期']] = 考苗结果汇总['日期'].apply(lambda x: pd.to_datetime(x, format='%Y-%m-%d %H:%M:%S').date())
考苗结果汇总.sort_values(by=['日期','处理', '小区'], inplace=True)

# 	SPAD处理
SPAD = pd.read_excel(r'E:\2020玉米整理.xlsx', sheet_name='SPAD')
SPAD_ZL = SPAD.groupby(by=['日期', '处理', '小区']).mean()
SPAD_ZL.rename(columns={'单株SPAD': 'SPAD'}, inplace=True)
SPAD_ZL.reset_index(inplace=True)
SPAD_ZL['日期'] = SPAD_ZL['日期'].apply(lambda x: pd.to_datetime(x, format='%Y-%m-%d %H:%M:%S').date())
SPAD_ZL.sort_values(by=['日期', '处理', '小区',], inplace=True)
SPAD_ZL = SPAD_ZL[['日期', '处理', '小区', 'SPAD']]

# 灌浆数据处理
fill = pd.read_excel(r'E:\2020玉米整理.xlsx', sheet_name='灌浆')
fill_ZL = fill.groupby(by=['日期', '处理', '小区']).mean()
fill_ZL.reset_index(inplace=True)
fill_ZL['日期'] = fill_ZL['日期'].apply(lambda x: pd.to_datetime(x, format='%Y-%m-%d %H:%M:%S').date())
fill_ZL.sort_values(by=['日期', '处理', '小区'], inplace=True)
fill_ZL = fill_ZL[['日期', '处理', '小区', '100粒干重']]

# 产量数据处理
maize_y = pd.read_excel(r'E:\2020玉米整理.xlsx', sheet_name='测产')
maize_zl = maize_y.groupby(by=['日期', '处理', '小区']).mean()
maize_zl['单穗粒数'] = maize_zl['行数']*(maize_zl['行粒数1']+maize_zl['行粒数2']+maize_zl['行粒数3'])/3
maize_zl['千粒重g'] = maize_zl['500粒干重']*2
maize_zl['穗数(10000/ha)'] = maize_zl['总穗数']/6
maize_zl['理论产量kg/ha'] = maize_zl['穗数(10000/ha)']*maize_zl['千粒重g']*maize_zl['单穗粒数']/100
maize_zl = maize_zl[['单穗粒数', '千粒重g', '穗数(10000/ha)', '理论产量kg/ha']]
maize_zl.reset_index(inplace=True)
maize_zl['日期'] = maize_zl['日期'].apply(lambda x: pd.to_datetime(x, format='%Y-%m-%d %H:%M:%S').date())
maize_zl.sort_values(by=['日期', '处理', '小区'], inplace=True)

# 保存Excel
with pd.ExcelWriter(r'E:\2020玉米数据处理.xlsx',mode='w') as writer:
    考苗结果汇总.to_excel(writer, index=False, sheet_name='考苗')
    SPAD_ZL.to_excel(writer, index=False, sheet_name='SPAD')
    fill_ZL.to_excel(writer, index=False, sheet_name='灌浆')
    maize_zl.to_excel(writer, index=False, sheet_name='产量')