Блог

Я столкнулся со сложной проблемой. У меня есть первый фрейм данных, в котором у меня есть клиенты (обратите внимание, что идентификатор клиента не уникален, у вас может быть тот же идентификатор клиента, связанный с другим TestDate) :

df1 :

 ClientID  TestDate
1A        2019-12-24
1B        2019-08-26
1B        2020-01-12
  

У меня есть другой фрейм данных «операций», указывающий дату и какой клиент задействован

df2 :

 LineNumber  ClientID  Date          Amount
1           1A        2020-01-12    50
2           1A        2019-09-24    15
3           1A        2019-12-25    20
4           1A        2018-12-30    30
5           1B        2018-12-30    60
6           1B        2019-12-12    40
  

Я хочу добавить в df1 столбец, содержащий среднее значение и количество строк, но принимающий только строки df2, в которых Date < TestDate

Например, для клиента 1A я возьму только номера строк 2 и 4 (поскольку даты строк 1 и 3 позже, чем TestDate), а затем получу следующий вывод для df1 :

Ожидаемый df1 :

 ClientID  TestDate      NumberOp  MeanOp
1A        2019-12-24    2         22.5
1B        2019-08-26    1         60
1B        2020-01-12    2         50
  

Примечание : С первой строкой клиента 1B, поскольку TestDate является 2019-08-26 , отображается только одна операция (операция lineNumber 6 выполняется в 2019-12-12 , поэтому ПОСЛЕ TestDate, поэтому я не принимаю это во внимание).

У меня уже есть код для этого, но я должен использовать iterrows на моем df1 , что занимает целую вечность :

Текущий код (рабочий, но длинный) :

 for index, row in df1.iterrows():
    id = row['ClientID']
    date = row['TestDate']
    df2_known = df2.loc[df2['ClientID'] == id]
    df2_known = df2_known.loc[df2_known['Date'] < date]
    df1.loc[index, 'NumberOp'] = df2_known.shape[0]
    df1.loc[index, 'MeanOp'] = df2_known['Amount'].mean()
  

У меня была идея использовать агрегаты и такие команды, как mean и count , но тот факт, что я должен фильтровать по дате для каждой строки, является огромной проблемой, которую я не могу понять.
Заранее большое спасибо за помощь.

Редактировать: оставшаяся проблема :

Исправление, указанное в редактировании ответа («на случай, если вы хотите сохранить недостающие совпадающие ключи df2»), не соответствует моей проблеме.

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

 df = df2.merge(df1, on=['ClientID'], how='right')
print(df[df['ClientID'] == '5C'])

Output :
ClientID  TestDate    Date          Amount
5C        2019-12-12  2020-01-12    50     
  

Если я выполню groupby и transform , как указано в ответе, в моем выводе не будет строки с CliendID == '5C' , потому что Date < TestDate и Date is null никогда не происходит, поэтому строка теряется, когда я делаю df = df[(df['Date']<df['TestDate']) | (df['Date'].isnull())] . Я лично хочу, чтобы в моем конечном выводе была строка с CliendID == '5C' , которая выглядела бы так :

 ClientID  TestDate      NumberOp  MeanOp
5C        2019-12-12    0         NaN
  

Вы можете объединять и преобразовывать:

 df = df2.merge(df1, on=['ClientID'])
#filter based on condition
df = df[df['Date']<df['TestDate']]
#get the mean and count into new columns
df['MeanOp'] = df.groupby(['ClientID'])['Amount'].transform('mean')
df['NumberOp'] = df.groupby(['ClientID'])['Amount'].transform('count')
#drop duplicates and irrelevant columns
df = df.drop(['Amount','Date','LineNumber'],1).drop_duplicates()
  

вывод:

   ClientID    TestDate  MeanOp  NumberOp
1       1A  2019-12-24    22.5         2
4       1B  2019-08-26    70.0         1
  

РЕДАКТИРОВАТЬ: в случае, если вы хотите сохранить недостающие совпадающие ключи df2 :

 df = df2.merge(df1, on=['ClientID'], how='right')
df = df[(df['Date']<df['TestDate']) | (df['Date'].isnull())]
df['MeanOp'] = df.groupby(['ClientID'])['Amount'].transform('mean')
df['NumberOp'] = df.groupby(['ClientID'])['Amount'].transform('count')
df = df.drop(['Amount','Date','LineNumber'],1).drop_duplicates()
  

пример:

 df1:

  ClientID    TestDate
0       1A  2019-12-24
1       1B  2019-08-26
2       1C  2019-08-26

output:

  ClientID    TestDate  MeanOp  NumberOp
1       1A  2019-12-24    22.5         2
4       1B  2019-08-26    70.0         1
5       1C  2019-08-26     NaN         0
  

ОБНОВЛЕНИЕ: на основе редактирования в сообщении, если вы хотите сгруппировать их по (Client_ID, TestDate) :

 df = df2.merge(df1, on=['ClientID'], how='right')
df = df[(df['Date']<df['TestDate']) | (df['Date'].isnull())]
df['MeanOp'] = df.groupby(['ClientID','TestDate'])['Amount'].transform('mean')
df['NumberOp'] = df.groupby(['ClientID','TestDate'])['Amount'].transform('count')
df = df.drop(['Amount','Date','LineNumber'],1).drop_duplicates()
  

вывод:

 df1
  ClientID    TestDate
0       1A  2019-12-24
1       1B  2019-08-26
2       1B  2020-01-12
3       1C  2019-08-26

df2
   LineNumber ClientID        Date  Amount
0           1       1A  2020-01-12      50
1           2       1A  2019-09-24      15
2           3       1A  2019-12-25      20
3           4       1A  2018-12-30      30
4           5       1B  2018-12-30      60
5           6       1B  2019-12-12      40

df
  ClientID    TestDate  MeanOp  NumberOp
1       1A  2019-12-24    22.5         2
4       1B  2019-08-26    60.0         1
6       1B  2020-01-12    50.0         2
8       1C  2019-08-26     NaN         0