pandas 在最近的时间戳上合并数据帧 [英] pandas merge dataframes on closest timestamp

问题描述

我想在三个列上合并两个数据框:电子邮件，主题和时间戳. 数据帧之间的时间戳不同，因此我需要为一组电子邮件和电子邮件标识最接近的匹配时间戳.学科.

I want to merge two dataframes on three columns: email, subject and timestamp. The timestamps between the dataframes differ and I therefore need to identify the closest matching timestamp for a group of email & subject.

以下是可重现的示例，其中使用了建议用于

Below is a reproducible example using a function for closest match suggested for this question.

import numpy as np
import pandas as pd
from pandas.io.parsers import StringIO

def find_closest_date(timepoint, time_series, add_time_delta_column=True):
   # takes a pd.Timestamp() instance and a pd.Series with dates in it
   # calcs the delta between `timepoint` and each date in `time_series`
   # returns the closest date and optionally the number of days in its time delta
   deltas = np.abs(time_series - timepoint)
   idx_closest_date = np.argmin(deltas)
   res = {"closest_date": time_series.ix[idx_closest_date]}
   idx = ['closest_date']
   if add_time_delta_column:
      res["closest_delta"] = deltas[idx_closest_date]
      idx.append('closest_delta')
   return pd.Series(res, index=idx)


a = """timestamp,email,subject
2016-07-01 10:17:00,[e-mail],subject3
2016-07-01 02:01:02,[e-mail],welcome
2016-07-01 14:45:04,[e-mail],subject3
2016-07-01 08:14:02,[e-mail],subject2
2016-07-01 16:26:35,[e-mail],subject4
2016-07-01 10:17:00,[e-mail],subject3
2016-07-01 02:01:02,[e-mail],welcome
2016-07-01 14:45:04,[e-mail],subject3
2016-07-01 08:14:02,[e-mail],subject2
2016-07-01 16:26:35,[e-mail],subject4
"""

b = """timestamp,email,subject,clicks,var1
2016-07-01 02:01:14,[e-mail],welcome,1,1
2016-07-01 08:15:48,[e-mail],subject2,2,2
2016-07-01 10:17:39,[e-mail],subject3,1,7
2016-07-01 14:46:01,[e-mail],subject3,1,2
2016-07-01 16:27:28,[e-mail],subject4,1,2
2016-07-01 10:17:05,[e-mail],subject3,0,0
2016-07-01 02:01:03,[e-mail],welcome,0,0
2016-07-01 14:45:05,[e-mail],subject3,0,0
2016-07-01 08:16:00,[e-mail],subject2,0,0
2016-07-01 17:00:00,[e-mail],subject4,0,0
"""

请注意，对于[e-mail]，最接近的匹配时间戳是10:17:39，而对于[e-mail]，最接近的匹配时间戳是10:17:05.

Notice that for [e-mail] the closest matched timestamp is 10:17:39, whereas for [e-mail] the closest match is 10:17:05.

a = """timestamp,email,subject
2016-07-01 10:17:00,[e-mail],subject3
2016-07-01 10:17:00,[e-mail],subject3
"""

b = """timestamp,email,subject,clicks,var1
2016-07-01 10:17:39,[e-mail],subject3,1,7
2016-07-01 10:17:05,[e-mail],subject3,0,0
"""
df1 = pd.read_csv(StringIO(a), parse_dates=['timestamp'])
df2 = pd.read_csv(StringIO(b), parse_dates=['timestamp'])

df1[['closest', 'time_bt_x_and_y']] = df1.timestamp.apply(find_closest_date, args=[df2.timestamp])
df1

df3 = pd.merge(df1, df2, left_on=['email','subject','closest'], right_on=['email','subject','timestamp'],how='left')

df3
timestamp_x        email   subject             closest  time_bt_x_and_y         timestamp_y  clicks  var1
  2016-07-01 10:17:00  [e-mail]  subject3 2016-07-01 10:17:05         00:00:05                 NaT     NaN   NaN
  2016-07-01 02:01:02  [e-mail]   welcome 2016-07-01 02:01:03         00:00:01                 NaT     NaN   NaN
  2016-07-01 14:45:04  [e-mail]  subject3 2016-07-01 14:45:05         00:00:01                 NaT     NaN   NaN
  2016-07-01 08:14:02  [e-mail]  subject2 2016-07-01 08:15:48         00:01:46 2016-07-01 08:15:48     2.0   2.0
  2016-07-01 16:26:35  [e-mail]  subject4 2016-07-01 16:27:28         00:00:53 2016-07-01 16:27:28     1.0   2.0
  2016-07-01 10:17:00  [e-mail]  subject3 2016-07-01 10:17:05         00:00:05 2016-07-01 10:17:05     0.0   0.0
  2016-07-01 02:01:02  [e-mail]   welcome 2016-07-01 02:01:03         00:00:01 2016-07-01 02:01:03     0.0   0.0
  2016-07-01 14:45:04  [e-mail]  subject3 2016-07-01 14:45:05         00:00:01 2016-07-01 14:45:05     0.0   0.0
  2016-07-01 08:14:02  [e-mail]  subject2 2016-07-01 08:15:48         00:01:46                 NaT     NaN   NaN
  2016-07-01 16:26:35  [e-mail]  subject4 2016-07-01 16:27:28         00:00:53                 NaT     NaN   NaN

结果是错误的，主要是因为最接近的日期不正确，因为它没有考虑电子邮件&主题.

The result is wrong, mainly because the closest date is incorrect since it does not take into account email & subject.

预期结果是

修改功能以为给定的电子邮件和主题提供最接近的时间戳将很有帮助.

Amending the function to give the closest timesstamps for a given email and subject would be helpful.

df1.groupby(['email','subject'])['timestamp'].apply(find_closest_date, args=[df1.timestamp])

但这会导致错误，因为未为组对象定义函数. 最好的方法是什么?

But that gives an error as the function is not defined for a group object. What's the best way of doing this?

推荐答案

请注意，如果在email和subject上合并df1和df2，则结果 具有所有可能的相关时间戳配对:

Notice that if you merge df1 and df2 on email and subject, then the result has all the possible relevant timestamp pairings:

In [108]: result = pd.merge(df1, df2, how='left', on=['email','subject'], suffixes=['', '_y']); result
Out[108]: 
             timestamp        email   subject         timestamp_y  clicks  var1
0  2016-07-01 10:17:00  [e-mail]  subject3 2016-07-01 10:17:39       1     7
1  2016-07-01 10:17:00  [e-mail]  subject3 2016-07-01 14:46:01       1     2
2  2016-07-01 02:01:02  [e-mail]   welcome 2016-07-01 02:01:14       1     1
3  2016-07-01 14:45:04  [e-mail]  subject3 2016-07-01 10:17:39       1     7
4  2016-07-01 14:45:04  [e-mail]  subject3 2016-07-01 14:46:01       1     2
5  2016-07-01 08:14:02  [e-mail]  subject2 2016-07-01 08:15:48       2     2
6  2016-07-01 16:26:35  [e-mail]  subject4 2016-07-01 16:27:28       1     2
7  2016-07-01 10:17:00  [e-mail]  subject3 2016-07-01 10:17:05       0     0
8  2016-07-01 10:17:00  [e-mail]  subject3 2016-07-01 14:45:05       0     0
9  2016-07-01 02:01:02  [e-mail]   welcome 2016-07-01 02:01:03       0     0
10 2016-07-01 14:45:04  [e-mail]  subject3 2016-07-01 10:17:05       0     0
11 2016-07-01 14:45:04  [e-mail]  subject3 2016-07-01 14:45:05       0     0
12 2016-07-01 08:14:02  [e-mail]  subject2 2016-07-01 08:16:00       0     0
13 2016-07-01 16:26:35  [e-mail]  subject4 2016-07-01 17:00:00       0     0

您现在可以获取每行时间戳差异的绝对值:

You could now take the absolute value of the difference in timestamps for each row:

result['diff'] = (result['timestamp_y'] - result['timestamp']).abs()

然后使用

idx = result.groupby(['timestamp','email','subject'])['diff'].idxmin()
result = result.loc[idx]

根据['timestamp','email','subject']查找各组差异最小的行.

to find the rows with the minimum difference for each group based on ['timestamp','email','subject'].

import numpy as np
import pandas as pd
from pandas.io.parsers import StringIO

a = """timestamp,email,subject
2016-07-01 10:17:00,[e-mail],subject3
2016-07-01 02:01:02,[e-mail],welcome
2016-07-01 14:45:04,[e-mail],subject3
2016-07-01 08:14:02,[e-mail],subject2
2016-07-01 16:26:35,[e-mail],subject4
2016-07-01 10:17:00,[e-mail],subject3
2016-07-01 02:01:02,[e-mail],welcome
2016-07-01 14:45:04,[e-mail],subject3
2016-07-01 08:14:02,[e-mail],subject2
2016-07-01 16:26:35,[e-mail],subject4
"""

b = """timestamp,email,subject,clicks,var1
2016-07-01 02:01:14,[e-mail],welcome,1,1
2016-07-01 08:15:48,[e-mail],subject2,2,2
2016-07-01 10:17:39,[e-mail],subject3,1,7
2016-07-01 14:46:01,[e-mail],subject3,1,2
2016-07-01 16:27:28,[e-mail],subject4,1,2
2016-07-01 10:17:05,[e-mail],subject3,0,0
2016-07-01 02:01:03,[e-mail],welcome,0,0
2016-07-01 14:45:05,[e-mail],subject3,0,0
2016-07-01 08:16:00,[e-mail],subject2,0,0
2016-07-01 17:00:00,[e-mail],subject4,0,0
"""

df1 = pd.read_csv(StringIO(a), parse_dates=['timestamp'])
df2 = pd.read_csv(StringIO(b), parse_dates=['timestamp'])

result = pd.merge(df1, df2, how='left', on=['email','subject'], suffixes=['', '_y'])
result['diff'] = (result['timestamp_y'] - result['timestamp']).abs()
idx = result.groupby(['timestamp','email','subject'])['diff'].idxmin()
result = result.loc[idx].drop(['timestamp_y','diff'], axis=1)
result = result.sort_index()
print(result)

收益

             timestamp        email   subject  clicks  var1
0  2016-07-01 10:17:00  [e-mail]  subject3       1     7
2  2016-07-01 02:01:02  [e-mail]   welcome       1     1
4  2016-07-01 14:45:04  [e-mail]  subject3       1     2
5  2016-07-01 08:14:02  [e-mail]  subject2       2     2
6  2016-07-01 16:26:35  [e-mail]  subject4       1     2
7  2016-07-01 10:17:00  [e-mail]  subject3       0     0
9  2016-07-01 02:01:02  [e-mail]   welcome       0     0
11 2016-07-01 14:45:04  [e-mail]  subject3       0     0
12 2016-07-01 08:14:02  [e-mail]  subject2       0     0
13 2016-07-01 16:26:35  [e-mail]  subject4       0     0

这篇关于 pandas 在最近的时间戳上合并数据帧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！