import pandas as pd

# 读取两个表
table1 = pd.read_excel(r"D:\guozhong\泾县数据集20241118\泾县转码表.xlsx")  # 表1
table2 = pd.read_excel(r"D:\guozhong\泾县数据集20241118\泾县水稳汇总20241110.xlsx")  # 表2


# 确保两表列数据类型一致，转换为字符串
table1["转码后样品编号"] = table1["转码后样品编号"].astype(str)
table1["样品编号"] = table1["样品编号"].astype(str)
table2["样品编号"] = table2["样品编号"].astype(str)

# 提取表2的样品编号列
sample_ids_table2 = table2["样品编号"].tolist()

# 创建一个列表，用于存储匹配结果
matched_results = []

# 遍历 table2 的样品编号，与 table1 的转码后样品编号匹配
for sample_id in sample_ids_table2:
    matches = table1[table1["转码后样品编号"] == sample_id]
    for _, row in matches.iterrows():
        matched_results.append((sample_id, row["样品编号"]))

# 将结果转为 DataFrame 并保存到 Excel 文件
output_df = pd.DataFrame(matched_results, columns=["表2样品编号", "表1样品编号"])
output_path = r"D:\guozhong\泾县剖面水稳转码.xlsx"
output_df.to_excel(output_path, index=False)

print(f"匹配完成！结果已保存到 {output_path}")