Pandas groupby() with conditions from another DataFrame

Question

I am trying to create a new column trial1['Return'] using the information from trial2. I need to get the product of the returns for specific ID within a given time range in trial1.

I have tried using groupby() with lambda as well just conditional refining. But both resulted in errors. The only way that works is the for loop. But I was wondering whether is a more efficient way of doing this.

import pandas as pd

trial1 = pd.DataFrame([[1,'2016-09-01','2016-09-05'],[1,'2016-09-03','2016-09-06'],[2,'2016-09-01','2016-09-05']] , columns=('Id','startDate','EndDate'))
trial1

trial2 = pd.DataFrame([[1,'2016-09-01',1.1],[1,'2016-09-02',1],[1,'2016-09-03',1],[1,'2016-09-04',1],[1,'2016-09-05',1],[1,'2016-09-06',1],[2,'2016-09-01',1.2],[2,'2016-09-02',1],[2,'2016-09-03',1],[2,'2016-09-04',1],[2,'2016-09-05',1]] , columns=('Id','Date','Return'))
trial2

trial1['EndDate'] = pd.to_datetime(trial1['EndDate'])
trial1['startDate'] = pd.to_datetime(trial1['startDate'])
trial2['Date'] = pd.to_datetime(trial2['Date'])

##This throws a Timestamp error
trial2_g = trial2.groupby('Id')
trial2_g.apply(lambda x: x[x['Date'].isin(pd.date_range(trial1['startDate'], trial1['EndDate']))]['Return'].prod())

##This throws a ValueError (can only compare identical-labeled series object)
trial2['Id'] = trial2['Id'].reset_index(drop=True)
trial1['Id'] = trial1['Id'].reset_index(drop=True)

trial1['Return'] = trial2[((trial2['Id']==trial1['Id']))
                    &(trial2['Date'].isin(pd.date_range(trial1['startDate'],trial1['EndDate'])))].prod()


##THIS WORKS AND THAT'S HOW I WANT IT TO LOOK LIKE
trial1['Return'] = 0
for nn in range(len(trial1)):
     trial1['Return'].loc[nn] = trial2.Return[(trial2.Id == trial1.Id[nn])
                  &(trial2.Date >= trial1.startDate[nn])
                  &(trial2.Date <= trial1.EndDate[nn])].prod()
trial1

Answer 1

I'd first set the index on trial2

t2 = trial2.set_index(['Id', 'Date'])

then use apply on trial1

trial1['Return'] = trial1.apply(
    lambda x: t2.xs(x.Id)[x.startDate:x.EndDate].prod(), 1)
trial1