其主要思想是获取两个字符串之间valid_from和valid_to重叠的行的成本-这是一种不等式连接的形式，可以通过conditional_join有效处理：

# pip install pyjanitor
import pandas as pd
import janitor

# fill df1's valid_to with a timestamp in the future
df1.valid_to = df1.valid_to.fillna(pd.Timestamp.max)

(df1
.conditional_join(
    df2, 
    ('valid_from', 'valid_from', '<='), 
    ('valid_to', 'valid_to', '>='), 
    df_columns='cost')
    #.move(source='cost',position='after',axis=1)
)
      cost          valid_from            valid_to  consumption
0  28.6700 2018-10-16 20:30:00 2018-10-16 21:00:00         0.03
1  26.2700 2019-10-17 00:30:00 2019-10-17 01:00:00         0.03
2  39.7339 2023-09-16 23:30:00 2023-09-17 00:00:00         0.04
3  39.7339 2023-09-17 00:00:00 2023-09-17 00:30:00         0.03

字符串

因此，由于您的范围是日期时间，您可以为第一个框架创建pd.IntervalIndex，并使用它来匹配第二个框架中的日期范围。

import numpy as np
# fillna with a far-away date 
df1['valid_to'] = df1['valid_to'].fillna(pd.Timestamp('2200-12-30'))
df1.index = pd.IntervalIndex.from_arrays(df1['valid_from'],df1['valid_to'],closed='both')

字符串
我将apply与一个lambda函数（它有一些检查）一起使用，如果存在匹配，它将返回来自Rename 1的cost

def lambda_func(r, colname='cost'):
    # get row number for "valid_from" and 'valid_to'
    try:
        row1 = df1.index.get_loc(r['valid_from'])
        row2 = df1.index.get_loc(r['valid_to'])
    except KeyError:
        return np.nan
    if row1 == row2:
        return df1.iloc[row1][colname]
    else:
        return np.nan

df2['cost'] = df2.apply(lambda r: lambda_func(r), axis=1)
print(df2)

型
输出量：

valid_from            valid_to  consumption     cost
0 2023-09-16 23:30:00 2023-09-17 00:00:00         0.04  39.7339
1 2023-09-17 00:00:00 2023-09-17 00:30:00         0.03  39.7339
2 2019-10-17 00:30:00 2019-10-17 01:00:00         0.03  26.2700
3 2018-10-16 20:30:00 2018-10-16 21:00:00         0.03  28.6700

型

你能试试这个吗：

bt_dates = (cost_df.values)  #a dataframe containing costs for time periods
'''
array([[Timestamp('2018-10-09 23:00:00'),
        Timestamp('2019-09-30 23:00:00'), 28.67],
       [Timestamp('2019-09-30 23:00:00'),
        Timestamp('2021-03-18 00:00:00'), 26.27],
       [Timestamp('2022-10-13 23:00:00'),
        Timestamp('2024-04-06 23:08:01.479568'), 39.7339]], dtype=object)
'''

字符串
然后创建一个函数：

def get_costs(row):
    for i in (bt_dates):
        if pd.notnull(i[1]):  # valid_to null or notnull?
            if (i[0] <= row["valid_from"]<= i[1]) & ((i[0] <= row["valid_to"]<= i[1])):
                return i[2]
                break
            else:
                pass
        else: # if valid_to is null, no need to check valid_to in main df
            if (i[0] <= row["valid_from"]):
                return i[2]
                break
            else:
                pass

型
最后，将函数应用于主框架：

df["cost"] = df[["valid_from","valid_to"]].apply(get_costs, axis=1)

型

测试：

df = pd.DataFrame({'valid_from': [Timestamp('2023-09-16 23:30:00'), Timestamp('2023-09-17 00:00:00'), Timestamp('2019-10-17 00:30:00'), Timestamp('2018-10-16 20:30:00')], 'valid_to': [Timestamp('2023-09-17 00:00:00'), Timestamp('2023-09-17 00:30:00'), Timestamp('2019-10-17 01:00:00'), Timestamp('2018-10-16 21:00:00')]})
df
'''
           valid_from            valid_to
0 2023-09-16 23:30:00 2023-09-17 00:00:00
1 2023-09-17 00:00:00 2023-09-17 00:30:00
2 2019-10-17 00:30:00 2019-10-17 01:00:00
3 2018-10-16 20:30:00 2018-10-16 21:00:00

'''
df["cost"] = df[["valid_from","valid_to"]].apply(get_costs, axis=1)

型

退出：

valid_from            valid_to     cost
0 2023-09-16 23:30:00 2023-09-17 00:00:00  39.7339
1 2023-09-17 00:00:00 2023-09-17 00:30:00  39.7339
2 2019-10-17 00:30:00 2019-10-17 01:00:00  26.2700
3 2018-10-16 20:30:00 2018-10-16 21:00:00  28.6700

型