我们用Python用来解决我们的需求;
代码如下所示:
大家拿去用,不用找我授权
import os
import pandas as pd
import shutil
# 定义目录路径
source_directory = r"D:\copula\STI_GJY\新疆"
destination_directory = os.path.join(source_directory, "modified")
# 创建目标目录(如果不存在)
os.makedirs(destination_directory, exist_ok=True)
# 获取目录中的所有文件
files = os.listdir(source_directory)
# 打印获取的所有文件名,检查文件名格式
print("目录中的所有文件:")
for file in files:
print(file)
# 初始化两个字典来存储文件路径
spei_files = {}
result_files = {}
# 遍历文件并分类存储到字典中
for file in files:
if file.endswith(".xlsx"):
if file.startswith("STI_SPEI_") and file[9:14].isdigit():
key = file[9:14]
spei_files[key] = os.path.join(source_directory, file)
elif file.endswith("_result_sti.xlsx") and file[:5].isdigit():
key = file[:5]
result_files[key] = os.path.join(source_directory, file)
# 打印找到的文件以进行调试
print("\n找到的STI_SPEI文件:")
for key, path in spei_files.items():
print(f"{key}: {path}")
print("\n找到的_result_sti文件:")
for key, path in result_files.items():
print(f"{key}: {path}")
# 遍历匹配的文件对
for key in spei_files:
if key in result_files:
spei_file_path = spei_files[key]
result_file_path = result_files[key]
# 读取Excel文件
print(f"\n正在处理文件对: {spei_file_path} 和 {result_file_path}")
spei_df = pd.read_excel(spei_file_path)
result_df = pd.read_excel(result_file_path)
# 检查列是否足够
if spei_df.shape[0] == result_df.shape[0]:
# 获取STI_SPEI文件的第二列
spei_column = spei_df.iloc[:, 1]
# 将STI_SPEI的第二列复制到result文件的第三列
result_df['SPEI'] = spei_column
# 保存修改后的result文件到目标目录
destination_file_path = os.path.join(destination_directory, os.path.basename(result_file_path))
result_df.to_excel(destination_file_path, index=False)
print(f"已更新文件: {destination_file_path}")
else:
print(f"文件 {spei_file_path} 行数与结果文件不匹配")
print("操作完成。")