Social Care - ELSA data analysis

{"metadata": {"language_info": {"mimetype": "text/x-python", "pygments_lexer": "ipython3", "name": "python", "file_extension": ".py", "nbconvert_exporter": "python", "codemirror_mode": {"name": "ipython", "version": 3}, "version": "3.4.5"}, "kernelspec": {"language": "python", "display_name": "Python 3", "name": "python3"}}, "nbformat": 4, "cells": [{"cell_type": "markdown", "metadata": {}, "source": "##What does the Conservative manifesto mean for social care?\n\n\nThis notebook continues the analysis carreid out in [Mosseri-Marlio and Vasilev (2017)](http://www.reform.uk/wp-content/uploads/2017/03/Social-care-funding-report.pdf) using [ELSA wave 7](https://www.elsa-project.ac.uk/) (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.\n\nSpecifically, we ask:\n\n* What is the distribution of income and assets across those aged 65 and above?\n* What proportion of the 65s and above is currently eligible for domiciliary and residential care respectively?\n* What proportion of the 65s and above would be eligible for residential and domiciliary care after the reforms are implemented?\n* How would eligibility for DPAs change if the single-inhabitant requirement is dropped?\n"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": true}, "execution_count": 1, "outputs": [], "source": "import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\n\n%matplotlib inline"}, {"cell_type": "markdown", "metadata": {}, "source": "##Load the data"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 2, "outputs": [], "source": "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\"\ndf_fdv = pd.read_stata(url_fdv, convert_categoricals=False, convert_missing=False)\n\nurl_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\"\ndf_ifs_variables = pd.read_stata(url_ifs_variables, convert_categoricals=False, convert_missing=False)\n\nurl_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\"\ndf_elsa = pd.read_stata(url_elsa, convert_categoricals=False, convert_missing=False)"}, {"cell_type": "markdown", "metadata": {}, "source": "##Define housing and non-housing wealth"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 3, "outputs": [{"metadata": {}, "execution_count": 3, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Work status</th>\n      <th>Couple status</th>\n      <th>DhCAg</th>\n      <th>Age</th>\n      <th>Equivalence scale</th>\n      <th>Total gross primary housing wealth</th>\n      <th>Total net primary housing wealth</th>\n      <th>Net value of other properties</th>\n      <th>Primary housing mortgage debt</th>\n      <th>Total gross non-housing wealth</th>\n      <th>Total net non-housing wealth</th>\n      <th>Total net income</th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Value of outstanding mortgage debt</th>\n      <th>Equivalised gross non-houisng wealth</th>\n      <th>Single or couple</th>\n      <th>Asset test: non-housing assets</th>\n      <th>Asset test: housing assets</th>\n    </tr>\n    <tr>\n      <th>idauniq</th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>100001</th>\n      <td>2.0</td>\n      <td>3.0</td>\n      <td>44.0</td>\n      <td>63.0</td>\n      <td>1.5</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>515.0</td>\n      <td>-5585.0</td>\n      <td>666.289490</td>\n      <td>444.192993</td>\n      <td>0.000000</td>\n      <td>0.0</td>\n      <td>343.333333</td>\n      <td>2</td>\n      <td>257.5</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>100007</th>\n      <td>1.0</td>\n      <td>3.0</td>\n      <td>42.0</td>\n      <td>66.0</td>\n      <td>1.5</td>\n      <td>140000.0</td>\n      <td>140000.0</td>\n      <td>500.0</td>\n      <td>0.0</td>\n      <td>127600.0</td>\n      <td>127200.0</td>\n      <td>502.407928</td>\n      <td>334.938629</td>\n      <td>93666.666667</td>\n      <td>0.0</td>\n      <td>85066.666667</td>\n      <td>2</td>\n      <td>63800.0</td>\n      <td>70250.0</td>\n    </tr>\n    <tr>\n      <th>100009</th>\n      <td>1.0</td>\n      <td>2.0</td>\n      <td>48.0</td>\n      <td>74.0</td>\n      <td>1.5</td>\n      <td>200000.0</td>\n      <td>200000.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>210000.0</td>\n      <td>210000.0</td>\n      <td>343.576935</td>\n      <td>229.051285</td>\n      <td>133333.333333</td>\n      <td>0.0</td>\n      <td>140000.000000</td>\n      <td>1</td>\n      <td>210000.0</td>\n      <td>200000.0</td>\n    </tr>\n    <tr>\n      <th>100023</th>\n      <td>2.0</td>\n      <td>2.0</td>\n      <td>32.0</td>\n      <td>65.0</td>\n      <td>1.5</td>\n      <td>160000.0</td>\n      <td>160000.0</td>\n      <td>0.0</td>\n      <td>0.0</td>\n      <td>48600.0</td>\n      <td>30600.0</td>\n      <td>896.135315</td>\n      <td>597.423523</td>\n      <td>106666.666667</td>\n      <td>0.0</td>\n      <td>32400.000000</td>\n      <td>1</td>\n      <td>48600.0</td>\n      <td>160000.0</td>\n    </tr>\n    <tr>\n      <th>100025</th>\n      <td>1.0</td>\n      <td>2.0</td>\n      <td>54.0</td>\n      <td>83.0</td>\n      <td>1.5</td>\n      <td>450000.0</td>\n      <td>450000.0</td>\n      <td>100000.0</td>\n      <td>0.0</td>\n      <td>125506.0</td>\n      <td>125506.0</td>\n      <td>433.162842</td>\n      <td>288.775238</td>\n      <td>366666.666667</td>\n      <td>0.0</td>\n      <td>83670.666667</td>\n      <td>1</td>\n      <td>125506.0</td>\n      <td>550000.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "         Work status  Couple status  DhCAg   Age  Equivalence scale  \\\nidauniq                                                               \n100001           2.0            3.0   44.0  63.0                1.5   \n100007           1.0            3.0   42.0  66.0                1.5   \n100009           1.0            2.0   48.0  74.0                1.5   \n100023           2.0            2.0   32.0  65.0                1.5   \n100025           1.0            2.0   54.0  83.0                1.5   \n\n         Total gross primary housing wealth  Total net primary housing wealth  \\\nidauniq                                                                         \n100001                                  0.0                               0.0   \n100007                             140000.0                          140000.0   \n100009                             200000.0                          200000.0   \n100023                             160000.0                          160000.0   \n100025                             450000.0                          450000.0   \n\n         Net value of other properties  Primary housing mortgage debt  \\\nidauniq                                                                 \n100001                             0.0                            0.0   \n100007                           500.0                            0.0   \n100009                             0.0                            0.0   \n100023                             0.0                            0.0   \n100025                        100000.0                            0.0   \n\n         Total gross non-housing wealth  Total net non-housing wealth  \\\nidauniq                                                                 \n100001                            515.0                       -5585.0   \n100007                         127600.0                      127200.0   \n100009                         210000.0                      210000.0   \n100023                          48600.0                       30600.0   \n100025                         125506.0                      125506.0   \n\n         Total net income  Equivalised total income  \\\nidauniq                                               \n100001         666.289490                444.192993   \n100007         502.407928                334.938629   \n100009         343.576935                229.051285   \n100023         896.135315                597.423523   \n100025         433.162842                288.775238   \n\n         Equivalised total net housing wealth  \\\nidauniq                                         \n100001                               0.000000   \n100007                           93666.666667   \n100009                          133333.333333   \n100023                          106666.666667   \n100025                          366666.666667   \n\n         Value of outstanding mortgage debt  \\\nidauniq                                       \n100001                                  0.0   \n100007                                  0.0   \n100009                                  0.0   \n100023                                  0.0   \n100025                                  0.0   \n\n         Equivalised gross non-houisng wealth  Single or couple  \\\nidauniq                                                           \n100001                             343.333333                 2   \n100007                           85066.666667                 2   \n100009                          140000.000000                 1   \n100023                           32400.000000                 1   \n100025                           83670.666667                 1   \n\n         Asset test: non-housing assets  Asset test: housing assets  \nidauniq                                                              \n100001                            257.5                         0.0  \n100007                          63800.0                     70250.0  \n100009                         210000.0                    200000.0  \n100023                          48600.0                    160000.0  \n100025                         125506.0                    550000.0  "}, "output_type": "execute_result"}], "source": "merged = pd.concat([df_elsa.set_index(\"idauniq\")[[\"WpDes\", \"futype\", \"DhCAg\"]],\n                     df_ifs_variables.set_index(\"idauniq\")[[\"agebuhead\"]],\n                     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\"]]]\n                    , axis = 1).rename(columns={\n                                \"agebuhead\": \"Age\",\n                                \"home_bu_i\": \"Net value of other properties\",\n                                \"futype\": \"Couple status\",\n                                \"bueq\": \"Equivalence scale\",\n                                \"nettotnhw_bu_s\": \"Total net non-housing wealth\",\n                                \"grosstotnhw_bu_s\": \"Total gross non-housing wealth\",\n                                \"mgdebt_bu_s\": \"Primary housing mortgage debt\",\n                                \"eqtotinc_bu_s\": \"Equivalised total income\",\n                                \"totinc_bu_s\": \"Total net income\",\n                                \"grosshw_bu_s\": \"Total gross primary housing wealth\",\n                                \"nethw_bu_s\": \"Total net primary housing wealth\",\n                                \"WpDes\": \"Work status\"\n                }).dropna().astype(float)\n\nmerged[\"Equivalised total net housing wealth\"] = merged[\"Total net primary housing wealth\"].add(merged[\"Net value of other properties\"]).div(merged[\"Equivalence scale\"])\nmerged[\"Value of outstanding mortgage debt\"] = merged[\"Primary housing mortgage debt\"].mul(100).div(merged[\"Total gross primary housing wealth\"]).fillna(0)\nmerged[\"Equivalised gross non-houisng wealth\"] = merged[\"Total gross non-housing wealth\"].div(merged[\"Equivalence scale\"])\n\ndef single_couple(value):\n    if value == 2:\n        return 1\n    elif value == 3:\n        return 2\n    else:\n        return 1\n    \nmerged[\"Single or couple\"] = merged[\"Couple status\"].apply(single_couple)\nmerged[\"Asset test: non-housing assets\"] = merged[\"Total gross non-housing wealth\"].div(merged[\"Single or couple\"])\nmerged[\"Asset test: housing assets\"] = merged[\"Total net primary housing wealth\"].add(merged[\"Net value of other properties\"]).div(merged[\"Single or couple\"])\n\nmerged.head()"}, {"cell_type": "markdown", "metadata": {}, "source": "# Whole cohort"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 13, "outputs": [{"metadata": {}, "execution_count": 13, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n      <th>totalAssets</th>\n      <th>Age</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>9417.000000</td>\n      <td>9.417000e+03</td>\n      <td>9.417000e+03</td>\n      <td>9.417000e+03</td>\n      <td>9417.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>395.874714</td>\n      <td>1.945928e+05</td>\n      <td>1.707732e+05</td>\n      <td>3.637531e+05</td>\n      <td>68.328449</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>279.563044</td>\n      <td>2.417663e+05</td>\n      <td>6.555226e+05</td>\n      <td>7.879194e+05</td>\n      <td>10.243660</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>-262.500031</td>\n      <td>-1.466667e+05</td>\n      <td>0.000000e+00</td>\n      <td>-1.466667e+05</td>\n      <td>29.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>226.189377</td>\n      <td>7.600000e+04</td>\n      <td>6.900000e+03</td>\n      <td>9.663333e+04</td>\n      <td>61.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>335.992493</td>\n      <td>1.466667e+05</td>\n      <td>3.950000e+04</td>\n      <td>2.076667e+05</td>\n      <td>67.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>490.528687</td>\n      <td>2.500000e+05</td>\n      <td>1.440000e+05</td>\n      <td>3.898767e+05</td>\n      <td>76.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>4564.812988</td>\n      <td>5.333333e+06</td>\n      <td>3.541600e+07</td>\n      <td>3.586600e+07</td>\n      <td>99.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount               9417.000000                          9.417000e+03   \nmean                 395.874714                          1.945928e+05   \nstd                  279.563044                          2.417663e+05   \nmin                 -262.500031                         -1.466667e+05   \n25%                  226.189377                          7.600000e+04   \n50%                  335.992493                          1.466667e+05   \n75%                  490.528687                          2.500000e+05   \nmax                 4564.812988                          5.333333e+06   \n\n       Total gross non-housing wealth   totalAssets          Age  \ncount                    9.417000e+03  9.417000e+03  9417.000000  \nmean                     1.707732e+05  3.637531e+05    68.328449  \nstd                      6.555226e+05  7.879194e+05    10.243660  \nmin                      0.000000e+00 -1.466667e+05    29.000000  \n25%                      6.900000e+03  9.663333e+04    61.000000  \n50%                      3.950000e+04  2.076667e+05    67.000000  \n75%                      1.440000e+05  3.898767e+05    76.000000  \nmax                      3.541600e+07  3.586600e+07    99.000000  "}, "output_type": "execute_result"}], "source": "merged[\"totalAssets\"] = merged[\"Equivalised total net housing wealth\"].add(merged[\"Total net non-housing wealth\"])\n\nmerged[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\", \"totalAssets\", \"Age\"]].describe()"}, {"cell_type": "markdown", "metadata": {}, "source": "##Restricting the sample to those on or over the age of 65:"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 5, "outputs": [{"metadata": {}, "execution_count": 5, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>5784.0</td>\n      <td>5784.0</td>\n      <td>5784.0</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>375.0</td>\n      <td>200627.0</td>\n      <td>181124.0</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>253.0</td>\n      <td>233622.0</td>\n      <td>775539.0</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>-116.0</td>\n      <td>-146667.0</td>\n      <td>0.0</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>224.0</td>\n      <td>86667.0</td>\n      <td>8500.0</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>314.0</td>\n      <td>160000.0</td>\n      <td>42000.0</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>452.0</td>\n      <td>250000.0</td>\n      <td>140000.0</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>4565.0</td>\n      <td>5333333.0</td>\n      <td>35416000.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount                    5784.0                                5784.0   \nmean                      375.0                              200627.0   \nstd                       253.0                              233622.0   \nmin                      -116.0                             -146667.0   \n25%                       224.0                               86667.0   \n50%                       314.0                              160000.0   \n75%                       452.0                              250000.0   \nmax                      4565.0                             5333333.0   \n\n       Total gross non-housing wealth  \ncount                          5784.0  \nmean                         181124.0  \nstd                          775539.0  \nmin                               0.0  \n25%                            8500.0  \n50%                           42000.0  \n75%                          140000.0  \nmax                        35416000.0  "}, "output_type": "execute_result"}], "source": "age_lim = 64\n\n\nold = merged[\n    \n                (merged[\"Age\"] > age_lim)]\n          \nold[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\" ]].describe().round()"}, {"cell_type": "markdown", "metadata": {}, "source": "###Find how many of the over-64s are currently eligible for domiciliary care"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 6, "outputs": [{"metadata": {}, "execution_count": 6, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n      <th>totalAssets</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>2706.000000</td>\n      <td>2.706000e+03</td>\n      <td>2706.000000</td>\n      <td>2.706000e+03</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>283.686426</td>\n      <td>1.152627e+05</td>\n      <td>11528.680525</td>\n      <td>1.259299e+05</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>153.351539</td>\n      <td>1.221280e+05</td>\n      <td>11914.711088</td>\n      <td>1.248962e+05</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.000000</td>\n      <td>-1.466667e+05</td>\n      <td>0.000000</td>\n      <td>-1.466667e+05</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>189.557500</td>\n      <td>0.000000e+00</td>\n      <td>2000.000000</td>\n      <td>1.253875e+04</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>250.920000</td>\n      <td>1.066667e+05</td>\n      <td>7350.000000</td>\n      <td>1.178333e+05</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>339.920000</td>\n      <td>1.666667e+05</td>\n      <td>17675.000000</td>\n      <td>1.794183e+05</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>1732.210000</td>\n      <td>2.000030e+06</td>\n      <td>46250.000000</td>\n      <td>2.002530e+06</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount               2706.000000                          2.706000e+03   \nmean                 283.686426                          1.152627e+05   \nstd                  153.351539                          1.221280e+05   \nmin                    0.000000                         -1.466667e+05   \n25%                  189.557500                          0.000000e+00   \n50%                  250.920000                          1.066667e+05   \n75%                  339.920000                          1.666667e+05   \nmax                 1732.210000                          2.000030e+06   \n\n       Total gross non-housing wealth   totalAssets  \ncount                     2706.000000  2.706000e+03  \nmean                     11528.680525  1.259299e+05  \nstd                      11914.711088  1.248962e+05  \nmin                          0.000000 -1.466667e+05  \n25%                       2000.000000  1.253875e+04  \n50%                       7350.000000  1.178333e+05  \n75%                      17675.000000  1.794183e+05  \nmax                      46250.000000  2.002530e+06  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 23250\n\nLA_old_dom_eligible = old[old[ \"Asset test: non-housing assets\"] < upper_cap_lim ]\n\nLA_old_dom_eligible[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\", \"totalAssets\"]].round(2).describe()\n"}, {"cell_type": "markdown", "metadata": {}, "source": "###Find how many of the over-64s are currently eligible for residential care"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 7, "outputs": [{"metadata": {}, "execution_count": 7, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>785.000000</td>\n      <td>785.00000</td>\n      <td>785.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>251.013057</td>\n      <td>-239.38428</td>\n      <td>5610.419108</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>130.268247</td>\n      <td>7529.75224</td>\n      <td>7980.521088</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.000000</td>\n      <td>-146666.67000</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>168.480000</td>\n      <td>0.00000</td>\n      <td>300.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>227.270000</td>\n      <td>0.00000</td>\n      <td>2300.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>301.160000</td>\n      <td>0.00000</td>\n      <td>8000.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>1361.820000</td>\n      <td>20000.00000</td>\n      <td>45000.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount                785.000000                             785.00000   \nmean                 251.013057                            -239.38428   \nstd                  130.268247                            7529.75224   \nmin                    0.000000                         -146666.67000   \n25%                  168.480000                               0.00000   \n50%                  227.270000                               0.00000   \n75%                  301.160000                               0.00000   \nmax                 1361.820000                           20000.00000   \n\n       Total gross non-housing wealth  \ncount                      785.000000  \nmean                      5610.419108  \nstd                       7980.521088  \nmin                          0.000000  \n25%                        300.000000  \n50%                       2300.000000  \n75%                       8000.000000  \nmax                      45000.000000  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 23250\n\nLA_old_res_eligible = old[(old[ \"Asset test: non-housing assets\"].add(old[\"Asset test: housing assets\"])) < upper_cap_lim ]\n                          \n\nLA_old_res_eligible[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\"]].round(2).describe()"}, {"cell_type": "markdown", "metadata": {}, "source": "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."}, {"cell_type": "markdown", "metadata": {}, "source": "###Find how many of the over-64s would be eligible for domiciliary care under manifesto\n\nNote: since there is only one means test under the manifesto proposals, there is only one group of beneficiaries."}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 8, "outputs": [{"metadata": {}, "execution_count": 8, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>1589.000000</td>\n      <td>1589.000000</td>\n      <td>1589.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>264.118357</td>\n      <td>35044.038943</td>\n      <td>13720.604154</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>135.625985</td>\n      <td>43770.155330</td>\n      <td>20471.327349</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.000000</td>\n      <td>-146666.670000</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>182.390000</td>\n      <td>0.000000</td>\n      <td>1000.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>241.010000</td>\n      <td>0.000000</td>\n      <td>6000.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>310.560000</td>\n      <td>76666.670000</td>\n      <td>17000.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>1431.930000</td>\n      <td>130000.000000</td>\n      <td>173000.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount               1589.000000                           1589.000000   \nmean                 264.118357                          35044.038943   \nstd                  135.625985                          43770.155330   \nmin                    0.000000                        -146666.670000   \n25%                  182.390000                              0.000000   \n50%                  241.010000                              0.000000   \n75%                  310.560000                          76666.670000   \nmax                 1431.930000                         130000.000000   \n\n       Total gross non-housing wealth  \ncount                     1589.000000  \nmean                     13720.604154  \nstd                      20471.327349  \nmin                          0.000000  \n25%                       1000.000000  \n50%                       6000.000000  \n75%                      17000.000000  \nmax                     173000.000000  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 100000\n\nLA_old_dom_eligible_manifesto = old[(old[ \"Asset test: non-housing assets\"].add(old[\"Asset test: housing assets\"])) < upper_cap_lim ]\n\nLA_old_dom_eligible_manifesto[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\"]].round(2).describe()"}, {"cell_type": "markdown", "metadata": {}, "source": "The proportion of people eligible for council-funded care under the manifesto proposlas is 27 per cent."}, {"cell_type": "markdown", "metadata": {}, "source": "###Find the group that is currently eligible for immediate support, but will not pass the means test under the manifesto proposals"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 9, "outputs": [{"metadata": {}, "execution_count": 9, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>1280.000000</td>\n      <td>1.280000e+03</td>\n      <td>1280.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>305.407273</td>\n      <td>2.025480e+05</td>\n      <td>15181.323828</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>167.380602</td>\n      <td>1.218784e+05</td>\n      <td>12840.512772</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.980000</td>\n      <td>6.666667e+04</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>203.167500</td>\n      <td>1.333333e+05</td>\n      <td>4485.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>269.140000</td>\n      <td>1.690000e+05</td>\n      <td>12000.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>375.000000</td>\n      <td>2.333333e+05</td>\n      <td>22623.250000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>1732.210000</td>\n      <td>2.000030e+06</td>\n      <td>46250.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount               1280.000000                          1.280000e+03   \nmean                 305.407273                          2.025480e+05   \nstd                  167.380602                          1.218784e+05   \nmin                    0.980000                          6.666667e+04   \n25%                  203.167500                          1.333333e+05   \n50%                  269.140000                          1.690000e+05   \n75%                  375.000000                          2.333333e+05   \nmax                 1732.210000                          2.000030e+06   \n\n       Total gross non-housing wealth  \ncount                     1280.000000  \nmean                     15181.323828  \nstd                      12840.512772  \nmin                          0.000000  \n25%                       4485.000000  \n50%                      12000.000000  \n75%                      22623.250000  \nmax                      46250.000000  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 23250\nnew_cap_lim = 100000\n\n\nold_lose = old[(old[\"Asset test: non-housing assets\"] < upper_cap_lim) &\n                         ((old[\"Asset test: non-housing assets\"]).add(old[\"Asset test: housing assets\"]) > new_cap_lim)\n                        ]\n\n                                                                 \nold_lose[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\"]].round(2).describe()"}, {"cell_type": "markdown", "metadata": {}, "source": "This group represents 22 per cent of people 65 and above."}, {"cell_type": "markdown", "metadata": {}, "source": "###Deferred payment agreemnts - current eligibility"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 10, "outputs": [{"metadata": {}, "execution_count": 10, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>603.000000</td>\n      <td>6.030000e+02</td>\n      <td>603.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>251.253507</td>\n      <td>1.964800e+05</td>\n      <td>8354.616094</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>129.165229</td>\n      <td>1.490076e+05</td>\n      <td>6893.552907</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.057692</td>\n      <td>3.850000e+04</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>175.000000</td>\n      <td>1.200000e+05</td>\n      <td>2000.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>226.609528</td>\n      <td>1.693510e+05</td>\n      <td>7000.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>298.570480</td>\n      <td>2.475000e+05</td>\n      <td>13640.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>1732.206665</td>\n      <td>2.000030e+06</td>\n      <td>23200.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount                603.000000                          6.030000e+02   \nmean                 251.253507                          1.964800e+05   \nstd                  129.165229                          1.490076e+05   \nmin                    0.057692                          3.850000e+04   \n25%                  175.000000                          1.200000e+05   \n50%                  226.609528                          1.693510e+05   \n75%                  298.570480                          2.475000e+05   \nmax                 1732.206665                          2.000030e+06   \n\n       Total gross non-housing wealth  \ncount                      603.000000  \nmean                      8354.616094  \nstd                       6893.552907  \nmin                          0.000000  \n25%                       2000.000000  \n50%                       7000.000000  \n75%                      13640.000000  \nmax                      23200.000000  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 23250\nequity_cap = 35000 \n\n\nDPA_eligible_manifesto = old[(old[\"Asset test: non-housing assets\"] < upper_cap_lim) & \n                (old[\"Asset test: housing assets\"] > equity_cap) &\n                               (old[\"Couple status\"] == 1)\n                           \n               ]\n\nDPA_eligible_manifesto[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\"]].describe()"}, {"cell_type": "markdown", "metadata": {}, "source": "###Deferred payment agreements, under manifesto proposals - only singles are eligible"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 11, "outputs": [{"metadata": {}, "execution_count": 11, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>920.000000</td>\n      <td>9.200000e+02</td>\n      <td>920.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>290.997869</td>\n      <td>2.336280e+05</td>\n      <td>27725.686418</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>165.347878</td>\n      <td>1.745387e+05</td>\n      <td>26188.946148</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.980769</td>\n      <td>5.000000e+04</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>187.726021</td>\n      <td>1.500000e+05</td>\n      <td>6101.500000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>254.659996</td>\n      <td>2.000000e+05</td>\n      <td>19102.500000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>350.800812</td>\n      <td>2.750000e+05</td>\n      <td>45250.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>2536.050537</td>\n      <td>2.500000e+06</td>\n      <td>99000.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount                920.000000                          9.200000e+02   \nmean                 290.997869                          2.336280e+05   \nstd                  165.347878                          1.745387e+05   \nmin                    0.980769                          5.000000e+04   \n25%                  187.726021                          1.500000e+05   \n50%                  254.659996                          2.000000e+05   \n75%                  350.800812                          2.750000e+05   \nmax                 2536.050537                          2.500000e+06   \n\n       Total gross non-housing wealth  \ncount                      920.000000  \nmean                     27725.686418  \nstd                      26188.946148  \nmin                          0.000000  \n25%                       6101.500000  \n50%                      19102.500000  \n75%                      45250.000000  \nmax                      99000.000000  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 100000\nequity_cap = 35000 \n\n\nDPA_eligible_manifesto = old[(old[\"Asset test: non-housing assets\"] < upper_cap_lim) & \n                (old[\"Asset test: housing assets\"] > equity_cap) &\n                              ((old[\"Asset test: non-housing assets\"]).add(old[\"Asset test: housing assets\"]) > upper_cap_lim) &\n                         (old[\"Couple status\"] == 1)\n                           \n               ]\n\nDPA_eligible_manifesto[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\"]].describe()"}, {"cell_type": "markdown", "metadata": {}, "source": "###Deferred payment agreements, under manifesto proposals - people living with partner eligible"}, {"cell_type": "code", "metadata": {"trusted": true, "collapsed": false}, "execution_count": 12, "outputs": [{"metadata": {}, "execution_count": 12, "data": {"text/html": "<div>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Equivalised total income</th>\n      <th>Equivalised total net housing wealth</th>\n      <th>Total gross non-housing wealth</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>2872.000000</td>\n      <td>2.872000e+03</td>\n      <td>2872.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>353.186129</td>\n      <td>2.129056e+05</td>\n      <td>55678.903034</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>184.044588</td>\n      <td>1.371275e+05</td>\n      <td>49937.951544</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>0.980769</td>\n      <td>4.333333e+04</td>\n      <td>0.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>232.807602</td>\n      <td>1.333333e+05</td>\n      <td>14198.750000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>320.025314</td>\n      <td>1.800000e+05</td>\n      <td>42000.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>436.588745</td>\n      <td>2.500000e+05</td>\n      <td>84000.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>2536.050537</td>\n      <td>2.500000e+06</td>\n      <td>198000.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>", "text/plain": "       Equivalised total income  Equivalised total net housing wealth  \\\ncount               2872.000000                          2.872000e+03   \nmean                 353.186129                          2.129056e+05   \nstd                  184.044588                          1.371275e+05   \nmin                    0.980769                          4.333333e+04   \n25%                  232.807602                          1.333333e+05   \n50%                  320.025314                          1.800000e+05   \n75%                  436.588745                          2.500000e+05   \nmax                 2536.050537                          2.500000e+06   \n\n       Total gross non-housing wealth  \ncount                     2872.000000  \nmean                     55678.903034  \nstd                      49937.951544  \nmin                          0.000000  \n25%                      14198.750000  \n50%                      42000.000000  \n75%                      84000.000000  \nmax                     198000.000000  "}, "output_type": "execute_result"}], "source": "upper_cap_lim = 100000\nequity_cap = 35000 \n\n\nDPA_eligible_manifesto_partner = old[(old[\"Asset test: non-housing assets\"] < upper_cap_lim) & \n                                     ((old[\"Asset test: non-housing assets\"]).add(old[\"Asset test: housing assets\"]) > upper_cap_lim) &\n                (old[\"Asset test: housing assets\"] > equity_cap) ]\n\nDPA_eligible_manifesto_partner[[\"Equivalised total income\", \"Equivalised total net housing wealth\", \"Total gross non-housing wealth\"]].describe()"}], "nbformat_minor": 0}