ways to code for better speed of processing date comparisions / slicing in python

Question

I wrote the below code to convert a long data frame containing start and end date event spans into a daily df with rows for each day:

output:

 **bene_id, day, death, hha** 
 row 1: abc, 1 ('2018-10-01'), 0,0
 row 2: abc, 2 ('2018-10-02'), 0,1
 row 3: abc, 3 ('2018-10-03'), 0,0
 row 4: abc, 4 ('2018-10-04'), 0,1

I am planning to use the daily output in a Tableau viz. The below code - which does work - heavily uses date comparisons and slicing - is very, very, very slow. Are there particular functions I am using that have faster alternatives? Both the for loop and the functions are slow..

from pandas import Timestamp, Series, date_range

#creates empty df for input with correct column order 
long = pd.DataFrame(columns={'bene_id', 'day','date'})
cols_to_order = ['bene_id', 'day','date']
new_columns = cols_to_order + (long.columns.drop(cols_to_order).tolist())
long = long[new_columns]

#gets only necessary columns for processing from main data set   
sample=s[['bene_id','event_type','event_thru_date','look_forward_90_date','service_epi_from_date','service_epi_thru_date']]

#creates the long daily table with count 1 to 90, and daily date freq 
for e in sample.bene_id.drop_duplicates():
    temp=sample[sample['bene_id']==e]
    start =Timestamp(temp[temp['event_type'] =='trigger'][['event_thru_date']].iloc[0][0])
    stop= temp[temp['event_type'] =='trigger'][['look_forward_90_date']]+pd.DateOffset(1)
    stop=Timestamp(stop.iloc[0][0])
    for i,j in zip(range(1,91), Series(date_range(start,stop))):
        long = long.append(pd.Series([e,i,j],index=cols_to_order), ignore_index=True)

#create functions to add events to daily df created above "long"; count first day of event span but not last date.
def checkdate(row,event):
    temp=sample[(sample['bene_id']==row['bene_id'])&(sample['event_type']==event)]
    temp['flag']= np.where((temp['service_epi_from_date']<=row['date']) &(temp['service_epi_thru_date']>row['date']),1,0)
    daily_status =temp['flag'].sum()
    return daily_status

def checkdeath(row,event):
    temp=sample[(sample['bene_id']==row['bene_id'])&(sample['event_type']==event)]
    temp['flag']= np.where(temp['service_epi_from_date']<=row['date'],1,0)
    daily_status =temp['flag'].sum()
    return daily_status

#apply functions referencing events in original sample df 
long['death']=long.apply(checkdeath, axis=1, args=('death',))
long['hha']=long.apply(checkdate, axis=1, args=('hha',))

Answer 1

There are a few small niceties that you can add.

long = pd.DataFrame(columns={'bene_id', 'day','date'})
cols_to_order = ['bene_id', 'day','date']

should reuse the list:

cols_to_order = ['bene_id', 'day','date']
long = pd.DataFrame(columns=set(cols_to_order))

This:

cols_to_order + (long.columns.drop(cols_to_order).tolist())

can drop the outer parens, since . takes precedence over +.

e, j and temp need better names.

This:

for i,j in zip(range(1,91), Series(date_range(start,stop))):
    long = long.append(pd.Series([e,i,j],index=cols_to_order), ignore_index=True)

shouldn't be a loop. There's almost certainly a way for Pandas to vectorize it.