In a df comprised of the columns asset_id, event_start_date, event_end_date
,
I wish to add a forth column datediff
that for each asset_id
will capture how many days passed between a end_date
and the following start_date
for the same asset_id
, but in case that following start_date
is earlier than the current end_date
, I would like to capture the difference between the two start_dates. Dataset is sorted by (asset_id, start_date asc).
In Excel it would look something like:
I tried:
events['datediff'] = df.groupby('asset_id').apply(lambda x: x['event_start_date'].shift(-1)-x['event_end_date'] if x['event_start_date'].shift(-1)>x['event_end_date'] else x['event_start_date'].shift(-1)-x['event_start_date'] ). fillna(pd.Timedelta(seconds=0)).reset_index(drop=True)
But this is:
- not working. Throwing
ValueError: The truth value of a Series is ambiguous.
- so un-elegant.
Thanks!
Advertisement
Answer
df = pd.DataFrame({ 'asset_id':[0,0,1,1], 'event_start_date':['2019-07-08','2019-07-11','2019-07-15','2019-07-25'], 'event_end_date':['2019-07-08','2019-07-23','2019-07-29','2019-07-25'] }) df['event_end_date'] = pd.to_datetime(df['event_end_date']) df['event_start_date'] = pd.to_datetime(df['event_start_date']) df['next_start']=df.groupby('asset_id')['event_start_date'].shift(-1) df['date_diff'] = np.where( df['next_start']>df['event_end_date'], (df['next_start']-df['event_end_date']).dt.days, (df['next_start']-df['event_start_date']).dt.days ) df = df.drop(columns=['next_start']).fillna(0)