Conditional Cumulative Sums in Pandas
You use a complex conditions depending on variables. It is easy to find a vectorized way for simple cumulative sums, but I cannot imagine a nice way for the Cumulative NCO.
So I would revert to Python comprehensions:
data = [
{ 'Reference Age': ref,
'Outstanding Balance': df.loc[df.iloc[:,6]>=ref,'Balance'].sum(),
'Cumulative NCO': df.loc[(df.iloc[:,6]>=ref)&(df.iloc[:,7]<=ref),
'NCO'].sum() }
for ref in [85, 90, 95, 100]]
result = pd.DataFrame(data).set_index('Reference Age').T
It produces:
Reference Age 85 90 95 100
Cumulative NCO 25 60 40 25
Outstanding Balance 16500 13000 6500 1000