python - Pandas 合并日期之间的列

Question

我有两个数据框 - 一个是给客户打电话，另一个是识别客户的事件服务持续时间。每个客户端可以有多个服务，但它们不会重叠。

df_calls = pd.DataFrame([['A','2016-02-03',1],['A','2016-05-11',2],['A','2016-10-01',3],['A','2016-11-02',4],
                        ['B','2016-01-10',5],['B','2016-04-25',6]], columns = ['cust_id','call_date','call_id'])

print df_calls

  cust_id   call_date  call_id
0       A  2016-02-03        1
1       A  2016-05-11        2
2       A  2016-10-01        3
3       A  2016-11-02        4
4       B  2016-01-10        5
5       B  2016-04-25        6

和

df_active = pd.DataFrame([['A','2016-01-10','2016-03-15',1],['A','2016-09-10','2016-11-15',2],
                          ['B','2016-01-02','2016-03-17',3]], columns = ['cust_id','service_start','service_end','service_id'])


print df_active

  cust_id service_start service_end  service_id
0       A    2016-01-10  2016-03-15           1
1       A    2016-09-10  2016-11-15           2
2       B    2016-01-02  2016-03-17           3

我需要找到每个调用所属的 service_id，由 service_start 和 service_end 日期标识。如果通话不在日期之间，则它们应保留在数据集中。

到目前为止，这是我尝试过的:

df_test_output = pd.merge(df_calls,df_active, how = 'left',on = ['cust_id'])
df_test_output = df_test_output[(df_test_output['call_date']>= df_test_output['service_start']) 
                      & (df_test_output['call_date']<= df_test_output['service_end'])].drop(['service_start','service_end'],axis = 1)

print df_test_output

  cust_id   call_date  call_id  service_id
0       A  2016-02-03        1           1
5       A  2016-10-01        3           2
7       A  2016-11-02        4           2
8       B  2016-01-10        5           3

这会丢弃所有不在服务日期之间的调用。关于如何合并符合条件的 service_id 但保留剩余记录的任何想法？

结果应该是这样的:

#do black magic

print df_calls

cust_id   call_date  call_id  service_id
0       A  2016-02-03        1         1.0
1       A  2016-05-11        2         NaN
2       A  2016-10-01        3         2.0
3       A  2016-11-02        4         2.0
4       B  2016-01-10        5         3.0
5       B  2016-04-25        6         NaN

Answer 1

您可以使用 merge左连接:

print (pd.merge(df_calls, df_calls2, how='left'))
  cust_id  call_date  call_id  service_id
0       A 2016-02-03        1         1.0
1       A 2016-05-11        2         NaN
2       A 2016-10-01        3         2.0
3       A 2016-11-02        4         2.0
4       B 2016-01-10        5         3.0
5       B 2016-04-25        6         NaN