Merging Two Pandas Dataframes With Complex Conditions

I would like to merge two dataframes. Let's consider the following two dfs: df1: id_A, ts_A, course, weight id1, 2017-04-27 01:35:30, cotton, 3.5 id1, 2017-04

Solution 1:

I think you need merge_asof, but for counter is used reset_index for unique value per row in df1:

df1 = df1.reset_index(drop=True)
print (df1.index)
RangeIndex(start=0, stop=8, step=1)

df = pd.merge_asof(df2_sorted, 
                   df1.reset_index(), 
                   left_on='ts_B', 
                   right_on='ts_A', 
                   left_by='id_B', 
                   right_by='id_A')

And then groupby by output columns (dont forget for index column) and aggregate mean:

df = df.groupby(['id_A','ts_A', 'course', 'weight', 'index'], as_index=False)['value']
       .mean()
       .drop('index', axis=1)
print (df)
  id_A                ts_A       course  weight        value
0  id1 2017-04-27 01:35:30       cotton     3.5   263.333333
1  id1 2017-04-27 01:36:05       cotton     3.5   600.000000
2  id1 2017-04-27 01:36:55       cotton     3.5   950.000000
3  id2 2017-04-27 02:35:35  cotton blue     5.0  1500.000000
4  id2 2017-04-27 02:36:00  cotton blue     5.0  4625.000000
5  id2 2017-04-27 02:36:35  cotton blue     5.0  5565.500000