3
\$\begingroup\$

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',))
\$\endgroup\$
1
  • \$\begingroup\$ Please edit your title so that it states the purpose of your code* and not what you expect in the review, that belongs in the body. \$\endgroup\$ – Aryan Parekh Oct 15 '20 at 18:35
3
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.