使用datetime条件快速将一个pandas dataframe的值映射到另一个dataframe。

Question

我有两个数据框。

df1=

ID Value BeginDate EndDate  
1  0.5   1/1/12    1/1/13  
1  0.6   1/1/13    1/1/14  
2  0.4   1/1/12    1/1/13  
3  0.7   1/1/12    1/1/13

df2=
ID Date  
1  6/6/12  
1  7/5/12   
1  10/5/13  
2  8/9/12 
3  6/6/12

我想根据ID列以及df2中的Date列是否在df1的BeginDate和EndDate之间，将df1中的Value列映射到df2上。问题是df1有10,000行，而df2有1,000,000行。

我知道这是可能的。我最关心的是如何快速完成这个任务。

我之前做过类似的事情，只不过df1只有10行，我使用了df1.iterrows来实现。

df2['Value'] = 1  
for index, row in df1.iterrows():
    df1['Value'] = np.where(
                  (row['BeginDate'] <= df2['Date']) 
                & (df2['Date'] <= row['EndDate']),  
                row['Value'],  
                df2['Value'])

然而，显然由于df1现在有10,000行，iterrows不再是首选方法。

我考虑过定义一个函数并使用df2.apply，但这将涉及在每次迭代中对df1进行过滤。我以前在这种方法上遇到过效率问题。

可以根据另一列（示例中未显示）将df1分解成较小的数据框。因此，不再有一个大的数据框，而是有10个较小的数据框。我考虑将它们放入一个字典中。这个字典的键可以从df2映射过来，这会有所帮助，但仍然让我面临最初的问题。

我见过使用掩码和df.loc的方法，但还没有找到使用日期条件的方法。

使用pandas完成这个任务并非必需。

- benja616

---

我预计你会使用merge函数并加入一些规则，就像这样：merged_df = pd.merge(df1, df2, on="ID")。 - D.L

我希望你能使用一些规则来进行合并，就像这样 merged_df = pd.merge(df1, df2, on="ID")... - D.L

我希望你能使用一些规则来使用merge函数。就像这样merged_df = pd.merge(df1, df2, on="ID")... - undefined

Answer 1

如果时间间隔不重叠，最高效的pandas方法是在BeginDate上使用merge_asof，然后检查EndDate是否有效：

df1[['BeginDate', 'EndDate']] = df1[['BeginDate', 'EndDate']].apply(pd.to_datetime, format='%d/%m/%y')
df2['Date'] = pd.to_datetime(df2['Date'], format='%d/%m/%y')


out = (pd.merge_asof(
 df2.reset_index()
    .sort_values(by='Date'),
 df1.sort_values(by='BeginDate'),
    left_on='Date', right_on='BeginDate', by='ID')
    .assign(Value=lambda d: d['Value'].where(d['Date'].le(d['EndDate'])))
    .set_index('index').sort_index()
)

输出（包含所有列，然后可以删除BeginDate/EndDate）：

       ID       Date  Value  BeginDate    EndDate
index                                            
0       1 2012-06-06    0.5 2012-01-01 2013-01-01
1       1 2012-05-07    0.5 2012-01-01 2013-01-01
2       1 2013-05-10    0.6 2013-01-01 2014-01-01
3       2 2012-09-08    0.4 2012-01-01 2013-01-01
4       3 2012-06-06    0.7 2012-01-01 2013-01-01

或者，一个更通用的方法，也可以处理重叠的区间，是使用janitor；

# pip install janitor
import janitor

df1[['BeginDate', 'EndDate']] = df1[['BeginDate', 'EndDate']].apply(pd.to_datetime, format='%d/%m/%y')
df2['Date'] = pd.to_datetime(df2['Date'], format='%d/%m/%y')

df2.conditional_join(df1,
                     ('ID', 'ID', '=='),
                     ('Date', 'BeginDate', '>='),
                     ('Date', 'EndDate', '<='),
                     )

- mozway

Answer 2

其实完全不需要使用iterrows，你可以高效地使用np.where而不需要iterrows，只需将整个Series传递进去：

def broadcast_approach(df1, df2):
    df2['Value'] = 1
    df1['Value'] = np.where(
                  (df1['BeginDate'] <= df2['Date'])
                & (df2['Date'] <= df1['EndDate']),
                df1['Value'],
                df2['Value'])

df1 = pd.DataFrame({
    'ID': [1, 1, 2, 3],
    'Value': [0.5, 0.6, 0.4, 0.7],
    'BeginDate': ['1/1/12', '1/1/13', '1/1/12', '1/1/12'],
    'EndDate': ['1/1/13', '1/1/14', '1/1/13', '1/1/13']
})

df2 = pd.DataFrame({
    'ID': [ 1, 1, 2, 3],
    'Date': [ '7/5/12', '10/5/13', '8/9/12', '1/1/12']
})

>>> broadcast_approach(df1, df2)
>>> print(df1)

ID  Value   BeginDate   EndDate
0   1   1.0 1/1/12  1/1/13
1   1   1.0 1/1/13  1/1/14
2   2   1.0 1/1/12  1/1/13
3   3   0.7 1/1/12  1/1/13

为了性能比较，我们进行了一些基准测试：

import timeit
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

def iterrow_approach(df1, df2):
    df2['Value'] = 1
    for index, row in df1.iterrows():
        df1['Value'] = np.where(
                      (row['BeginDate'] <= df2['Date'])
                    & (df2['Date'] <= row['EndDate']),
                    row['Value'],
                    df2['Value'])

def broadcast_approach(df1, df2):
    df2['Value'] = 1
    df1['Value'] = np.where(
                  (df1['BeginDate'] <= df2['Date'])
                & (df2['Date'] <= df1['EndDate']),
                df1['Value'],
                df2['Value'])

dataset_sizes = [100, 1000, 10000, 50000]

iterrow_times = []
broadcast_times = []

for size in dataset_sizes:
    df1 = pd.DataFrame({
        'ID': np.random.randint(1, 10, size=size),
        'Value': np.random.rand(size),
        'BeginDate': pd.date_range('1/1/2012', periods=size).strftime('%m/%d/%y'),
        'EndDate': pd.date_range('1/1/2013', periods=size).strftime('%m/%d/%y')
    })

    df2 = pd.DataFrame({
        'ID': np.random.randint(1, 10, size=size),
        'Date': pd.date_range('1/1/2012', periods=size).strftime('%m/%d/%y')
    })

    iterrow_time = timeit.timeit(lambda: iterrow_approach(df1.copy(), df2.copy()), number=1)
    broadcast_time = timeit.timeit(lambda: broadcast_approach(df1.copy(), df2.copy()), number=1)
    iterrow_times.append(iterrow_time)
    broadcast_times.append(broadcast_time)

plt.plot(dataset_sizes, iterrow_times, label='iterrow_approach')
plt.plot(dataset_sizes, broadcast_times, label='broadcast_approach')
plt.xlabel('Dataset Size')
plt.ylabel('Execution Time (seconds)')
plt.title('Performance Comparison')
plt.legend()
plt.show()

- Sebastian Wozny

---

如果在一个ID内有很多行，组合可能会迅速增加。您应该在图表上使用对数刻度来了解numpy方法的真实计时情况。 - mozway

如果在一个ID内有很多行，组合可能会迅速增加。您应该在图表上使用对数刻度来了解numpy方法的真实时间。 - mozway

如果您在一个ID内有很多行，组合可能会迅速增加。您应该在图表上使用对数刻度来了解numpy方法的真实时间。 - undefined

谢谢你的回复，我正在尝试一下。我说的没错吗？这种方法只适用于数据框具有相同行数的情况吗？ - benja616

谢谢您的回复，我现在只是在试验一下。请问我说的对吗？这种方法只适用于数据框具有相同行数的情况吗？ - benja616

谢谢您的回复，我现在只是在试试玩而已。我说这种方法只在数据框具有相同行数时才有效，是不是错了？ - undefined

Answer 3

能否将df1的数据合并到df2中，然后在新的数据框上对两列进行逻辑操作？

import pandas as pd

data = {'ID': [1, 1, 2, 3],
        'Value': [0.5, 0.6, 0.4, 0.7],
        'BeginDate': ['1/1/12', '1/1/13', '1/1/12', '1/1/12'],
        'EndDate': ['1/1/13', '1/1/14', '1/1/13', '1/1/13']}

df1 = pd.DataFrame(data)

data = {'ID': [1, 1, 1, 2, 3], 
        'Date': ['6/6/12', '7/5/12', '10/5/13', '8/9/12', '6/6/12']}

df2 = pd.DataFrame(data)

merged_df=pd.merge(df2, df1, how='left', left_on=['ID'], right_on=['ID'])

然后merged_df看起来像这样

ID  Date    Value   BeginDate   EndDate
1   6/6/12  0.5 1/1/12  1/1/13
1   6/6/12  0.6 1/1/13  1/1/14
1   7/5/12  0.5 1/1/12  1/1/13
1   7/5/12  0.6 1/1/13  1/1/14
1   10/5/13 0.5 1/1/12  1/1/13
1   10/5/13 0.6 1/1/13  1/1/14
2   8/9/12  0.4 1/1/12  1/1/13
3   6/6/12  0.7 1/1/12  1/1/13

并且

merged_df['BeginDate'] < merged_df['Date']

返回

0    True
1    True
2    True
3    True
4    True
5    True
6    True
7    True
dtype: bool

- Yoshimi

---

这种方法效率较低，因为它会计算每个ID的所有行的组合。想象一下，在每个输入中，ID 1有100行，那么中间结果将有1万行。 - mozway

这种方法效率低下，因为它会计算每个ID的所有行的组合。想象一下，如果每个输入中ID 1有100行，那么中间结果将有10,000行。 - mozway

这种方法效率低下，因为你需要计算每个ID的所有行的组合，假设每个输入中ID为1的行有100行，那么中间结果将会有10,000行。 - undefined