This notebook continues the analysis carreid out in Mosseri-Marlio and Vasilev (2017) using ELSA wave 7 (2014-15) data. The proposed means test for council-funded support in the Conservative manifesto is applied to the data in order to assess the impact on eligibility. The income and assets of the different stakeholders are compared.
Specifically, we ask:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
%matplotlib inline
url_fdv = r"N:\Research\2016-17 programme\Value for money\2016 Funding models\2016 Social care\Data\ELSA\stata\stata13_se\wave_7_financial_derived_variables.dta"
df_fdv = pd.read_stata(url_fdv, convert_categoricals=False, convert_missing=False)
url_ifs_variables = r"N:\Research\2016-17 programme\Value for money\2016 Funding models\2016 Social care\Data\ELSA\stata\stata13_se\wave_7_ifs_derived_variables.dta"
df_ifs_variables = pd.read_stata(url_ifs_variables, convert_categoricals=False, convert_missing=False)
url_elsa = r"N:\Research\2016-17 programme\Value for money\2016 Funding models\2016 Social care\Data\ELSA\stata\stata13_se\wave_7_elsa_data.dta"
df_elsa = pd.read_stata(url_elsa, convert_categoricals=False, convert_missing=False)
merged = pd.concat([df_elsa.set_index("idauniq")[["WpDes", "futype", "DhCAg"]],
df_ifs_variables.set_index("idauniq")[["agebuhead"]],
df_fdv.set_index("idauniq")[["bueq", "grosshw_bu_s", "nethw_bu_s", "home_bu_i", "mgdebt_bu_s", "grosstotnhw_bu_s", "nettotnhw_bu_s", "totinc_bu_s", "eqtotinc_bu_s"]]]
, axis = 1).rename(columns={
"agebuhead": "Age",
"home_bu_i": "Net value of other properties",
"futype": "Couple status",
"bueq": "Equivalence scale",
"nettotnhw_bu_s": "Total net non-housing wealth",
"grosstotnhw_bu_s": "Total gross non-housing wealth",
"mgdebt_bu_s": "Primary housing mortgage debt",
"eqtotinc_bu_s": "Equivalised total income",
"totinc_bu_s": "Total net income",
"grosshw_bu_s": "Total gross primary housing wealth",
"nethw_bu_s": "Total net primary housing wealth",
"WpDes": "Work status"
}).dropna().astype(float)
merged["Equivalised total net housing wealth"] = merged["Total net primary housing wealth"].add(merged["Net value of other properties"]).div(merged["Equivalence scale"])
merged["Value of outstanding mortgage debt"] = merged["Primary housing mortgage debt"].mul(100).div(merged["Total gross primary housing wealth"]).fillna(0)
merged["Equivalised gross non-houisng wealth"] = merged["Total gross non-housing wealth"].div(merged["Equivalence scale"])
def single_couple(value):
if value == 2:
return 1
elif value == 3:
return 2
else:
return 1
merged["Single or couple"] = merged["Couple status"].apply(single_couple)
merged["Asset test: non-housing assets"] = merged["Total gross non-housing wealth"].div(merged["Single or couple"])
merged["Asset test: housing assets"] = merged["Total net primary housing wealth"].add(merged["Net value of other properties"]).div(merged["Single or couple"])
merged.head()
Work status | Couple status | DhCAg | Age | Equivalence scale | Total gross primary housing wealth | Total net primary housing wealth | Net value of other properties | Primary housing mortgage debt | Total gross non-housing wealth | Total net non-housing wealth | Total net income | Equivalised total income | Equivalised total net housing wealth | Value of outstanding mortgage debt | Equivalised gross non-houisng wealth | Single or couple | Asset test: non-housing assets | Asset test: housing assets | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
idauniq | |||||||||||||||||||
100001 | 2.0 | 3.0 | 44.0 | 63.0 | 1.5 | 0.0 | 0.0 | 0.0 | 0.0 | 515.0 | -5585.0 | 666.289490 | 444.192993 | 0.000000 | 0.0 | 343.333333 | 2 | 257.5 | 0.0 |
100007 | 1.0 | 3.0 | 42.0 | 66.0 | 1.5 | 140000.0 | 140000.0 | 500.0 | 0.0 | 127600.0 | 127200.0 | 502.407928 | 334.938629 | 93666.666667 | 0.0 | 85066.666667 | 2 | 63800.0 | 70250.0 |
100009 | 1.0 | 2.0 | 48.0 | 74.0 | 1.5 | 200000.0 | 200000.0 | 0.0 | 0.0 | 210000.0 | 210000.0 | 343.576935 | 229.051285 | 133333.333333 | 0.0 | 140000.000000 | 1 | 210000.0 | 200000.0 |
100023 | 2.0 | 2.0 | 32.0 | 65.0 | 1.5 | 160000.0 | 160000.0 | 0.0 | 0.0 | 48600.0 | 30600.0 | 896.135315 | 597.423523 | 106666.666667 | 0.0 | 32400.000000 | 1 | 48600.0 | 160000.0 |
100025 | 1.0 | 2.0 | 54.0 | 83.0 | 1.5 | 450000.0 | 450000.0 | 100000.0 | 0.0 | 125506.0 | 125506.0 | 433.162842 | 288.775238 | 366666.666667 | 0.0 | 83670.666667 | 1 | 125506.0 | 550000.0 |
merged["totalAssets"] = merged["Equivalised total net housing wealth"].add(merged["Total net non-housing wealth"])
merged[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth", "totalAssets", "Age"]].describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | totalAssets | Age | |
---|---|---|---|---|---|
count | 9417.000000 | 9.417000e+03 | 9.417000e+03 | 9.417000e+03 | 9417.000000 |
mean | 395.874714 | 1.945928e+05 | 1.707732e+05 | 3.637531e+05 | 68.328449 |
std | 279.563044 | 2.417663e+05 | 6.555226e+05 | 7.879194e+05 | 10.243660 |
min | -262.500031 | -1.466667e+05 | 0.000000e+00 | -1.466667e+05 | 29.000000 |
25% | 226.189377 | 7.600000e+04 | 6.900000e+03 | 9.663333e+04 | 61.000000 |
50% | 335.992493 | 1.466667e+05 | 3.950000e+04 | 2.076667e+05 | 67.000000 |
75% | 490.528687 | 2.500000e+05 | 1.440000e+05 | 3.898767e+05 | 76.000000 |
max | 4564.812988 | 5.333333e+06 | 3.541600e+07 | 3.586600e+07 | 99.000000 |
age_lim = 64
old = merged[
(merged["Age"] > age_lim)]
old[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth" ]].describe().round()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 5784.0 | 5784.0 | 5784.0 |
mean | 375.0 | 200627.0 | 181124.0 |
std | 253.0 | 233622.0 | 775539.0 |
min | -116.0 | -146667.0 | 0.0 |
25% | 224.0 | 86667.0 | 8500.0 |
50% | 314.0 | 160000.0 | 42000.0 |
75% | 452.0 | 250000.0 | 140000.0 |
max | 4565.0 | 5333333.0 | 35416000.0 |
upper_cap_lim = 23250
LA_old_dom_eligible = old[old[ "Asset test: non-housing assets"] < upper_cap_lim ]
LA_old_dom_eligible[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth", "totalAssets"]].round(2).describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | totalAssets | |
---|---|---|---|---|
count | 2706.000000 | 2.706000e+03 | 2706.000000 | 2.706000e+03 |
mean | 283.686426 | 1.152627e+05 | 11528.680525 | 1.259299e+05 |
std | 153.351539 | 1.221280e+05 | 11914.711088 | 1.248962e+05 |
min | 0.000000 | -1.466667e+05 | 0.000000 | -1.466667e+05 |
25% | 189.557500 | 0.000000e+00 | 2000.000000 | 1.253875e+04 |
50% | 250.920000 | 1.066667e+05 | 7350.000000 | 1.178333e+05 |
75% | 339.920000 | 1.666667e+05 | 17675.000000 | 1.794183e+05 |
max | 1732.210000 | 2.000030e+06 | 46250.000000 | 2.002530e+06 |
upper_cap_lim = 23250
LA_old_res_eligible = old[(old[ "Asset test: non-housing assets"].add(old["Asset test: housing assets"])) < upper_cap_lim ]
LA_old_res_eligible[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth"]].round(2).describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 785.000000 | 785.00000 | 785.000000 |
mean | 251.013057 | -239.38428 | 5610.419108 |
std | 130.268247 | 7529.75224 | 7980.521088 |
min | 0.000000 | -146666.67000 | 0.000000 |
25% | 168.480000 | 0.00000 | 300.000000 |
50% | 227.270000 | 0.00000 | 2300.000000 |
75% | 301.160000 | 0.00000 | 8000.000000 |
max | 1361.820000 | 20000.00000 | 45000.000000 |
The proportions eligible for domiciliary care and residential care (as a percentage of the whole cohort) are 13.6 and 46.8 per cent respectively.
Note: since there is only one means test under the manifesto proposals, there is only one group of beneficiaries.
upper_cap_lim = 100000
LA_old_dom_eligible_manifesto = old[(old[ "Asset test: non-housing assets"].add(old["Asset test: housing assets"])) < upper_cap_lim ]
LA_old_dom_eligible_manifesto[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth"]].round(2).describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 1589.000000 | 1589.000000 | 1589.000000 |
mean | 264.118357 | 35044.038943 | 13720.604154 |
std | 135.625985 | 43770.155330 | 20471.327349 |
min | 0.000000 | -146666.670000 | 0.000000 |
25% | 182.390000 | 0.000000 | 1000.000000 |
50% | 241.010000 | 0.000000 | 6000.000000 |
75% | 310.560000 | 76666.670000 | 17000.000000 |
max | 1431.930000 | 130000.000000 | 173000.000000 |
The proportion of people eligible for council-funded care under the manifesto proposlas is 27 per cent.
upper_cap_lim = 23250
new_cap_lim = 100000
old_lose = old[(old["Asset test: non-housing assets"] < upper_cap_lim) &
((old["Asset test: non-housing assets"]).add(old["Asset test: housing assets"]) > new_cap_lim)
]
old_lose[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth"]].round(2).describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 1280.000000 | 1.280000e+03 | 1280.000000 |
mean | 305.407273 | 2.025480e+05 | 15181.323828 |
std | 167.380602 | 1.218784e+05 | 12840.512772 |
min | 0.980000 | 6.666667e+04 | 0.000000 |
25% | 203.167500 | 1.333333e+05 | 4485.000000 |
50% | 269.140000 | 1.690000e+05 | 12000.000000 |
75% | 375.000000 | 2.333333e+05 | 22623.250000 |
max | 1732.210000 | 2.000030e+06 | 46250.000000 |
This group represents 22 per cent of people 65 and above.
upper_cap_lim = 23250
equity_cap = 35000
DPA_eligible_manifesto = old[(old["Asset test: non-housing assets"] < upper_cap_lim) &
(old["Asset test: housing assets"] > equity_cap) &
(old["Couple status"] == 1)
]
DPA_eligible_manifesto[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth"]].describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 603.000000 | 6.030000e+02 | 603.000000 |
mean | 251.253507 | 1.964800e+05 | 8354.616094 |
std | 129.165229 | 1.490076e+05 | 6893.552907 |
min | 0.057692 | 3.850000e+04 | 0.000000 |
25% | 175.000000 | 1.200000e+05 | 2000.000000 |
50% | 226.609528 | 1.693510e+05 | 7000.000000 |
75% | 298.570480 | 2.475000e+05 | 13640.000000 |
max | 1732.206665 | 2.000030e+06 | 23200.000000 |
upper_cap_lim = 100000
equity_cap = 35000
DPA_eligible_manifesto = old[(old["Asset test: non-housing assets"] < upper_cap_lim) &
(old["Asset test: housing assets"] > equity_cap) &
((old["Asset test: non-housing assets"]).add(old["Asset test: housing assets"]) > upper_cap_lim) &
(old["Couple status"] == 1)
]
DPA_eligible_manifesto[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth"]].describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 920.000000 | 9.200000e+02 | 920.000000 |
mean | 290.997869 | 2.336280e+05 | 27725.686418 |
std | 165.347878 | 1.745387e+05 | 26188.946148 |
min | 0.980769 | 5.000000e+04 | 0.000000 |
25% | 187.726021 | 1.500000e+05 | 6101.500000 |
50% | 254.659996 | 2.000000e+05 | 19102.500000 |
75% | 350.800812 | 2.750000e+05 | 45250.000000 |
max | 2536.050537 | 2.500000e+06 | 99000.000000 |
upper_cap_lim = 100000
equity_cap = 35000
DPA_eligible_manifesto_partner = old[(old["Asset test: non-housing assets"] < upper_cap_lim) &
((old["Asset test: non-housing assets"]).add(old["Asset test: housing assets"]) > upper_cap_lim) &
(old["Asset test: housing assets"] > equity_cap) ]
DPA_eligible_manifesto_partner[["Equivalised total income", "Equivalised total net housing wealth", "Total gross non-housing wealth"]].describe()
Equivalised total income | Equivalised total net housing wealth | Total gross non-housing wealth | |
---|---|---|---|
count | 2872.000000 | 2.872000e+03 | 2872.000000 |
mean | 353.186129 | 2.129056e+05 | 55678.903034 |
std | 184.044588 | 1.371275e+05 | 49937.951544 |
min | 0.980769 | 4.333333e+04 | 0.000000 |
25% | 232.807602 | 1.333333e+05 | 14198.750000 |
50% | 320.025314 | 1.800000e+05 | 42000.000000 |
75% | 436.588745 | 2.500000e+05 | 84000.000000 |
max | 2536.050537 | 2.500000e+06 | 198000.000000 |