import pandas as pd
import numpy as np
df_1 = pd.read_excel(r"D:\guozhong\19、定远县\19、定远县\定远县表层数据统计20241222.xlsx",converters={"原样品编号":str})
df_1
df_2 = pd.read_excel(r"D:\guozhong\19、定远县\19、定远县\定远县水稳清除后汇总20241222.xlsx",converters={"原样品编号":str})
df_2
# 确保列为字符串类型
df_1["原样品编号"] = df_1["原样品编号"].astype(str)
df_2["样品编号"] = df_2["原样品编号"].astype(str)

# 提取前16位进行匹配
df_1["原样品编号前16位"] = df_1["原样品编号"].str[:16]
df_2["样品编号前16位"] = df_2["样品编号"].str[:16]

# 合并表格（左连接，保留df_1中的所有数据）
merged_df = pd.merge(
    df_1,
    df_2[["样品编号前16位", "样品编号","水稳>5mm（%）", "水稳3mm~5mm（%）", "水稳2mm~3mm（%）", "水稳1mm~2mm（%）", "水稳0.5mm~1mm（%）","水稳0.25mm~0.5mm（%）","水稳性大团聚体总和（%）"]],
    left_on="原样品编号前16位",
    right_on="样品编号前16位",
    how="left"
)

# 删除辅助列
merged_df = merged_df.drop(columns=["原样品编号前16位", "样品编号前16位"])

# 输出结果
print(merged_df)
merged_df.to_excel(r"定远表层水稳.xlsx", index=False, engine="openpyxl")