{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "

Intangible Intensity for S corps

\n", "\n", "In this note we compute *Intangible Intensity* using SOI data on business receipts and assets with price to sales valuations using compustat data" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [ { "data": { "text/html": [ "\n", "
" ], "text/plain": [ "" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "from IPython.display import HTML\n", "from IPython.core.interactiveshell import InteractiveShell\n", "InteractiveShell.ast_node_interactivity = \"all\"\n", "HTML('''\n", "
''')" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "\n", "import difflib as dl\n", "\n", "from difflib import SequenceMatcher\n", "from heapq import nlargest as _nlargest\n", "\n", "pd.options.display.float_format = '{:,.2f}'.format\n", "\n", "colnames=pd.read_csv('../RawData/IRS/colnames_soi_1120S.csv')\n", "colnames.columns=[x.strip() for x in colnames.columns]\n", "colnames.SOI1120S = colnames.SOI1120S.astype(int)\n", "colnames.SOI1065 = colnames.SOI1065.astype(int)\n", "colnames.CFC= colnames.CFC.astype(int)\n", "colnames.CCANonCorporate= colnames.CCANonCorporate.astype(int)\n", "colnames.CCACorporate= colnames.CCACorporate.astype(int)\n" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [], "source": [ "def format_str(string):\n", " string=string.replace('[33]','')\n", " string=string.replace('[26]','')\n", " string=string.replace(\"[12]\",'')\n", " string=string.replace('[34]','')\n", " string=string.replace(':','')\n", " string=string.strip()\n", " return string\n", "\n", "\n", "def getBSData(soi_link):\n", " #soi_link= \"https://www.irs.gov/pub/irs-soi/13co07s.xls\"\n", " temp2=pd.read_excel(soi_link,skiprows=6)\n", " BSData=temp2.iloc[:,colnames.SOI1120S.values]\n", " BSData.index=[ format_str(str(x[0])) for x in temp2.iloc[:,[0]].values]\n", " BSData.columns=colnames.name.values\n", " BSData=BSData.iloc[:-1]\n", " return BSData\n", "\n", "def get_close_matches_indexes(word, possibilities, n=3, cutoff=0.6):\n", " \"\"\"Use SequenceMatcher to return a list of the indexes of the best \n", " \"good enough\" matches. word is a sequence for which close matches \n", " are desired (typically a string).\n", " possibilities is a list of sequences against which to match word\n", " (typically a list of strings).\n", " Optional arg n (default 3) is the maximum number of close matches to\n", " return. n must be > 0.\n", " Optional arg cutoff (default 0.6) is a float in [0, 1]. Possibilities\n", " that don't score at least that similar to word are ignored.\n", " \"\"\"\n", "\n", " if not n > 0:\n", " raise ValueError(\"n must be > 0: %r\" % (n,))\n", " if not 0.0 <= cutoff <= 1.0:\n", " raise ValueError(\"cutoff must be in [0.0, 1.0]: %r\" % (cutoff,))\n", " result = []\n", " s = SequenceMatcher()\n", " s.set_seq2(word)\n", " for idx, x in enumerate(possibilities):\n", " s.set_seq1(x)\n", " if s.real_quick_ratio() >= cutoff and \\\n", " s.quick_ratio() >= cutoff and \\\n", " s.ratio() >= cutoff:\n", " result.append((s.ratio(), idx))\n", "\n", " # Move the best scorers to head of list\n", " result = _nlargest(n, result)\n", "\n", " # Strip scores for the best n matches\n", " return [x for score, x in result]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Years" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "['2007', '2008', '2009', '2010', '2011', '2012', '2013']\n" ] } ], "source": [ "years=['2007','2008','2009','2010','2011','2012','2013']\n", "print(years)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Source of NIPA data " ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "fa_section3_link=\"http://users.cla.umn.edu/~erm/data/sr560/Data/RawData/BEA/FA/Section3all_xls.xlsx\"\n", "nipa_section6_link=\"http://users.cla.umn.edu/~erm/data/sr560/Data/RawData/BEA/NIPA/Section6all_xls.xlsx\"" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "http://users.cla.umn.edu/~erm/data/sr560/Data/RawData/BEA/FA/Section3all_xls.xlsx\n", "http://users.cla.umn.edu/~erm/data/sr560/Data/RawData/BEA/NIPA/Section6all_xls.xlsx\n" ] } ], "source": [ "print(fa_section3_link)\n", "print(nipa_section6_link)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: CFC\n", "Table 3.4ESI. Current-Cost Depreciation of Private Fixed Assets by Industry" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All1,852.501,931.801,928.701,933.801,997.302,082.402,176.60
Agriculture, forestry, fishing, and hunting34.1035.9037.0037.6039.6042.2045.60
Mining112.60125.40118.70119.70128.70145.10151.40
Utilities58.7064.8067.2069.9073.6077.4080.00
Construction34.9036.9036.1034.0034.1035.5037.10
Manufacturing326.20347.50353.50356.70369.30383.40395.20
Wholesale trade55.1056.1055.4054.3056.4059.6061.30
Retail trade70.1073.0072.9071.4074.3078.2081.10
Transportation and warehousing64.2066.6066.8065.9067.5070.3073.00
Information169.80176.20178.80185.10197.50206.50216.80
Finance and insurance129.00133.70133.10130.70133.50138.50145.90
Real estate and rental and leasing491.70489.30470.30462.10462.30469.60497.70
Professional scientific\\nand technical services90.2096.0099.30102.50106.00109.30112.70
Administrative and support and waste management and remediation services28.1029.0029.8030.9032.9035.2037.90
Educational services17.6019.0020.6021.7023.1024.5025.50
Health care and social assistance70.8076.2079.3081.3085.3089.5093.40
Arts, entertainment, and recreation21.0021.7022.0021.9022.1022.4023.10
Accommodation and food services28.4030.3030.7029.8029.9030.6031.80
Other services26.5028.2028.5028.3029.2030.2031.00
\n", "
" ], "text/plain": [ " 2007 2008 2009 \\\n", "name \n", "All 1,852.50 1,931.80 1,928.70 \n", "Agriculture, forestry, fishing, and hunting 34.10 35.90 37.00 \n", "Mining 112.60 125.40 118.70 \n", "Utilities 58.70 64.80 67.20 \n", "Construction 34.90 36.90 36.10 \n", "Manufacturing 326.20 347.50 353.50 \n", "Wholesale trade 55.10 56.10 55.40 \n", "Retail trade 70.10 73.00 72.90 \n", "Transportation and warehousing 64.20 66.60 66.80 \n", "Information 169.80 176.20 178.80 \n", "Finance and insurance 129.00 133.70 133.10 \n", "Real estate and rental and leasing 491.70 489.30 470.30 \n", "Professional scientific\\nand technical services 90.20 96.00 99.30 \n", "Administrative and support and waste management... 28.10 29.00 29.80 \n", "Educational services 17.60 19.00 20.60 \n", "Health care and social assistance 70.80 76.20 79.30 \n", "Arts, entertainment, and recreation 21.00 21.70 22.00 \n", "Accommodation and food services 28.40 30.30 30.70 \n", "Other services 26.50 28.20 28.50 \n", "\n", " 2010 2011 2012 \\\n", "name \n", "All 1,933.80 1,997.30 2,082.40 \n", "Agriculture, forestry, fishing, and hunting 37.60 39.60 42.20 \n", "Mining 119.70 128.70 145.10 \n", "Utilities 69.90 73.60 77.40 \n", "Construction 34.00 34.10 35.50 \n", "Manufacturing 356.70 369.30 383.40 \n", "Wholesale trade 54.30 56.40 59.60 \n", "Retail trade 71.40 74.30 78.20 \n", "Transportation and warehousing 65.90 67.50 70.30 \n", "Information 185.10 197.50 206.50 \n", "Finance and insurance 130.70 133.50 138.50 \n", "Real estate and rental and leasing 462.10 462.30 469.60 \n", "Professional scientific\\nand technical services 102.50 106.00 109.30 \n", "Administrative and support and waste management... 30.90 32.90 35.20 \n", "Educational services 21.70 23.10 24.50 \n", "Health care and social assistance 81.30 85.30 89.50 \n", "Arts, entertainment, and recreation 21.90 22.10 22.40 \n", "Accommodation and food services 29.80 29.90 30.60 \n", "Other services 28.30 29.20 30.20 \n", "\n", " 2013 \n", "name \n", "All 2,176.60 \n", "Agriculture, forestry, fishing, and hunting 45.60 \n", "Mining 151.40 \n", "Utilities 80.00 \n", "Construction 37.10 \n", "Manufacturing 395.20 \n", "Wholesale trade 61.30 \n", "Retail trade 81.10 \n", "Transportation and warehousing 73.00 \n", "Information 216.80 \n", "Finance and insurance 145.90 \n", "Real estate and rental and leasing 497.70 \n", "Professional scientific\\nand technical services 112.70 \n", "Administrative and support and waste management... 37.90 \n", "Educational services 25.50 \n", "Health care and social assistance 93.40 \n", "Arts, entertainment, and recreation 23.10 \n", "Accommodation and food services 31.80 \n", "Other services 31.00 " ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "CFC=pd.read_excel(fa_section3_link,sheet_name=\"FAAt304ESI-A\",skiprows=7)\n", "CFC['IndustryName']=CFC['Unnamed: 1']\n", "CFC=CFC.drop(columns=['Unnamed: 1','Unnamed: 2'])\n", "CFC=CFC[:-7]\n", "CFC.Line=CFC.Line.astype(int)\n", "CFC=CFC[CFC['Line'].isin(colnames.CFC.values)]\n", "#CFC.iloc[get_close_matches_indexes('Management of companies and enterprise', CFC.IndustryName)[0],3:]=CFC[CFC.IndustryName==dl.get_close_matches('Administrative and support and waste', CFC.IndustryName)[0]].iloc[0,3:]+\\\n", "#CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].iloc[0,3:]\n", "CFC.index=colnames.name\n", "CFC=CFC.drop(CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].index)\n", "CFC[years]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: CAA (Noncorporate)\n", "Table 6.13D. Noncorporate Capital Consumption Allowances by Industry" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All313.09386.18381.62392.37447.09420.68458.61
Agriculture, forestry, fishing, and hunting17.8518.9718.9220.0320.5722.0424.52
Mining23.4832.5135.2938.7147.5948.4357.33
Utilities8.5615.5315.6716.1523.1321.4125.03
Construction10.5810.788.537.888.247.848.17
Manufacturing27.6742.9338.6536.3043.8941.9240.46
Wholesale trade5.126.687.107.189.198.7610.61
Retail trade7.118.397.958.029.168.198.55
Transportation and warehousing12.2815.6317.5921.4527.8625.9634.94
Information34.4542.1243.6646.3752.9841.0543.23
Finance and insurance11.5113.519.8810.9612.0011.6913.55
Real estate and rental and leasing108.70125.21125.89124.97136.89129.35135.52
Professional scientific\\nand technical services12.1713.8013.3313.7014.5014.8015.09
Administrative and support and waste management and remediation services6.906.896.657.577.477.438.13
Educational services0.700.780.780.720.800.800.98
Health care and social assistance8.6610.6110.3211.0211.359.649.68
Arts, entertainment, and recreation4.815.745.395.625.866.736.97
Accommodation and food services11.7315.4115.3214.8915.1013.9415.07
Other services0.810.680.710.830.500.670.76
\n", "
" ], "text/plain": [ " 2007 2008 2009 \\\n", "name \n", "All 313.09 386.18 381.62 \n", "Agriculture, forestry, fishing, and hunting 17.85 18.97 18.92 \n", "Mining 23.48 32.51 35.29 \n", "Utilities 8.56 15.53 15.67 \n", "Construction 10.58 10.78 8.53 \n", "Manufacturing 27.67 42.93 38.65 \n", "Wholesale trade 5.12 6.68 7.10 \n", "Retail trade 7.11 8.39 7.95 \n", "Transportation and warehousing 12.28 15.63 17.59 \n", "Information 34.45 42.12 43.66 \n", "Finance and insurance 11.51 13.51 9.88 \n", "Real estate and rental and leasing 108.70 125.21 125.89 \n", "Professional scientific\\nand technical services 12.17 13.80 13.33 \n", "Administrative and support and waste management... 6.90 6.89 6.65 \n", "Educational services 0.70 0.78 0.78 \n", "Health care and social assistance 8.66 10.61 10.32 \n", "Arts, entertainment, and recreation 4.81 5.74 5.39 \n", "Accommodation and food services 11.73 15.41 15.32 \n", "Other services 0.81 0.68 0.71 \n", "\n", " 2010 2011 2012 2013 \n", "name \n", "All 392.37 447.09 420.68 458.61 \n", "Agriculture, forestry, fishing, and hunting 20.03 20.57 22.04 24.52 \n", "Mining 38.71 47.59 48.43 57.33 \n", "Utilities 16.15 23.13 21.41 25.03 \n", "Construction 7.88 8.24 7.84 8.17 \n", "Manufacturing 36.30 43.89 41.92 40.46 \n", "Wholesale trade 7.18 9.19 8.76 10.61 \n", "Retail trade 8.02 9.16 8.19 8.55 \n", "Transportation and warehousing 21.45 27.86 25.96 34.94 \n", "Information 46.37 52.98 41.05 43.23 \n", "Finance and insurance 10.96 12.00 11.69 13.55 \n", "Real estate and rental and leasing 124.97 136.89 129.35 135.52 \n", "Professional scientific\\nand technical services 13.70 14.50 14.80 15.09 \n", "Administrative and support and waste management... 7.57 7.47 7.43 8.13 \n", "Educational services 0.72 0.80 0.80 0.98 \n", "Health care and social assistance 11.02 11.35 9.64 9.68 \n", "Arts, entertainment, and recreation 5.62 5.86 6.73 6.97 \n", "Accommodation and food services 14.89 15.10 13.94 15.07 \n", "Other services 0.83 0.50 0.67 0.76 " ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "CCA_NonCorporate=pd.read_excel(nipa_section6_link,sheet_name=\"T61300D-A\",skiprows=7)\n", "CCA_NonCorporate['IndustryName']=CCA_NonCorporate['Unnamed: 1']\n", "CCA_NonCorporate=CCA_NonCorporate.drop(columns=['Unnamed: 1','Unnamed: 2'])\n", "CCA_NonCorporate=CCA_NonCorporate[:-4]\n", "CCA_NonCorporate.Line=CCA_NonCorporate.Line.astype(int)\n", "CCA_NonCorporate=CCA_NonCorporate[CCA_NonCorporate['Line'].isin(colnames.CCANonCorporate.values)]\n", "CCA_NonCorporate.index=CFC.index\n", "CCA_NonCorporate[years]/1000.0" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: CAA (Corporate)\n", "Table 6.22D. Corporate Capital Consumption Allowances by Industry" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All1,013.761,197.951,180.491,213.481,384.171,245.211,294.97
Agriculture, forestry, fishing, and hunting7.629.669.3810.6212.0810.9611.62
Mining29.0543.2744.6744.7362.8761.7062.14
Utilities42.6160.0973.9880.5698.9092.7783.50
Construction25.1728.1422.9720.6422.7218.7121.08
Manufacturing369.20410.98412.04417.75470.19439.29460.50
Wholesale trade75.5493.8889.3097.07112.2899.65107.65
Retail trade52.2768.2561.6666.2673.3460.7263.76
Transportation and warehousing50.5757.5755.2657.9170.4759.6061.36
Information118.36139.95137.63138.52149.65129.31134.04
Finance and insurance64.8872.2468.1469.4074.4368.8778.69
Real estate and rental and leasing41.7249.3744.4544.6755.4445.8247.27
Professional scientific\\nand technical services38.3344.4746.0347.7552.5648.6150.92
Administrative and support and waste management and remediation services12.4114.7814.3415.0416.8414.8615.16
Educational services0.851.201.691.802.211.591.39
Health care and social assistance10.6913.6212.2912.8915.3811.2812.17
Arts, entertainment, and recreation4.675.685.606.456.936.246.25
Accommodation and food services17.4322.1721.5521.3422.3619.7319.63
Other services5.586.185.414.975.754.945.16
\n", "
" ], "text/plain": [ " 2007 2008 2009 \\\n", "name \n", "All 1,013.76 1,197.95 1,180.49 \n", "Agriculture, forestry, fishing, and hunting 7.62 9.66 9.38 \n", "Mining 29.05 43.27 44.67 \n", "Utilities 42.61 60.09 73.98 \n", "Construction 25.17 28.14 22.97 \n", "Manufacturing 369.20 410.98 412.04 \n", "Wholesale trade 75.54 93.88 89.30 \n", "Retail trade 52.27 68.25 61.66 \n", "Transportation and warehousing 50.57 57.57 55.26 \n", "Information 118.36 139.95 137.63 \n", "Finance and insurance 64.88 72.24 68.14 \n", "Real estate and rental and leasing 41.72 49.37 44.45 \n", "Professional scientific\\nand technical services 38.33 44.47 46.03 \n", "Administrative and support and waste management... 12.41 14.78 14.34 \n", "Educational services 0.85 1.20 1.69 \n", "Health care and social assistance 10.69 13.62 12.29 \n", "Arts, entertainment, and recreation 4.67 5.68 5.60 \n", "Accommodation and food services 17.43 22.17 21.55 \n", "Other services 5.58 6.18 5.41 \n", "\n", " 2010 2011 2012 \\\n", "name \n", "All 1,213.48 1,384.17 1,245.21 \n", "Agriculture, forestry, fishing, and hunting 10.62 12.08 10.96 \n", "Mining 44.73 62.87 61.70 \n", "Utilities 80.56 98.90 92.77 \n", "Construction 20.64 22.72 18.71 \n", "Manufacturing 417.75 470.19 439.29 \n", "Wholesale trade 97.07 112.28 99.65 \n", "Retail trade 66.26 73.34 60.72 \n", "Transportation and warehousing 57.91 70.47 59.60 \n", "Information 138.52 149.65 129.31 \n", "Finance and insurance 69.40 74.43 68.87 \n", "Real estate and rental and leasing 44.67 55.44 45.82 \n", "Professional scientific\\nand technical services 47.75 52.56 48.61 \n", "Administrative and support and waste management... 15.04 16.84 14.86 \n", "Educational services 1.80 2.21 1.59 \n", "Health care and social assistance 12.89 15.38 11.28 \n", "Arts, entertainment, and recreation 6.45 6.93 6.24 \n", "Accommodation and food services 21.34 22.36 19.73 \n", "Other services 4.97 5.75 4.94 \n", "\n", " 2013 \n", "name \n", "All 1,294.97 \n", "Agriculture, forestry, fishing, and hunting 11.62 \n", "Mining 62.14 \n", "Utilities 83.50 \n", "Construction 21.08 \n", "Manufacturing 460.50 \n", "Wholesale trade 107.65 \n", "Retail trade 63.76 \n", "Transportation and warehousing 61.36 \n", "Information 134.04 \n", "Finance and insurance 78.69 \n", "Real estate and rental and leasing 47.27 \n", "Professional scientific\\nand technical services 50.92 \n", "Administrative and support and waste management... 15.16 \n", "Educational services 1.39 \n", "Health care and social assistance 12.17 \n", "Arts, entertainment, and recreation 6.25 \n", "Accommodation and food services 19.63 \n", "Other services 5.16 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "CCA_Corporate=pd.read_excel(nipa_section6_link,sheet_name=\"T62200D-A\",skiprows=7)\n", "CCA_Corporate['IndustryName']=CCA_Corporate['Unnamed: 1']\n", "CCA_Corporate=CCA_Corporate.drop(columns=['Unnamed: 1','Unnamed: 2'])\n", "CCA_Corporate=CCA_Corporate[:-7]\n", "CCA_Corporate.Line=CCA_Corporate.Line.astype(int)\n", "CCA_Corporate.index=CCA_Corporate.Line\n", "CCA_Corporate=CCA_Corporate[CCA_Corporate['Line'].isin(colnames.CCACorporate.values)]\n", "#CCA_Corporate.iloc[get_close_matches_indexes('Management of companies and enterprise', CCA_Corporate.IndustryName)[0],3:]=CCA_Corporate[CCA_Corporate.IndustryName==dl.get_close_matches('Administrative and support and waste', CCA_Corporate.IndustryName)[0]].iloc[0,3:]+\\\n", "#CCA_Corporate[CCA_Corporate.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCA_Corporate.IndustryName)[0]].iloc[0,3:]\n", "CCA_Corporate.index=colnames.name\n", "CCA_Corporate=CCA_Corporate.drop(CCA_Corporate[CCA_Corporate.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCA_Corporate.IndustryName)[0]].index)\n", "CCA_Corporate[years]/1000.0" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Index for converting CCA to CFC \n", "\n", "Tax depreciation to economic depreciation using \n", "$$ \\frac {\\text{CFC}} { \\text{CCA ( Noncorporate) + CCA ( Corporate)}} $$" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All1.401.221.231.201.091.251.24
Agriculture, forestry, fishing, and hunting1.341.251.311.231.211.281.26
Mining2.141.651.481.431.171.321.27
Utilities1.150.860.750.720.600.680.74
Construction0.980.951.151.191.101.341.27
Manufacturing0.820.770.780.790.720.800.79
Wholesale trade0.680.560.570.520.460.550.52
Retail trade1.180.951.050.960.901.131.12
Transportation and warehousing1.020.910.920.830.690.820.76
Information1.110.970.991.000.971.211.22
Finance and insurance1.691.561.711.631.541.721.58
Real estate and rental and leasing3.272.802.762.722.402.682.72
Professional scientific\\nand technical services1.791.651.671.671.581.721.71
Administrative and support and waste management and remediation services1.461.341.421.371.351.581.63
Educational services11.309.598.338.607.6710.2510.77
Health care and social assistance3.663.153.513.403.194.284.27
Arts, entertainment, and recreation2.211.902.001.811.731.731.75
Accommodation and food services0.970.810.830.820.800.910.92
Other services4.154.114.664.884.675.385.24
\n", "
" ], "text/plain": [ " 2007 2008 2009 2010 \\\n", "name \n", "All 1.40 1.22 1.23 1.20 \n", "Agriculture, forestry, fishing, and hunting 1.34 1.25 1.31 1.23 \n", "Mining 2.14 1.65 1.48 1.43 \n", "Utilities 1.15 0.86 0.75 0.72 \n", "Construction 0.98 0.95 1.15 1.19 \n", "Manufacturing 0.82 0.77 0.78 0.79 \n", "Wholesale trade 0.68 0.56 0.57 0.52 \n", "Retail trade 1.18 0.95 1.05 0.96 \n", "Transportation and warehousing 1.02 0.91 0.92 0.83 \n", "Information 1.11 0.97 0.99 1.00 \n", "Finance and insurance 1.69 1.56 1.71 1.63 \n", "Real estate and rental and leasing 3.27 2.80 2.76 2.72 \n", "Professional scientific\\nand technical services 1.79 1.65 1.67 1.67 \n", "Administrative and support and waste management... 1.46 1.34 1.42 1.37 \n", "Educational services 11.30 9.59 8.33 8.60 \n", "Health care and social assistance 3.66 3.15 3.51 3.40 \n", "Arts, entertainment, and recreation 2.21 1.90 2.00 1.81 \n", "Accommodation and food services 0.97 0.81 0.83 0.82 \n", "Other services 4.15 4.11 4.66 4.88 \n", "\n", " 2011 2012 2013 \n", "name \n", "All 1.09 1.25 1.24 \n", "Agriculture, forestry, fishing, and hunting 1.21 1.28 1.26 \n", "Mining 1.17 1.32 1.27 \n", "Utilities 0.60 0.68 0.74 \n", "Construction 1.10 1.34 1.27 \n", "Manufacturing 0.72 0.80 0.79 \n", "Wholesale trade 0.46 0.55 0.52 \n", "Retail trade 0.90 1.13 1.12 \n", "Transportation and warehousing 0.69 0.82 0.76 \n", "Information 0.97 1.21 1.22 \n", "Finance and insurance 1.54 1.72 1.58 \n", "Real estate and rental and leasing 2.40 2.68 2.72 \n", "Professional scientific\\nand technical services 1.58 1.72 1.71 \n", "Administrative and support and waste management... 1.35 1.58 1.63 \n", "Educational services 7.67 10.25 10.77 \n", "Health care and social assistance 3.19 4.28 4.27 \n", "Arts, entertainment, and recreation 1.73 1.73 1.75 \n", "Accommodation and food services 0.80 0.91 0.92 \n", "Other services 4.67 5.38 5.24 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "IndexDep=CFC[years]/(CCA_NonCorporate[years]/1000.0+CCA_Corporate[years]/1000.0)\n", "display(IndexDep)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: Current Cost FA\n", "Table 3.1ESI. Current-Cost Net Stock of Private Fixed Assets by Industry" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All34,154.6034,981.3034,101.1034,582.2035,557.7036,693.1038,699.60
Agriculture, forestry, fishing, and hunting472.70492.10479.70487.10507.70536.50577.40
Mining1,512.501,648.901,491.201,574.501,752.101,886.402,050.60
Utilities1,590.601,745.001,736.101,859.301,980.402,064.602,126.20
Construction264.90284.00267.00263.60271.10278.60288.30
Manufacturing3,009.103,199.903,160.103,214.903,340.203,438.903,580.30
Wholesale trade480.40507.10487.50491.20515.30533.70544.20
Retail trade1,107.101,183.601,127.501,131.801,177.201,205.601,235.40
Transportation and warehousing1,036.801,098.401,072.501,095.201,136.401,170.201,209.00
Information1,656.301,722.201,722.901,829.801,908.301,948.402,007.60
Finance and insurance1,164.201,215.901,161.301,153.801,183.701,209.201,247.30
Real estate and rental and leasing17,842.6017,613.7017,200.5017,227.4017,382.4017,893.5019,099.20
Professional scientific\\nand technical services512.30549.40557.40576.30596.60616.40640.50
Administrative and support and waste management and remediation services230.40238.40237.90244.60257.30265.80277.40
Educational services398.80448.50455.30469.20497.10511.80534.80
Health care and social assistance1,160.901,220.601,195.501,220.701,268.301,309.001,378.60
Arts, entertainment, and recreation280.30293.40287.60289.10296.60305.30323.30
Accommodation and food services529.20569.00548.00541.90551.80566.50597.10
Other services561.60583.80557.50557.30568.20578.40601.10
\n", "
" ], "text/plain": [ " 2007 2008 \\\n", "name \n", "All 34,154.60 34,981.30 \n", "Agriculture, forestry, fishing, and hunting 472.70 492.10 \n", "Mining 1,512.50 1,648.90 \n", "Utilities 1,590.60 1,745.00 \n", "Construction 264.90 284.00 \n", "Manufacturing 3,009.10 3,199.90 \n", "Wholesale trade 480.40 507.10 \n", "Retail trade 1,107.10 1,183.60 \n", "Transportation and warehousing 1,036.80 1,098.40 \n", "Information 1,656.30 1,722.20 \n", "Finance and insurance 1,164.20 1,215.90 \n", "Real estate and rental and leasing 17,842.60 17,613.70 \n", "Professional scientific\\nand technical services 512.30 549.40 \n", "Administrative and support and waste management... 230.40 238.40 \n", "Educational services 398.80 448.50 \n", "Health care and social assistance 1,160.90 1,220.60 \n", "Arts, entertainment, and recreation 280.30 293.40 \n", "Accommodation and food services 529.20 569.00 \n", "Other services 561.60 583.80 \n", "\n", " 2009 2010 \\\n", "name \n", "All 34,101.10 34,582.20 \n", "Agriculture, forestry, fishing, and hunting 479.70 487.10 \n", "Mining 1,491.20 1,574.50 \n", "Utilities 1,736.10 1,859.30 \n", "Construction 267.00 263.60 \n", "Manufacturing 3,160.10 3,214.90 \n", "Wholesale trade 487.50 491.20 \n", "Retail trade 1,127.50 1,131.80 \n", "Transportation and warehousing 1,072.50 1,095.20 \n", "Information 1,722.90 1,829.80 \n", "Finance and insurance 1,161.30 1,153.80 \n", "Real estate and rental and leasing 17,200.50 17,227.40 \n", "Professional scientific\\nand technical services 557.40 576.30 \n", "Administrative and support and waste management... 237.90 244.60 \n", "Educational services 455.30 469.20 \n", "Health care and social assistance 1,195.50 1,220.70 \n", "Arts, entertainment, and recreation 287.60 289.10 \n", "Accommodation and food services 548.00 541.90 \n", "Other services 557.50 557.30 \n", "\n", " 2011 2012 \\\n", "name \n", "All 35,557.70 36,693.10 \n", "Agriculture, forestry, fishing, and hunting 507.70 536.50 \n", "Mining 1,752.10 1,886.40 \n", "Utilities 1,980.40 2,064.60 \n", "Construction 271.10 278.60 \n", "Manufacturing 3,340.20 3,438.90 \n", "Wholesale trade 515.30 533.70 \n", "Retail trade 1,177.20 1,205.60 \n", "Transportation and warehousing 1,136.40 1,170.20 \n", "Information 1,908.30 1,948.40 \n", "Finance and insurance 1,183.70 1,209.20 \n", "Real estate and rental and leasing 17,382.40 17,893.50 \n", "Professional scientific\\nand technical services 596.60 616.40 \n", "Administrative and support and waste management... 257.30 265.80 \n", "Educational services 497.10 511.80 \n", "Health care and social assistance 1,268.30 1,309.00 \n", "Arts, entertainment, and recreation 296.60 305.30 \n", "Accommodation and food services 551.80 566.50 \n", "Other services 568.20 578.40 \n", "\n", " 2013 \n", "name \n", "All 38,699.60 \n", "Agriculture, forestry, fishing, and hunting 577.40 \n", "Mining 2,050.60 \n", "Utilities 2,126.20 \n", "Construction 288.30 \n", "Manufacturing 3,580.30 \n", "Wholesale trade 544.20 \n", "Retail trade 1,235.40 \n", "Transportation and warehousing 1,209.00 \n", "Information 2,007.60 \n", "Finance and insurance 1,247.30 \n", "Real estate and rental and leasing 19,099.20 \n", "Professional scientific\\nand technical services 640.50 \n", "Administrative and support and waste management... 277.40 \n", "Educational services 534.80 \n", "Health care and social assistance 1,378.60 \n", "Arts, entertainment, and recreation 323.30 \n", "Accommodation and food services 597.10 \n", "Other services 601.10 " ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "CCNFA=pd.read_excel(fa_section3_link,sheet_name=\"FAAt301ESI-A\",skiprows=7)\n", "CCNFA['IndustryName']=CCNFA['Unnamed: 1']\n", "CCNFA=CCNFA.iloc[:-7]\n", "CCNFA.Line=CCNFA.Line.astype(int)\n", "CCNFA.index=CCNFA.Line\n", "CCNFA=CCNFA[CCNFA['Line'].isin(colnames.CCNFA.values)]\n", "#CCNFA.iloc[get_close_matches_indexes('Management of companies and enterprise', CCNFA.IndustryName)[0],3:]=CCNFA[CCNFA.IndustryName==dl.get_close_matches('Administrative and support and waste', CCNFA.IndustryName)[0]].iloc[0,3:]+\\\n", "#CCNFA[CCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCNFA.IndustryName)[0]].iloc[0,3:]\n", "CCNFA.index=colnames.name\n", "CCNFA=CCNFA.drop(CCNFA[CCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', CCNFA.IndustryName)[0]].index)\n", "CCNFA[years]\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: Historical Cost FA\n", "Table 3.3ESI. Historical-Cost Net Stock of Private Fixed Assets by Industry" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All20,120.4021,021.3021,458.9021,923.2022,519.0023,282.2024,150.10
Agriculture, forestry, fishing, and hunting264.80280.20293.30307.00324.80351.90382.00
Mining662.90764.40813.20876.00974.701,098.501,213.30
Utilities854.90917.00979.901,031.301,088.501,161.801,225.60
Construction215.20228.20216.20213.10214.90220.10230.60
Manufacturing2,179.002,295.202,341.302,390.502,472.302,571.102,680.30
Wholesale trade367.00376.90374.10378.30392.50408.10415.10
Retail trade697.10722.40726.20735.00752.40770.80787.20
Transportation and warehousing591.00619.50626.70639.90659.50690.60728.30
Information1,422.801,482.301,517.801,575.401,633.301,680.701,746.30
Finance and insurance859.90876.80864.10862.00870.20893.30921.60
Real estate and rental and leasing9,411.509,691.609,809.009,927.2010,054.6010,246.5010,518.90
Professional scientific\\nand technical services434.70462.10481.00497.50509.60527.60544.60
Administrative and support and waste management and remediation services171.30174.00181.10187.10194.90202.30212.90
Educational services233.20251.30267.80280.80293.40306.60317.60
Health care and social assistance755.20810.80855.70895.00935.60976.701,020.70
Arts, entertainment, and recreation191.80203.00213.20218.50224.10230.80239.40
Accommodation and food services322.60355.90372.10373.20377.50386.20399.80
Other services308.50324.20334.20340.80346.00350.50354.40
\n", "
" ], "text/plain": [ " 2007 2008 \\\n", "name \n", "All 20,120.40 21,021.30 \n", "Agriculture, forestry, fishing, and hunting 264.80 280.20 \n", "Mining 662.90 764.40 \n", "Utilities 854.90 917.00 \n", "Construction 215.20 228.20 \n", "Manufacturing 2,179.00 2,295.20 \n", "Wholesale trade 367.00 376.90 \n", "Retail trade 697.10 722.40 \n", "Transportation and warehousing 591.00 619.50 \n", "Information 1,422.80 1,482.30 \n", "Finance and insurance 859.90 876.80 \n", "Real estate and rental and leasing 9,411.50 9,691.60 \n", "Professional scientific\\nand technical services 434.70 462.10 \n", "Administrative and support and waste management... 171.30 174.00 \n", "Educational services 233.20 251.30 \n", "Health care and social assistance 755.20 810.80 \n", "Arts, entertainment, and recreation 191.80 203.00 \n", "Accommodation and food services 322.60 355.90 \n", "Other services 308.50 324.20 \n", "\n", " 2009 2010 \\\n", "name \n", "All 21,458.90 21,923.20 \n", "Agriculture, forestry, fishing, and hunting 293.30 307.00 \n", "Mining 813.20 876.00 \n", "Utilities 979.90 1,031.30 \n", "Construction 216.20 213.10 \n", "Manufacturing 2,341.30 2,390.50 \n", "Wholesale trade 374.10 378.30 \n", "Retail trade 726.20 735.00 \n", "Transportation and warehousing 626.70 639.90 \n", "Information 1,517.80 1,575.40 \n", "Finance and insurance 864.10 862.00 \n", "Real estate and rental and leasing 9,809.00 9,927.20 \n", "Professional scientific\\nand technical services 481.00 497.50 \n", "Administrative and support and waste management... 181.10 187.10 \n", "Educational services 267.80 280.80 \n", "Health care and social assistance 855.70 895.00 \n", "Arts, entertainment, and recreation 213.20 218.50 \n", "Accommodation and food services 372.10 373.20 \n", "Other services 334.20 340.80 \n", "\n", " 2011 2012 \\\n", "name \n", "All 22,519.00 23,282.20 \n", "Agriculture, forestry, fishing, and hunting 324.80 351.90 \n", "Mining 974.70 1,098.50 \n", "Utilities 1,088.50 1,161.80 \n", "Construction 214.90 220.10 \n", "Manufacturing 2,472.30 2,571.10 \n", "Wholesale trade 392.50 408.10 \n", "Retail trade 752.40 770.80 \n", "Transportation and warehousing 659.50 690.60 \n", "Information 1,633.30 1,680.70 \n", "Finance and insurance 870.20 893.30 \n", "Real estate and rental and leasing 10,054.60 10,246.50 \n", "Professional scientific\\nand technical services 509.60 527.60 \n", "Administrative and support and waste management... 194.90 202.30 \n", "Educational services 293.40 306.60 \n", "Health care and social assistance 935.60 976.70 \n", "Arts, entertainment, and recreation 224.10 230.80 \n", "Accommodation and food services 377.50 386.20 \n", "Other services 346.00 350.50 \n", "\n", " 2013 \n", "name \n", "All 24,150.10 \n", "Agriculture, forestry, fishing, and hunting 382.00 \n", "Mining 1,213.30 \n", "Utilities 1,225.60 \n", "Construction 230.60 \n", "Manufacturing 2,680.30 \n", "Wholesale trade 415.10 \n", "Retail trade 787.20 \n", "Transportation and warehousing 728.30 \n", "Information 1,746.30 \n", "Finance and insurance 921.60 \n", "Real estate and rental and leasing 10,518.90 \n", "Professional scientific\\nand technical services 544.60 \n", "Administrative and support and waste management... 212.90 \n", "Educational services 317.60 \n", "Health care and social assistance 1,020.70 \n", "Arts, entertainment, and recreation 239.40 \n", "Accommodation and food services 399.80 \n", "Other services 354.40 " ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "HCNFA=pd.read_excel(fa_section3_link,sheet_name=\"FAAt303ESI-A\",skiprows=7)\n", "HCNFA['IndustryName']=HCNFA['Unnamed: 1']\n", "HCNFA=HCNFA.iloc[:-7]\n", "HCNFA.Line=HCNFA.Line.astype(int)\n", "HCNFA.index=HCNFA.Line\n", "HCNFA=HCNFA[HCNFA['Line'].isin(colnames.HCNFA.values)]\n", "#HCNFA.iloc[get_close_matches_indexes('Management of companies and enterprise', HCNFA.IndustryName)[0],3:]=HCNFA[HCNFA.IndustryName==dl.get_close_matches('Administrative and support and waste', HCNFA.IndustryName)[0]].iloc[0,3:]+\\\n", "#HCNFA[HCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', HCNFA.IndustryName)[0]].iloc[0,3:]\n", "HCNFA.index=colnames.name\n", "HCNFA=HCNFA.drop(HCNFA[HCNFA.IndustryName==dl.get_close_matches('Management of companies and enterprise', HCNFA.IndustryName)[0]].index)\n", "\n", "HCNFA[years]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: Historical Cost Depreciation\n", "Table 3.6ESI. Historical-Cost Depreciation of Private Fixed Assets by Industry" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All1,507.801,578.801,616.701,639.501,683.901,752.001,833.50
Agriculture, forestry, fishing, and hunting26.4027.6028.8030.1031.8034.4037.80
Mining51.2059.1064.9069.2076.0086.0096.40
Utilities40.7043.9047.2049.5051.4054.2057.10
Construction32.3034.0032.9031.1030.8031.6033.30
Manufacturing284.90301.20311.70315.70323.80335.80349.10
Wholesale trade52.6052.6051.9051.2052.6055.3057.00
Retail trade59.4060.9060.7060.5062.4065.5068.10
Transportation and warehousing51.1052.5052.8052.3053.2055.3058.10
Information172.00179.40184.60191.50201.60210.90221.40
Finance and insurance124.50128.10127.10125.50126.10130.30137.30
Real estate and rental and leasing338.30347.40346.50344.00344.10348.30358.20
Professional scientific\\nand technical services89.2094.2098.50101.70103.90106.50109.80
Administrative and support and waste management and remediation services27.1027.6028.5030.0031.6033.7036.30
Educational services14.7015.8017.1018.3019.5020.7021.60
Health care and social assistance64.2069.3073.4076.7080.4084.7089.10
Arts, entertainment, and recreation17.1017.9018.5018.9019.2019.5020.10
Accommodation and food services21.7023.1024.0024.0024.0024.5025.40
Other services20.8022.2022.9023.3023.9024.7025.40
\n", "
" ], "text/plain": [ " 2007 2008 2009 \\\n", "name \n", "All 1,507.80 1,578.80 1,616.70 \n", "Agriculture, forestry, fishing, and hunting 26.40 27.60 28.80 \n", "Mining 51.20 59.10 64.90 \n", "Utilities 40.70 43.90 47.20 \n", "Construction 32.30 34.00 32.90 \n", "Manufacturing 284.90 301.20 311.70 \n", "Wholesale trade 52.60 52.60 51.90 \n", "Retail trade 59.40 60.90 60.70 \n", "Transportation and warehousing 51.10 52.50 52.80 \n", "Information 172.00 179.40 184.60 \n", "Finance and insurance 124.50 128.10 127.10 \n", "Real estate and rental and leasing 338.30 347.40 346.50 \n", "Professional scientific\\nand technical services 89.20 94.20 98.50 \n", "Administrative and support and waste management... 27.10 27.60 28.50 \n", "Educational services 14.70 15.80 17.10 \n", "Health care and social assistance 64.20 69.30 73.40 \n", "Arts, entertainment, and recreation 17.10 17.90 18.50 \n", "Accommodation and food services 21.70 23.10 24.00 \n", "Other services 20.80 22.20 22.90 \n", "\n", " 2010 2011 2012 \\\n", "name \n", "All 1,639.50 1,683.90 1,752.00 \n", "Agriculture, forestry, fishing, and hunting 30.10 31.80 34.40 \n", "Mining 69.20 76.00 86.00 \n", "Utilities 49.50 51.40 54.20 \n", "Construction 31.10 30.80 31.60 \n", "Manufacturing 315.70 323.80 335.80 \n", "Wholesale trade 51.20 52.60 55.30 \n", "Retail trade 60.50 62.40 65.50 \n", "Transportation and warehousing 52.30 53.20 55.30 \n", "Information 191.50 201.60 210.90 \n", "Finance and insurance 125.50 126.10 130.30 \n", "Real estate and rental and leasing 344.00 344.10 348.30 \n", "Professional scientific\\nand technical services 101.70 103.90 106.50 \n", "Administrative and support and waste management... 30.00 31.60 33.70 \n", "Educational services 18.30 19.50 20.70 \n", "Health care and social assistance 76.70 80.40 84.70 \n", "Arts, entertainment, and recreation 18.90 19.20 19.50 \n", "Accommodation and food services 24.00 24.00 24.50 \n", "Other services 23.30 23.90 24.70 \n", "\n", " 2013 \n", "name \n", "All 1,833.50 \n", "Agriculture, forestry, fishing, and hunting 37.80 \n", "Mining 96.40 \n", "Utilities 57.10 \n", "Construction 33.30 \n", "Manufacturing 349.10 \n", "Wholesale trade 57.00 \n", "Retail trade 68.10 \n", "Transportation and warehousing 58.10 \n", "Information 221.40 \n", "Finance and insurance 137.30 \n", "Real estate and rental and leasing 358.20 \n", "Professional scientific\\nand technical services 109.80 \n", "Administrative and support and waste management... 36.30 \n", "Educational services 21.60 \n", "Health care and social assistance 89.10 \n", "Arts, entertainment, and recreation 20.10 \n", "Accommodation and food services 25.40 \n", "Other services 25.40 " ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "HFC=pd.read_excel(fa_section3_link,sheet_name=\"FAAt306ESI-A\",skiprows=7)\n", "HFC['IndustryName']=HFC['Unnamed: 1']\n", "HFC=HFC.iloc[:-7]\n", "HFC.Line=HFC.Line.astype(int)\n", "HFC.index=HFC.Line\n", "HFC=HFC[HFC['Line'].isin(colnames.HFC.values)]\n", "#HFC.iloc[get_close_matches_indexes('Management of companies and enterprise', HFC.IndustryName)[0],3:]=HFC[HFC.IndustryName==dl.get_close_matches('Administrative and support and waste', HFC.IndustryName)[0]].iloc[0,3:]+\\\n", "#HFC[HFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', HFC.IndustryName)[0]].iloc[0,3:]\n", "HFC.index=colnames.name\n", "HFC=HFC.drop(HFC[HFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', HFC.IndustryName)[0]].index)\n", "HFC[years]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## BEA FA: Current Cost Depreciation\n", "Table 3.4ESI. Current-Cost Depreciation of Private Fixed Assets by Industry" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All1,852.501,931.801,928.701,933.801,997.302,082.402,176.60
Agriculture, forestry, fishing, and hunting34.1035.9037.0037.6039.6042.2045.60
Mining112.60125.40118.70119.70128.70145.10151.40
Utilities58.7064.8067.2069.9073.6077.4080.00
Construction34.9036.9036.1034.0034.1035.5037.10
Manufacturing326.20347.50353.50356.70369.30383.40395.20
Wholesale trade55.1056.1055.4054.3056.4059.6061.30
Retail trade70.1073.0072.9071.4074.3078.2081.10
Transportation and warehousing64.2066.6066.8065.9067.5070.3073.00
Information169.80176.20178.80185.10197.50206.50216.80
Finance and insurance129.00133.70133.10130.70133.50138.50145.90
Real estate and rental and leasing491.70489.30470.30462.10462.30469.60497.70
Professional scientific\\nand technical services90.2096.0099.30102.50106.00109.30112.70
Administrative and support and waste management and remediation services28.1029.0029.8030.9032.9035.2037.90
Educational services17.6019.0020.6021.7023.1024.5025.50
Health care and social assistance70.8076.2079.3081.3085.3089.5093.40
Arts, entertainment, and recreation21.0021.7022.0021.9022.1022.4023.10
Accommodation and food services28.4030.3030.7029.8029.9030.6031.80
Other services26.5028.2028.5028.3029.2030.2031.00
\n", "
" ], "text/plain": [ " 2007 2008 2009 \\\n", "name \n", "All 1,852.50 1,931.80 1,928.70 \n", "Agriculture, forestry, fishing, and hunting 34.10 35.90 37.00 \n", "Mining 112.60 125.40 118.70 \n", "Utilities 58.70 64.80 67.20 \n", "Construction 34.90 36.90 36.10 \n", "Manufacturing 326.20 347.50 353.50 \n", "Wholesale trade 55.10 56.10 55.40 \n", "Retail trade 70.10 73.00 72.90 \n", "Transportation and warehousing 64.20 66.60 66.80 \n", "Information 169.80 176.20 178.80 \n", "Finance and insurance 129.00 133.70 133.10 \n", "Real estate and rental and leasing 491.70 489.30 470.30 \n", "Professional scientific\\nand technical services 90.20 96.00 99.30 \n", "Administrative and support and waste management... 28.10 29.00 29.80 \n", "Educational services 17.60 19.00 20.60 \n", "Health care and social assistance 70.80 76.20 79.30 \n", "Arts, entertainment, and recreation 21.00 21.70 22.00 \n", "Accommodation and food services 28.40 30.30 30.70 \n", "Other services 26.50 28.20 28.50 \n", "\n", " 2010 2011 2012 \\\n", "name \n", "All 1,933.80 1,997.30 2,082.40 \n", "Agriculture, forestry, fishing, and hunting 37.60 39.60 42.20 \n", "Mining 119.70 128.70 145.10 \n", "Utilities 69.90 73.60 77.40 \n", "Construction 34.00 34.10 35.50 \n", "Manufacturing 356.70 369.30 383.40 \n", "Wholesale trade 54.30 56.40 59.60 \n", "Retail trade 71.40 74.30 78.20 \n", "Transportation and warehousing 65.90 67.50 70.30 \n", "Information 185.10 197.50 206.50 \n", "Finance and insurance 130.70 133.50 138.50 \n", "Real estate and rental and leasing 462.10 462.30 469.60 \n", "Professional scientific\\nand technical services 102.50 106.00 109.30 \n", "Administrative and support and waste management... 30.90 32.90 35.20 \n", "Educational services 21.70 23.10 24.50 \n", "Health care and social assistance 81.30 85.30 89.50 \n", "Arts, entertainment, and recreation 21.90 22.10 22.40 \n", "Accommodation and food services 29.80 29.90 30.60 \n", "Other services 28.30 29.20 30.20 \n", "\n", " 2013 \n", "name \n", "All 2,176.60 \n", "Agriculture, forestry, fishing, and hunting 45.60 \n", "Mining 151.40 \n", "Utilities 80.00 \n", "Construction 37.10 \n", "Manufacturing 395.20 \n", "Wholesale trade 61.30 \n", "Retail trade 81.10 \n", "Transportation and warehousing 73.00 \n", "Information 216.80 \n", "Finance and insurance 145.90 \n", "Real estate and rental and leasing 497.70 \n", "Professional scientific\\nand technical services 112.70 \n", "Administrative and support and waste management... 37.90 \n", "Educational services 25.50 \n", "Health care and social assistance 93.40 \n", "Arts, entertainment, and recreation 23.10 \n", "Accommodation and food services 31.80 \n", "Other services 31.00 " ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "CFC=pd.read_excel(fa_section3_link,sheet_name=\"FAAt304ESI-A\",skiprows=7)\n", "CFC['IndustryName']=CFC['Unnamed: 1']\n", "CFC=CFC.drop(columns=['Unnamed: 1','Unnamed: 2'])\n", "CFC=CFC[:-7]\n", "CFC.Line=CFC.Line.astype(int)\n", "CFC=CFC[CFC['Line'].isin(colnames.CFC.values)]\n", "#CFC.iloc[get_close_matches_indexes('Management of companies and enterprise', CFC.IndustryName)[0],3:]=CFC[CFC.IndustryName==dl.get_close_matches('Administrative and support and waste', CFC.IndustryName)[0]].iloc[0,3:]+\\\n", "#CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].iloc[0,3:]\n", "CFC.index=colnames.name\n", "CFC=CFC.drop(CFC[CFC.IndustryName==dl.get_close_matches('Management of companies and enterprise', CFC.IndustryName)[0]].index)\n", "CFC[years]" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Index for Historical Cost to Current Cost for Gross Fixed Assets \n", "\n", "$$ \\frac{\\text{HCNFA}+ \\text{HFC} } {{\\text{CCNFA}+ \\text{CFC} } } $$" ] }, { "cell_type": "code", "execution_count": 15, "metadata": { "scrolled": false }, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
name
All1.661.631.561.551.551.551.57
Agriculture, forestry, fishing, and hunting1.741.721.601.561.531.501.48
Mining2.282.151.831.791.791.721.68
Utilities1.841.881.761.781.801.761.72
Construction1.211.221.221.221.241.251.23
Manufacturing1.351.371.321.321.331.311.31
Wholesale trade1.281.311.271.271.281.281.28
Retail trade1.561.601.531.511.541.541.54
Transportation and warehousing1.711.731.681.681.691.661.63
Information1.151.141.121.141.151.141.13
Finance and insurance1.311.341.311.301.321.321.32
Real estate and rental and leasing1.881.801.741.721.721.731.80
Professional scientific\\nand technical services1.151.161.131.131.151.141.15
Administrative and support and waste management and remediation services1.301.331.281.271.281.281.27
Educational services1.681.751.671.641.661.641.65
Health care and social assistance1.501.471.371.341.331.321.33
Arts, entertainment, and recreation1.441.431.341.311.311.311.33
Accommodation and food services1.621.581.461.441.451.451.48
Other services1.791.771.641.611.621.621.66
\n", "
" ], "text/plain": [ " 2007 2008 2009 2010 \\\n", "name \n", "All 1.66 1.63 1.56 1.55 \n", "Agriculture, forestry, fishing, and hunting 1.74 1.72 1.60 1.56 \n", "Mining 2.28 2.15 1.83 1.79 \n", "Utilities 1.84 1.88 1.76 1.78 \n", "Construction 1.21 1.22 1.22 1.22 \n", "Manufacturing 1.35 1.37 1.32 1.32 \n", "Wholesale trade 1.28 1.31 1.27 1.27 \n", "Retail trade 1.56 1.60 1.53 1.51 \n", "Transportation and warehousing 1.71 1.73 1.68 1.68 \n", "Information 1.15 1.14 1.12 1.14 \n", "Finance and insurance 1.31 1.34 1.31 1.30 \n", "Real estate and rental and leasing 1.88 1.80 1.74 1.72 \n", "Professional scientific\\nand technical services 1.15 1.16 1.13 1.13 \n", "Administrative and support and waste management... 1.30 1.33 1.28 1.27 \n", "Educational services 1.68 1.75 1.67 1.64 \n", "Health care and social assistance 1.50 1.47 1.37 1.34 \n", "Arts, entertainment, and recreation 1.44 1.43 1.34 1.31 \n", "Accommodation and food services 1.62 1.58 1.46 1.44 \n", "Other services 1.79 1.77 1.64 1.61 \n", "\n", " 2011 2012 2013 \n", "name \n", "All 1.55 1.55 1.57 \n", "Agriculture, forestry, fishing, and hunting 1.53 1.50 1.48 \n", "Mining 1.79 1.72 1.68 \n", "Utilities 1.80 1.76 1.72 \n", "Construction 1.24 1.25 1.23 \n", "Manufacturing 1.33 1.31 1.31 \n", "Wholesale trade 1.28 1.28 1.28 \n", "Retail trade 1.54 1.54 1.54 \n", "Transportation and warehousing 1.69 1.66 1.63 \n", "Information 1.15 1.14 1.13 \n", "Finance and insurance 1.32 1.32 1.32 \n", "Real estate and rental and leasing 1.72 1.73 1.80 \n", "Professional scientific\\nand technical services 1.15 1.14 1.15 \n", "Administrative and support and waste management... 1.28 1.28 1.27 \n", "Educational services 1.66 1.64 1.65 \n", "Health care and social assistance 1.33 1.32 1.33 \n", "Arts, entertainment, and recreation 1.31 1.31 1.33 \n", "Accommodation and food services 1.45 1.45 1.48 \n", "Other services 1.62 1.62 1.66 " ] }, "metadata": {}, "output_type": "display_data" }, { "name": "stderr", "output_type": "stream", "text": [ "C:\\Users\\admin\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexing.py:670: SettingWithCopyWarning: \n", "A value is trying to be set on a copy of a slice from a DataFrame\n", "\n", "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n", " self._setitem_with_indexer(indexer, value)\n" ] } ], "source": [ "HCGFA=HCNFA+HFC\n", "CCGFA=CCNFA+CFC\n", "IndexCCFA=pd.DataFrame(CCGFA[years]/HCGFA[years])\n", "display(IndexCCFA)\n", "IndexCCFA['IndustryName']=CCNFA.IndustryName\n", "IndexCCFA['IndustryName'].iloc[0]='All'\n", "IndexCCFA['IndustryName'].iloc[-1]='Other services'" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Source SOI Data\n", " From the SOI data we extract business receipts and define total tangible assets using\n" ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "scrolled": true }, "outputs": [], "source": [ "\n", "def downloadSCorpData(year):\n", " if int(year) > 2005:\n", " soi_link= \"https://www.irs.gov/pub/irs-soi/\"+ year[2:] + \"co07s.xls\"\n", " elif int(year)==2005:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/05co1120s07.xls\"\n", " elif int(year)==2004:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/04co14ccr.xls\"\n", " elif int(year)==2003:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/03co14bs.xls\"\n", " elif int(year)==2002:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/02co14bs.xls\"\n", " elif int(year)==2001:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/01co14bs.xls\"\n", " elif int(year)==2000:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/00co14bs.xls\"\n", " elif int(year)==1999:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/99co14bs.xls\"\n", " elif int(year)==1998:\n", " soi_link=\"https://www.irs.gov/pub/irs-soi/98co14bs.xls\"\n", " \n", " print(soi_link)\n", " ScorpData=getBSData(soi_link)\n", " ScorpData=ScorpData.T\n", " # ScorpData.iloc[get_close_matches_indexes('Management of companies and enterprise', ScorpData.index)[0],7:]=ScorpData.iloc[get_close_matches_indexes('Management of companies and enterprise', ScorpData.index)[0],7:]+ScorpData.iloc[get_close_matches_indexes('Administrative and support and waste', ScorpData.index)[0],7:]\n", " ScorpData=ScorpData.drop(ScorpData[ScorpData.index==dl.get_close_matches('Management of companies and enterprise', ScorpData.index)[0]].index)\n", " return ScorpData\n", "\n", "def addHCFA(ScorpData):\n", " CCFASOI=[]\n", " ScorpData['HCFA']=ScorpData[dl.get_close_matches('Depreciable assets', ScorpData.columns)[0]]+\\\n", " ScorpData[dl.get_close_matches('Depletable assets', ScorpData.columns)[0]]\n", " return ScorpData\n", "\n", "def addCCFA(ScorpData,year):\n", " CCFASOI=[]\n", " for SOIindex in ScorpData.index:\n", " factor=IndexCCFA.iloc[get_close_matches_indexes(SOIindex,IndexCCFA.index)][year].values\n", " HCFASOI=ScorpData['HCFA'][SOIindex] \n", " HCFASOI=float(HCFASOI)\n", " factor=float(factor[0])\n", " CCFASOI.append(HCFASOI*factor)\n", "\n", " ScorpData['CCFA']= np.array(CCFASOI)\n", "\n", " return ScorpData\n", "\n", "def addCCA(ScorpData):\n", " ScorpData['CCA']=ScorpData[dl.get_close_matches('Less: Accumulated depreciation', ScorpData.columns)[0]]+\\\n", " ScorpData[dl.get_close_matches('Less: Accumulated depletion', ScorpData.columns)[0]]\n", " return ScorpData\n", "\n", "def addCFC(ScorpData,year):\n", " CFCSOI=[]\n", "\n", " factorAll=IndexDep.iloc[get_close_matches_indexes('All',IndexDep.index)][year].values \n", " \n", " for SOIindex in ScorpData.index:\n", " if len(get_close_matches_indexes(SOIindex,IndexDep.index))>0:\n", " factor=IndexDep.iloc[get_close_matches_indexes(SOIindex,IndexDep.index)][year].values\n", " else:\n", " factor=factorAll \n", " \n", " if factor[0]>3.0:\n", " factor=factorAll\n", " \n", " CCASOI=ScorpData['CCA'][SOIindex] \n", " CCASOI=float(CCASOI)\n", " factor=float(factor[0])\n", " CFCSOI.append(CCASOI*factor)\n", " \n", "\n", " ScorpData['CFC']= np.array(CFCSOI)\n", " return ScorpData\n", "\n", "\n", "def addTotalTangibleAssets(ScorpData):\n", " ScorpData['CurrentAssets']= ScorpData[dl.get_close_matches('Notes and accounts receivable', ScorpData.columns)[0]]\\\n", " - ScorpData[dl.get_close_matches('Less: Allowance for bad debts..', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('other assets', ScorpData.columns)[0]] \n", " \n", " ScorpData['Inventories']= ScorpData[dl.get_close_matches('Inventories', ScorpData.columns)[0]] \n", " \n", " ScorpData['FixedAssetsLessDep']= ScorpData[dl.get_close_matches('CCFA', ScorpData.columns)[0]] \\\n", " - ScorpData[dl.get_close_matches('CFC', ScorpData.columns)[0]] \\\n", " \n", " ScorpData['Land']= ScorpData[dl.get_close_matches('Land', ScorpData.columns)[0]] \n", " \n", " ScorpData['CurrentLiabilities']= ScorpData[dl.get_close_matches('Accounts payable..', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('Other current liabilities..', ScorpData.columns)[0]] \n", " \n", " ScorpData['TotalTangibleAssets']= ScorpData[dl.get_close_matches('Notes and accounts receivable', ScorpData.columns)[0]]\\\n", " - ScorpData[dl.get_close_matches('Less: Allowance for bad debts..', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('Inventories', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('other current assets', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('CCFA', ScorpData.columns)[0]] \\\n", " - ScorpData[dl.get_close_matches('CFC', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('Land', ScorpData.columns)[0]] \\\n", " + ScorpData[dl.get_close_matches('other assets', ScorpData.columns)[0]]\\\n", " \n", "\n", " ScorpData['TotalTangibleAssets']= ScorpData['TotalTangibleAssets']+\\\n", " - ScorpData[dl.get_close_matches('Accounts payable..', ScorpData.columns)[0]] \\\n", " - ScorpData[dl.get_close_matches('Other current liabilities..', ScorpData.columns)[0]] \\\n", "\n", " ScorpData['TotalTangibleAssetsMrtg'] = ScorpData['TotalTangibleAssets']+ \\\n", " + ScorpData[dl.get_close_matches('Mortgage and real estate loans..', ScorpData.columns)[0]]\\\n", " - ScorpData[dl.get_close_matches('Mortgages, notes, bonds, one year or more', ScorpData.columns)[0]] \n", "\n", " ScorpData['BusinessReceipts'] = ScorpData[dl.get_close_matches('Business receipts', ScorpData.columns)[0]]\n", " return ScorpData\n", "\n", " \n", " \n", "\n", "\n", "def getSCropData(year):\n", " ScorpData = downloadSCorpData(year)\n", " ScorpData = addHCFA (ScorpData )\n", " ScorpData = addCCFA (ScorpData,year)\n", " ScorpData = addCCA (ScorpData )\n", " ScorpData = addCFC (ScorpData,year )\n", " ScorpData = addTotalTangibleAssets (ScorpData)\n", " return ScorpData\n", "\n", "def getPriceSalesMultiples():\n", " price_sales_multiple=pd.read_csv('price_sales_mean.csv')\n", " price_sales_multiple.columns=['IndustryName','PSMultiple']\n", " price_sales_multiple['IndustryName'].iloc[1]='Mining'\n", " price_sales_multiple['IndustryName'].iloc[12]='Administrative and support and waste management and remediation services'\n", " return price_sales_multiple\n", "\n", "def getIntanIntensity(ScorpData,price_sales_multiple,liquidity_factor):\n", " IITaxdata=pd.DataFrame( ScorpData['BusinessReceipts']/ScorpData['TotalTangibleAssets'],columns=['Sales_TangibleAssets'])\n", " price_sales_multiple = getPriceSalesMultiples()\n", " Value_Sales=[]\n", "\n", " factorAll=IndexDep.iloc[get_close_matches_indexes('All',price_sales_multiple.IndustryName)].values \n", " \n", " for IITaxdataindex in IITaxdata.index:\n", " if len(get_close_matches_indexes(IITaxdataindex,price_sales_multiple.IndustryName))>0:\n", " factor=price_sales_multiple.iloc[get_close_matches_indexes(IITaxdataindex,price_sales_multiple.IndustryName)].PSMultiple.values \n", " else:\n", " factor=factorAll \n", " \n", " Value_Sales.append(factor[0])\n", " \n", "\n", " IITaxdata['Value_Sales']= np.array(Value_Sales)\n", " IITaxdata['Value_Sales_alt']= np.array(Value_Sales)*liquidity_factor\n", "\n", " IITaxdata['Value_TangibleAssets']=IITaxdata['Sales_TangibleAssets']*IITaxdata['Value_Sales']\n", " IITaxdata['Intan_TangibleAssets']=IITaxdata['Value_TangibleAssets']-1.0\n", " IITaxdata['IntanIntensity']=IITaxdata['Intan_TangibleAssets']/IITaxdata['Value_TangibleAssets']\n", " IITaxdata['Value_TangibleAssets_alt']=IITaxdata['Sales_TangibleAssets']*IITaxdata['Value_Sales_alt']\n", " IITaxdata['Intan_TangibleAssets_alt']=IITaxdata['Value_TangibleAssets_alt']-1.0\n", " IITaxdata['IntanIntensity_alt']=IITaxdata['Intan_TangibleAssets_alt']/IITaxdata['Value_TangibleAssets_alt']\n", " IITaxdata['IntanIntensityMrtg']= 1- (ScorpData['TotalTangibleAssetsMrtg']/ScorpData['BusinessReceipts'])/IITaxdata['Value_Sales']\n", "\n", "\n", " return IITaxdata[['IntanIntensity','IntanIntensity_alt','Sales_TangibleAssets','Value_Sales','IntanIntensityMrtg']]\n", " \n" ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "https://www.irs.gov/pub/irs-soi/07co07s.xls\n", "https://www.irs.gov/pub/irs-soi/08co07s.xls\n", "https://www.irs.gov/pub/irs-soi/09co07s.xls\n", "https://www.irs.gov/pub/irs-soi/10co07s.xls\n", "https://www.irs.gov/pub/irs-soi/11co07s.xls\n", "https://www.irs.gov/pub/irs-soi/12co07s.xls\n", "https://www.irs.gov/pub/irs-soi/13co07s.xls\n" ] } ], "source": [ "\n", "IITaxdataList=[]\n", "IITaxdata_altList=[]\n", "IITaxdataMrtgList=[]\n", "MktCapToSalesList=[]\n", "BusReceiptsToTangibleAssetsList=[]\n", " \n", "for year in years:\n", " ScorpData =getSCropData (year) \n", " price_sales_multiple = getPriceSalesMultiples()\n", " liquidity_factor =0.75\n", " temp=[]\n", " temp = getIntanIntensity(ScorpData,price_sales_multiple,liquidity_factor)\n", " IITaxdataList.append(temp.IntanIntensity.values )\n", " IITaxdata_altList.append(temp.IntanIntensity_alt.values)\n", " MktCapToSalesList.append(temp.Value_Sales.values)\n", " BusReceiptsToTangibleAssetsList.append(temp.Sales_TangibleAssets.values)\n", " IITaxdataMrtgList.append(temp.IntanIntensityMrtg.values)\n", "\n", "IITaxdata = pd.DataFrame(IITaxdataList,index=years,columns=temp.index).T\n", "IITaxdataAlt = pd.DataFrame(IITaxdata_altList,index=years,columns=temp.index).T\n", "MktCapToSales = pd.DataFrame(MktCapToSalesList,index=years,columns=temp.index).T\n", "BusReceiptsToTangibleAssets = pd.DataFrame(BusReceiptsToTangibleAssetsList,index=years,columns=temp.index).T\n", "IITaxdataMrtg = pd.DataFrame(IITaxdataMrtgList,index=years,columns=temp.index).T\n", " # IITaxdataList.append(IITaxdata)\n", " # IITaxdata_altList.append(IITaxdata_alt)\n", " \n", " \n", "\n", "#IITaxdata = pd.DataFrame(IITaxdataList,index=years).T\n", "#IITaxdataAlt = pd.DataFrame(IITaxdata_altList,index=years).T\n", "\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Total Tangible Assets SOI\n", "$$\\text{TotalTangibleAssets} = \\text{Accounts receivable net of bad debts and payables} + \\text{inventories} +\\text{other current assets net of current liabilities} + \\text{Fixed assets (current cost) net of depreciation} + \\text{land} + \\text{other assets} $$\n" ] }, { "cell_type": "code", "execution_count": 18, "metadata": {}, "outputs": [], "source": [ "#ScorpData[['BusinessReceipts','TotalTangibleAssets']]/1000.0" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Tangible Assets (SOI) divided by Business Receipts (SOI)\n", "$$ \\frac{ \\text{TotalTangibleAssets (SOI) } } {\\text{Business Receipts (SOI) } } $$" ] }, { "cell_type": "code", "execution_count": 19, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
All0.320.340.340.330.320.280.29
Agriculture, forestry, fishing, and hunting0.981.000.970.930.790.670.70
Mining0.951.011.101.081.030.971.00
Utilities0.550.520.770.530.630.770.65
Construction0.240.240.240.220.210.160.15
Manufacturing0.440.470.520.490.480.450.46
Wholesale trade0.180.180.200.190.190.180.19
Retail trade0.210.240.240.230.230.220.22
Transportation and warehousing0.610.590.670.620.610.580.60
Information0.210.240.240.250.240.180.13
Finance and insurance0.070.11-0.03-0.09-0.24-0.54-0.58
Real estate and rental and leasing3.582.692.832.682.822.302.22
Professional scientific\\nand technical services0.020.040.030.030.040.010.03
Administrative and support and waste management and remediation services0.090.130.110.120.110.080.07
Educational services0.250.270.280.280.290.260.27
Health care and social assistance0.150.170.130.140.150.130.13
Arts, entertainment, and recreation0.250.290.210.220.200.160.14
Accommodation and food services0.620.700.640.580.560.530.54
Other services0.330.370.340.320.350.310.33
\n", "
" ], "text/plain": [ " 2007 2008 2009 2010 \\\n", "All 0.32 0.34 0.34 0.33 \n", "Agriculture, forestry, fishing, and hunting 0.98 1.00 0.97 0.93 \n", "Mining 0.95 1.01 1.10 1.08 \n", "Utilities 0.55 0.52 0.77 0.53 \n", "Construction 0.24 0.24 0.24 0.22 \n", "Manufacturing 0.44 0.47 0.52 0.49 \n", "Wholesale trade 0.18 0.18 0.20 0.19 \n", "Retail trade 0.21 0.24 0.24 0.23 \n", "Transportation and warehousing 0.61 0.59 0.67 0.62 \n", "Information 0.21 0.24 0.24 0.25 \n", "Finance and insurance 0.07 0.11 -0.03 -0.09 \n", "Real estate and rental and leasing 3.58 2.69 2.83 2.68 \n", "Professional scientific\\nand technical services 0.02 0.04 0.03 0.03 \n", "Administrative and support and waste management... 0.09 0.13 0.11 0.12 \n", "Educational services 0.25 0.27 0.28 0.28 \n", "Health care and social assistance 0.15 0.17 0.13 0.14 \n", "Arts, entertainment, and recreation 0.25 0.29 0.21 0.22 \n", "Accommodation and food services 0.62 0.70 0.64 0.58 \n", "Other services 0.33 0.37 0.34 0.32 \n", "\n", " 2011 2012 2013 \n", "All 0.32 0.28 0.29 \n", "Agriculture, forestry, fishing, and hunting 0.79 0.67 0.70 \n", "Mining 1.03 0.97 1.00 \n", "Utilities 0.63 0.77 0.65 \n", "Construction 0.21 0.16 0.15 \n", "Manufacturing 0.48 0.45 0.46 \n", "Wholesale trade 0.19 0.18 0.19 \n", "Retail trade 0.23 0.22 0.22 \n", "Transportation and warehousing 0.61 0.58 0.60 \n", "Information 0.24 0.18 0.13 \n", "Finance and insurance -0.24 -0.54 -0.58 \n", "Real estate and rental and leasing 2.82 2.30 2.22 \n", "Professional scientific\\nand technical services 0.04 0.01 0.03 \n", "Administrative and support and waste management... 0.11 0.08 0.07 \n", "Educational services 0.29 0.26 0.27 \n", "Health care and social assistance 0.15 0.13 0.13 \n", "Arts, entertainment, and recreation 0.20 0.16 0.14 \n", "Accommodation and food services 0.56 0.53 0.54 \n", "Other services 0.35 0.31 0.33 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "\n", "display(1/BusReceiptsToTangibleAssets)\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Sales divided by Market Cap (Compustat)\n", "$$ \\frac{\\text{ sales (COMPUSTAT) }} {\\text{market cap (COMPUSTAT)} } $$" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Avg: 1994-2018
All0.77
Agriculture, forestry, fishing, and hunting0.84
Mining0.59
Utilities1.69
Construction1.88
Manufacturing0.63
Wholesale trade3.66
Retail trade1.45
Transportation and warehousing1.45
Information0.44
Finance and insurance0.60
Real estate and rental and leasing0.37
Professional scientific\\nand technical services0.70
Administrative and support and waste management and remediation services0.90
Educational services0.55
Health care and social assistance1.35
Arts, entertainment, and recreation0.77
Accommodation and food services0.68
Other services1.00
\n", "
" ], "text/plain": [ " Avg: 1994-2018\n", "All 0.77\n", "Agriculture, forestry, fishing, and hunting 0.84\n", "Mining 0.59\n", "Utilities 1.69\n", "Construction 1.88\n", "Manufacturing 0.63\n", "Wholesale trade 3.66\n", "Retail trade 1.45\n", "Transportation and warehousing 1.45\n", "Information 0.44\n", "Finance and insurance 0.60\n", "Real estate and rental and leasing 0.37\n", "Professional scientific\\nand technical services 0.70\n", "Administrative and support and waste management... 0.90\n", "Educational services 0.55\n", "Health care and social assistance 1.35\n", "Arts, entertainment, and recreation 0.77\n", "Accommodation and food services 0.68\n", "Other services 1.00" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "\n", "display(1/pd.DataFrame(MktCapToSales.iloc[:,0].values,index=MktCapToSales.index,columns=['Avg: 1994-2018'])) \n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Intangible Intensity \n", "Define $$ii = 1-\\frac{K}{V} =1- \\frac{K}{S} \\times \\frac{S}{V} $$\n", "where \n", "$$ \\frac{K}{S} = \\frac{ \\text{TotalTangibleAssets (SOI) } } {\\text{Business Receipts (SOI) } } $$\n", "and\n", "$$ \\frac{S}{V} = \\frac{\\text{ sales (COMPUSTAT) }} {\\text{market cap (COMPUSTAT)} } $$\n" ] }, { "cell_type": "code", "execution_count": 21, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
All0.750.740.740.750.760.780.78
Agriculture, forestry, fishing, and hunting0.180.170.190.220.340.430.41
Mining0.440.410.350.370.400.430.41
Utilities0.070.12-0.310.11-0.06-0.31-0.09
Construction0.540.550.550.580.600.700.72
Manufacturing0.720.700.670.690.690.720.71
Wholesale trade0.340.340.270.300.320.320.30
Retail trade0.690.650.660.670.670.690.68
Transportation and warehousing0.120.140.030.100.110.160.13
Information0.910.890.890.890.900.920.94
Finance and insurance0.960.931.021.061.141.321.35
Real estate and rental and leasing-0.330.00-0.050.00-0.050.150.18
Professional scientific\\nand technical services0.980.970.980.980.980.990.98
Administrative and support and waste management and remediation services0.920.880.900.890.900.930.94
Educational services0.860.850.850.850.840.860.85
Health care and social assistance0.800.770.820.810.800.830.83
Arts, entertainment, and recreation0.810.780.830.830.840.880.89
Accommodation and food services0.570.520.560.600.620.640.63
Other services0.670.630.660.680.650.690.67
\n", "
" ], "text/plain": [ " 2007 2008 2009 2010 \\\n", "All 0.75 0.74 0.74 0.75 \n", "Agriculture, forestry, fishing, and hunting 0.18 0.17 0.19 0.22 \n", "Mining 0.44 0.41 0.35 0.37 \n", "Utilities 0.07 0.12 -0.31 0.11 \n", "Construction 0.54 0.55 0.55 0.58 \n", "Manufacturing 0.72 0.70 0.67 0.69 \n", "Wholesale trade 0.34 0.34 0.27 0.30 \n", "Retail trade 0.69 0.65 0.66 0.67 \n", "Transportation and warehousing 0.12 0.14 0.03 0.10 \n", "Information 0.91 0.89 0.89 0.89 \n", "Finance and insurance 0.96 0.93 1.02 1.06 \n", "Real estate and rental and leasing -0.33 0.00 -0.05 0.00 \n", "Professional scientific\\nand technical services 0.98 0.97 0.98 0.98 \n", "Administrative and support and waste management... 0.92 0.88 0.90 0.89 \n", "Educational services 0.86 0.85 0.85 0.85 \n", "Health care and social assistance 0.80 0.77 0.82 0.81 \n", "Arts, entertainment, and recreation 0.81 0.78 0.83 0.83 \n", "Accommodation and food services 0.57 0.52 0.56 0.60 \n", "Other services 0.67 0.63 0.66 0.68 \n", "\n", " 2011 2012 2013 \n", "All 0.76 0.78 0.78 \n", "Agriculture, forestry, fishing, and hunting 0.34 0.43 0.41 \n", "Mining 0.40 0.43 0.41 \n", "Utilities -0.06 -0.31 -0.09 \n", "Construction 0.60 0.70 0.72 \n", "Manufacturing 0.69 0.72 0.71 \n", "Wholesale trade 0.32 0.32 0.30 \n", "Retail trade 0.67 0.69 0.68 \n", "Transportation and warehousing 0.11 0.16 0.13 \n", "Information 0.90 0.92 0.94 \n", "Finance and insurance 1.14 1.32 1.35 \n", "Real estate and rental and leasing -0.05 0.15 0.18 \n", "Professional scientific\\nand technical services 0.98 0.99 0.98 \n", "Administrative and support and waste management... 0.90 0.93 0.94 \n", "Educational services 0.84 0.86 0.85 \n", "Health care and social assistance 0.80 0.83 0.83 \n", "Arts, entertainment, and recreation 0.84 0.88 0.89 \n", "Accommodation and food services 0.62 0.64 0.63 \n", "Other services 0.65 0.69 0.67 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "\n", "display(IITaxdata)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Intangible Intensity with FoF liquidity adjustment of 75 %" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
All0.670.650.650.670.670.710.71
Agriculture, forestry, fishing, and hunting-0.10-0.11-0.08-0.040.120.250.21
Mining0.250.210.140.160.190.240.22
Utilities-0.25-0.18-0.74-0.19-0.41-0.74-0.46
Construction0.390.400.410.450.460.600.62
Manufacturing0.630.610.560.590.590.620.61
Wholesale trade0.120.120.030.060.090.100.07
Retail trade0.590.530.540.550.560.580.57
Transportation and warehousing-0.18-0.15-0.29-0.21-0.18-0.12-0.17
Information0.880.860.860.850.860.900.92
Finance and insurance0.950.911.021.071.191.431.46
Real estate and rental and leasing-0.77-0.33-0.40-0.33-0.39-0.14-0.10
Professional scientific\\nand technical services0.980.960.970.970.970.990.98
Administrative and support and waste management and remediation services0.890.850.860.860.870.900.92
Educational services0.820.800.790.800.790.810.80
Health care and social assistance0.730.690.760.750.730.770.77
Arts, entertainment, and recreation0.740.700.780.770.790.840.85
Accommodation and food services0.430.360.410.470.490.510.50
Other services0.570.510.550.570.540.590.56
\n", "
" ], "text/plain": [ " 2007 2008 2009 2010 \\\n", "All 0.67 0.65 0.65 0.67 \n", "Agriculture, forestry, fishing, and hunting -0.10 -0.11 -0.08 -0.04 \n", "Mining 0.25 0.21 0.14 0.16 \n", "Utilities -0.25 -0.18 -0.74 -0.19 \n", "Construction 0.39 0.40 0.41 0.45 \n", "Manufacturing 0.63 0.61 0.56 0.59 \n", "Wholesale trade 0.12 0.12 0.03 0.06 \n", "Retail trade 0.59 0.53 0.54 0.55 \n", "Transportation and warehousing -0.18 -0.15 -0.29 -0.21 \n", "Information 0.88 0.86 0.86 0.85 \n", "Finance and insurance 0.95 0.91 1.02 1.07 \n", "Real estate and rental and leasing -0.77 -0.33 -0.40 -0.33 \n", "Professional scientific\\nand technical services 0.98 0.96 0.97 0.97 \n", "Administrative and support and waste management... 0.89 0.85 0.86 0.86 \n", "Educational services 0.82 0.80 0.79 0.80 \n", "Health care and social assistance 0.73 0.69 0.76 0.75 \n", "Arts, entertainment, and recreation 0.74 0.70 0.78 0.77 \n", "Accommodation and food services 0.43 0.36 0.41 0.47 \n", "Other services 0.57 0.51 0.55 0.57 \n", "\n", " 2011 2012 2013 \n", "All 0.67 0.71 0.71 \n", "Agriculture, forestry, fishing, and hunting 0.12 0.25 0.21 \n", "Mining 0.19 0.24 0.22 \n", "Utilities -0.41 -0.74 -0.46 \n", "Construction 0.46 0.60 0.62 \n", "Manufacturing 0.59 0.62 0.61 \n", "Wholesale trade 0.09 0.10 0.07 \n", "Retail trade 0.56 0.58 0.57 \n", "Transportation and warehousing -0.18 -0.12 -0.17 \n", "Information 0.86 0.90 0.92 \n", "Finance and insurance 1.19 1.43 1.46 \n", "Real estate and rental and leasing -0.39 -0.14 -0.10 \n", "Professional scientific\\nand technical services 0.97 0.99 0.98 \n", "Administrative and support and waste management... 0.87 0.90 0.92 \n", "Educational services 0.79 0.81 0.80 \n", "Health care and social assistance 0.73 0.77 0.77 \n", "Arts, entertainment, and recreation 0.79 0.84 0.85 \n", "Accommodation and food services 0.49 0.51 0.50 \n", "Other services 0.54 0.59 0.56 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "display(IITaxdataAlt )" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Additional Details for Real estate and rental and leasing\n", "In the SOI tax data, the real estate sector has a lot of liabilities.These are mainly mortgages/loans on the real estate that is used in the business. We compute the intangible intensity after netting out the mortgage liabilities. " ] }, { "cell_type": "code", "execution_count": 23, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
2007200820092010201120122013
All0.830.820.820.830.820.850.84
Agriculture, forestry, fishing, and hunting0.370.360.420.420.510.600.57
Mining0.550.510.480.480.490.550.53
Utilities0.350.390.040.360.17-0.050.12
Construction0.710.710.720.740.730.820.83
Manufacturing0.780.760.730.750.750.770.76
Wholesale trade0.480.470.430.430.440.460.44
Retail trade0.760.720.730.730.730.750.74
Transportation and warehousing0.340.330.260.280.280.330.32
Information0.981.010.940.940.940.970.98
Finance and insurance1.000.981.061.071.121.221.25
Real estate and rental and leasing0.190.600.630.640.570.680.67
Professional scientific\\nand technical services1.011.001.011.011.011.021.01
Administrative and support and waste management and remediation services0.970.940.960.950.950.981.00
Educational services0.910.890.880.880.870.890.88
Health care and social assistance0.920.890.940.930.910.950.94
Arts, entertainment, and recreation1.010.981.051.011.011.031.03
Accommodation and food services0.730.690.730.760.760.780.77
Other services0.790.750.780.790.760.800.78
\n", "
" ], "text/plain": [ " 2007 2008 2009 2010 \\\n", "All 0.83 0.82 0.82 0.83 \n", "Agriculture, forestry, fishing, and hunting 0.37 0.36 0.42 0.42 \n", "Mining 0.55 0.51 0.48 0.48 \n", "Utilities 0.35 0.39 0.04 0.36 \n", "Construction 0.71 0.71 0.72 0.74 \n", "Manufacturing 0.78 0.76 0.73 0.75 \n", "Wholesale trade 0.48 0.47 0.43 0.43 \n", "Retail trade 0.76 0.72 0.73 0.73 \n", "Transportation and warehousing 0.34 0.33 0.26 0.28 \n", "Information 0.98 1.01 0.94 0.94 \n", "Finance and insurance 1.00 0.98 1.06 1.07 \n", "Real estate and rental and leasing 0.19 0.60 0.63 0.64 \n", "Professional scientific\\nand technical services 1.01 1.00 1.01 1.01 \n", "Administrative and support and waste management... 0.97 0.94 0.96 0.95 \n", "Educational services 0.91 0.89 0.88 0.88 \n", "Health care and social assistance 0.92 0.89 0.94 0.93 \n", "Arts, entertainment, and recreation 1.01 0.98 1.05 1.01 \n", "Accommodation and food services 0.73 0.69 0.73 0.76 \n", "Other services 0.79 0.75 0.78 0.79 \n", "\n", " 2011 2012 2013 \n", "All 0.82 0.85 0.84 \n", "Agriculture, forestry, fishing, and hunting 0.51 0.60 0.57 \n", "Mining 0.49 0.55 0.53 \n", "Utilities 0.17 -0.05 0.12 \n", "Construction 0.73 0.82 0.83 \n", "Manufacturing 0.75 0.77 0.76 \n", "Wholesale trade 0.44 0.46 0.44 \n", "Retail trade 0.73 0.75 0.74 \n", "Transportation and warehousing 0.28 0.33 0.32 \n", "Information 0.94 0.97 0.98 \n", "Finance and insurance 1.12 1.22 1.25 \n", "Real estate and rental and leasing 0.57 0.68 0.67 \n", "Professional scientific\\nand technical services 1.01 1.02 1.01 \n", "Administrative and support and waste management... 0.95 0.98 1.00 \n", "Educational services 0.87 0.89 0.88 \n", "Health care and social assistance 0.91 0.95 0.94 \n", "Arts, entertainment, and recreation 1.01 1.03 1.03 \n", "Accommodation and food services 0.76 0.78 0.77 \n", "Other services 0.76 0.80 0.78 " ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "display(IITaxdataMrtg)" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.7.3" } }, "nbformat": 4, "nbformat_minor": 4 }