目标:提取每一年气象数据和通量数据的指定列,生成新表
前提:通量数据和气象数据名称已经规范化
结果
代码
# -*- coding: utf-8 -*-
"""
批量处理路径下的文件
分别提取通量数据表和气象数据表中的指定列,合并写入一张新的excel表格
YMJ 20230118 16:30
"""
import pandas as pd
import os
from pandas import DataFrame
# 文件存放路径
dir = "D:\\YMJ_file\\通量数据相关\\黑河流域通量数据批量处理"
# 创建空列表,把通量、气象数据名称分别存入到列表中
flux_files = []
meteo_files = []
# 遍历路径,获取文件绝对路径
for root,subroot,files in os.walk(dir):
for file in files:
if "气象" in file:
meteo_file = os.path.join(root, file)
meteo_files.append(meteo_file)
elif "通量" in file:
flux_file = os.path.join(root, file)
flux_files.append(flux_file)
# 判断两个列表中名称前8位相等的文件,并将其合并为1个文件
for i in range(len(flux_files)):
# 读取excel表并将指定列以class的形式存放在df中
df1 = pd.read_excel(flux_files[i], usecols=['date',"Hs_day_sum","LE_day_sum"])
df2 = pd.read_excel(meteo_files[i], usecols=["date","DOY","TA_day_mean","RH_day_mean",\
"WS_day_mean","SWC_day_mean","Rn_day_sum","G_day_sum","Rain_day_sum"])
# 列合并
flux_meto_data = pd.concat([df1, df2], axis=1)
# 构造输出路径
out_path = os.path.join(dir,flux_files[i].split("\\")[-1][:8] + ".xlsx")
# 数据写入表
flux_meto_data.to_excel(out_path, index=None)