I have apparently correct code that still runs for weeks on my data (tens of millions of rows). I show the entire code for reference (and maybe other gains to be made), but the key operation is in the loop between lines 66 and 79. Basically, if a spell (spent in hospital) extended over a single calendar month, I wanted to have separate lines counting the number of days spent in hospital for each of those calendar months.I thought things won't be this bad iterating over rows if I allocate space for all the new rows in a single step (the concatenation before the loop) and only reset values row by row in the loop. # -*- coding: utf-8 -*-import numpy as npimport pandas as pdall_treatments = list()filelist = ['slutenvard1997','slutenvard2011','slutenvard2012','slutenvard19982004','slutenvard20052010']tobacco_codes = '|'.join([C{}.format(i) for i in range(30, 40)] + [F17])nutrition_codes = '|'.join([D{}.format(i) for i in range(50, 54)] +  [E{}.format(i) for i in range(10, 15)] + [E{}.format(i) for i in range(40, 47)] +  [E{}.format(i) for i in range(50, 69)])mental_codes = 'F'alcohol_codes = '|'.join([K70] + [F0])circulatory_codes = 'I'dental_codes = '|'.join([K0{}.format(i) for i in range(2, 4)])accident_codes = '|'.join([X{}.format(i) for i in range(10, 60)] + [V] + [X0])selfharm_codes = '|'.join([X{}.format(i) for i in range(60, 85)])cancer_codes = 'C'endonutrimetab_codes = 'E'pregnancy_codes = 'O'other_stress_codes = '|'.join([J{}.format(i) for i in range(11, 48)] + [L{}.format(i) for i in range(20, 66)] + [K{}.format(i) for i in range(20, 60)] + [X{}.format(i) for i in range(86, 99)] + [Z{}.format(i) for i in range(10, 77)] + [R] + [J0] + [Z0])items = {}conds = ['tobacco','nutrition','mental','alcohol','circulatory','dental','accident','selfharm','cancer','endonutrimetab','pregnancy','other_stress']for c in conds:    items[c] = eval(c + '_codes')treatment_summaries = {item: list() for item in items.keys()}for file in filelist:    filename = '/PATH/' + file +'.txt'    treatments = pd.read_table(filename,usecols=[0,8,9,11])    if file == 'slutenvard20052010':        treatments.loc[treatments['INDATUMA']==20060230,'INDATUMA'] = 20060203        treatments.loc[treatments['INDATUMA']==20108024,'INDATUMA'] = 20100824    if file == 'slutenvard19982004':        treatments.loc[treatments['UTDATUMA']==2003071,'UTDATUMA'] = 20030701        treatments.loc[treatments['UTDATUMA']==2003091,'UTDATUMA'] = 20030901                treatments = treatments[(treatments['INDATUMA'] !='.') & (treatments['UTDATUMA'] > 19971231)]        treatments['INDATUMA'] = treatments['INDATUMA'].astype(float)    all_treatments.append(treatments)    del treatmentsall_treatments = pd.concat(all_treatments, ignore_index=True)print Remember datatypes for future use:print all_treatments.dtypesall_treatments['indate'] = pd.to_datetime(all_treatments['INDATUMA'], errors='coerce',format='%Y%m%d')all_treatments['outdate'] = pd.to_datetime(all_treatments['UTDATUMA'], errors='coerce',format='%Y%m%d')# Separating months:all_treatments['monthlyindate'] = all_treatments['indate']all_treatments['monthlyoutdate'] = all_treatments['outdate']micolix     = all_treatments.columns.get_loc('monthlyindate')mocolix     = all_treatments.columns.get_loc('monthlyoutdate')ocolix      = all_treatments.columns.get_loc('outdate')all_treatments['extramonths'] = 12*(all_treatments['outdate'].dt.year-all_treatments['indate'].dt.year)+(all_treatments['outdate'].dt.month-all_treatments['indate'].dt.month)emcolix     = all_treatments.columns.get_loc('extramonths')originalN   = len(all_treatments)newrowcount = int(all_treatments['extramonths'].sum())newN        = int(originalN+newrowcount)all_treatments  = pd.concat([all_treatments,all_treatments.iloc[:newrowcount,:]],ignore_index=True) # this fills the new rows with the wrong data instead of NaNs, but will be overwrittenBOMoffset = pd.tseries.offsets.MonthBegin()newrowix    = originalNfor i in range(0,originalN):    monthstoadd = all_treatments.iloc[i,emcolix].astype('int')    for x in range(0,monthstoadd):        all_treatments.iloc[newrowix,:] = all_treatments.iloc[i,:]        if x==0:            all_treatments.iloc[i,mocolix] = BOMoffset.rollforward(all_treatments.iloc[i,micolix])        all_treatments.iloc[newrowix,micolix] = BOMoffset.rollforward(all_treatments.iloc[i,micolix] + pd.tseries.offsets.DateOffset(months = x))        if x < monthstoadd-1:            all_treatments.iloc[newrowix,mocolix] = BOMoffset.rollforward(all_treatments.iloc[newrowix,micolix]+ pd.tseries.offsets.DateOffset(months = 1))        else:            all_treatments.iloc[newrowix,mocolix] = all_treatments.iloc[newrowix,ocolix]        newrowix += 1all_treatments['monthlyyear'] = all_treatments['monthlyindate'].dt.yearall_treatments['monthlymonth'] = all_treatments['monthlyindate'].dt.monthall_treatments['monthlystay'] = (all_treatments['monthlyoutdate']-all_treatments['monthlyindate']).astype('timedelta64[D]')# Cleaning up:all_treatments = all_treatments.drop(['INDATUMA','indate','UTDATUMA','outdate','extramonths'], axis=1)print Non-missing values across columns (missing will be dropped):print all_treatments.count(axis=0)all_treatments = all_treatments.dropna()treatment_summaries = {name: all_treatments[(all_treatments.DIAGNOS.str.contains('{0}'.format(code)))].groupby(by=['LopNr','monthlyyear','monthlymonth'],as_index=False,sort=False).sum().astype(int, copy=False,raise_on_error=False) for name, code in items.iteritems()}del all_treatments# Finally, save the aggregated results to files.[treatment_summaries[name].to_csv('PATH/inpatient_treatments_monthly_sliced_{0}.csv'.format(name)) for name in items.keys()]I haven't done extensive profiling of where the memory or processing bottlenecks are with the current model.
Slicing time spans into calendar months
python;performance;numpy;pandas