Construct a numpy array by repeating a 1 dimensional array sliced at different indices

Question

I have a pandas dataframe (called base_mortality) with 1 column and n rows, which is of the following form:

 age | death_prob --------------------------- 60 | 0.005925 61 | 0.006656 62 | 0.007474 63 | 0.008387 64 | 0.009405 65 | 0.010539 66 | 0.0118 67 | 0.013201 68 | 0.014756 69 | 0.016477 

age is the index and death_prob is the probability that a person who is a given age will die in the next year. I want to use these death probabilities to project the expected annuity payment that would be paid to an annuitant over the next t years.

Suppose I have 3 annuitants, whose names and ages are contained in a dictionary:

policy_holders = {'John' : 65, 'Mike': 67, 'Alan': 71} 

Then I would want to construct a new dataframe whose index is time (rather than age) which has 3 columns (one for each annuitant) and t rows (one for each time step). Each column should specify the probability of death for each policy holder at that time step. For example:

 John Mike Alan 0 0.010539 0.013201 0.020486 1 0.011800 0.014756 0.022807 2 0.013201 0.016477 0.025365 3 0.014756 0.018382 0.028179 4 0.016477 0.020486 0.031269 .. ... ... ... 96 1.000000 1.000000 1.000000 97 1.000000 1.000000 1.000000 98 1.000000 1.000000 1.000000 99 1.000000 1.000000 1.000000 100 1.000000 1.000000 1.000000 

At present, my code for doing this is as follows:

import pandas as pd base_mortality = pd.read_csv('/Users/joshchapman/PycharmProjects/VectorisedAnnuityModel/venv/assumptions/base_mortality.csv', index_col=['x']) policy_holders = {'John' : 65, 'Mike': 67, 'Alan': 71} out = pd.DataFrame(index=range(0,101)) for name, age in policy_holders.items(): out[name] = base_mortality.loc[age:].reset_index()['age'] out = out.fillna(1) print(out) 

However, my aim is to remove this loop and achieve this using vector operations (i.e. pandas and/or numpy functions). Any suggestions on how I might improve my code to work in this way would be great!

Answer 1

Enter pandas.cut. It returns the bin in which each event lies. You can even pass the labels directly. This way you can reduce it to a Python loop over the people:

import pandas as pd import numpy as np age_bins = range(59, 70) # one more than the probabilities death_prob = [0.005925, 0.006656, 0.007474, 0.008387, 0.009405, 0.010539, 0.0118, 0.013201, 0.014756, 0.016477] policy_holders = {'John' : 65, 'Mike': 67, 'Alan': 71} values = {name: pd.cut(range(age, age + 101), age_bins, labels=death_prob) for name, age in policy_holders.items()} out = pd.DataFrame(values, dtype=np.float64).fillna(1) print(out) # John Mike Alan # 0 0.010539 0.013201 1.0 # 1 0.011800 0.014756 1.0 # 2 0.013201 0.016477 1.0 # 3 0.014756 1.000000 1.0 # 4 0.016477 1.000000 1.0 # .. ... ... ... # 96 1.000000 1.000000 1.0 # 97 1.000000 1.000000 1.0 # 98 1.000000 1.000000 1.0 # 99 1.000000 1.000000 1.0 # 100 1.000000 1.000000 1.0 # # [101 rows x 3 columns] 

Note that the hin edges need to be one larger than the labels, because technically, this is interpreted as (59, 60], (60, 61], ..., i.e. including the right edge.