如何基于两个DataFrame的多条件为DF1生成新列？

核心思路 #

先统一数据格式，将DF2从宽表转为长表以便和DF1的「Technology」「Status」匹配，再通过日期运算生成目标列。

代码实现 #

import pandas as pd

# 构建示例数据
df1 = pd.DataFrame({
    "Current Date": ["18/03/2022", "15/02/2022", "24/01/2022", "23/09/2020",
                    "18/11/2021", "25/06/2020", "27/02/2020", "10/03/2022"],
    "Technology": ["Wind", "Solar", "Battery", "Wind",
                   "Solar", "Solar", "Wind", "Battery"],
    "Status": ["Construction", "Construction", "Application approved", "Application approved",
               "Application submitted", "Application approved", "Application submitted", "Application submitted"]
})

df2 = pd.DataFrame({
    "Technology": ["Battery", "Solar Photovoltaics", "Wind"],
    "Application submitted": [730, 730, 1825],
    "Application approved": [273.75, 273.75, 912.5],
    "Construction": [273.75, 273.75, 1095]
})

# 1. 转换DF1的日期列为可运算的datetime类型
df1["Current Date"] = pd.to_datetime(df1["Current Date"], format="%d/%m/%Y")

# 2. 重塑DF2为长格式，同时统一技术名称（匹配DF1的"Solar"）
df2_long = df2.melt(
    id_vars="Technology",
    var_name="Status",
    value_name="Days"
).replace({"Solar Photovoltaics": "Solar"})

# 3. 按Technology和Status匹配天数
merged_df = df1.merge(df2_long, on=["Technology", "Status"], how="left")

# 4. 计算新日期并格式化为dd/mm/yyyy样式
merged_df["New Date"] = (merged_df["Current Date"] + pd.to_timedelta(merged_df["Days"], unit="D")).dt.strftime("%d/%m/%Y")

# 输出结果（保留目标列）
print(merged_df[["Current Date", "Technology", "Status", "New Date"]])

运行结果 #

内容的提问来源于stack exchange，提问作者Chetan Patel