Given the following data frame:
import pandas as pd
df=pd.DataFrame({'A':['a','b','c'],
'first_date':['2015-08-31 00:00:00','2015-08-24 00:00:00','2015-08-25 00:00:00']})
df.first_date=pd.to_datetime(df.first_date) #(dtype='<M8[ns]')
df['last_date']=pd.to_datetime('5/6/2016') #(dtype='datetime64[ns]')
def fnl(x):
l = pd.date_range(x.loc['first_date'], x.loc['last_date'], freq='B')
return pd.Series([l])
df['range'] = df.apply(fnl, axis=1)
df
A first_date last_date range
0 a 2015-08-31 2016-05-06 DatetimeIndex(['2015-08-31', '2015-09-01', '20...
1 b 2015-08-24 2016-05-06 DatetimeIndex(['2015-08-24', '2015-08-25', '20...
2 c 2015-08-25 2016-05-06 DatetimeIndex(['2015-08-25', '2015-08-26', '20...
I'd like to have dates from exc (below) removed from df['range'] where the exc['A'] matches df['A'], for each date that falls into its corresponding range (i.e. if a date in exc['A'] is outside of its corresponding range in df['A'], it obviously could not be excluded.
exc=pd.DataFrame({'A':['a','a','b','b','c','c'],
'Exclusions':['2014-12-30 00:00:00','2015-08-31 00:00:00',\
'2015-08-25 00:00:00','2015-10-20 00:00:00',\
'2015-08-26 00:00:00','2016-10-05 00:00:00']
})
exc
A Exclusions
0 a 2014-12-30 00:00:00
1 a 2015-08-31 00:00:00
2 b 2015-08-25 00:00:00
3 b 2015-10-20 00:00:00
4 c 2015-08-26 00:00:00
5 c 2016-10-05 00:00:00
Desired result:
A first_date last_date range
0 a 2015-08-31 2016-05-06 DatetimeIndex(['2015-09-01', '2015-09-02', '20...
1 b 2015-08-24 2016-05-06 DatetimeIndex(['2015-08-24', '2015-08-26', '20...
2 c 2015-08-25 2016-05-06 DatetimeIndex(['2015-08-25', '2015-08-27', '20...
Thanks in advance!
I think you can first create new column
range
byconcat
and reshape bymelt
. Thenmerge
and filter byboolean indexing
with maskdf._merge == 'left_only'
: