панды объединяют кадры данных на ближайшей отметке времени

Я хочу объединить два кадра данных в трех столбцах: электронная почта, тема и метка времени. Временные метки между фреймами данных различаются, и поэтому мне нужно определить метку наиболее близкого соответствия для группы электронной почты и темы.

Ниже приведен воспроизводимый пример использования функции ближайшего соответствия, предложенной дляэтот вопрос.

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,[email protected],subject3
2016-07-01 02:01:02,[email protected],welcome
2016-07-01 14:45:04,[email protected],subject3
2016-07-01 08:14:02,[email protected],subject2
2016-07-01 16:26:35,[email protected],subject4
2016-07-01 10:17:00,[email protected],subject3
2016-07-01 02:01:02,[email protected],welcome
2016-07-01 14:45:04,[email protected],subject3
2016-07-01 08:14:02,[email protected],subject2
2016-07-01 16:26:35,[email protected],subject4
"""

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

Обратите внимание, что для [email protected] самая близкая совпавшая отметка времени - 10:17:39, тогда как для [email protected] самое близкое совпадение - 10:17:05.

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

b = """timestamp,email,subject,clicks,var1
2016-07-01 10:17:39,[email protected],subject3,1,7
2016-07-01 10:17:05,[email protected],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  [email protected]  subject3 2016-07-01 10:17:05         00:00:05                 NaT     NaN   NaN
  2016-07-01 02:01:02  [email protected]   welcome 2016-07-01 02:01:03         00:00:01                 NaT     NaN   NaN
  2016-07-01 14:45:04  [email protected]  subject3 2016-07-01 14:45:05         00:00:01                 NaT     NaN   NaN
  2016-07-01 08:14:02  [email protected]  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  [email protected]  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  [email protected]  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  [email protected]   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  [email protected]  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  [email protected]  subject2 2016-07-01 08:15:48         00:01:46                 NaT     NaN   NaN
  2016-07-01 16:26:35  [email protected]  subject4 2016-07-01 16:27:28         00:00:53                 NaT     NaN   NaN

Результат неверен, главным образом потому, что ближайшая дата неверна, так как не учитывает электронную почту и тему.

Ожидаемый результат

Было бы полезно изменить функцию, чтобы дать самые близкие метки времени для данного письма и темы.

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

Но это дает ошибку, так как функция не определена для группового объекта. Какой лучший способ сделать это?

 Martijn Pieters29 авг. 2016 г., 09:06
Ваш ожидаемый результат - текст; добавьте его в свой пост как текст, а не как изображение.
 TinaW06 авг. 2016 г., 22:51
хорошо, какой формат вы хотите вместо этого?
 Merlin06 авг. 2016 г., 22:04
Пожалуйста, не используйте png для кода или данных.

Ответы на вопрос(2)

Решение Вопроса

Обратите внимание, что если вы сливаетеdf1 а такжеdf2 наemail а такжеsubjectтогда результат имеет все возможныеСоответствующий метки времени:

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  [email protected]  subject3 2016-07-01 10:17:39       1     7
1  2016-07-01 10:17:00  [email protected]  subject3 2016-07-01 14:46:01       1     2
2  2016-07-01 02:01:02  [email protected]   welcome 2016-07-01 02:01:14       1     1
3  2016-07-01 14:45:04  [email protected]  subject3 2016-07-01 10:17:39       1     7
4  2016-07-01 14:45:04  [email protected]  subject3 2016-07-01 14:46:01       1     2
5  2016-07-01 08:14:02  [email protected]  subject2 2016-07-01 08:15:48       2     2
6  2016-07-01 16:26:35  [email protected]  subject4 2016-07-01 16:27:28       1     2
7  2016-07-01 10:17:00  [email protected]  subject3 2016-07-01 10:17:05       0     0
8  2016-07-01 10:17:00  [email protected]  subject3 2016-07-01 14:45:05       0     0
9  2016-07-01 02:01:02  [email protected]   welcome 2016-07-01 02:01:03       0     0
10 2016-07-01 14:45:04  [email protected]  subject3 2016-07-01 10:17:05       0     0
11 2016-07-01 14:45:04  [email protected]  subject3 2016-07-01 14:45:05       0     0
12 2016-07-01 08:14:02  [email protected]  subject2 2016-07-01 08:16:00       0     0
13 2016-07-01 16:26:35  [email protected]  subject4 2016-07-01 17:00:00       0     0

Теперь вы можете взять абсолютное значение разницы во временных метках для каждой строки:

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

а затем использовать

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

найти строки с минимальной разницей для каждой группы на основе['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,[email protected],subject3
2016-07-01 02:01:02,[email protected],welcome
2016-07-01 14:45:04,[email protected],subject3
2016-07-01 08:14:02,[email protected],subject2
2016-07-01 16:26:35,[email protected],subject4
2016-07-01 10:17:00,[email protected],subject3
2016-07-01 02:01:02,[email protected],welcome
2016-07-01 14:45:04,[email protected],subject3
2016-07-01 08:14:02,[email protected],subject2
2016-07-01 16:26:35,[email protected],subject4
"""

b = """timestamp,email,subject,clicks,var1
2016-07-01 02:01:14,[email protected],welcome,1,1
2016-07-01 08:15:48,[email protected],subject2,2,2
2016-07-01 10:17:39,[email protected],subject3,1,7
2016-07-01 14:46:01,[email protected],subject3,1,2
2016-07-01 16:27:28,[email protected],subject4,1,2
2016-07-01 10:17:05,[email protected],subject3,0,0
2016-07-01 02:01:03,[email protected],welcome,0,0
2016-07-01 14:45:05,[email protected],subject3,0,0
2016-07-01 08:16:00,[email protected],subject2,0,0
2016-07-01 17:00:00,[email protected],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  [email protected]  subject3       1     7
2  2016-07-01 02:01:02  [email protected]   welcome       1     1
4  2016-07-01 14:45:04  [email protected]  subject3       1     2
5  2016-07-01 08:14:02  [email protected]  subject2       2     2
6  2016-07-01 16:26:35  [email protected]  subject4       1     2
7  2016-07-01 10:17:00  [email protected]  subject3       0     0
9  2016-07-01 02:01:02  [email protected]   welcome       0     0
11 2016-07-01 14:45:04  [email protected]  subject3       0     0
12 2016-07-01 08:14:02  [email protected]  subject2       0     0
13 2016-07-01 16:26:35  [email protected]  subject4       0     0
 TinaW06 авг. 2016 г., 23:24
Большое спасибо !!!

пы «электронная почта» и «тема»

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

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

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

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
    time_series = time_series.values
    timepoint = np.datetime64(timepoint)
    deltas = np.abs(np.subtract(time_series, timepoint))
    idx_closest_date = np.argmin(deltas)
    res = {"closest_date": time_series[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)

# Then group df1 as needed
grouped = df1.groupby(['email', 'subject'])

# Finally loop over the group items, finding the closest timestamps
join_ts = pd.DataFrame()
for name, group in grouped:
    try:
        join_ts = pd.concat([join_ts, group['timestamp']\
                             .apply(find_closest_date, time_series=df2.loc[name, 'timestamp'])],
                            axis=0)
    except KeyError:
        pass

df3 = pd.merge(pd.concat([df1, join_ts], axis=1), df2, left_on=['closest_date'], right_on=['timestamp'])
 TinaW06 авг. 2016 г., 23:57
Большое спасибо !!!
 Kartik06 авг. 2016 г., 21:25
Так что это дает? Ошибка, что-то еще? Можете ли вы быть более конкретным? Пожалуйста.
 TinaW06 авг. 2016 г., 22:54
Я добавил небольшой пример к вопросу, который показывает разницу более четко. Проблема по-прежнему исходит от функции find_closest_date, которая не выбирает правильную дату.
 TinaW06 авг. 2016 г., 22:18
Спасибо, Катик! Это очень полезно! Вывод почти правильный. Просто отметьте, что [email protected] должен иметь только 0 кликов & var1. сравнитьdf3.sort_values(['email_x']) с ожидаемым результатом. адрес электронной почты B имеет нули во всех случаях.
 TinaW06 авг. 2016 г., 21:29
Картинка в моем посте показывает ожидаемый результат. Основная проблема заключается в том, что самая близкая временная метка неверна, так как она не учитывает 2 других измерения, таких как электронная почта и тема. Если вы посмотрите на результат вашего внутреннего объединения, оно содержит только 5 электронных писем, но оно должно показывать 10. (см. Рисунок в моем посте).
 Kartik06 авг. 2016 г., 21:31
О, понял! Вы хотите ближайшие метки времени для данного письма и темы. Я редактирую свой ответ, он должен работать после редактирования.
 Kartik06 авг. 2016 г., 23:50
Было несколько проблем: 1.find_closest_date функции не былоMultiIndex и 2.append не работал, как я себе это представлял. Я отредактировал свой ответ, и он должен работать в качестве замены вашего кода.
 TinaW06 авг. 2016 г., 21:24
Извините, это не дает ожидаемого результата.

Ваш ответ на вопрос