使用DataFrame.melt，通过to_timedelta将值转换为时间增量（如果需要更高的精度，请删除.astype(int)）：

df2 = (df2.melt('Technology', var_name='Status', value_name='New Date')
          .assign(**{'New Date': 
                    lambda x: pd.to_timedelta(x['New Date'].astype(int), unit='d')}))
print (df2)
            Technology                 Status  New Date
0              Battery  Application submitted  730 days
1  Solar Photovoltaics  Application submitted  730 days
2                 Wind  Application submitted 1825 days
3              Battery   Application approved  273 days
4  Solar Photovoltaics   Application approved  273 days
5                 Wind   Application approved  912 days
6              Battery           Construction  273 days
7  Solar Photovoltaics           Construction  273 days
8                 Wind           Construction 1095 days

然后使用左连接并添加列Current Date：

df = df1.merge(df2, on=['Technology','Status'], how='left')
df['New Date'] += pd.to_datetime(df['Current Date'], dayfirst=True)
print (df)
  Current Date Technology                 Status   New Date
0   18/03/2022       Wind           Construction 2025-03-17
1   15/02/2022      Solar           Construction        NaT
2   24/01/2022    Battery   Application approved 2022-10-24
3   23/09/2020       Wind   Application approved 2023-03-24
4   18/11/2021      Solar  Application submitted        NaT
5   25/06/2020      Solar   Application approved        NaT
6   27/02/2020       Wind  Application submitted 2025-02-25
7   10/03/2022    Battery  Application submitted 2024-03-09

对于匹配Solar Photovoltaics值，可以使用拆分并选择第一个值：

df2['Technology'] = df2['Technology'].str.split().str[0]

df2 = (df2.melt('Technology', var_name='Status', value_name='New Date')
          .assign(**{'New Date':  
                  lambda x: pd.to_timedelta(x['New Date'].astype(int), unit='d')}))
print (df2)
  Technology                 Status  New Date
0    Battery  Application submitted  730 days
1      Solar  Application submitted  730 days
2       Wind  Application submitted 1825 days
3    Battery   Application approved  273 days
4      Solar   Application approved  273 days
5       Wind   Application approved  912 days
6    Battery           Construction  273 days
7      Solar           Construction  273 days
8       Wind           Construction 1095 days

df = df1.merge(df2, on=['Technology','Status'], how='left')
df['New Date'] += pd.to_datetime(df['Current Date'], dayfirst=True)
print (df)
  Current Date Technology                 Status   New Date
0   18/03/2022       Wind           Construction 2025-03-17
1   15/02/2022      Solar           Construction 2022-11-15
2   24/01/2022    Battery   Application approved 2022-10-24
3   23/09/2020       Wind   Application approved 2023-03-24
4   18/11/2021      Solar  Application submitted 2023-11-18
5   25/06/2020      Solar   Application approved 2021-03-25
6   27/02/2020       Wind  Application submitted 2025-02-25
7   10/03/2022    Battery  Application submitted 2024-03-09