Skip to content

Instantly share code, notes, and snippets.

@nilshg

nilshg/scf2007

Created September 19, 2014 10:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save nilshg/bbcacd6e6a11987db58b to your computer and use it in GitHub Desktop.
Save nilshg/bbcacd6e6a11987db58b to your computer and use it in GitHub Desktop.
Download ZIP
SCF 2007
Raw
scf2007
{
"metadata": {
"name": ""
},
"nbformat": 3,
"nbformat_minor": 0,
"worksheets": [
{
"cells": [
{
"cell_type": "heading",
"level": 1,
"metadata": {},
"source": [
"The Distribution of US Wealth in the SCF 2007"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In this notebook, we will analyse data on the US wealth distribution coming from the Federal Reserve's <a href=\"http://www.federalreserve.gov/econresdata/scf/scfindex.htm\">Survey of Consumer Finances (SCF)</a> in the years 1983 and 1989 to 2010 (in 3-yearly intervals), as well as the data from <a href = \"http://piketty.pse.ens.fr/fichiers/PikettyZucman2014QJE.pdf\">Piketty/Zucman (2014)</a>.\n",
"As usual, to do so we first want to import the relevant packages to be able to import, analyze and visualize our data:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"import numpy as np\n",
"import pandas as pd\n",
"import matplotlib.pyplot as plt\n",
"%matplotlib inline\n",
"import tables as tab"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 1
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Then, we can start importing our data:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"SCF2007 = pd.read_stata('/Users/tew207/Dropbox/QMUL/PhD/PhD Data/SCF/2007/scf2007.dta')"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 2
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"SCF2007.describe()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"html": [
"<div style=\"max-height:1000px;max-width:1500px;overflow:auto;\">\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>v1</th>\n",
" <th>b3001</th>\n",
" <th>b3002</th>\n",
" <th>b3003</th>\n",
" <th>b3004</th>\n",
" <th>b3005</th>\n",
" <th>b3006</th>\n",
" <th>b3007</th>\n",
" <th>b3008</th>\n",
" <th>b3009</th>\n",
" <th>b3010</th>\n",
" <th>b3011</th>\n",
" <th>b3012</th>\n",
" <th>b3013</th>\n",
" <th>b3014</th>\n",
" <th>b3015</th>\n",
" <th>b3016</th>\n",
" <th>b3017</th>\n",
" <th>b3018</th>\n",
" <th>b3019</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>count</th>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td> 4262.000000</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>mean</th>\n",
" <td> 2143.418348</td>\n",
" <td> 1.934538</td>\n",
" <td> 12652.092914</td>\n",
" <td> 13315.012905</td>\n",
" <td> 13941.711872</td>\n",
" <td> 19690.006335</td>\n",
" <td> 16513.193102</td>\n",
" <td> 8970.138902</td>\n",
" <td> 13325.784843</td>\n",
" <td> 13946.148287</td>\n",
" <td> 19690.064289</td>\n",
" <td> 19854.151103</td>\n",
" <td> 131.579071</td>\n",
" <td> 39.191694</td>\n",
" <td> 18740.701314</td>\n",
" <td> 19689.812529</td>\n",
" <td> 19689.811122</td>\n",
" <td> 19573.416940</td>\n",
" <td> 165.923041</td>\n",
" <td> 19573.465744</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>std</th>\n",
" <td> 1239.139652</td>\n",
" <td> 0.368540</td>\n",
" <td> 4867.917591</td>\n",
" <td> 4575.403089</td>\n",
" <td> 5429.198446</td>\n",
" <td> 8581.927859</td>\n",
" <td> 7762.136041</td>\n",
" <td> 3657.515558</td>\n",
" <td> 5484.225982</td>\n",
" <td> 6320.462425</td>\n",
" <td> 9792.720879</td>\n",
" <td> 8735.402290</td>\n",
" <td> 519.859855</td>\n",
" <td> 24.109381</td>\n",
" <td> 6645.110937</td>\n",
" <td> 9302.678286</td>\n",
" <td> 9299.915472</td>\n",
" <td> 8752.316741</td>\n",
" <td> 972.134504</td>\n",
" <td> 8399.244842</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>min</th>\n",
" <td> 1.000000</td>\n",
" <td> 1.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 1.000000</td>\n",
" <td> 173.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 0.000000</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25%</th>\n",
" <td> 1070.250000</td>\n",
" <td> 2.000000</td>\n",
" <td> 11810.000000</td>\n",
" <td> 14133.000000</td>\n",
" <td> 13213.000000</td>\n",
" <td> 16319.000000</td>\n",
" <td> 14479.500000</td>\n",
" <td> 10087.000000</td>\n",
" <td> 14284.000000</td>\n",
" <td> 13295.750000</td>\n",
" <td> 16490.000000</td>\n",
" <td> 18945.500000</td>\n",
" <td> 0.000000</td>\n",
" <td> 17.000000</td>\n",
" <td> 17153.000000</td>\n",
" <td> 16369.750000</td>\n",
" <td> 16335.500000</td>\n",
" <td> 18412.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 18378.000000</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>50%</th>\n",
" <td> 2144.500000</td>\n",
" <td> 2.000000</td>\n",
" <td> 13300.000000</td>\n",
" <td> 14133.000000</td>\n",
" <td> 14958.000000</td>\n",
" <td> 19806.000000</td>\n",
" <td> 18617.500000</td>\n",
" <td> 10218.000000</td>\n",
" <td> 15238.000000</td>\n",
" <td> 15594.500000</td>\n",
" <td> 20240.000000</td>\n",
" <td> 22513.500000</td>\n",
" <td> 0.000000</td>\n",
" <td> 36.000000</td>\n",
" <td> 19623.000000</td>\n",
" <td> 20081.000000</td>\n",
" <td> 20082.000000</td>\n",
" <td> 21178.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 20776.000000</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>75%</th>\n",
" <td> 3215.750000</td>\n",
" <td> 2.000000</td>\n",
" <td> 14970.000000</td>\n",
" <td> 15613.000000</td>\n",
" <td> 16665.000000</td>\n",
" <td> 25564.000000</td>\n",
" <td> 21653.750000</td>\n",
" <td> 10455.000000</td>\n",
" <td> 16005.000000</td>\n",
" <td> 17498.750000</td>\n",
" <td> 26156.750000</td>\n",
" <td> 25204.750000</td>\n",
" <td> 0.000000</td>\n",
" <td> 61.000000</td>\n",
" <td> 22277.000000</td>\n",
" <td> 25881.750000</td>\n",
" <td> 25796.500000</td>\n",
" <td> 23893.000000</td>\n",
" <td> 0.000000</td>\n",
" <td> 23723.000000</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>max</th>\n",
" <td> 4288.000000</td>\n",
" <td> 3.000000</td>\n",
" <td> 29240.000000</td>\n",
" <td> 16319.000000</td>\n",
" <td> 21608.000000</td>\n",
" <td> 50299.000000</td>\n",
" <td> 48565.000000</td>\n",
" <td> 17135.000000</td>\n",
" <td> 25536.000000</td>\n",
" <td> 35950.000000</td>\n",
" <td> 56337.000000</td>\n",
" <td> 50409.000000</td>\n",
" <td> 2533.000000</td>\n",
" <td> 84.000000</td>\n",
" <td> 40069.000000</td>\n",
" <td> 56264.000000</td>\n",
" <td> 56473.000000</td>\n",
" <td> 44471.000000</td>\n",
" <td> 11783.000000</td>\n",
" <td> 43601.000000</td>\n",
" <td>...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>8 rows \u00d7 1395 columns</p>\n",
"</div>"
],
"metadata": {},
"output_type": "pyout",
"prompt_number": 5,
"text": [
" v1 b3001 b3002 b3003 b3004 \\\n",
"count 4262.000000 4262.000000 4262.000000 4262.000000 4262.000000 \n",
"mean 2143.418348 1.934538 12652.092914 13315.012905 13941.711872 \n",
"std 1239.139652 0.368540 4867.917591 4575.403089 5429.198446 \n",
"min 1.000000 1.000000 0.000000 0.000000 0.000000 \n",
"25% 1070.250000 2.000000 11810.000000 14133.000000 13213.000000 \n",
"50% 2144.500000 2.000000 13300.000000 14133.000000 14958.000000 \n",
"75% 3215.750000 2.000000 14970.000000 15613.000000 16665.000000 \n",
"max 4288.000000 3.000000 29240.000000 16319.000000 21608.000000 \n",
"\n",
" b3005 b3006 b3007 b3008 b3009 \\\n",
"count 4262.000000 4262.000000 4262.000000 4262.000000 4262.000000 \n",
"mean 19690.006335 16513.193102 8970.138902 13325.784843 13946.148287 \n",
"std 8581.927859 7762.136041 3657.515558 5484.225982 6320.462425 \n",
"min 0.000000 0.000000 0.000000 0.000000 0.000000 \n",
"25% 16319.000000 14479.500000 10087.000000 14284.000000 13295.750000 \n",
"50% 19806.000000 18617.500000 10218.000000 15238.000000 15594.500000 \n",
"75% 25564.000000 21653.750000 10455.000000 16005.000000 17498.750000 \n",
"max 50299.000000 48565.000000 17135.000000 25536.000000 35950.000000 \n",
"\n",
" b3010 b3011 b3012 b3013 b3014 \\\n",
"count 4262.000000 4262.000000 4262.000000 4262.000000 4262.000000 \n",
"mean 19690.064289 19854.151103 131.579071 39.191694 18740.701314 \n",
"std 9792.720879 8735.402290 519.859855 24.109381 6645.110937 \n",
"min 0.000000 0.000000 0.000000 1.000000 173.000000 \n",
"25% 16490.000000 18945.500000 0.000000 17.000000 17153.000000 \n",
"50% 20240.000000 22513.500000 0.000000 36.000000 19623.000000 \n",
"75% 26156.750000 25204.750000 0.000000 61.000000 22277.000000 \n",
"max 56337.000000 50409.000000 2533.000000 84.000000 40069.000000 \n",
"\n",
" b3015 b3016 b3017 b3018 b3019 \\\n",
"count 4262.000000 4262.000000 4262.000000 4262.000000 4262.000000 \n",
"mean 19689.812529 19689.811122 19573.416940 165.923041 19573.465744 \n",
"std 9302.678286 9299.915472 8752.316741 972.134504 8399.244842 \n",
"min 0.000000 0.000000 0.000000 0.000000 0.000000 \n",
"25% 16369.750000 16335.500000 18412.000000 0.000000 18378.000000 \n",
"50% 20081.000000 20082.000000 21178.000000 0.000000 20776.000000 \n",
"75% 25881.750000 25796.500000 23893.000000 0.000000 23723.000000 \n",
"max 56264.000000 56473.000000 44471.000000 11783.000000 43601.000000 \n",
"\n",
" \n",
"count ... \n",
"mean ... \n",
"std ... \n",
"min ... \n",
"25% ... \n",
"50% ... \n",
"75% ... \n",
"max ... \n",
"\n",
"[8 rows x 1395 columns]"
]
}
],
"prompt_number": 5
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Consolidating Asset side"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"The major asset classes in the SCF are:\n",
"\n",
" 1. Housing\n",
" 2. Pension Wealth (IRA, Keogh, roll-over etc.)\n",
" 3. Checking Accounts\n",
" 4. Saving/Money Market Funds\n",
" 5. Mutual Funds\n",
" 6. Bonds\n",
" 7. Stocks\n",
" 8. Certificates of Deposit\n",
" 9. Annuities & Managed Trusts\n",
" 10. Life Insurance\n",
" 11. Miscellaneous Assets\n",
" \n",
"Since these are usually coded in different variables (e.g. one variable for each credit card, or one variable for different household member's assets), we need to sum up these variables to consolidate the assets.\n",
" \n",
"Note that in summing up the variables, we are glossing over some details that are used in the construction of the SCF net worth variables. This has mostly to do with the reclassification of different categories of assets and liabilities during the interview. One example of this would be the construction of the checking account and the money market account variable. While we simply sum up x3506, x3510,... and so on, the official SCF statistics make sure that the accounts mentioned are not money-market accounts by checking that x3507, x3511,... are equal to 5, which amounts to the respondent answering no when asked whether the account was a money market account type. So our calculation is simply `SCF2007['x3506'].clip(0, None) + SCF2007['x3510'].clip(0, None)...` for the checking account and then adding the money market variables x3730, x3736 etc to calculate money market fund wealth, the calculation in the SCF Bulletin would be `max(x3506, 0)*(x3507==5) + max(x3510, 0)*(x3511==5) + ...` for the checking account and `max(x3506, 0)*(x3507==1) + ... + max(x3730, 0) + ...` for the money market accounts. However this shouldn't matter for net worth, as it would just result in us underestimating money market fund wealth and overestimating checking account wealth by the same amount.\n",
"\n",
"The SAS code that produces the net worth variables used in the SCF bulletin can be found [here](http://www.federalreserve.gov/econresdata/scf/files/bulletin.macro.txt)."
]
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Housing "
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hsng = ['x513', 'x526', 'x604', 'x614', 'x617', 'x623', 'x627']"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 43
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"housing = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in hsng:\n",
" housing += SCF2007[i].clip(0, None)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 44
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Checking Accounts"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much is in account #1 to #6: x3506, x3510, x3514, x3518, x3522, x3526\n",
"* How much is in all your remaining accounts: x3529\n",
"* Remaining total for respondents outside grid structure: x8446\n",
"\n",
"Note: x8446 is not included in the SCF bulletin calculations, although it has a mean of \\$7425 with a standard deviation of \\$262,029, so its inclusion makes a substantial difference!"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"chkng = ['x3506', 'x3510', 'x3514', 'x3518', 'x3522', 'x3526', 'x3529', 'x8446']"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 17
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"checking = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in chkng:\n",
" checking += SCF2007[i].clip(0, None)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 42
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"SCF2007.x8446.describe()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 67,
"text": [
"count 22020.000000\n",
"mean 7425.222525\n",
"std 262029.465151\n",
"min 0.000000\n",
"25% 0.000000\n",
"50% 0.000000\n",
"75% 0.000000\n",
"max 15340000.000000\n",
"Name: x8446, dtype: float64"
]
}
],
"prompt_number": 67
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"checking.describe()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 63,
"text": [
"count 22090.000000\n",
"mean 75278.572838\n",
"std 568567.670108\n",
"min -100.000000\n",
"25% 720.000000\n",
"50% 3000.000000\n",
"75% 14992.500000\n",
"max 16159100.000000\n",
"dtype: float64"
]
}
],
"prompt_number": 63
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"checking2 = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in chkng:\n",
" checking2 += SCF2007[i].clip(0, None)"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 64
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Pension Wealth"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much is in your/your partner's/other family members Roth IRA account: x6551, x6559, x6567\n",
"* How much is in your/... roll-over IRA accounts: x6552, x6560, x6568\n",
"* How much is in your/... regular IRA accounts: x6553, x6561, x6569\n",
"* How much is in your/... Keogh accounts: x6554, x6562, x6570\n",
"* How much is in your/... unspecified accounts: x6756, x6757, x6758"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pensionwealth = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in pnsn:\n",
" pensionwealth += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 41
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"pnsn = ['x6551', 'x6559', 'x6567', 'x6554', 'x6562', 'x6570', 'x6552', 'x6560', 'x6568',\n",
" 'x6553', 'x6561', 'x6569', 'x6756', 'x6757', 'x6758']"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 40
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Certificates of Deposit"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* What is the total dollar value of all CDs: x3721"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"certificates = SCF2007.x3721"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 42
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Savings/Money Market Accounts"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much is in account #1 to #6: x3730, x3736, x3742, x3748, x3754, x3760\n",
"* How much is in all remaining accounts: x3765\n",
"* Remaining total for respondents outside grid: x8473\n",
"\n",
"Again, the SCF Bulletin calculations do not include x8473"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"svgmmkt = ['x3730', 'x3736', 'x3742', 'x3748', 'x3754', 'x3760', 'x3765', 'x8473']"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"savingsmmkt = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in svgmmkt:\n",
" savingsmmkt += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Mutual Funds"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Total value of stock mutual funds: x3822\n",
"* Market value of tax-free bond mutual funds: x3824\n",
"* Market value of government bond mutual funds: x3826\n",
"* Market value of other bond mutual funds: x3828\n",
"* Market value of combination funds: x3830\n",
"* Market value of all other funds: x7787\n",
"* Total market value of all funds if not specified: x6704"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mtlfnd = ['x3822', 'x3824', 'x3826', 'x3828', 'x3830', 'x7787', 'x6704']"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mutual = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in mtlfnd:\n",
" mutual += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Bonds"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Face value of all government savings bonds: x3902\n",
"* Mortgage backed bonds: face value x3906, market value x7635\n",
"* US govnmt bonds or T-bills: face value x3908, market value x7636\n",
"* State/municipal/tax-free bonds: face value x3910, market value x7637\n",
"* Foreign bonds: face value x7633, market value x7638\n",
"* Corporate bonds: face value x7634, market value x7639\n",
"* Total value if bonds unspecified: face value x6705, market value x6706\n",
"\n",
"For our purposes we are probably interested in the market value rather than the face value of bonds, hence the variable is constructed accordingly:"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bnd = ['x3902', 'x7635', 'x7636', 'x7637', 'x7638', 'x7639', 'x6706']"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"bonds = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in bnd: \n",
" bonds += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Stock"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Total market value of stock: x3915\n",
"* Cash or call brokerage account, value: x3930"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"stock = SCF2007.x3915 + SCF2007.x3930"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Annuities, Trusts, Managed Investments"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much would you receive if you cashed in the annuities: x6577\n",
"* Cash value of equity interest in trust/managed investment: x6587"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"annuities = SCF.x6577 + SCF.x6587"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Life Insurance"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Current Cash Value of Life Insurance: x4006\n",
"\n",
"Note: there might be loans taken out on the value of the life insurance, this is coded in x4007 (=1 if respondent is borrowing agains insurance). If there are loans taken out on the value of the insurance, x4008 records whether the value in x4006 is net of the loan (=1) or gross (=2). x4009 records whether the loans were recorded earlier in the interview and x7645 gives the category this loan has been recorded in."
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"lifeins = SCF.x4006"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"SCF2007.x4007.value_counts()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 46,
"text": [
"0 15485\n",
"5 6292\n",
"1 313\n",
"dtype: int64"
]
}
],
"prompt_number": 46
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"SCF2007.x4008.value_counts()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 47,
"text": [
"0 21777\n",
"3 195\n",
"2 113\n",
"1 5\n",
"dtype: int64"
]
}
],
"prompt_number": 47
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"SCF2007.x4009.value_counts()"
],
"language": "python",
"metadata": {},
"outputs": [
{
"metadata": {},
"output_type": "pyout",
"prompt_number": 48,
"text": [
"0 21777\n",
"5 308\n",
"3 5\n",
"dtype: int64"
]
}
],
"prompt_number": 48
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"From the above, it appears that all loans were recorded previously (as x4007 shows 313 people have borrowed against their insurances, while x4009 indicates that all 313 loans have been classified previously). \n",
"However, it is important to correctly add up the assets and liabilities for the 308 people who have given gross values for life insurance, in that case we might have to subtract x4010 (How much is borrowed?) from x4006. [But double-check that we don't subtract this loan twice since it has been recorded earlier!]"
]
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Miscellaneous Assets and Debts"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much are you owed by friends, businesses, relatives, others: x4017\n",
"* Other assets not yet recorded (#1 to #3): x4022, x4026, x4030\n",
"* Land Contracts and Notes, how much are you owed (#1 to #3): x1405, x1505, x1605\n",
"* How much are you owed on remaining notes, land contracts and mortgages: x1619\n",
"* How much is still owed to you on this loan? x1409, x1509, x1609\n",
"* Remaining total for respondents outside grid: x8402"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mscass = ['x4017', 'x4022', 'x4026', 'x4030', 'x1405', 'x1505', 'x1605', 'x1619', 'x1409', 'x1509', 'x1609', 'x8402']"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"misc_assets = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in mscass:\n",
" misc_assets += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 2,
"metadata": {},
"source": [
"Consolidating Liabilities side"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"As with assets, liabilities are coded in a number of variables and we have to consolidate them into single variables. The categories of liabilities in the SCF are:\n",
"\n",
"1. [Credit Card Balances](http://127.0.0.1:8888/c6ed94eb-936e-4ab9-8a27-9dc178a175af#Credit-Card-balances:)\n",
"2. Mortgages/Land Contracts/Equity Loans on Residence/Other home purchase loan\n",
"3. Home improvement loan/Loan for other real estate\n",
"7. Lines of Credit\n",
"8. Business Loan\n",
"9. Vehicle Loan\n",
"10. Education Loan\n",
"11. Other Installment loan\n",
"12. Margin Loans at Brokerages\n",
"13. Insurance Loan\n",
"14. Pension Loan\n",
"15. Miscellaneous Debts"
]
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Credit Card balances:\n",
" "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Total amount owed on credit card #1 to #5: x413, x421, x424, x427, x430\n",
"* Total amount owed on all other cards: x7575"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"ccard = ['x413', 'x421', 'x424', 'x427', 'x430', 'x7575']"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 38
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"creditcard = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in ccard:\n",
" creditcard += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": [],
"prompt_number": 39
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Mortgages/Land Contracts/Equity Loans on Residence"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much is still owed on this land contract/loan: x805, x905, x1005\n",
"* Other loans to purchase property, amount still owed: x1044\n",
"* Do you still owe any money on loans for this property (#1 to #3): x1416, x1516, x1616\n",
"* How much do you still owe on loans for other properties? x1621"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mrtg = ['x805', 'x905', 'x1005', 'x1044', 'x1416', 'x1516', 'x1616', 'x1621']"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"mortgages = pd.Series(np.zeros9(len(SCF2007)))\n",
"for i in mrtg:\n",
" mortgages += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Home Improvement Loans, Loans for other Property"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Do you owe money on loans taken out for home improvement, amount still owed: x1215\n",
"* How much is owed on all other loans for home improvement: x1219"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"hiloans = ['x1215', 'x1219'"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Lines of Credit"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Amount Currently Owed on Line of Credit #1 to #3: x1108, x1119, x1130\n",
"* Amount Currently Owed on all other lines of credit: x1136\n",
"* Remaining total for respondents answering outside of grid structure: x8401"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"crdlines = ['x1108', 'x1119', 'x1130', 'x1136', 'x8401']"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"lines_of_credit = pd.Series(np.zeros(len(SCF2007)))\n",
"for i in lines_of_credit:\n",
" lines_of_credit += SCF2007[i]"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Margin Loans at Brokerages"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* Current balance on margin loans x3932"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"margin = SCF2007.x3932"
],
"language": "python",
"metadata": {},
"outputs": []
},
{
"cell_type": "heading",
"level": 4,
"metadata": {},
"source": [
"Miscellaneous Debts"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"* How much do you owe that was not recorded earlier: x4032"
]
},
{
"cell_type": "code",
"collapsed": false,
"input": [
"misc_debts = x4032"
],
"language": "python",
"metadata": {},
"outputs": []
}
],
"metadata": {}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment