根据具体情况，逐行合并FAST两个数据帧

Question

在过去的10天里，我一直在与我的案例作斗争，我找不到快速高效的解决方案。

情况是这样的。我有一个DF包含一个人力资源网站的网络流量数据。该数据框的每一行都指向一个应用程序(也就是：某人通过特定的网络来源访问网站，并在特定的时间申请特定的工作机会(。这里有一个例子：

import pandas as pd
web_data = {'source':  ['Google', 'Facebook','Email'],
'job_id': ['123456', '654321','010101'],
'd_date_hour_event' : ['2019-11-01 00:09:59','2019-11-01 00:10:41','2019-11-01 00:19:20'],
}
web_data = pd.DataFrame(web_data)

在第二个DataFrame中，我有一个人力资源内部工具的摘录，我们在其中收集了所有收到的应用程序和一些补充数据。这里有一个例子：

hr_data ={'candidate_id':  ['ago23ak', 'bli78gro','123tru456'],
'job_id': ['675848', '343434','010101'],
'date_time_submission' : ['2019-11-10 00:24:59','2019-11-09 12:10:41','2019-11-01 00:19:22'],
'job_label':['HR internship','Data Science Supervisor','Project Manager']
}
hr_data = pd.DataFrame(hr_data)

以下是我面临的困难：

• 没有唯一的键可以用来合并这两个表。我必须使用"；Job_id"；(对于每个作业都是唯一的(与应用程序通过列"发生的时间相结合；d_date_hour_event"；(在web_data DF上(和"；提交日期时间；(在hr_data DF上(
• 对于同一个应用程序，在两个表上注册的时间可能不相同(相差几秒(
• hr_data中可能不存在某些web_data值

最后，我想得到一个看起来像这样的DataFrame：

result_dataframe.png

实际上，我已经编码了函数来实现这个合并。它看起来像这样：

for i, row in web_data.iterrows() :
#we stock the needed value for hr_data research
date = row.d_date_hour_event
job = row.job_id

#we compute the time period
inf = date - timedelta(seconds=10)
sup = date + timedelta(seconds=10)

#we check if there a matching row in hr_data
temp_df = pd.DataFrame()
temp_df = hr_data[(hr_data.job_id == job) & 
(hr_data.date_time_submission >= inf) & (hr_data.date_time_submission <= sup)].tail(1)

#if there is a matching row, we merge them and update web_data table
if not temp_df.empty:
row = row.to_frame().transpose()

join = pd.merge(row, temp_df, how='inner', on='job_id',left_index=False, right_index=True)

web_data.update(join)

但是，因为我的Web_data超过了250K行，我的HR_data超过140k行，所以需要几个小时！(估计运行脚本35小时…(。

我确信iterrows不是最优的，并且可以优化此代码。我尝试使用.apply(lambda x:…(的自定义函数，但没有成功。

任何帮助都是非常受欢迎的！

如果你需要更多的解释，请告诉我。

非常感谢！

Answer 1

让我们分几个步骤来尝试：

1.将两个数据帧中的日期时间列转换为pd.datetime格式。

web_data = web_data.assign(d_date_hour_event= lambda x: pd.to_datetime(x['d_date_hour_event']))
hr_data = hr_data.assign(date_time_submission=lambda x: pd.to_datetime(x['date_time_submission']))

2.重命名hr_data数据帧的job_id，以便在合并时不会出现任何错误。

hr_data = hr_data.rename(columns={"job_id": "job_id_hr"})

3。从两个数据帧中包含timestamps和job_ids的列中生成numpy阵列，并使用numpy广播检查web_data数据帧中的时间戳与hr_data数据帧中timestamp的时间戳相差10秒内且具有相同job_id的行。

web_data_dates = web_data['d_date_hour_event'].values
hr_data_dates = hr_data['date_time_submission'].values
web_data_job_ids = web_data['job_id'].values
hr_data_job_ids = hr_data['job_id_hr'].values
i, j = np.where(
(hr_data_dates[:, None] <= (web_data_dates+pd.Timedelta(10, 'S'))) & 
(hr_data_dates[:, None] >= (web_data_dates-pd.Timedelta(10, 'S'))) & 
(hr_data_job_ids[:, None] == web_data_job_ids)
)
overlapping_rows = pd.DataFrame(
np.column_stack([web_data.values[j], hr_data.values[i]]),
columns=web_data.columns.append(hr_data.columns)
)

4.为原始web_data数据帧分配新列，这样我们就可以用所有信息更新这些行，以防任何行与重叠

web_data = web_data.assign(candidate_id=np.nan, job_id_hr=np.nan, date_time_submission=np.datetime64('NaT'), job_label=np.nan)

最后只需更新web_data数据帧(或者如果不想覆盖原始数据帧，则首先创建一个副本(

web_data.update(overlapping_rows)

必须比在所有行上迭代快得多

Answer 2

这是我正在使用的代码(除非你做出我在评论中描述的更改，否则它不起作用(

web_data = {'source':  ['Google', 'Facebook','Email'],
'job_id': ['123456', '654321','010101'],
'd_date_hour_event' : ['2019-11-01 00:09:59','2019-11-01 00:10:41','2019-11-01 00:19:20'],
}
web_data = pd.DataFrame(web_data)
#placed '010101'  and '2019-11-01 00:19:22' in second position instead of third position like it used to be
#if you reswitch these values to 3rd position in respectively 'job_id' and 'date_time_submission' arrays, it should work
hr_data ={'candidate_id':  ['ago23ak', 'bli78gro','123tru456'],
'job_id': ['675848', '010101','343434'],
'date_time_submission' : ['2019-11-10 00:24:59','2019-11-01 00:19:22','2019-11-09 12:10:41'],
'job_label':['HR internship','Data Science Supervisor','Project Manager']
}
hr_data = pd.DataFrame(hr_data)
hr_data = hr_data.rename(columns={"job_id": "job_id_hr"})
web_data = web_data.assign(d_date_hour_event= lambda x: pd.to_datetime(x['d_date_hour_event']))
hr_data = hr_data.assign(date_time_submission=lambda x: pd.to_datetime(x['date_time_submission']))
web_data_dates = web_data['d_date_hour_event'].values
hr_data_dates = hr_data['date_time_submission'].values
web_data_job_ids = web_data['job_id'].values
hr_data_job_ids = hr_data['job_id_hr'].values
i, j = np.where(
(hr_data_dates[:, None] <= (web_data_dates+pd.Timedelta(10, 'S'))) & 
(hr_data_dates[:, None] >= (web_data_dates-pd.Timedelta(10, 'S'))) & 
(hr_data_job_ids[:, None] == web_data_job_ids[:, None])
)
overlapping_rows = pd.DataFrame(
np.column_stack([web_data.values[j], hr_data.values[i]]),
columns=web_data.columns.append(hr_data.columns)
)
overlapping_rows