{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<h1 align=\"center\">Data Appendix: Pratts </h1> \n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## This file generates the tables   from Pratts data that are  reported in data appendix to  \"Sweat Equity in U.S. Private Business\" "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import libraries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\admin\\Anaconda3\\lib\\site-packages\\ipykernel_launcher.py:8: FutureWarning: Passing a negative integer is deprecated in version 1.0 and will not be supported in future version. Instead, use None to not limit the column width.\n",
      "  \n"
     ]
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import dask.dataframe as dd\n",
    "import re,string\n",
    "#import stats\n",
    "import os.path\n",
    "pd.options.display.float_format = '{:.3f}'.format\n",
    "pd.set_option('display.max_colwidth', -1)\n",
    "#from SCEconomy import *\n",
    "import seaborn as sns\n",
    "sns.set_style('whitegrid')\n",
    "pd.set_option('display.width', 1000)\n",
    "import swifter\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "\n",
    "%matplotlib inline\n",
    "import matplotlib as mpl\n",
    "mpl.rc(\"savefig\",dpi=200)\n",
    "from matplotlib import pyplot as plt\n",
    "# plt.rcParams[\"figure.figsize\"] = (10.,10.)\n",
    "import statsmodels.api as sm\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<script>\n",
       "code_show=true; \n",
       "function code_toggle() {\n",
       " if (code_show){\n",
       " $('div.input').hide();\n",
       " } else {\n",
       " $('div.input').show();\n",
       " }\n",
       " code_show = !code_show\n",
       "} \n",
       "$( document ).ready(code_toggle);\n",
       "</script>\n",
       "<form action=\"javascript:code_toggle()\"><input type=\"submit\" value=\"Click here to toggle on/off the raw code.\"></form>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 2,
     "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('''<script>\n",
    "code_show=true; \n",
    "function code_toggle() {\n",
    " if (code_show){\n",
    " $('div.input').hide();\n",
    " } else {\n",
    " $('div.input').show();\n",
    " }\n",
    " code_show = !code_show\n",
    "} \n",
    "$( document ).ready(code_toggle);\n",
    "</script>\n",
    "<form action=\"javascript:code_toggle()\"><input type=\"submit\" value=\"Click here to toggle on/off the raw code.\"></form>''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_baseline=[]\n",
    "df_target=[]\n",
    "def gini(series, weights):\n",
    "    if series.size!=weights.size:\n",
    "        print(\"Weights are not the same size as the series\")\n",
    "        return\n",
    "    sorted_series = series.sort_values()\n",
    "    height, area = 0, 0\n",
    "    for i in range(series.size):\n",
    "        value = series[sorted_series.index[i]]\n",
    "        wgt = weights[sorted_series.index[i]]\n",
    "        height += wgt*value\n",
    "        area += wgt*(height - value / 2)\n",
    "    fair_area = height * weights.sum() / 2.\n",
    "    return (fair_area - area) / fair_area\n",
    "\n",
    "\n",
    "def gini_alt(series):\n",
    "    csum= 0.0\n",
    "    N=len(series)\n",
    "    for i in range(N):\n",
    "        for j in range(N):\n",
    "            value_i = series.iloc[i]\n",
    "            value_j = series.iloc[j]\n",
    "            csum+=np.abs(value_i-value_j)\n",
    "        \n",
    "    gini_val=0.5*(csum/(N)**2)/series.mean()\n",
    "    \n",
    "    return gini_val\n",
    "\n",
    "\n",
    "def Lorenz(series,quantiles):\n",
    "    N=len(quantiles)\n",
    "    Lz=np.zeros(N)\n",
    "    for n in range(N):\n",
    "        selection = series<series.quantile(quantiles[n])\n",
    "        Lz[n]=series[selection].sum()/series.sum()\n",
    "               \n",
    "        \n",
    "    return  Lz\n",
    "\n",
    "\n",
    "def uploadData(case,w,rs,rc,p,delta):\n",
    "    data=pd.DataFrame()\n",
    "    is_iz=pd.read_csv(case+'/is_to_iz.csv',delim_whitespace=True,header=None)\n",
    "    zgrid=pd.read_csv(case+'/zgrid.csv',delim_whitespace=True,header=None)\n",
    "    is_to_ieps=pd.read_csv(case+'/is_to_ieps.csv', delim_whitespace=True,header=None)\n",
    "    epsgrid=pd.read_csv(case+'/epsgrid.csv', delim_whitespace=True,header=None)\n",
    "    T=100-1\n",
    "    \n",
    "    data['s_ind_']=pd.read_csv(case+'/data_i_s.csv', delim_whitespace=True,header=None).iloc[:,T-1]\n",
    "    data['s_ind']=pd.read_csv(case+'/data_i_s.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    \n",
    "    \n",
    "    data['eps']=epsgrid.values.flatten()[is_to_ieps.astype('int').values.flatten()[data.s_ind.astype('int').values.flatten()]]   \n",
    "    data['eps_']=epsgrid.values.flatten()[is_to_ieps.astype('int').values.flatten()[data.s_ind_.astype('int').values.flatten()]]   \n",
    "    data['iz']=is_iz.astype('int').values.flatten()[data.s_ind.astype('int').values.flatten()]\n",
    "    data['iz_']=is_iz.astype('int').values.flatten()[data.s_ind_.astype('int').values.flatten()]\n",
    "    data['ieps']=is_to_ieps.astype('int').values.flatten()[data.s_ind.astype('int').values.flatten()]  \n",
    "    data['ieps_']=is_to_ieps.astype('int').values.flatten()[data.s_ind_.astype('int').values.flatten()]\n",
    "    data['z']=zgrid.values.flatten()[is_iz.astype('int').values.flatten()[data.s_ind.astype('int').values.flatten()]]    \n",
    "    data['z_']=zgrid.values.flatten()[is_iz.astype('int').values.flatten()[data.s_ind_.astype('int').values.flatten()]]    \n",
    "    data['ys']=pd.read_csv(case+'/data_ys.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['ys_']=pd.read_csv(case+'/data_ys.csv', delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['ks']=pd.read_csv(case+'/data_ks.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['ns']=pd.read_csv(case+'/data_ns.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['x']=pd.read_csv(case+'/data_x.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['n']=pd.read_csv(case+'/data_n.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['ks_']=pd.read_csv(case+'/data_ks.csv', delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['ns_']=pd.read_csv(case+'/data_ns.csv', delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['x_']=pd.read_csv(case+'/data_x.csv',  delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['n_']=pd.read_csv(case+'/data_n.csv', delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['flagC']=pd.read_csv(case+'/data_is_c.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['flagC_']=pd.read_csv(case+'/data_is_c.csv', delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['flagO']=pd.read_csv(case+'/data_is_o.csv',delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['kappa']=pd.read_csv(case+'/data_kap.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['hkap']=pd.read_csv(case+'/data_hkap.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['hy']=pd.read_csv(case+'/data_hy.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['s_age']=pd.read_csv(case+'/s_age.csv',  delim_whitespace=True,header=None)\n",
    "    data['sind_age']=pd.read_csv(case+'/sind_age.csv',  delim_whitespace=True,header=None)\n",
    "    data['ind_age']=pd.read_csv(case+'/ind_age.csv',  delim_whitespace=True,header=None)\n",
    "    data['c_age']=pd.read_csv(case+'/c_age.csv',  delim_whitespace=True,header=None)\n",
    "    data['div_']=pd.read_csv(case+'/data_div_sweat.csv',  delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['div']= pd.read_csv(case+'/data_div_sweat.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['val_sdf_']=pd.read_csv(case+'/data_val_sweat_dyna_sdf.csv',  delim_whitespace=True,header=None, usecols=[T-1])\n",
    "    data['val_mf_']=pd.read_csv(case+'/data_val_sweat_dyna_fix.csv',  delim_whitespace=True,header=None, usecols=[T-1])\n",
    "\n",
    "    data['val_sdf']=pd.read_csv(case+'/data_val_sweat_dyna_sdf.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['val_mf']=pd.read_csv(case+'/data_val_sweat_dyna_fix.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    #data['val_sdf_post_tax']=pd.read_csv(case+'/data_val_sweat_dyna_sdf_quasi_taxed.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    #data['val_mf_post_tax']=pd.read_csv(case+'/data_val_sweat_dyna_fix_quasi_taxed.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['a']= pd.read_csv(case+'/data_a.csv',  delim_whitespace=True,header=None, usecols=[T])  \n",
    "    data['flagC']=data['flagC'].astype('bool')\n",
    "    data['flagC_']=data['flagC_'].astype('bool')\n",
    "    data['flagO']=data['flagO'].astype('bool')\n",
    "    data['expenses']=(rs+delta)*data['ks']+w*data['ns']+data['x']\n",
    "    data['expenses_']=(rs+delta)*data['ks_']+w*data['ns_']+data['x_']\n",
    "    data['wns']=data['ns']*w\n",
    "    data['pys']=p*data['ys']\n",
    "\n",
    "    data['bus_inc']=p*data['ys']-data['expenses']\n",
    "    data['bus_inc_']=p*data['ys_']-data['expenses_']\n",
    "    data['lab_inc']=data.eps*w*data.n\n",
    "    data['lab_inc_']=data.eps_*w*data.n_\n",
    "    data['n_e']=data['hkap']+data['hy']\n",
    "    data['ne']=data['n_e']\n",
    "    data['kappa_adj']=(data.kappa**0.15)*data.z\n",
    "    data['ret_sdf']=(data['val_sdf']+data['div_'])/data['val_sdf_']+0.02\n",
    "    data['ret_mf']=(data['val_mf']+data['div_'])/data['val_mf_']+0.02\n",
    "    data['div_yld_sdf']=(data['div'])/data['val_sdf']\n",
    "    data['div_yld_mf']=(data['div'])/data['val_mf']\n",
    "    #data['intan_int_sdf']=data['val_sdf'] /(data['val_sdf']+data['ks'])\n",
    "    #data['intan_int_mf']=data['val_mf'] /(data['val_mf']+data['ks'])\n",
    "    data['profit_rate']=100*data['bus_inc']/data['ks']\n",
    "    data['measured_markups']=100*(data['bus_inc'])/(data['x']+(rs+delta)*data['ks']+w*data['ns'])\n",
    "    data['measured_markups_alt']=100*(data['bus_inc']+data['x'])/((rs+delta)*data['ks']+w*data['ns'])\n",
    "    data['x_pys']=data['x']/(p*data['ys'])\n",
    "    data['cogs']=((rs+delta)*data['ks']+w*data['ns']+data['x'])    \n",
    "    data['wealth_sdf']=data['a']+data['val_sdf']   \n",
    "    data['wealth_mf']=data['a']+data['val_mf']   \n",
    "    data['ztilde'] = np.log(data['z'])\n",
    "    data['zkapphi']=data['z']*data['kappa']**(0.15)\n",
    "    data['ztilde_meas'] = np.log(data['ys']/(data['ks']**(0.333)*data['ns']**(0.666)))\n",
    "    data['inc']=0.0\n",
    "    data['inc'][data['flagC']==True]=data[data['flagC']==True]['lab_inc']\n",
    "    data['inc'][data['flagC']==False]=data[data['flagC']==False]['bus_inc']\n",
    "    data['tot_inc']=data['inc']+rc*data['a'] \n",
    "    if os.path.exists(case+'/data_R.csv'):\n",
    "        data['R']= pd.read_csv(case+'/data_R.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "        data['intan_int']=data['R']/(data['R']+data['ks'])\n",
    "        data['wealth_R']=0.0\n",
    "        data['wealth_R'][data['flagC']==True]=data[data['flagC']==True]['a']   \n",
    "        data['wealth_R'][data['flagC']==False]=data[data['flagC']==False]['a']+data[data['flagC']==False]['R']   \n",
    "        data['wealth_income_ratio']=data['wealth_R']/data['tot_inc']\n",
    "  \n",
    "  \n",
    "\n",
    "    data['wgt']=1.0\n",
    "    return data\n",
    "\n",
    "\n",
    "def get_wealth_income_stats(case,w,rs,rc,p,delta):\n",
    "    data=pd.read_csv(path_baseline[n]+'/data_R.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data.columns=['R']\n",
    "    data['flagC']=dd.read_csv(path_baseline[n]+'/data_is_c.csv', delim_whitespace=True,header=None, usecols=[T]).compute().values\n",
    "    data['flagC']=data['flagC'].astype('bool')\n",
    "    data['ys']=pd.read_csv(case+'/data_ys.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['ks']=pd.read_csv(case+'/data_ks.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['ns']=pd.read_csv(case+'/data_ns.csv', delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['x']=pd.read_csv(case+'/data_x.csv',  delim_whitespace=True,header=None, usecols=[T])\n",
    "    data['sind_age']=pd.read_csv(case+'/sind_age.csv',  delim_whitespace=True,header=None)\n",
    "    data['expenses']=(rs+delta)*data['ks']+w*data['ns']+data['x']\n",
    "    data['wns']=data['ns']*w\n",
    "    data['pys']=p*data['ys']\n",
    "    data['bus_inc']=p*data['ys']-data['expenses']\n",
    "    \n",
    "  \n",
    "\n",
    "    data['wgt']=1.0\n",
    "    return data\n",
    "\n",
    "\n",
    "def get_prices(path_baseline,momfilename):\n",
    "    with open (path_baseline+ momfilename, 'rt') as myfile:  # Open file lorem.txt for reading text\n",
    "        for myline in myfile:                 # For each line, read it to a string \n",
    "          #print(\"gdp line\")\n",
    "          #print(myline.find(\"GDP(\"))\n",
    "          if myline.find(\"w\")==0:\n",
    "            #print(myline)\n",
    "            w=float(myline[ myline.find(\"=\")+1:])\n",
    "          if myline.find(\"S-good price\")==0:\n",
    "            #print(myline)\n",
    "            p=float(myline[ myline.find(\"=\")+1:])\n",
    "          if myline.find(\"rc\")==0:\n",
    "            #print(myline)\n",
    "            rc=float(myline[ myline.find(\"=\")+1:])\n",
    "          if myline.find(\"rs\")==0:\n",
    "            #print(myline)\n",
    "            rs=float(myline[ myline.find(\"=\")+1:])\n",
    "          if myline.find(\"GDP(\")==2:\n",
    "            #print(myline)\n",
    "            GVA=float(myline[ myline.find(\"=\")+1:])\n",
    "          if myline.find(\"investment,\")==2:\n",
    "            #print(myline)\n",
    "            x=float(myline[ myline.find(\"=\")+1:])\n",
    "            #print(\"GDP = \"+ str(GVA-x))\n",
    "          if myline.find(\"delk \")==0:\n",
    "            #print(myline)\n",
    "            delta=float(myline[ myline.find(\"=\")+1:])\n",
    "            #print(\"delta= \"+ str(delta))\n",
    "            \n",
    "    return w,p,rc,rs,delta,GVA-x\n",
    "\n",
    "\n",
    "\n",
    "def decomposition (var_bin,bins,varlist):\n",
    "    print('')\n",
    "    print(\"SORTED BY \" + var_bin)\n",
    "    print(\"BINS = \" + str([round(b,2) for b in bins ]) )\n",
    "    \n",
    "\n",
    "    data['bin_' + var_bin] = pd.cut(data[var_bin], bins, labels = False)     \n",
    "    mean_baseline=data.groupby('bin_' + var_bin).mean()[varlist]\n",
    "    count_baseline=data.groupby('bin_' + var_bin).count()[varlist]\n",
    "    \n",
    "    \n",
    "    print(\"-------------- baseline means ----------\")\n",
    "    print(mean_baseline)\n",
    "    print(\"-------------- baseline counts ----------\")\n",
    "    print(count_baseline)\n",
    "    print('')\n",
    "    return mean_baseline,count_baseline\n",
    "\n",
    "def format_table(data,total):\n",
    "    results=[]\n",
    "    results=pd.DataFrame(data) \n",
    "    results['bins/total']=(sorted(pd.cut(df_baseline[var_bin], bins).unique()) )\n",
    "    cols = results.columns.tolist()\n",
    "    cols = cols[-1:] + cols[:-1]\n",
    "    results=results[cols]  #    OR    df = df.ix[:, cols]\n",
    "    results=results.append(total,ignore_index=True)\n",
    "    results.iloc[-1,0]='total'\n",
    "    \n",
    "    return results\n",
    "\n",
    "\n",
    "def print_results (var_bin,bins,varlist,df_baseline=df_baseline,df_target=df_target):\n",
    "    print('')\n",
    "    print(\"SORTED BY \" + var_bin)\n",
    "    print(\"BINS = \" + str([round(b,2) for b in bins ]) )\n",
    "    \n",
    "\n",
    "    df_baseline['bin_' + var_bin] = pd.cut(df_baseline[var_bin], bins, labels = False) \n",
    "    df_target['bin_' + var_bin] = pd.cut(df_target[var_bin], bins, labels = False) \n",
    "    \n",
    "    mean_baseline=df_baseline.groupby('bin_' + var_bin).mean()[varlist]\n",
    "    mean_target=df_target.groupby('bin_' + var_bin).mean()[varlist]\n",
    "    sum_baseline=df_baseline.groupby('bin_' + var_bin).sum()[varlist]\n",
    "    sum_target=df_target.groupby('bin_' + var_bin).sum()[varlist]\n",
    "    count_baseline=df_baseline.groupby('bin_' + var_bin).count()[varlist]\n",
    "    count_target=df_target.groupby('bin_' + var_bin).count()[varlist]\n",
    "    \n",
    "    intensive=(mean_target-mean_baseline)*(count_target+count_baseline)/2\n",
    "    extensive=(count_target-count_baseline)*(mean_target+mean_baseline)/2\n",
    "    total=intensive+extensive\n",
    "    intensive=100.*(intensive/(total.sum()))\n",
    "    extensive=100.*(extensive/(total.sum()))\n",
    "    total_per_change=(100*total.sum()/sum_baseline.sum()).T\n",
    "    intensive_alt=intensive*total_per_change*(1.0/100)\n",
    "    extensive_alt=extensive*total_per_change*(1.0/100)\n",
    "   \n",
    "    return total,sum_baseline,intensive_alt,extensive_alt\n",
    "\n",
    "   \n",
    "\n",
    "    print('')\n",
    "    \n",
    "def __extract_num__(item_name, text):\n",
    "    \n",
    "    import re\n",
    "    for ln in text.splitlines():\n",
    "        if ln.startswith(item_name):\n",
    "\n",
    "            match = re.search(r'[-+]?[0-9]*\\.?[0-9]+([eE][-+]?[0-9]+)?', ln[len(item_name):])\n",
    "\n",
    "            if match is not None:\n",
    "                return float(match.group(0))\n",
    "            else:\n",
    "                return np.nan\n",
    "                    \n",
    "                \n",
    "    return np.nan\n",
    "\n",
    "def get_data_textbuffer(item_name, outputs):\n",
    "    \n",
    "    return np.array([__extract_num__(item_name, text) for text in outputs])\n",
    "    \n",
    "\n",
    "def get_duration(sentence):\n",
    "    sentence=sentence.lower()\n",
    "    sentence=sentence.replace('-', '')\n",
    "    sentence=sentence.replace(\"one\", \"1\")\n",
    "    sentence=sentence.replace(\"two\", \"2\")\n",
    "    sentence=sentence.replace(\"three\", \"3\")\n",
    "    sentence=sentence.replace(\"four\", \"4\")\n",
    "    sentence=sentence.replace(\"five\", \"5\")\n",
    "    sentence=sentence.replace(\"six\", \"6\")\n",
    "    sentence=sentence.replace(\"seven\", \"7\")\n",
    "    sentence=sentence.replace(\"eight\", \"8\")\n",
    "    sentence=sentence.replace(\"nine\", \"9\")\n",
    "    sentence=sentence.replace(\"ten\", \"10\")\n",
    "\n",
    "    pattern = 'week|month|year|day'\n",
    "    convert_to_days={'week':7,'month':30,'year':365,'day':1}\n",
    "    num_days=0\n",
    "    for match in re.finditer(pattern, sentence):\n",
    "        s = match.start()\n",
    "        e = match.end()\n",
    "        numstr=sentence[np.maximum(0,s-3) :e]\n",
    "        if len(re.findall(r'\\d+', numstr ))>0:\n",
    "            days=convert_to_days[sentence[s:e]]*int(re.findall(r'\\d+', numstr )[0])\n",
    "            num_days=np.maximum(num_days,days)\n",
    "    \n",
    "    return num_days\n",
    "\n",
    "def get_distance(sentence):\n",
    "    sentence=sentence.lower()\n",
    "    pattern = 'mil|kilo|km'\n",
    "    convert_to_miles={'mil':1.0,'kilo':0.621371,'km':0.621371}\n",
    "    num_miles=0\n",
    "    for match in re.finditer(pattern, sentence):\n",
    "        s = match.start()\n",
    "        e = match.end()\n",
    "        numstr=sentence[np.maximum(0,s-4) :e]\n",
    "        if len(re.findall(r'\\d+', numstr ))>0:\n",
    "            miles=convert_to_miles[sentence[s:e]]*int(re.findall(r'\\d+', numstr )[0])\n",
    "            num_miles=np.maximum(num_miles,miles)\n",
    "    \n",
    "    return num_miles\n",
    "\n",
    "\n",
    "\n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Table A3. Statistics from Pratt’s Stats Database, 1994–2017 By Legal Structure and Industry\n",
    "We use all firms who report legal form of organization to be in C Corporation',S Corporation, Sole Proprietorship, Partnership,LLC and have a valid IntanglibleIntensity, that is,  the ratio \"TotalIntangiblesPPA/TotalAssetsPPA\" is finite. To compute sales weight we use the variable \"NetSales\" normalized by \"GDPDeflator\" so that all sales are in 2007 dollars.## Read data from PrattStats,GDP deflator "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data=pd.read_csv('../RawData/PRATTS/prattsdatabase.csv',encoding='latin-1')    \n",
    "f = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "\n",
    "\n",
    "f2 = {'RelGoodwillIntensityPPA':['count','mean','median','std']}\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "GDPDeflator=pd.read_csv('../RawData/BEA/GDPDEFAnnual.csv',encoding='latin-1')  \n",
    "GDPDeflator=GDPDeflator[:-1]\n",
    "GDPDeflator.year=GDPDeflator.year.astype(int)\n",
    "data['year'] = data['SaleDate'].str[-4:]\n",
    "data['year']=data['year'].astype(int)\n",
    "data['pricedef'] = data['year'].map(GDPDeflator.set_index('year')['GDPDEF2007'])\n",
    "data['RealNetSales']=data.NetSales/data.pricedef\n",
    "data['RealNetSales']=data['RealNetSales']/1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "9a72aba58da24ee9b284eb1b0f778431",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, description='Pandas Apply', max=25999.0, style=ProgressStyle(descripti…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    },
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c9fdf24876354497a5918268160382dd",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(FloatProgress(value=0.0, description='Pandas Apply', max=25999.0, style=ProgressStyle(descripti…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "\n",
    "data['IntanglibleIntensityPPA']=data.TotalIntangiblesPPA/data.TotalAssetsPPA\n",
    "data['NonCompete'][data['NonCompete']=='Yes']=1.0\n",
    "data['NonCompete'][data['NonCompete']=='No']=0.0\n",
    "data['NetIncomeOverSalePrice']=(data.EarningsBeforeTaxes/data.MVICPrice)*100\n",
    "data['IdentifiableIntanglibleIntensityPPA']=100*data.TotalIdentifiableIntangiblesPPA/data.TotalAssetsPPA\n",
    "data['TotalIntangiblesFAPPARatio']=100*data['TotalIntangiblesPPA']/(data['TotalIntangiblesPPA']+data['FixedAssetsPPA'])\n",
    "data['GoodwillIntensityPPA']=100*data.GoodwillPPA/data.TotalAssetsPPA\n",
    "data['IntanglibleIntensityPPA']=100*data.TotalIntangiblesPPA/data.TotalAssetsPPA\n",
    "data['logIntant']=np.log(data.TotalIntangiblesPPA)\n",
    "data['TargetAge2']=data['TargetAge']**2\n",
    "data['IntanglibleSales']=100*data.TotalIntangiblesPPA/data.NetSales\n",
    "\n",
    "data['RelGoodwillIntensityPPA']=100*(data['GoodwillIntensityPPA']/data['IntanglibleIntensityPPA'])\n",
    "data['FixedIntensityPPA']=100*data.FixedAssetsPPA/data.TotalAssetsPPA\n",
    "data['FixedAssetsBM']=100*data.FixedAssets/data.FixedAssetsPPA\n",
    "\n",
    "data['LeveragePPA']=data.InterestBearingLiabilitiesPPA/data.TotalAssetsPPA\n",
    "data['Leverage']=data.LongTermLiabilities/data.TotalAssets\n",
    "data['Leverage'][np.isinf(data['Leverage'])]=np.nan\n",
    "\n",
    "data['EmploymentAgreementDescription']=data['EmploymentAgreementDescription'].astype(str)\n",
    "data[\"EmploymentAgreementLength\"] = data.EmploymentAgreementDescription.swifter.apply(get_duration)\n",
    "data['NonCompeteDescription']=data['NonCompeteDescription'].astype(str)\n",
    "data[\"NonCompeteDistance\"] = data.NonCompeteDescription.swifter.apply(get_distance)\n",
    "data['EmploymentAgreementAlt']=(data[\"EmploymentAgreementLength\"]>0)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "naics_labels=['Agriculture, Forestry, Fishing and Hunting',\\\n",
    "        'Mining, Quarrying, and Oil and Gas Extraction',\\\n",
    "        'Utilities',\\\n",
    "        'Construction',\\\n",
    "        'NA1',\\\n",
    "        'Manufacturing',\\\n",
    "        'NA2',\\\n",
    "        'Wholesale Trade',\\\n",
    "        'Retail Trade',\\\n",
    "        'NA3',\\\n",
    "        'Transportation and Warehousing',\\\n",
    "        'NA4',\\\n",
    "        'Information',\\\n",
    "        'Finance and Insurance',\\\n",
    "        'Real Estate and Rental and Leasing',\\\n",
    "        'Professional, Scientific, and Technical Services',\\\n",
    "        'Management of Companies and Enterprises',\\\n",
    "        'Administrative and Support and Waste Management and Remediation Services',\\\n",
    "        'Educational Services',\\\n",
    "        'Health Care and Social Assistance',\\\n",
    "        'Arts, Entertainment, and Recreation',\\\n",
    "        'Accommodation and Food Service',\\\n",
    "        'Other Services','Public administration']\n",
    "\n",
    "\n",
    "naics_code=[11,21,22,23,24,31,35,42,44,46,48,50,51,52,53,54,55,56,61,62,71,72,81,83,92]\n",
    "n=24\n",
    "\n",
    "\n",
    "#naics_labels=['Ag. Mining and Utilities',\\\n",
    "#        'Consts and Mfg',\\\n",
    "#        'Trade and warehousing',\\\n",
    "#        'Information and FIRE',\\\n",
    "#        'RE',\\\n",
    "#        'Professional services',\\\n",
    "#        'Nonprof services']\n",
    "\n",
    "#naics_code=[11,23,42,51,53,54,57,92]\n",
    "\n",
    "data['TargetStructureAlt']=data['TargetStructure'].values\n",
    "sel=data['TargetStructure'].isin(['Partnership', 'Limited Partnership', 'LLP'])\n",
    "data.loc[sel,'TargetStructureAlt']='Partnership'\n",
    "sel=data['TargetStructure'].isin(['Consolidated', 'UK Corporation', 'PC','Non-Profit','Ltd','Limited Corporation'])\n",
    "data.loc[sel,'TargetStructureAlt']='Others'\n",
    "sel=data['TargetStructure'].isin(['C corporation'])\n",
    "data.loc[sel,'TargetStructureAlt']='C Corporation'\n",
    "data['naics_label']=pd.cut(data['TargetNAICS1']/10**10,naics_code[:n+1],labels=naics_labels[:n],include_lowest=True,right=False)\n",
    "data['naics_code_cat']=pd.cut(data['TargetNAICS1']/10**10,naics_code[:n+1],include_lowest=True,right=False)\n",
    "data['naics_2d']=pd.cut(data['TargetNAICS1']/10**10,naics_code[:n+1],labels=naics_code[:n],include_lowest=True,right=False)\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#data['naics_label']=data['naics_label'].astype(str)\n",
    "#merge_naics_list=['Mining, Quarrying, and Oil and Gas Extraction','Agriculture, Forestry, Fishing and Hunting','Utilities','Wholesale Trade','Educational Services','Arts, Entertainment, and Recreation','nan']\n",
    "#for naics in merge_naics_list:\n",
    "#    sel=data['naics_label']==naics\n",
    "#    data.loc[sel,'naics_label']='others'\n",
    "    #data.loc[sel,'naics_label'].replace(naics,'others')\n",
    "  #  data.loc[sel,'naics_code_cat'].replace(naics,'others')\n",
    "  #  data.loc[sel,'naics_2d']=99\n",
    "    \n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "selection=selection&sel\n",
    "selPassThru=data['TargetStructureAlt'].isin(['S Corporation', 'Sole Proprietorship', 'Partnership'])\n",
    "data['TargetStructurePassThru']=data['TargetStructure'].values\n",
    "data.loc[selPassThru,'TargetStructurePassThru']=True\n",
    "data.loc[~selPassThru,'TargetStructurePassThru']=False\n",
    "selPassThru=data['TargetStructureAlt'].isin(['S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "data['TargetStructurePassThruLLC']=data['TargetStructure'].values\n",
    "data.loc[selPassThru,'TargetStructurePassThruLLC']=True\n",
    "data.loc[~selPassThru,'TargetStructurePassThruLLC']=False\n",
    "data['TargetStructureBus']=data['TargetStructure'].values\n",
    "data.loc[sel,'TargetStructureBus']='all_bus'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['wgt_sales']=data['RealNetSales']/data[selection]['RealNetSales'].sum()*100\n",
    "selPassThru=data['TargetStructureAlt'].isin(['S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "selPassThru=selection&selPassThru\n",
    "data['wgt_sales_passthru']=data['RealNetSales']/data[selPassThru]['RealNetSales'].sum()*100\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>6858</td>\n",
       "      <td>58.010</td>\n",
       "      <td>64.000</td>\n",
       "      <td>31.779</td>\n",
       "      <td>8.919</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>8863</td>\n",
       "      <td>57.498</td>\n",
       "      <td>63.222</td>\n",
       "      <td>32.145</td>\n",
       "      <td>24.963</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>10854</td>\n",
       "      <td>57.582</td>\n",
       "      <td>63.208</td>\n",
       "      <td>32.130</td>\n",
       "      <td>100.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  6858  58.010 64.000  31.779 8.919     \n",
       "Passthru, including LLCs  8863  57.498 63.222  32.145 24.963    \n",
       "All businesses            10854 57.582 63.208  32.130 100.000   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "temp1=data[selection][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[selection].TargetStructurePassThru).agg(f)\n",
    "temp1=temp1.iloc[1,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['Passthru, excluding LLCs']\n",
    "\n",
    "temp2=data[selection][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[selection].TargetStructurePassThruLLC).agg(f)\n",
    "temp2=temp2.iloc[1,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru, including LLCs']\n",
    "\n",
    "\n",
    "temp3=data[selection][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[selection].TargetStructureBus).agg(f)\n",
    "temp3=temp3.iloc[0,:].values\n",
    "temp3=pd.DataFrame(temp3,index=['count','mean','median','std','sales wgt']).T\n",
    "temp3.index=['All businesses']\n",
    "\n",
    "temp=pd.concat([temp1,temp2,temp3])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "\n",
    "display(temp)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### All businesses by legal form"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Sole Proprietors</th>\n",
       "      <td>1140</td>\n",
       "      <td>57.497</td>\n",
       "      <td>63.795</td>\n",
       "      <td>30.654</td>\n",
       "      <td>0.225</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnerships</th>\n",
       "      <td>197</td>\n",
       "      <td>56.575</td>\n",
       "      <td>66.667</td>\n",
       "      <td>32.218</td>\n",
       "      <td>1.331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S corporation</th>\n",
       "      <td>5521</td>\n",
       "      <td>58.168</td>\n",
       "      <td>63.889</td>\n",
       "      <td>31.995</td>\n",
       "      <td>7.364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLCs</th>\n",
       "      <td>2005</td>\n",
       "      <td>55.745</td>\n",
       "      <td>61.081</td>\n",
       "      <td>33.312</td>\n",
       "      <td>16.044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>C corporations</th>\n",
       "      <td>1991</td>\n",
       "      <td>57.959</td>\n",
       "      <td>63.092</td>\n",
       "      <td>32.069</td>\n",
       "      <td>75.037</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>10854</td>\n",
       "      <td>285.943</td>\n",
       "      <td>318.524</td>\n",
       "      <td>160.247</td>\n",
       "      <td>100.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  count    mean  median     std  sales wgt\n",
       "Sole Proprietors  1140  57.497  63.795  30.654  0.225     \n",
       "Partnerships      197   56.575  66.667  32.218  1.331     \n",
       "S corporation     5521  58.168  63.889  31.995  7.364     \n",
       "LLCs              2005  55.745  61.081  33.312  16.044    \n",
       "C corporations    1991  57.959  63.092  32.069  75.037    \n",
       "Total             10854 285.943 318.524 160.247 100.000   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "temp=data[selection][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[selection].TargetStructureAlt).agg(f)\n",
    "temp=temp.iloc[:,:].values\n",
    "temp=pd.DataFrame(temp,columns=['count','mean','median','std','sales wgt'])\n",
    "temp.index=['C corporations','LLCs','Partnerships','S corporation','Sole Proprietors']\n",
    "tempsum=temp.sum()\n",
    "tempsum=tempsum.values\n",
    "tempsum=pd.DataFrame(tempsum,index=['count','mean','median','std','sales wgt']).T\n",
    "tempsum.index=['Total']\n",
    "temp=pd.concat([temp,tempsum]) \n",
    "temp['count']=temp['count'].astype(int)\n",
    "\n",
    "display(temp.iloc[[4,2,3,1,0,5],:])\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Pass-throughs by industry (NAICs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Agriculture, Forestry, Fishing and Hunting</th>\n",
       "      <td>16</td>\n",
       "      <td>30.428</td>\n",
       "      <td>35.230</td>\n",
       "      <td>24.947</td>\n",
       "      <td>0.729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mining, Quarrying, and Oil and Gas Extraction</th>\n",
       "      <td>16</td>\n",
       "      <td>43.164</td>\n",
       "      <td>41.806</td>\n",
       "      <td>29.033</td>\n",
       "      <td>6.512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>10</td>\n",
       "      <td>60.773</td>\n",
       "      <td>66.558</td>\n",
       "      <td>33.485</td>\n",
       "      <td>0.102</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Construction</th>\n",
       "      <td>383</td>\n",
       "      <td>69.284</td>\n",
       "      <td>74.312</td>\n",
       "      <td>37.939</td>\n",
       "      <td>5.689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manufacturing</th>\n",
       "      <td>797</td>\n",
       "      <td>54.474</td>\n",
       "      <td>59.025</td>\n",
       "      <td>29.303</td>\n",
       "      <td>40.262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale Trade</th>\n",
       "      <td>17</td>\n",
       "      <td>54.821</td>\n",
       "      <td>52.147</td>\n",
       "      <td>28.897</td>\n",
       "      <td>0.014</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Retail Trade</th>\n",
       "      <td>1569</td>\n",
       "      <td>54.177</td>\n",
       "      <td>58.842</td>\n",
       "      <td>30.001</td>\n",
       "      <td>6.642</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transportation and Warehousing</th>\n",
       "      <td>327</td>\n",
       "      <td>66.232</td>\n",
       "      <td>73.867</td>\n",
       "      <td>25.400</td>\n",
       "      <td>4.980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information</th>\n",
       "      <td>189</td>\n",
       "      <td>80.830</td>\n",
       "      <td>89.474</td>\n",
       "      <td>22.532</td>\n",
       "      <td>10.309</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finance and Insurance</th>\n",
       "      <td>155</td>\n",
       "      <td>85.525</td>\n",
       "      <td>95.726</td>\n",
       "      <td>25.207</td>\n",
       "      <td>2.842</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate and Rental and Leasing</th>\n",
       "      <td>268</td>\n",
       "      <td>76.292</td>\n",
       "      <td>92.660</td>\n",
       "      <td>31.027</td>\n",
       "      <td>1.755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Professional, Scientific, and Technical Services</th>\n",
       "      <td>462</td>\n",
       "      <td>81.299</td>\n",
       "      <td>89.530</td>\n",
       "      <td>21.027</td>\n",
       "      <td>4.875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Management of Companies and Enterprises</th>\n",
       "      <td>12</td>\n",
       "      <td>56.271</td>\n",
       "      <td>64.005</td>\n",
       "      <td>33.390</td>\n",
       "      <td>5.252</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Administrative and Support and Waste Management and Remediation Services</th>\n",
       "      <td>1030</td>\n",
       "      <td>73.228</td>\n",
       "      <td>79.833</td>\n",
       "      <td>24.739</td>\n",
       "      <td>2.796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Educational Services</th>\n",
       "      <td>115</td>\n",
       "      <td>59.075</td>\n",
       "      <td>72.000</td>\n",
       "      <td>34.288</td>\n",
       "      <td>0.518</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care and Social Assistance</th>\n",
       "      <td>450</td>\n",
       "      <td>62.816</td>\n",
       "      <td>68.939</td>\n",
       "      <td>29.359</td>\n",
       "      <td>2.774</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arts, Entertainment, and Recreation</th>\n",
       "      <td>168</td>\n",
       "      <td>45.763</td>\n",
       "      <td>44.456</td>\n",
       "      <td>30.867</td>\n",
       "      <td>0.890</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Accommodation and Food Service</th>\n",
       "      <td>1689</td>\n",
       "      <td>41.837</td>\n",
       "      <td>41.875</td>\n",
       "      <td>30.182</td>\n",
       "      <td>1.803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other Services</th>\n",
       "      <td>1187</td>\n",
       "      <td>45.465</td>\n",
       "      <td>45.455</td>\n",
       "      <td>31.326</td>\n",
       "      <td>1.257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>8860</td>\n",
       "      <td>1141.756</td>\n",
       "      <td>1245.740</td>\n",
       "      <td>552.951</td>\n",
       "      <td>99.999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          count     mean   median     std  sales wgt\n",
       "Agriculture, Forestry, Fishing and Hunting                                16    30.428   35.230   24.947  0.729     \n",
       "Mining, Quarrying, and Oil and Gas Extraction                             16    43.164   41.806   29.033  6.512     \n",
       "Utilities                                                                 10    60.773   66.558   33.485  0.102     \n",
       "Construction                                                              383   69.284   74.312   37.939  5.689     \n",
       "Manufacturing                                                             797   54.474   59.025   29.303  40.262    \n",
       "Wholesale Trade                                                           17    54.821   52.147   28.897  0.014     \n",
       "Retail Trade                                                              1569  54.177   58.842   30.001  6.642     \n",
       "Transportation and Warehousing                                            327   66.232   73.867   25.400  4.980     \n",
       "Information                                                               189   80.830   89.474   22.532  10.309    \n",
       "Finance and Insurance                                                     155   85.525   95.726   25.207  2.842     \n",
       "Real Estate and Rental and Leasing                                        268   76.292   92.660   31.027  1.755     \n",
       "Professional, Scientific, and Technical Services                          462   81.299   89.530   21.027  4.875     \n",
       "Management of Companies and Enterprises                                   12    56.271   64.005   33.390  5.252     \n",
       "Administrative and Support and Waste Management and Remediation Services  1030  73.228   79.833   24.739  2.796     \n",
       "Educational Services                                                      115   59.075   72.000   34.288  0.518     \n",
       "Health Care and Social Assistance                                         450   62.816   68.939   29.359  2.774     \n",
       "Arts, Entertainment, and Recreation                                       168   45.763   44.456   30.867  0.890     \n",
       "Accommodation and Food Service                                            1689  41.837   41.875   30.182  1.803     \n",
       "Other Services                                                            1187  45.465   45.455   31.326  1.257     \n",
       "Total                                                                     8860  1141.756 1245.740 552.951 99.999    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "selPassThru=data['TargetStructureAlt'].isin(['S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "selPassThru=selection&selPassThru\n",
    "temp=data[selPassThru][['IntanglibleIntensityPPA','wgt_sales_passthru']].groupby(data[selPassThru].naics_label).agg(f_pass)\n",
    "indexlist=temp.index.to_list()\n",
    "temp=temp.iloc[:,:].values\n",
    "temp=pd.DataFrame(temp,columns=['count','mean','median','std','sales wgt'])\n",
    "temp.index=indexlist\n",
    "temp['count']=temp['count'].astype(int)\n",
    "tempsum=temp.sum()\n",
    "tempsum=tempsum.values\n",
    "tempsum=pd.DataFrame(tempsum,index=['count','mean','median','std','sales wgt']).T\n",
    "tempsum.index=['Total']\n",
    "temp=pd.concat([temp,tempsum]) \n",
    "temp['count']=temp['count'].astype(int)\n",
    "display(temp[~np.isnan(temp['mean'])] )\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Agriculture, Forestry, Fishing and Hunting</th>\n",
       "      <td>9</td>\n",
       "      <td>22.844</td>\n",
       "      <td>18.158</td>\n",
       "      <td>22.066</td>\n",
       "      <td>0.215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Mining, Quarrying, and Oil and Gas Extraction</th>\n",
       "      <td>5</td>\n",
       "      <td>42.877</td>\n",
       "      <td>36.647</td>\n",
       "      <td>26.079</td>\n",
       "      <td>0.569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Utilities</th>\n",
       "      <td>8</td>\n",
       "      <td>61.637</td>\n",
       "      <td>72.500</td>\n",
       "      <td>36.961</td>\n",
       "      <td>0.033</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Construction</th>\n",
       "      <td>289</td>\n",
       "      <td>70.146</td>\n",
       "      <td>74.082</td>\n",
       "      <td>40.699</td>\n",
       "      <td>10.792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Manufacturing</th>\n",
       "      <td>549</td>\n",
       "      <td>53.558</td>\n",
       "      <td>57.500</td>\n",
       "      <td>28.513</td>\n",
       "      <td>22.676</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Wholesale Trade</th>\n",
       "      <td>12</td>\n",
       "      <td>54.414</td>\n",
       "      <td>48.634</td>\n",
       "      <td>27.764</td>\n",
       "      <td>0.027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Retail Trade</th>\n",
       "      <td>1029</td>\n",
       "      <td>55.331</td>\n",
       "      <td>60.000</td>\n",
       "      <td>29.402</td>\n",
       "      <td>19.210</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Transportation and Warehousing</th>\n",
       "      <td>250</td>\n",
       "      <td>67.516</td>\n",
       "      <td>75.192</td>\n",
       "      <td>24.959</td>\n",
       "      <td>9.914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Information</th>\n",
       "      <td>103</td>\n",
       "      <td>79.599</td>\n",
       "      <td>86.000</td>\n",
       "      <td>23.202</td>\n",
       "      <td>4.032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Finance and Insurance</th>\n",
       "      <td>116</td>\n",
       "      <td>87.896</td>\n",
       "      <td>95.789</td>\n",
       "      <td>22.025</td>\n",
       "      <td>5.799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Real Estate and Rental and Leasing</th>\n",
       "      <td>140</td>\n",
       "      <td>73.434</td>\n",
       "      <td>90.339</td>\n",
       "      <td>31.590</td>\n",
       "      <td>0.730</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Professional, Scientific, and Technical Services</th>\n",
       "      <td>294</td>\n",
       "      <td>80.884</td>\n",
       "      <td>88.420</td>\n",
       "      <td>20.397</td>\n",
       "      <td>6.544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Management of Companies and Enterprises</th>\n",
       "      <td>5</td>\n",
       "      <td>40.983</td>\n",
       "      <td>60.152</td>\n",
       "      <td>31.355</td>\n",
       "      <td>6.884</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Administrative and Support and Waste Management and Remediation Services</th>\n",
       "      <td>655</td>\n",
       "      <td>73.633</td>\n",
       "      <td>81.750</td>\n",
       "      <td>24.589</td>\n",
       "      <td>4.043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Educational Services</th>\n",
       "      <td>73</td>\n",
       "      <td>59.588</td>\n",
       "      <td>72.000</td>\n",
       "      <td>31.452</td>\n",
       "      <td>0.502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Health Care and Social Assistance</th>\n",
       "      <td>273</td>\n",
       "      <td>61.698</td>\n",
       "      <td>66.601</td>\n",
       "      <td>30.903</td>\n",
       "      <td>1.747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Arts, Entertainment, and Recreation</th>\n",
       "      <td>94</td>\n",
       "      <td>41.685</td>\n",
       "      <td>43.530</td>\n",
       "      <td>31.974</td>\n",
       "      <td>0.467</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Accommodation and Food Service</th>\n",
       "      <td>931</td>\n",
       "      <td>41.767</td>\n",
       "      <td>42.360</td>\n",
       "      <td>29.971</td>\n",
       "      <td>3.625</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other Services</th>\n",
       "      <td>685</td>\n",
       "      <td>45.434</td>\n",
       "      <td>45.946</td>\n",
       "      <td>30.783</td>\n",
       "      <td>2.189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>5520</td>\n",
       "      <td>1114.921</td>\n",
       "      <td>1215.600</td>\n",
       "      <td>544.684</td>\n",
       "      <td>99.999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          count     mean   median     std  sales wgt\n",
       "Agriculture, Forestry, Fishing and Hunting                                9     22.844   18.158   22.066  0.215     \n",
       "Mining, Quarrying, and Oil and Gas Extraction                             5     42.877   36.647   26.079  0.569     \n",
       "Utilities                                                                 8     61.637   72.500   36.961  0.033     \n",
       "Construction                                                              289   70.146   74.082   40.699  10.792    \n",
       "Manufacturing                                                             549   53.558   57.500   28.513  22.676    \n",
       "Wholesale Trade                                                           12    54.414   48.634   27.764  0.027     \n",
       "Retail Trade                                                              1029  55.331   60.000   29.402  19.210    \n",
       "Transportation and Warehousing                                            250   67.516   75.192   24.959  9.914     \n",
       "Information                                                               103   79.599   86.000   23.202  4.032     \n",
       "Finance and Insurance                                                     116   87.896   95.789   22.025  5.799     \n",
       "Real Estate and Rental and Leasing                                        140   73.434   90.339   31.590  0.730     \n",
       "Professional, Scientific, and Technical Services                          294   80.884   88.420   20.397  6.544     \n",
       "Management of Companies and Enterprises                                   5     40.983   60.152   31.355  6.884     \n",
       "Administrative and Support and Waste Management and Remediation Services  655   73.633   81.750   24.589  4.043     \n",
       "Educational Services                                                      73    59.588   72.000   31.452  0.502     \n",
       "Health Care and Social Assistance                                         273   61.698   66.601   30.903  1.747     \n",
       "Arts, Entertainment, and Recreation                                       94    41.685   43.530   31.974  0.467     \n",
       "Accommodation and Food Service                                            931   41.767   42.360   29.971  3.625     \n",
       "Other Services                                                            685   45.434   45.946   30.783  2.189     \n",
       "Total                                                                     5520  1114.921 1215.600 544.684 99.999    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "grp=['S Corporation']\n",
    "selPassThruWoLLC=data['TargetStructureAlt'].isin(grp)\n",
    "selPassThruWoLLC=selection&selPassThruWoLLC\n",
    "data['wgt_sales_passthruWoLLC']=data['RealNetSales']/data[selPassThruWoLLC]['RealNetSales'].sum()*100\n",
    "\n",
    "f_passWoLLC = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales_passthruWoLLC':['sum']}\n",
    "selPassThruWoLLC=data['TargetStructureAlt'].isin(grp)\n",
    "selPassThruWoLLC=selection&selPassThruWoLLC\n",
    "temp=data[selPassThruWoLLC][['IntanglibleIntensityPPA','wgt_sales_passthruWoLLC']].groupby(data[selPassThruWoLLC].naics_label).agg(f_passWoLLC)\n",
    "indexlist=temp.index.to_list()\n",
    "temp=temp.iloc[:,:].values\n",
    "temp=pd.DataFrame(temp,columns=['count','mean','median','std','sales wgt'])\n",
    "temp.index=indexlist\n",
    "temp['count']=temp['count'].astype(int)\n",
    "tempsum=temp.sum()\n",
    "tempsum=tempsum.values\n",
    "tempsum=pd.DataFrame(tempsum,index=['count','mean','median','std','sales wgt']).T\n",
    "tempsum.index=['Total']\n",
    "temp=pd.concat([temp,tempsum]) \n",
    "temp['count']=temp['count'].astype(int)\n",
    "display(temp[~np.isnan(temp['mean'])] )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Breakdown of Intangible Assets\n",
    "\n",
    "Below we show summary statistics for different types of intangibles. For each type we compute the share in total intangibles for observations with positive  values. \n",
    "\n",
    "* **Customer Relationships / Lists PPA** :The value attributed to any customer relationships or customer list acquired as part of the acquisition.\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "* **Backlog PPA** :Any acquired purchase orders or booked sales on orders that have not been fully completed. \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "* **Developed / Existing Technology PPA** :Any acquired developed/completed technology, core technology, and/or acquired or purchased technology. Technology that is in the process of being developed is included in In-Process R&D.\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "* **In-Process R&D PPA** :\tIntangible assets acquired relating to any uncompleted/in process research and development.\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "* **Trade Names / Trademarks PPA** :\tThe value of acquired trademarks/service marks to identify and/or differentiate goods and services or business trade names. \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "* **Noncompete Agreements PPA**\tThe value placed on an agreement with the selling party not to compete with the purchaser, usually for a certain period of time and usually in a specified geographic area. \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "* **Other Intangibles PPA** :\tAny other intangible asset acquired that is not listed in the preceding fields. \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n",
    "\t\t\t\t\t\t\t\n",
    "* **Goodwill PPA**\tRepresents the excess of the aggregate purchase price over the fair value of net assets of the acquired business.\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CustomerRelationshipsListsPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>160</td>\n",
       "      <td>32.016</td>\n",
       "      <td>27.809</td>\n",
       "      <td>25.020</td>\n",
       "      <td>2.364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>281</td>\n",
       "      <td>33.477</td>\n",
       "      <td>28.188</td>\n",
       "      <td>27.429</td>\n",
       "      <td>9.451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>590</td>\n",
       "      <td>29.013</td>\n",
       "      <td>23.994</td>\n",
       "      <td>23.407</td>\n",
       "      <td>34.191</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  160   32.016 27.809  25.020 2.364     \n",
       "Passthru, including LLCs  281   33.477 28.188  27.429 9.451     \n",
       "All businesses            590   29.013 23.994  23.407 34.191    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>309</td>\n",
       "      <td>24.954</td>\n",
       "      <td>21.175</td>\n",
       "      <td>18.142</td>\n",
       "      <td>24.739</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>121</td>\n",
       "      <td>35.408</td>\n",
       "      <td>28.462</td>\n",
       "      <td>30.322</td>\n",
       "      <td>7.087</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>10</td>\n",
       "      <td>34.822</td>\n",
       "      <td>33.197</td>\n",
       "      <td>27.453</td>\n",
       "      <td>0.138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>145</td>\n",
       "      <td>32.363</td>\n",
       "      <td>28.188</td>\n",
       "      <td>25.111</td>\n",
       "      <td>2.224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>5</td>\n",
       "      <td>16.335</td>\n",
       "      <td>22.388</td>\n",
       "      <td>12.497</td>\n",
       "      <td>0.002</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>590</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>34.191</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count   mean median    std  sales wgt\n",
       "C Corporation        309   24.954 21.175 18.142 24.739    \n",
       "LLC                  121   35.408 28.462 30.322 7.087     \n",
       "Partnership          10    34.822 33.197 27.453 0.138     \n",
       "S Corporation        145   32.363 28.188 25.111 2.224     \n",
       "Sole Proprietorship  5     16.335 22.388 12.497 0.002     \n",
       "Total                590    --     --     --    34.191    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "BacklogPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>16</td>\n",
       "      <td>6.193</td>\n",
       "      <td>1.736</td>\n",
       "      <td>15.716</td>\n",
       "      <td>0.850</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>31</td>\n",
       "      <td>6.874</td>\n",
       "      <td>2.201</td>\n",
       "      <td>15.174</td>\n",
       "      <td>2.675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>71</td>\n",
       "      <td>4.790</td>\n",
       "      <td>2.201</td>\n",
       "      <td>10.398</td>\n",
       "      <td>8.068</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count  mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  16    6.193 1.736   15.716 0.850     \n",
       "Passthru, including LLCs  31    6.874 2.201   15.174 2.675     \n",
       "All businesses            71    4.790 2.201   10.398 8.068     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>40</td>\n",
       "      <td>3.175</td>\n",
       "      <td>2.081</td>\n",
       "      <td>3.289</td>\n",
       "      <td>5.392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>15</td>\n",
       "      <td>7.601</td>\n",
       "      <td>3.147</td>\n",
       "      <td>15.087</td>\n",
       "      <td>1.826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>1</td>\n",
       "      <td>0.318</td>\n",
       "      <td>0.318</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>15</td>\n",
       "      <td>6.585</td>\n",
       "      <td>1.755</td>\n",
       "      <td>16.187</td>\n",
       "      <td>0.830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>71</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>8.068</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               count  mean median    std  sales wgt\n",
       "C Corporation  40    3.175 2.081  3.289  5.392     \n",
       "LLC            15    7.601 3.147  15.087 1.826     \n",
       "Partnership    1     0.318 0.318   NaN   0.019     \n",
       "S Corporation  15    6.585 1.755  16.187 0.830     \n",
       "Total          71     --    --     --    8.068     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "DevelopedExistingTechnologyPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>76</td>\n",
       "      <td>18.715</td>\n",
       "      <td>11.024</td>\n",
       "      <td>22.864</td>\n",
       "      <td>0.875</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>127</td>\n",
       "      <td>19.063</td>\n",
       "      <td>11.662</td>\n",
       "      <td>22.628</td>\n",
       "      <td>5.852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>380</td>\n",
       "      <td>22.854</td>\n",
       "      <td>13.790</td>\n",
       "      <td>24.224</td>\n",
       "      <td>18.212</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  76    18.715 11.024  22.864 0.875     \n",
       "Passthru, including LLCs  127   19.063 11.662  22.628 5.852     \n",
       "All businesses            380   22.854 13.790  24.224 18.212    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>253</td>\n",
       "      <td>24.757</td>\n",
       "      <td>15.361</td>\n",
       "      <td>24.812</td>\n",
       "      <td>12.360</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>51</td>\n",
       "      <td>19.582</td>\n",
       "      <td>12.121</td>\n",
       "      <td>22.487</td>\n",
       "      <td>4.977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>73</td>\n",
       "      <td>19.390</td>\n",
       "      <td>12.315</td>\n",
       "      <td>23.081</td>\n",
       "      <td>0.874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>3</td>\n",
       "      <td>2.280</td>\n",
       "      <td>1.238</td>\n",
       "      <td>1.806</td>\n",
       "      <td>0.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>380</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>18.212</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count   mean median    std  sales wgt\n",
       "C Corporation        253   24.757 15.361 24.812 12.360    \n",
       "LLC                  51    19.582 12.121 22.487 4.977     \n",
       "S Corporation        73    19.390 12.315 23.081 0.874     \n",
       "Sole Proprietorship  3     2.280  1.238  1.806  0.001     \n",
       "Total                380    --     --     --    18.212    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "InProcessRandDPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>8</td>\n",
       "      <td>22.980</td>\n",
       "      <td>5.663</td>\n",
       "      <td>31.654</td>\n",
       "      <td>0.119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>12</td>\n",
       "      <td>22.512</td>\n",
       "      <td>7.027</td>\n",
       "      <td>29.685</td>\n",
       "      <td>2.907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>90</td>\n",
       "      <td>28.711</td>\n",
       "      <td>14.236</td>\n",
       "      <td>31.409</td>\n",
       "      <td>6.051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  8     22.980 5.663   31.654 0.119     \n",
       "Passthru, including LLCs  12    22.512 7.027   29.685 2.907     \n",
       "All businesses            90    28.711 14.236  31.409 6.051     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>78</td>\n",
       "      <td>29.665</td>\n",
       "      <td>17.162</td>\n",
       "      <td>31.741</td>\n",
       "      <td>3.144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>4</td>\n",
       "      <td>21.575</td>\n",
       "      <td>9.880</td>\n",
       "      <td>29.856</td>\n",
       "      <td>2.788</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>8</td>\n",
       "      <td>22.980</td>\n",
       "      <td>5.663</td>\n",
       "      <td>31.654</td>\n",
       "      <td>0.119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>90</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>6.051</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               count   mean median    std  sales wgt\n",
       "C Corporation  78    29.665 17.162 31.741 3.144     \n",
       "LLC            4     21.575 9.880  29.856 2.788     \n",
       "S Corporation  8     22.980 5.663  31.654 0.119     \n",
       "Total          90     --     --     --    6.051     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "TradeNamesTradeMarksPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>178</td>\n",
       "      <td>19.549</td>\n",
       "      <td>13.826</td>\n",
       "      <td>19.533</td>\n",
       "      <td>2.390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>276</td>\n",
       "      <td>19.319</td>\n",
       "      <td>11.663</td>\n",
       "      <td>22.684</td>\n",
       "      <td>9.149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>523</td>\n",
       "      <td>16.210</td>\n",
       "      <td>8.696</td>\n",
       "      <td>20.386</td>\n",
       "      <td>36.141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  178   19.549 13.826  19.533 2.390     \n",
       "Passthru, including LLCs  276   19.319 11.663  22.684 9.149     \n",
       "All businesses            523   16.210 8.696   20.386 36.141    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>247</td>\n",
       "      <td>12.735</td>\n",
       "      <td>5.961</td>\n",
       "      <td>16.842</td>\n",
       "      <td>26.992</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>98</td>\n",
       "      <td>18.901</td>\n",
       "      <td>8.584</td>\n",
       "      <td>27.610</td>\n",
       "      <td>6.759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>7</td>\n",
       "      <td>10.030</td>\n",
       "      <td>2.971</td>\n",
       "      <td>16.263</td>\n",
       "      <td>0.135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>155</td>\n",
       "      <td>18.994</td>\n",
       "      <td>13.889</td>\n",
       "      <td>18.881</td>\n",
       "      <td>2.250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>16</td>\n",
       "      <td>29.087</td>\n",
       "      <td>25.622</td>\n",
       "      <td>24.367</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>523</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>36.141</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count   mean median    std  sales wgt\n",
       "C Corporation        247   12.735 5.961  16.842 26.992    \n",
       "LLC                  98    18.901 8.584  27.610 6.759     \n",
       "Partnership          7     10.030 2.971  16.263 0.135     \n",
       "S Corporation        155   18.994 13.889 18.881 2.250     \n",
       "Sole Proprietorship  16    29.087 25.622 24.367 0.005     \n",
       "Total                523    --     --     --    36.141    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "NonCompeteAgreementsPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>1972</td>\n",
       "      <td>23.261</td>\n",
       "      <td>13.333</td>\n",
       "      <td>25.179</td>\n",
       "      <td>2.296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>2364</td>\n",
       "      <td>22.438</td>\n",
       "      <td>12.195</td>\n",
       "      <td>25.011</td>\n",
       "      <td>3.885</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>2831</td>\n",
       "      <td>23.080</td>\n",
       "      <td>12.922</td>\n",
       "      <td>25.963</td>\n",
       "      <td>7.317</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  1972  23.261 13.333  25.179 2.296     \n",
       "Passthru, including LLCs  2364  22.438 12.195  25.011 3.885     \n",
       "All businesses            2831  23.080 12.922  25.963 7.317     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>467</td>\n",
       "      <td>26.326</td>\n",
       "      <td>16.667</td>\n",
       "      <td>30.150</td>\n",
       "      <td>3.433</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>392</td>\n",
       "      <td>18.300</td>\n",
       "      <td>7.692</td>\n",
       "      <td>23.746</td>\n",
       "      <td>1.589</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>70</td>\n",
       "      <td>25.503</td>\n",
       "      <td>18.296</td>\n",
       "      <td>23.164</td>\n",
       "      <td>0.135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>1330</td>\n",
       "      <td>21.785</td>\n",
       "      <td>10.914</td>\n",
       "      <td>25.562</td>\n",
       "      <td>2.038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>572</td>\n",
       "      <td>26.418</td>\n",
       "      <td>19.164</td>\n",
       "      <td>24.230</td>\n",
       "      <td>0.123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>2831</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>7.317</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count   mean median    std  sales wgt\n",
       "C Corporation        467   26.326 16.667 30.150 3.433     \n",
       "LLC                  392   18.300 7.692  23.746 1.589     \n",
       "Partnership          70    25.503 18.296 23.164 0.135     \n",
       "S Corporation        1330  21.785 10.914 25.562 2.038     \n",
       "Sole Proprietorship  572   26.418 19.164 24.230 0.123     \n",
       "Total                2831   --     --     --    7.317     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "OtherIntangiblesPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>304</td>\n",
       "      <td>42.118</td>\n",
       "      <td>29.851</td>\n",
       "      <td>36.053</td>\n",
       "      <td>1.312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>461</td>\n",
       "      <td>43.290</td>\n",
       "      <td>31.034</td>\n",
       "      <td>36.555</td>\n",
       "      <td>5.303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>681</td>\n",
       "      <td>38.359</td>\n",
       "      <td>24.242</td>\n",
       "      <td>35.955</td>\n",
       "      <td>23.591</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  304   42.118 29.851  36.053 1.312     \n",
       "Passthru, including LLCs  461   43.290 31.034  36.555 5.303     \n",
       "All businesses            681   38.359 24.242  35.955 23.591    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>220</td>\n",
       "      <td>28.027</td>\n",
       "      <td>13.177</td>\n",
       "      <td>32.385</td>\n",
       "      <td>18.288</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>157</td>\n",
       "      <td>45.559</td>\n",
       "      <td>32.735</td>\n",
       "      <td>37.521</td>\n",
       "      <td>3.991</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>11</td>\n",
       "      <td>29.586</td>\n",
       "      <td>17.290</td>\n",
       "      <td>32.440</td>\n",
       "      <td>0.029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>260</td>\n",
       "      <td>42.441</td>\n",
       "      <td>29.851</td>\n",
       "      <td>36.585</td>\n",
       "      <td>1.276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>33</td>\n",
       "      <td>43.748</td>\n",
       "      <td>43.333</td>\n",
       "      <td>32.984</td>\n",
       "      <td>0.007</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>681</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>23.591</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count   mean median    std  sales wgt\n",
       "C Corporation        220   28.027 13.177 32.385 18.288    \n",
       "LLC                  157   45.559 32.735 37.521 3.991     \n",
       "Partnership          11    29.586 17.290 32.440 0.029     \n",
       "S Corporation        260   42.441 29.851 36.585 1.276     \n",
       "Sole Proprietorship  33    43.748 43.333 32.984 0.007     \n",
       "Total                681    --     --     --    23.591    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "GoodwillPPA\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru, excluding LLCs</th>\n",
       "      <td>3732</td>\n",
       "      <td>85.069</td>\n",
       "      <td>97.917</td>\n",
       "      <td>22.292</td>\n",
       "      <td>6.593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru, including LLCs</th>\n",
       "      <td>5063</td>\n",
       "      <td>85.702</td>\n",
       "      <td>98.974</td>\n",
       "      <td>22.352</td>\n",
       "      <td>19.146</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses</th>\n",
       "      <td>6370</td>\n",
       "      <td>82.674</td>\n",
       "      <td>96.970</td>\n",
       "      <td>24.189</td>\n",
       "      <td>87.521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          count   mean  median    std  sales wgt\n",
       "Passthru, excluding LLCs  3732  85.069 97.917  22.292 6.593     \n",
       "Passthru, including LLCs  5063  85.702 98.974  22.352 19.146    \n",
       "All businesses            6370  82.674 96.970  24.189 87.521    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>1307</td>\n",
       "      <td>70.948</td>\n",
       "      <td>74.017</td>\n",
       "      <td>27.275</td>\n",
       "      <td>68.376</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>1331</td>\n",
       "      <td>87.476</td>\n",
       "      <td>100.000</td>\n",
       "      <td>22.432</td>\n",
       "      <td>12.553</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>106</td>\n",
       "      <td>74.516</td>\n",
       "      <td>81.873</td>\n",
       "      <td>24.984</td>\n",
       "      <td>0.402</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>2894</td>\n",
       "      <td>86.709</td>\n",
       "      <td>99.502</td>\n",
       "      <td>21.917</td>\n",
       "      <td>6.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>732</td>\n",
       "      <td>80.112</td>\n",
       "      <td>89.342</td>\n",
       "      <td>22.231</td>\n",
       "      <td>0.156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Total</th>\n",
       "      <td>6370</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>--</td>\n",
       "      <td>87.521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     count   mean  median    std  sales wgt\n",
       "C Corporation        1307  70.948 74.017  27.275 68.376    \n",
       "LLC                  1331  87.476 100.000 22.432 12.553    \n",
       "Partnership          106   74.516 81.873  24.984 0.402     \n",
       "S Corporation        2894  86.709 99.502  21.917 6.034     \n",
       "Sole Proprietorship  732   80.112 89.342  22.231 0.156     \n",
       "Total                6370   --     --      --    87.521    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "varlist=['CustomerRelationshipsListsPPA','BacklogPPA','DevelopedExistingTechnologyPPA','InProcessRandDPPA','TradeNamesTradeMarksPPA','NonCompeteAgreementsPPA','OtherIntangiblesPPA','GoodwillPPA']\n",
    "for var in varlist:\n",
    "    print(var)\n",
    "    data[var+'Intensity']=data[var]/data['TotalIntangiblesPPA']*100\n",
    "    var=var+'Intensity'\n",
    "    selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "    sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "    selection=selection&sel\n",
    "    sel2=selection&(data[selection][var]>0)\n",
    "\n",
    "    f = {var:['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "\n",
    "\n",
    "    temp1=data[sel2][[var,'wgt_sales']].groupby(data[sel2].TargetStructurePassThru).agg(f)\n",
    "    temp1=temp1.iloc[1,:].values\n",
    "    temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "    temp1.index=['Passthru, excluding LLCs']\n",
    "\n",
    "    temp2=data[sel2][[var,'wgt_sales']].groupby(data[sel2].TargetStructurePassThruLLC).agg(f)\n",
    "    temp2=temp2.iloc[1,:].values\n",
    "    temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "    temp2.index=['Passthru, including LLCs']\n",
    "\n",
    "\n",
    "    temp3=data[sel2][[var,'wgt_sales']].groupby(data[sel2].TargetStructureBus).agg(f)\n",
    "    temp3=temp3.iloc[0,:].values\n",
    "    temp3=pd.DataFrame(temp3,index=['count','mean','median','std','sales wgt']).T\n",
    "    temp3.index=['All businesses']\n",
    "\n",
    "    temp=pd.concat([temp1,temp2,temp3])\n",
    "    temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "\n",
    "    display(temp)\n",
    "    \n",
    "    temp=data[sel2][[var,'wgt_sales']].groupby(data[sel2].TargetStructureAlt).agg(f)\n",
    "    to_list=temp.index.to_list()\n",
    "    temp=temp.iloc[:,:].values\n",
    "    temp=pd.DataFrame(temp,columns=['count','mean','median','std','sales wgt'])\n",
    "    temp.index=to_list\n",
    "    tempsum=temp.sum()\n",
    "    tempsum=tempsum.values\n",
    "    tempsum=pd.DataFrame(tempsum,index=['count','mean','median','std','sales wgt']).T\n",
    "    tempsum.index=['Total']\n",
    "    temp=pd.concat([temp,tempsum]) \n",
    "    temp['count']=temp['count'].astype(int)\n",
    "    temp.iloc[-1,1]='--'\n",
    "    temp.iloc[-1,2]='--'\n",
    "    temp.iloc[-1,3]='--'\n",
    "    \n",
    "    display(temp)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Business Noncompete\n",
    "Agreement with the selling party not to compete with the purchaser, usually for a certain period of time and usually in a specified geographic area.  Pratts provides information on whether there was a a Non compete or not, and if yes, in some cases its description such as value/length of time.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intangible Intensity\n",
    "\n",
    "Below we show summary statistics for  intangible intensity for businesses broken down by whether they have a business noncompete\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs without NonCompete agreements</th>\n",
       "      <td>581</td>\n",
       "      <td>57.011</td>\n",
       "      <td>62.155</td>\n",
       "      <td>31.418</td>\n",
       "      <td>4.867</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with NonCompete agreement and  duration  = 0</th>\n",
       "      <td>25</td>\n",
       "      <td>56.228</td>\n",
       "      <td>61.714</td>\n",
       "      <td>29.831</td>\n",
       "      <td>0.011</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with NoNonCompete but no description of terms</th>\n",
       "      <td>314</td>\n",
       "      <td>62.484</td>\n",
       "      <td>67.792</td>\n",
       "      <td>27.275</td>\n",
       "      <td>1.182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with NonCompete agreement with strictly positive duration</th>\n",
       "      <td>5938</td>\n",
       "      <td>57.879</td>\n",
       "      <td>63.819</td>\n",
       "      <td>32.031</td>\n",
       "      <td>2.860</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                   count   mean  median    std  sales wgt\n",
       "Passthru excluding LLCs without NonCompete agreements                              581   57.011 62.155  31.418 4.867     \n",
       "Passthru excluding LLCs with NonCompete agreement and  duration  = 0               25    56.228 61.714  29.831 0.011     \n",
       "Passthru excluding LLCs with NoNonCompete but no description of terms              314   62.484 67.792  27.275 1.182     \n",
       "Passthru excluding LLCs with NonCompete agreement with strictly positive duration  5938  57.879 63.819  32.031 2.860     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs without NonCompete agreements</th>\n",
       "      <td>834</td>\n",
       "      <td>57.362</td>\n",
       "      <td>62.358</td>\n",
       "      <td>31.631</td>\n",
       "      <td>18.724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with NonCompete agreement and  duration  = 0</th>\n",
       "      <td>35</td>\n",
       "      <td>55.380</td>\n",
       "      <td>60.000</td>\n",
       "      <td>32.765</td>\n",
       "      <td>0.028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with NonCompete but no description of terms</th>\n",
       "      <td>379</td>\n",
       "      <td>62.014</td>\n",
       "      <td>67.423</td>\n",
       "      <td>27.989</td>\n",
       "      <td>1.411</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with NonCompete agreement with strictly positive duration</th>\n",
       "      <td>7615</td>\n",
       "      <td>57.298</td>\n",
       "      <td>63.077</td>\n",
       "      <td>32.379</td>\n",
       "      <td>4.801</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                                   count   mean  median    std  sales wgt\n",
       "Passthru including LLCs without NonCompete agreements                              834   57.362 62.358  31.631 18.724    \n",
       "Passthru including LLCs with NonCompete agreement and  duration  = 0               35    55.380 60.000  32.765 0.028     \n",
       "Passthru including LLCs with NonCompete but no description of terms                379   62.014 67.423  27.989 1.411     \n",
       "Passthru including LLCs with NonCompete agreement with strictly positive duration  7615  57.298 63.077  32.379 4.801     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>All businesses without NonCompete agreements</th>\n",
       "      <td>1578</td>\n",
       "      <td>59.893</td>\n",
       "      <td>66.703</td>\n",
       "      <td>31.795</td>\n",
       "      <td>89.710</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses with NonCompete agreement and  duration  = 0</th>\n",
       "      <td>44</td>\n",
       "      <td>55.645</td>\n",
       "      <td>60.857</td>\n",
       "      <td>31.707</td>\n",
       "      <td>0.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses with NonCompete but no description of terms</th>\n",
       "      <td>502</td>\n",
       "      <td>62.131</td>\n",
       "      <td>68.289</td>\n",
       "      <td>28.223</td>\n",
       "      <td>3.423</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All businesses with NonCompete agreement with strictly positive duration</th>\n",
       "      <td>8730</td>\n",
       "      <td>56.913</td>\n",
       "      <td>62.494</td>\n",
       "      <td>32.366</td>\n",
       "      <td>6.833</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                          count   mean  median    std  sales wgt\n",
       "All businesses without NonCompete agreements                              1578  59.893 66.703  31.795 89.710    \n",
       "All businesses with NonCompete agreement and  duration  = 0               44    55.645 60.857  31.707 0.034     \n",
       "All businesses with NonCompete but no description of terms                502   62.131 68.289  28.223 3.423     \n",
       "All businesses with NonCompete agreement with strictly positive duration  8730  56.913 62.494  32.366 6.833     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">IntanglibleIntensityPPA</th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th>NonCompeteAlt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">C Corporation</th>\n",
       "      <th>No Agreement</th>\n",
       "      <td>744</td>\n",
       "      <td>62.729</td>\n",
       "      <td>70.728</td>\n",
       "      <td>31.760</td>\n",
       "      <td>70.986</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete  with strictly positive duration</th>\n",
       "      <td>1115</td>\n",
       "      <td>54.287</td>\n",
       "      <td>57.500</td>\n",
       "      <td>32.170</td>\n",
       "      <td>2.032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with   duration  = 0</th>\n",
       "      <td>9</td>\n",
       "      <td>56.676</td>\n",
       "      <td>69.050</td>\n",
       "      <td>28.976</td>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with  no description of terms</th>\n",
       "      <td>123</td>\n",
       "      <td>62.494</td>\n",
       "      <td>70.690</td>\n",
       "      <td>29.047</td>\n",
       "      <td>2.013</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">LLC</th>\n",
       "      <th>No Agreement</th>\n",
       "      <td>253</td>\n",
       "      <td>58.168</td>\n",
       "      <td>64.355</td>\n",
       "      <td>32.162</td>\n",
       "      <td>13.857</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete  with strictly positive duration</th>\n",
       "      <td>1677</td>\n",
       "      <td>55.239</td>\n",
       "      <td>60.506</td>\n",
       "      <td>33.512</td>\n",
       "      <td>1.941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with   duration  = 0</th>\n",
       "      <td>10</td>\n",
       "      <td>53.260</td>\n",
       "      <td>54.444</td>\n",
       "      <td>40.933</td>\n",
       "      <td>0.017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with  no description of terms</th>\n",
       "      <td>65</td>\n",
       "      <td>59.741</td>\n",
       "      <td>65.400</td>\n",
       "      <td>31.344</td>\n",
       "      <td>0.229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Partnership</th>\n",
       "      <th>No Agreement</th>\n",
       "      <td>28</td>\n",
       "      <td>58.694</td>\n",
       "      <td>70.360</td>\n",
       "      <td>29.711</td>\n",
       "      <td>1.171</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete  with strictly positive duration</th>\n",
       "      <td>150</td>\n",
       "      <td>54.291</td>\n",
       "      <td>64.576</td>\n",
       "      <td>32.985</td>\n",
       "      <td>0.131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with  no description of terms</th>\n",
       "      <td>19</td>\n",
       "      <td>71.479</td>\n",
       "      <td>77.143</td>\n",
       "      <td>26.218</td>\n",
       "      <td>0.028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">S Corporation</th>\n",
       "      <th>No Agreement</th>\n",
       "      <td>456</td>\n",
       "      <td>58.266</td>\n",
       "      <td>62.159</td>\n",
       "      <td>30.824</td>\n",
       "      <td>3.675</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete  with strictly positive duration</th>\n",
       "      <td>4817</td>\n",
       "      <td>58.038</td>\n",
       "      <td>64.000</td>\n",
       "      <td>32.262</td>\n",
       "      <td>2.537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with   duration  = 0</th>\n",
       "      <td>23</td>\n",
       "      <td>55.918</td>\n",
       "      <td>61.714</td>\n",
       "      <td>30.158</td>\n",
       "      <td>0.010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with  no description of terms</th>\n",
       "      <td>225</td>\n",
       "      <td>60.982</td>\n",
       "      <td>65.672</td>\n",
       "      <td>28.619</td>\n",
       "      <td>1.142</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Sole Proprietorship</th>\n",
       "      <th>No Agreement</th>\n",
       "      <td>97</td>\n",
       "      <td>50.626</td>\n",
       "      <td>60.000</td>\n",
       "      <td>34.103</td>\n",
       "      <td>0.020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete  with strictly positive duration</th>\n",
       "      <td>971</td>\n",
       "      <td>57.647</td>\n",
       "      <td>63.055</td>\n",
       "      <td>30.702</td>\n",
       "      <td>0.192</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with   duration  = 0</th>\n",
       "      <td>2</td>\n",
       "      <td>59.794</td>\n",
       "      <td>59.794</td>\n",
       "      <td>36.349</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NonCompete with  no description of terms</th>\n",
       "      <td>70</td>\n",
       "      <td>64.870</td>\n",
       "      <td>67.736</td>\n",
       "      <td>22.382</td>\n",
       "      <td>0.012</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                                IntanglibleIntensityPPA                      wgt_sales\n",
       "                                                                                  count   mean median    std       sum\n",
       "TargetStructureAlt  NonCompeteAlt                                                                                     \n",
       "C Corporation       No Agreement                                 744                    62.729 70.728 31.760 70.986   \n",
       "                    NonCompete  with strictly positive duration  1115                   54.287 57.500 32.170 2.032    \n",
       "                    NonCompete with   duration  = 0              9                      56.676 69.050 28.976 0.005    \n",
       "                    NonCompete with  no description of terms     123                    62.494 70.690 29.047 2.013    \n",
       "LLC                 No Agreement                                 253                    58.168 64.355 32.162 13.857   \n",
       "                    NonCompete  with strictly positive duration  1677                   55.239 60.506 33.512 1.941    \n",
       "                    NonCompete with   duration  = 0              10                     53.260 54.444 40.933 0.017    \n",
       "                    NonCompete with  no description of terms     65                     59.741 65.400 31.344 0.229    \n",
       "Partnership         No Agreement                                 28                     58.694 70.360 29.711 1.171    \n",
       "                    NonCompete  with strictly positive duration  150                    54.291 64.576 32.985 0.131    \n",
       "                    NonCompete with  no description of terms     19                     71.479 77.143 26.218 0.028    \n",
       "S Corporation       No Agreement                                 456                    58.266 62.159 30.824 3.675    \n",
       "                    NonCompete  with strictly positive duration  4817                   58.038 64.000 32.262 2.537    \n",
       "                    NonCompete with   duration  = 0              23                     55.918 61.714 30.158 0.010    \n",
       "                    NonCompete with  no description of terms     225                    60.982 65.672 28.619 1.142    \n",
       "Sole Proprietorship No Agreement                                 97                     50.626 60.000 34.103 0.020    \n",
       "                    NonCompete  with strictly positive duration  971                    57.647 63.055 30.702 0.192    \n",
       "                    NonCompete with   duration  = 0              2                      59.794 59.794 36.349 0.000    \n",
       "                    NonCompete with  no description of terms     70                     64.870 67.736 22.382 0.012    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "f = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "data['NonCompete']=data['NonCompete'].replace(0,False)\n",
    "data['NonCompete']=data['NonCompete'].replace(1,True)\n",
    "data['NonCompete']=data['NonCompete'].astype(bool)\n",
    "data['NonCompeteAlt']=np.NaN\n",
    "sel=(data['NonCompete']==True)&(data.NonCompeteLength>0)\n",
    "data.loc[sel,'NonCompeteAlt']='NonCompete  with strictly positive duration'\n",
    "sel=(data['NonCompete']==True)&(data.NonCompeteLength==0) \n",
    "data.loc[sel,'NonCompeteAlt']='NonCompete with   duration  = 0'\n",
    "sel=(data['NonCompete']==True)& np.isnan(data.NonCompeteLength) \n",
    "data.loc[sel,'NonCompeteAlt']='NonCompete with  no description of terms'\n",
    "sel=(data['NonCompete']==False)\n",
    "data.loc[sel,'NonCompeteAlt']='No Agreement'\n",
    "\n",
    "data['NonCompeteLength']=data['NonCompeteLength']*30\n",
    "\n",
    "\n",
    "#(data.NonCompeteLength>0)\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThru\n",
    "\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)\n",
    "#print(temp)\n",
    "temp1=[]\n",
    "temp1=temp.iloc[3,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['Passthru excluding LLCs with NoNonCompete but no description of terms']\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[2,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru excluding LLCs with NonCompete agreement and  duration  = 0']\n",
    "\n",
    "\n",
    "temp3=[]\n",
    "temp3=temp.iloc[1,:].values\n",
    "temp3=pd.DataFrame(temp3,index=['count','mean','median','std','sales wgt']).T\n",
    "temp3.index=['Passthru excluding LLCs with NonCompete agreement with strictly positive duration']\n",
    "\n",
    "\n",
    "temp4=[]\n",
    "temp4=temp.iloc[0,:].values\n",
    "temp4=pd.DataFrame(temp4,index=['count','mean','median','std','sales wgt']).T\n",
    "temp4.index=['Passthru excluding LLCs without NonCompete agreements']\n",
    "\n",
    "\n",
    "temp=pd.concat([temp4,temp2,temp1,temp3])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThruLLC\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "temp1=[]\n",
    "temp1=temp.iloc[3,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['Passthru including LLCs with NonCompete but no description of terms']\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[2,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru including LLCs with NonCompete agreement and  duration  = 0']\n",
    "\n",
    "\n",
    "temp3=[]\n",
    "temp3=temp.iloc[1,:].values\n",
    "temp3=pd.DataFrame(temp3,index=['count','mean','median','std','sales wgt']).T\n",
    "temp3.index=['Passthru including LLCs with NonCompete agreement with strictly positive duration']\n",
    "\n",
    "\n",
    "temp4=[]\n",
    "temp4=temp.iloc[0,:].values\n",
    "temp4=pd.DataFrame(temp4,index=['count','mean','median','std','sales wgt']).T\n",
    "temp4.index=['Passthru including LLCs without NonCompete agreements']\n",
    "\n",
    "\n",
    "temp=pd.concat([temp4,temp2,temp1,temp3])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel&data.TargetStructureBus\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "temp1=[]\n",
    "temp1=temp.iloc[3,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['All businesses with NonCompete but no description of terms']\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[2,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['All businesses with NonCompete agreement and  duration  = 0']\n",
    "\n",
    "\n",
    "temp3=[]\n",
    "temp3=temp.iloc[1,:].values\n",
    "temp3=pd.DataFrame(temp3,index=['count','mean','median','std','sales wgt']).T\n",
    "temp3.index=['All businesses with NonCompete agreement with strictly positive duration']\n",
    "\n",
    "\n",
    "temp4=[]\n",
    "temp4=temp.iloc[0,:].values\n",
    "temp4=pd.DataFrame(temp4,index=['count','mean','median','std','sales wgt']).T\n",
    "temp4.index=['All businesses without NonCompete agreements']\n",
    "\n",
    "\n",
    "temp=pd.concat([temp4,temp2,temp1,temp3])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "\n",
    "sel2=selection&sel\n",
    "\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby([data[sel2].TargetStructureAlt,data[sel2].NonCompeteAlt] ).agg(f)\n",
    "\n",
    "display(temp)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Terms of Contract: Length of contract\n",
    "Below we describe statistics for the length of the NonCompete in **days**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with NonCompete</th>\n",
       "      <td>5938</td>\n",
       "      <td>1612.998</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>1251.640</td>\n",
       "      <td>2.860</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         count     mean   median      std  sales wgt\n",
       "Passthru excluding LLCs with NonCompete  5938  1612.998 1800.000 1251.640 2.860     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with NonCompete</th>\n",
       "      <td>7615</td>\n",
       "      <td>1589.622</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>1282.746</td>\n",
       "      <td>4.801</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         count     mean   median      std  sales wgt\n",
       "Passthru including LLCs with NonCompete  7615  1589.622 1800.000 1282.746 4.801     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>All business  including LLCs with NonCompete</th>\n",
       "      <td>8730</td>\n",
       "      <td>1595.502</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>1272.922</td>\n",
       "      <td>6.833</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                              count     mean   median      std  sales wgt\n",
       "All business  including LLCs with NonCompete  8730  1595.502 1800.000 1272.922 6.833     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">NonCompeteLength</th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>1115</td>\n",
       "      <td>1635.659</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>1203.468</td>\n",
       "      <td>2.032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>1677</td>\n",
       "      <td>1506.852</td>\n",
       "      <td>1080.000</td>\n",
       "      <td>1384.518</td>\n",
       "      <td>1.941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>150</td>\n",
       "      <td>1725.200</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>1595.284</td>\n",
       "      <td>0.131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>4817</td>\n",
       "      <td>1601.204</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>1292.476</td>\n",
       "      <td>2.537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>971</td>\n",
       "      <td>1654.171</td>\n",
       "      <td>1800.000</td>\n",
       "      <td>948.370</td>\n",
       "      <td>0.192</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    NonCompeteLength                            wgt_sales\n",
       "                               count     mean   median      std       sum\n",
       "TargetStructureAlt                                                       \n",
       "C Corporation        1115            1635.659 1800.000 1203.468 2.032    \n",
       "LLC                  1677            1506.852 1080.000 1384.518 1.941    \n",
       "Partnership          150             1725.200 1800.000 1595.284 0.131    \n",
       "S Corporation        4817            1601.204 1800.000 1292.476 2.537    \n",
       "Sole Proprietorship  971             1654.171 1800.000 948.370  0.192    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "f = {'NonCompeteLength':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'NonCompeteLength':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) ) & (data.NonCompeteAlt=='NonCompete  with strictly positive duration')\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThru\n",
    "temp=data[sel2][['NonCompeteLength','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru excluding LLCs with NonCompete']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "\n",
    "temp['count']=temp['count'].astype(int)\n",
    "        \n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThruLLC\n",
    "temp=data[sel2][['NonCompeteLength','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru including LLCs with NonCompete']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "\n",
    "temp['count']=temp['count'].astype(int)\n",
    "        \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel&data.TargetStructureBus\n",
    "temp=data[sel2][['NonCompeteLength','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['All business  including LLCs with NonCompete']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "\n",
    "temp['count']=temp['count'].astype(int)\n",
    "        \n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel\n",
    "\n",
    "temp=data[sel2][['NonCompeteLength','wgt_sales']].groupby([data[sel2].TargetStructureAlt] ).agg(f)\n",
    "\n",
    "display(temp)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Terms of Contract: radius \n",
    "Below we describe statistics for the radius of the NonCompete in **miles**. There are several observations where the radius is described as a list of counties/states. We drop those when we construct the stats below."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with NonCompete</th>\n",
       "      <td>4889</td>\n",
       "      <td>39.913</td>\n",
       "      <td>20.000</td>\n",
       "      <td>64.218</td>\n",
       "      <td>1.808</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         count   mean  median    std  sales wgt\n",
       "Passthru excluding LLCs with NonCompete  4889  39.913 20.000  64.218 1.808     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with NonCompete</th>\n",
       "      <td>6326</td>\n",
       "      <td>39.264</td>\n",
       "      <td>20.000</td>\n",
       "      <td>65.693</td>\n",
       "      <td>2.200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                         count   mean  median    std  sales wgt\n",
       "Passthru including LLCs with NonCompete  6326  39.264 20.000  65.693 2.200     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>All business  with NonCompete</th>\n",
       "      <td>7163</td>\n",
       "      <td>39.397</td>\n",
       "      <td>20.000</td>\n",
       "      <td>66.331</td>\n",
       "      <td>2.538</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               count   mean  median    std  sales wgt\n",
       "All business  with NonCompete  7163  39.397 20.000  66.331 2.538     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">NonCompeteDistance</th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>837</td>\n",
       "      <td>40.404</td>\n",
       "      <td>20.000</td>\n",
       "      <td>70.999</td>\n",
       "      <td>0.338</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>1437</td>\n",
       "      <td>37.057</td>\n",
       "      <td>20.000</td>\n",
       "      <td>70.458</td>\n",
       "      <td>0.392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>119</td>\n",
       "      <td>28.504</td>\n",
       "      <td>20.000</td>\n",
       "      <td>43.866</td>\n",
       "      <td>0.029</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>3999</td>\n",
       "      <td>40.746</td>\n",
       "      <td>20.000</td>\n",
       "      <td>65.260</td>\n",
       "      <td>1.626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>771</td>\n",
       "      <td>37.350</td>\n",
       "      <td>20.000</td>\n",
       "      <td>61.168</td>\n",
       "      <td>0.154</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    NonCompeteDistance                      wgt_sales\n",
       "                                 count   mean median    std       sum\n",
       "TargetStructureAlt                                                   \n",
       "C Corporation        837               40.404 20.000 70.999 0.338    \n",
       "LLC                  1437              37.057 20.000 70.458 0.392    \n",
       "Partnership          119               28.504 20.000 43.866 0.029    \n",
       "S Corporation        3999              40.746 20.000 65.260 1.626    \n",
       "Sole Proprietorship  771               37.350 20.000 61.168 0.154    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "f = {'NonCompeteDistance':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'NonCompeteDistance':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )&(data[\"NonCompeteDistance\"]>0)& (data.NonCompeteAlt=='NonCompete  with strictly positive duration')\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThru&(data[\"NonCompeteDistance\"]>0)\n",
    "temp=data[sel2][['NonCompeteDistance','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "\n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru excluding LLCs with NonCompete']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThruLLC\n",
    "temp=data[sel2][['NonCompeteDistance','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru including LLCs with NonCompete']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel&data.TargetStructureBus\n",
    "temp=data[sel2][['NonCompeteDistance','wgt_sales']].groupby(data[sel2].NonCompeteAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['All business  with NonCompete']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel\n",
    "\n",
    "temp=data[sel2][['NonCompeteDistance','wgt_sales']].groupby([data[sel2].TargetStructureAlt] ).agg(f)\n",
    "\n",
    "display(temp)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Owner Consulting Agreements\n",
    "An agreement between the buyer and seller for the seller's personal services to be provided to the buyer either as an employee or consultant after the sale of the business."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Intangible Intensity\n",
    "Below we show summary statistics for  intangible intensity for businesses broken down by whether they have a owner consulting agreement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs without consulting agreements</th>\n",
       "      <td>3126</td>\n",
       "      <td>58.205</td>\n",
       "      <td>64.234</td>\n",
       "      <td>30.250</td>\n",
       "      <td>7.038</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with consulting agreements</th>\n",
       "      <td>3732</td>\n",
       "      <td>57.848</td>\n",
       "      <td>63.636</td>\n",
       "      <td>33.010</td>\n",
       "      <td>1.882</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                       count   mean  median    std  sales wgt\n",
       "Passthru excluding LLCs without consulting agreements  3126  58.205 64.234  30.250 7.038     \n",
       "Passthru excluding LLCs with consulting agreements     3732  57.848 63.636  33.010 1.882     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs without consulting agreements</th>\n",
       "      <td>3837</td>\n",
       "      <td>58.396</td>\n",
       "      <td>64.721</td>\n",
       "      <td>30.487</td>\n",
       "      <td>22.677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with consulting agreements</th>\n",
       "      <td>5026</td>\n",
       "      <td>56.812</td>\n",
       "      <td>62.408</td>\n",
       "      <td>33.342</td>\n",
       "      <td>2.287</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                       count   mean  median    std  sales wgt\n",
       "Passthru including LLCs without consulting agreements  3837  58.396 64.721  30.487 22.677    \n",
       "Passthru including LLCs with consulting agreements     5026  56.812 62.408  33.342 2.287     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>All business without consulting agreements</th>\n",
       "      <td>5127</td>\n",
       "      <td>58.719</td>\n",
       "      <td>64.912</td>\n",
       "      <td>30.774</td>\n",
       "      <td>97.188</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>All business  including LLCs with consulting agreements</th>\n",
       "      <td>5727</td>\n",
       "      <td>56.565</td>\n",
       "      <td>61.845</td>\n",
       "      <td>33.266</td>\n",
       "      <td>2.812</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                         count   mean  median    std  sales wgt\n",
       "All business without consulting agreements               5127  58.719 64.912  30.774 97.188    \n",
       "All business  including LLCs with consulting agreements  5727  56.565 61.845  33.266 2.812     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">IntanglibleIntensityPPA</th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th>EmploymentAgreementAlt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">C Corporation</th>\n",
       "      <th>False</th>\n",
       "      <td>1290</td>\n",
       "      <td>59.681</td>\n",
       "      <td>65.881</td>\n",
       "      <td>31.606</td>\n",
       "      <td>74.511</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>701</td>\n",
       "      <td>54.791</td>\n",
       "      <td>58.182</td>\n",
       "      <td>32.689</td>\n",
       "      <td>0.526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">LLC</th>\n",
       "      <th>False</th>\n",
       "      <td>711</td>\n",
       "      <td>59.235</td>\n",
       "      <td>65.844</td>\n",
       "      <td>31.517</td>\n",
       "      <td>15.639</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>1294</td>\n",
       "      <td>53.827</td>\n",
       "      <td>58.503</td>\n",
       "      <td>34.119</td>\n",
       "      <td>0.405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Partnership</th>\n",
       "      <th>False</th>\n",
       "      <td>114</td>\n",
       "      <td>61.712</td>\n",
       "      <td>71.034</td>\n",
       "      <td>29.565</td>\n",
       "      <td>1.296</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>83</td>\n",
       "      <td>49.518</td>\n",
       "      <td>50.480</td>\n",
       "      <td>34.489</td>\n",
       "      <td>0.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">S Corporation</th>\n",
       "      <th>False</th>\n",
       "      <td>2392</td>\n",
       "      <td>58.004</td>\n",
       "      <td>63.597</td>\n",
       "      <td>30.455</td>\n",
       "      <td>5.607</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>3129</td>\n",
       "      <td>58.293</td>\n",
       "      <td>64.000</td>\n",
       "      <td>33.127</td>\n",
       "      <td>1.757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Sole Proprietorship</th>\n",
       "      <th>False</th>\n",
       "      <td>620</td>\n",
       "      <td>58.336</td>\n",
       "      <td>64.777</td>\n",
       "      <td>29.577</td>\n",
       "      <td>0.134</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>520</td>\n",
       "      <td>56.496</td>\n",
       "      <td>62.500</td>\n",
       "      <td>31.890</td>\n",
       "      <td>0.091</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           IntanglibleIntensityPPA                      wgt_sales\n",
       "                                                             count   mean median    std       sum\n",
       "TargetStructureAlt  EmploymentAgreementAlt                                                       \n",
       "C Corporation       False                   1290                   59.681 65.881 31.606 74.511   \n",
       "                    True                    701                    54.791 58.182 32.689 0.526    \n",
       "LLC                 False                   711                    59.235 65.844 31.517 15.639   \n",
       "                    True                    1294                   53.827 58.503 34.119 0.405    \n",
       "Partnership         False                   114                    61.712 71.034 29.565 1.296    \n",
       "                    True                    83                     49.518 50.480 34.489 0.034    \n",
       "S Corporation       False                   2392                   58.004 63.597 30.455 5.607    \n",
       "                    True                    3129                   58.293 64.000 33.127 1.757    \n",
       "Sole Proprietorship False                   620                    58.336 64.777 29.577 0.134    \n",
       "                    True                    520                    56.496 62.500 31.890 0.091    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "f = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "data['EmploymentAgreement']=data['EmploymentAgreement'].replace(0,'False')\n",
    "data['EmploymentAgreement']=data['EmploymentAgreement'].replace(1,'True')\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThru\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[sel2].EmploymentAgreementAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp1=[]\n",
    "temp1=temp.iloc[0,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['Passthru excluding LLCs without consulting agreements']\n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[1,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru excluding LLCs with consulting agreements']\n",
    "\n",
    "temp=pd.concat([temp1,temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThruLLC\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[sel2].EmploymentAgreementAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp1=[]\n",
    "temp1=temp.iloc[0,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['Passthru including LLCs without consulting agreements']\n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[1,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru including LLCs with consulting agreements']\n",
    "\n",
    "temp=pd.concat([temp1,temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "\n",
    "\n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel&data.TargetStructureBus\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(data[sel2].EmploymentAgreementAlt).agg(f)   \n",
    "\n",
    "temp1=[]\n",
    "temp1=temp.iloc[0,:].values\n",
    "temp1=pd.DataFrame(temp1,index=['count','mean','median','std','sales wgt']).T\n",
    "temp1.index=['All business without consulting agreements']\n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[1,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['All business  including LLCs with consulting agreements']\n",
    "\n",
    "temp=pd.concat([temp1,temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel\n",
    "\n",
    "temp=data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby([data[sel2].TargetStructureAlt,data[sel2].EmploymentAgreementAlt] ).agg(f)\n",
    "\n",
    "display(temp)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Terms of Contracts: Length\n",
    "Below we describe statistics for the length of the consulting agreement measured in **days.** This is extracted from the descriptive notes "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru excluding LLCs with consulting agreements</th>\n",
       "      <td>3732</td>\n",
       "      <td>93.993</td>\n",
       "      <td>60.000</td>\n",
       "      <td>232.581</td>\n",
       "      <td>1.882</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    count   mean  median     std  sales wgt\n",
       "Passthru excluding LLCs with consulting agreements  3732  93.993 60.000  232.581 1.882     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Passthru including LLCs with consulting agreements</th>\n",
       "      <td>5026</td>\n",
       "      <td>87.369</td>\n",
       "      <td>60.000</td>\n",
       "      <td>207.979</td>\n",
       "      <td>2.287</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    count   mean  median     std  sales wgt\n",
       "Passthru including LLCs with consulting agreements  5026  87.369 60.000  207.979 2.287     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sales wgt</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>All business  with consulting agreements</th>\n",
       "      <td>5727</td>\n",
       "      <td>93.285</td>\n",
       "      <td>60.000</td>\n",
       "      <td>227.561</td>\n",
       "      <td>2.812</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          count   mean  median     std  sales wgt\n",
       "All business  with consulting agreements  5727  93.285 60.000  227.561 2.812     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">EmploymentAgreementLength</th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>C Corporation</th>\n",
       "      <td>701</td>\n",
       "      <td>135.703</td>\n",
       "      <td>60</td>\n",
       "      <td>333.210</td>\n",
       "      <td>0.526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>1294</td>\n",
       "      <td>68.267</td>\n",
       "      <td>60</td>\n",
       "      <td>107.333</td>\n",
       "      <td>0.405</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>83</td>\n",
       "      <td>113.843</td>\n",
       "      <td>60</td>\n",
       "      <td>290.563</td>\n",
       "      <td>0.034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>3129</td>\n",
       "      <td>98.168</td>\n",
       "      <td>60</td>\n",
       "      <td>243.678</td>\n",
       "      <td>1.757</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>520</td>\n",
       "      <td>65.698</td>\n",
       "      <td>30</td>\n",
       "      <td>129.188</td>\n",
       "      <td>0.091</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    EmploymentAgreementLength                        wgt_sales\n",
       "                                        count    mean median     std       sum\n",
       "TargetStructureAlt                                                            \n",
       "C Corporation        701                      135.703  60    333.210 0.526    \n",
       "LLC                  1294                     68.267   60    107.333 0.405    \n",
       "Partnership          83                       113.843  60    290.563 0.034    \n",
       "S Corporation        3129                     98.168   60    243.678 1.757    \n",
       "Sole Proprietorship  520                      65.698   30    129.188 0.091    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "f = {'EmploymentAgreementLength':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'EmploymentAgreementLength':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )&data.EmploymentAgreementAlt\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThru\n",
    "temp=data[sel2][['EmploymentAgreementLength','wgt_sales']].groupby(data[sel2].EmploymentAgreementAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru excluding LLCs with consulting agreements']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "\n",
    "display(temp)\n",
    "\n",
    "\n",
    "sel2=selection&sel&data.TargetStructurePassThruLLC\n",
    "temp=data[sel2][['EmploymentAgreementLength','wgt_sales']].groupby(data[sel2].EmploymentAgreementAlt).agg(f)   \n",
    "\n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['Passthru including LLCs with consulting agreements']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "    \n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel&data.TargetStructureBus\n",
    "temp=data[sel2][['EmploymentAgreementLength','wgt_sales']].groupby(data[sel2].EmploymentAgreementAlt).agg(f)   \n",
    "\n",
    "\n",
    "temp2=[]\n",
    "temp2=temp.iloc[0,:].values\n",
    "temp2=pd.DataFrame(temp2,index=['count','mean','median','std','sales wgt']).T\n",
    "temp2.index=['All business  with consulting agreements']\n",
    "\n",
    "temp=pd.concat([temp2])\n",
    "temp['count']=temp['count'].astype(int)\n",
    "    \n",
    "display(temp)\n",
    "\n",
    "sel2=selection&sel\n",
    "\n",
    "temp=data[sel2][['EmploymentAgreementLength','wgt_sales']].groupby([data[sel2].TargetStructureAlt] ).agg(f)\n",
    "\n",
    "display(temp)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Decomposition of Intangible Intensity by total assets\n",
    "### For all passthrus including LLCS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">IntanglibleIntensityPPA</th>\n",
       "      <th>wgt_sales_passthru</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TotalAssetsPPA</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(1000.0, 77500.0]</th>\n",
       "      <td>1774</td>\n",
       "      <td>47.112</td>\n",
       "      <td>46.656</td>\n",
       "      <td>34.829</td>\n",
       "      <td>0.692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(77500.0, 145000.0]</th>\n",
       "      <td>1812</td>\n",
       "      <td>54.332</td>\n",
       "      <td>57.922</td>\n",
       "      <td>32.138</td>\n",
       "      <td>1.163</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(145000.0, 256130.6]</th>\n",
       "      <td>1731</td>\n",
       "      <td>58.415</td>\n",
       "      <td>63.171</td>\n",
       "      <td>33.119</td>\n",
       "      <td>1.769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(256130.6, 575000.0]</th>\n",
       "      <td>1773</td>\n",
       "      <td>62.905</td>\n",
       "      <td>69.195</td>\n",
       "      <td>28.300</td>\n",
       "      <td>3.021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(575000.0, 21824700000.0]</th>\n",
       "      <td>1772</td>\n",
       "      <td>64.803</td>\n",
       "      <td>72.088</td>\n",
       "      <td>28.710</td>\n",
       "      <td>93.355</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          IntanglibleIntensityPPA                      wgt_sales_passthru\n",
       "                                            count   mean median    std                sum\n",
       "TotalAssetsPPA                                                                           \n",
       "(1000.0, 77500.0]          1774                   47.112 46.656 34.829 0.692             \n",
       "(77500.0, 145000.0]        1812                   54.332 57.922 32.138 1.163             \n",
       "(145000.0, 256130.6]       1731                   58.415 63.171 33.119 1.769             \n",
       "(256130.6, 575000.0]       1773                   62.905 69.195 28.300 3.021             \n",
       "(575000.0, 21824700000.0]  1772                   64.803 72.088 28.710 93.355            "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "sel2=selection&sel&data.TargetStructurePassThruLLC\n",
    "\n",
    "data[sel2][['IntanglibleIntensityPPA','wgt_sales_passthru']].groupby(pd.cut( data[sel2]['TotalAssetsPPA'] , [ data[sel2]['TotalAssetsPPA'].quantile(x) for x in [0.0,0.2,0.4,0.6,0.8,1.0]] )).agg(f_pass)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For all businesses \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">IntanglibleIntensityPPA</th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>median</th>\n",
       "      <th>std</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TotalAssetsPPA</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>(1000.0, 85000.0]</th>\n",
       "      <td>2275</td>\n",
       "      <td>47.609</td>\n",
       "      <td>47.783</td>\n",
       "      <td>34.772</td>\n",
       "      <td>0.229</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(85000.0, 160000.0]</th>\n",
       "      <td>2158</td>\n",
       "      <td>54.923</td>\n",
       "      <td>59.015</td>\n",
       "      <td>31.922</td>\n",
       "      <td>0.383</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(160000.0, 300000.0]</th>\n",
       "      <td>2092</td>\n",
       "      <td>59.877</td>\n",
       "      <td>64.680</td>\n",
       "      <td>32.081</td>\n",
       "      <td>0.601</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(300000.0, 850000.0]</th>\n",
       "      <td>2173</td>\n",
       "      <td>61.836</td>\n",
       "      <td>68.876</td>\n",
       "      <td>29.059</td>\n",
       "      <td>1.164</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>(850000.0, 153000000000.0]</th>\n",
       "      <td>2155</td>\n",
       "      <td>64.239</td>\n",
       "      <td>70.877</td>\n",
       "      <td>29.550</td>\n",
       "      <td>97.623</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           IntanglibleIntensityPPA                      wgt_sales\n",
       "                                             count   mean median    std       sum\n",
       "TotalAssetsPPA                                                                   \n",
       "(1000.0, 85000.0]           2275                   47.609 47.783 34.772 0.229    \n",
       "(85000.0, 160000.0]         2158                   54.923 59.015 31.922 0.383    \n",
       "(160000.0, 300000.0]        2092                   59.877 64.680 32.081 0.601    \n",
       "(300000.0, 850000.0]        2173                   61.836 68.876 29.059 1.164    \n",
       "(850000.0, 153000000000.0]  2155                   64.239 70.877 29.550 97.623   "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "f = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales':['sum']}\n",
    "f_pass = {'IntanglibleIntensityPPA':['count','mean','median','std'],'wgt_sales_passthru':['sum']}\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "sel2=selection&sel\n",
    "\n",
    "data[sel2][['IntanglibleIntensityPPA','wgt_sales']].groupby(pd.cut( data[sel2]['TotalAssetsPPA'] , [ data[sel2]['TotalAssetsPPA'].quantile(x) for x in [0.0,0.2,0.4,0.6,0.8,1.0]] )).agg(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Regression of $\\log V_{\\kappa}$ on $age,age^2$\n",
    "The sample includes all pass-thru firms in Pratts who have a valid entry for \"TargetAge\" and a positive value for \"TotalIntangiblesPPA\". The regression specification is \n",
    "\n",
    "$$ \\log(\\text{TotalIntangiblesPPA}) = \\text{constant} + \\beta_{1} \\text{TagetAge} + \\beta_{2} \\text{TagetAge}^2 + \\text{sector fixed effects}+ \\text{year fixed effects} $$\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"simpletable\">\n",
       "<caption>OLS Regression Results</caption>\n",
       "<tr>\n",
       "  <th>Dep. Variable:</th>    <td>TotalIntangiblesPPA</td> <th>  R-squared:         </th> <td>   0.236</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Model:</th>                    <td>OLS</td>         <th>  Adj. R-squared:    </th> <td>   0.232</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Method:</th>              <td>Least Squares</td>    <th>  F-statistic:       </th> <td>   58.75</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Date:</th>              <td>Wed, 19 Aug 2020</td>   <th>  Prob (F-statistic):</th>  <td>  0.00</td>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Time:</th>                  <td>13:35:33</td>       <th>  Log-Likelihood:    </th> <td> -16865.</td> \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>No. Observations:</th>       <td>  8607</td>        <th>  AIC:               </th> <td>3.382e+04</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Df Residuals:</th>           <td>  8561</td>        <th>  BIC:               </th> <td>3.415e+04</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Df Model:</th>               <td>    45</td>        <th>                     </th>     <td> </td>    \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Covariance Type:</th>       <td>nonrobust</td>      <th>                     </th>     <td> </td>    \n",
       "</tr>\n",
       "</table>\n",
       "<table class=\"simpletable\">\n",
       "<tr>\n",
       "             <td></td>               <th>coef</th>     <th>std err</th>      <th>t</th>      <th>P>|t|</th>  <th>[0.025</th>    <th>0.975]</th>  \n",
       "</tr>\n",
       "<tr>\n",
       "  <th>const</th>                  <td>   11.2272</td> <td>    0.960</td> <td>   11.693</td> <td> 0.000</td> <td>    9.345</td> <td>   13.109</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>TargetAge</th>              <td>    0.0169</td> <td>    0.002</td> <td>    8.518</td> <td> 0.000</td> <td>    0.013</td> <td>    0.021</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>TargetAge2</th>             <td>-4.637e-05</td> <td> 1.42e-05</td> <td>   -3.270</td> <td> 0.001</td> <td>-7.42e-05</td> <td>-1.86e-05</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[11, 21)</th> <td>    0.2525</td> <td>    1.099</td> <td>    0.230</td> <td> 0.818</td> <td>   -1.902</td> <td>    2.407</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[21, 22)</th> <td>    4.4811</td> <td>    1.087</td> <td>    4.123</td> <td> 0.000</td> <td>    2.351</td> <td>    6.612</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[22, 23)</th> <td>   -0.2470</td> <td>    1.191</td> <td>   -0.207</td> <td> 0.836</td> <td>   -2.583</td> <td>    2.089</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[23, 24)</th> <td>    0.2224</td> <td>    1.001</td> <td>    0.222</td> <td> 0.824</td> <td>   -1.740</td> <td>    2.185</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[24, 31)</th> <td> 2.674e-14</td> <td> 2.24e-14</td> <td>    1.192</td> <td> 0.233</td> <td>-1.72e-14</td> <td> 7.07e-14</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[31, 35)</th> <td>    0.6615</td> <td>    0.999</td> <td>    0.662</td> <td> 0.508</td> <td>   -1.298</td> <td>    2.620</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[35, 42)</th> <td>-2.324e-15</td> <td> 9.11e-15</td> <td>   -0.255</td> <td> 0.799</td> <td>-2.02e-14</td> <td> 1.55e-14</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[42, 44)</th> <td>   -0.9709</td> <td>    1.087</td> <td>   -0.893</td> <td> 0.372</td> <td>   -3.101</td> <td>    1.160</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[44, 46)</th> <td>   -0.7390</td> <td>    0.998</td> <td>   -0.740</td> <td> 0.459</td> <td>   -2.696</td> <td>    1.218</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[46, 48)</th> <td>-3.898e-15</td> <td>  2.2e-14</td> <td>   -0.177</td> <td> 0.859</td> <td> -4.7e-14</td> <td> 3.92e-14</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[48, 50)</th> <td>   -0.3017</td> <td>    1.002</td> <td>   -0.301</td> <td> 0.763</td> <td>   -2.265</td> <td>    1.662</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[50, 51)</th> <td>-1.141e-15</td> <td> 2.37e-15</td> <td>   -0.482</td> <td> 0.630</td> <td>-5.78e-15</td> <td>  3.5e-15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[51, 52)</th> <td>    2.3662</td> <td>    1.005</td> <td>    2.355</td> <td> 0.019</td> <td>    0.397</td> <td>    4.336</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[52, 53)</th> <td>    0.7913</td> <td>    1.007</td> <td>    0.786</td> <td> 0.432</td> <td>   -1.182</td> <td>    2.765</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[53, 54)</th> <td>   -0.1870</td> <td>    1.003</td> <td>   -0.187</td> <td> 0.852</td> <td>   -2.152</td> <td>    1.778</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[54, 55)</th> <td>    0.8193</td> <td>    1.001</td> <td>    0.819</td> <td> 0.413</td> <td>   -1.142</td> <td>    2.781</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[55, 56)</th> <td>    6.4695</td> <td>    1.137</td> <td>    5.690</td> <td> 0.000</td> <td>    4.241</td> <td>    8.698</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[56, 61)</th> <td>   -0.5017</td> <td>    0.999</td> <td>   -0.502</td> <td> 0.615</td> <td>   -2.460</td> <td>    1.456</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[61, 62)</th> <td>   -0.9277</td> <td>    1.011</td> <td>   -0.918</td> <td> 0.359</td> <td>   -2.910</td> <td>    1.054</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[62, 71)</th> <td>   -0.1758</td> <td>    1.001</td> <td>   -0.176</td> <td> 0.861</td> <td>   -2.138</td> <td>    1.787</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[71, 72)</th> <td>   -0.8380</td> <td>    1.007</td> <td>   -0.833</td> <td> 0.405</td> <td>   -2.811</td> <td>    1.135</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[72, 81)</th> <td>   -1.2648</td> <td>    0.998</td> <td>   -1.267</td> <td> 0.205</td> <td>   -3.221</td> <td>    0.692</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[81, 83)</th> <td>   -1.3000</td> <td>    0.999</td> <td>   -1.302</td> <td> 0.193</td> <td>   -3.258</td> <td>    0.657</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>nacis_code_cat[83, 92)</th> <td>  2.13e-15</td> <td> 3.81e-15</td> <td>    0.560</td> <td> 0.576</td> <td>-5.33e-15</td> <td> 9.59e-15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1990</th>               <td> -3.83e-15</td> <td> 5.19e-15</td> <td>   -0.737</td> <td> 0.461</td> <td> -1.4e-14</td> <td> 6.35e-15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1991</th>               <td> 1.658e-15</td> <td> 1.74e-15</td> <td>    0.952</td> <td> 0.341</td> <td>-1.76e-15</td> <td> 5.07e-15</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1992</th>               <td> 3.969e-16</td> <td> 6.46e-15</td> <td>    0.061</td> <td> 0.951</td> <td>-1.23e-14</td> <td> 1.31e-14</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1993</th>               <td>   -1.2132</td> <td>    1.173</td> <td>   -1.035</td> <td> 0.301</td> <td>   -3.512</td> <td>    1.085</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1994</th>               <td>   -0.2682</td> <td>    0.630</td> <td>   -0.426</td> <td> 0.670</td> <td>   -1.503</td> <td>    0.966</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1995</th>               <td>   -0.1314</td> <td>    0.466</td> <td>   -0.282</td> <td> 0.778</td> <td>   -1.044</td> <td>    0.781</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1996</th>               <td>    0.0906</td> <td>    0.333</td> <td>    0.272</td> <td> 0.786</td> <td>   -0.562</td> <td>    0.744</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1997</th>               <td>    0.1838</td> <td>    0.261</td> <td>    0.705</td> <td> 0.481</td> <td>   -0.327</td> <td>    0.695</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1998</th>               <td>    0.4123</td> <td>    0.184</td> <td>    2.237</td> <td> 0.025</td> <td>    0.051</td> <td>    0.774</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year1999</th>               <td>    0.2481</td> <td>    0.169</td> <td>    1.466</td> <td> 0.143</td> <td>   -0.084</td> <td>    0.580</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2000</th>               <td>    0.6193</td> <td>    0.172</td> <td>    3.609</td> <td> 0.000</td> <td>    0.283</td> <td>    0.956</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2001</th>               <td>    0.0279</td> <td>    0.165</td> <td>    0.169</td> <td> 0.866</td> <td>   -0.295</td> <td>    0.351</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2002</th>               <td>    0.3575</td> <td>    0.148</td> <td>    2.420</td> <td> 0.016</td> <td>    0.068</td> <td>    0.647</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2003</th>               <td>    0.2876</td> <td>    0.149</td> <td>    1.924</td> <td> 0.054</td> <td>   -0.005</td> <td>    0.581</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2004</th>               <td>    0.3198</td> <td>    0.136</td> <td>    2.360</td> <td> 0.018</td> <td>    0.054</td> <td>    0.585</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2005</th>               <td>    0.8324</td> <td>    0.131</td> <td>    6.355</td> <td> 0.000</td> <td>    0.576</td> <td>    1.089</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2006</th>               <td>    0.7937</td> <td>    0.120</td> <td>    6.637</td> <td> 0.000</td> <td>    0.559</td> <td>    1.028</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2007</th>               <td>    0.9219</td> <td>    0.112</td> <td>    8.217</td> <td> 0.000</td> <td>    0.702</td> <td>    1.142</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2008</th>               <td>    0.8160</td> <td>    0.106</td> <td>    7.723</td> <td> 0.000</td> <td>    0.609</td> <td>    1.023</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2009</th>               <td>    0.5219</td> <td>    0.102</td> <td>    5.126</td> <td> 0.000</td> <td>    0.322</td> <td>    0.721</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2010</th>               <td>    0.5082</td> <td>    0.098</td> <td>    5.175</td> <td> 0.000</td> <td>    0.316</td> <td>    0.701</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2011</th>               <td>    0.6108</td> <td>    0.098</td> <td>    6.210</td> <td> 0.000</td> <td>    0.418</td> <td>    0.804</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2012</th>               <td>    0.8147</td> <td>    0.097</td> <td>    8.383</td> <td> 0.000</td> <td>    0.624</td> <td>    1.005</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2013</th>               <td>    0.8809</td> <td>    0.096</td> <td>    9.203</td> <td> 0.000</td> <td>    0.693</td> <td>    1.069</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2014</th>               <td>    1.0353</td> <td>    0.093</td> <td>   11.126</td> <td> 0.000</td> <td>    0.853</td> <td>    1.218</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2015</th>               <td>    1.0461</td> <td>    0.092</td> <td>   11.350</td> <td> 0.000</td> <td>    0.865</td> <td>    1.227</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2016</th>               <td>    0.8004</td> <td>    0.092</td> <td>    8.740</td> <td> 0.000</td> <td>    0.621</td> <td>    0.980</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>year2017</th>               <td>    0.7109</td> <td>    0.118</td> <td>    6.034</td> <td> 0.000</td> <td>    0.480</td> <td>    0.942</td>\n",
       "</tr>\n",
       "</table>\n",
       "<table class=\"simpletable\">\n",
       "<tr>\n",
       "  <th>Omnibus:</th>       <td>842.453</td> <th>  Durbin-Watson:     </th> <td>   1.647</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Prob(Omnibus):</th> <td> 0.000</td>  <th>  Jarque-Bera (JB):  </th> <td>4493.515</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Skew:</th>          <td> 0.321</td>  <th>  Prob(JB):          </th> <td>    0.00</td>\n",
       "</tr>\n",
       "<tr>\n",
       "  <th>Kurtosis:</th>      <td> 6.481</td>  <th>  Cond. No.          </th> <td>1.03e+16</td>\n",
       "</tr>\n",
       "</table><br/><br/>Warnings:<br/>[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.<br/>[2] The smallest eigenvalue is 3.1e-22. This might indicate that there are<br/>strong multicollinearity problems or that the design matrix is singular."
      ],
      "text/plain": [
       "<class 'statsmodels.iolib.summary.Summary'>\n",
       "\"\"\"\n",
       "                             OLS Regression Results                            \n",
       "===============================================================================\n",
       "Dep. Variable:     TotalIntangiblesPPA   R-squared:                       0.236\n",
       "Model:                             OLS   Adj. R-squared:                  0.232\n",
       "Method:                  Least Squares   F-statistic:                     58.75\n",
       "Date:                 Wed, 19 Aug 2020   Prob (F-statistic):               0.00\n",
       "Time:                         13:35:33   Log-Likelihood:                -16865.\n",
       "No. Observations:                 8607   AIC:                         3.382e+04\n",
       "Df Residuals:                     8561   BIC:                         3.415e+04\n",
       "Df Model:                           45                                         \n",
       "Covariance Type:             nonrobust                                         \n",
       "==========================================================================================\n",
       "                             coef    std err          t      P>|t|      [0.025      0.975]\n",
       "------------------------------------------------------------------------------------------\n",
       "const                     11.2272      0.960     11.693      0.000       9.345      13.109\n",
       "TargetAge                  0.0169      0.002      8.518      0.000       0.013       0.021\n",
       "TargetAge2             -4.637e-05   1.42e-05     -3.270      0.001   -7.42e-05   -1.86e-05\n",
       "nacis_code_cat[11, 21)     0.2525      1.099      0.230      0.818      -1.902       2.407\n",
       "nacis_code_cat[21, 22)     4.4811      1.087      4.123      0.000       2.351       6.612\n",
       "nacis_code_cat[22, 23)    -0.2470      1.191     -0.207      0.836      -2.583       2.089\n",
       "nacis_code_cat[23, 24)     0.2224      1.001      0.222      0.824      -1.740       2.185\n",
       "nacis_code_cat[24, 31)  2.674e-14   2.24e-14      1.192      0.233   -1.72e-14    7.07e-14\n",
       "nacis_code_cat[31, 35)     0.6615      0.999      0.662      0.508      -1.298       2.620\n",
       "nacis_code_cat[35, 42) -2.324e-15   9.11e-15     -0.255      0.799   -2.02e-14    1.55e-14\n",
       "nacis_code_cat[42, 44)    -0.9709      1.087     -0.893      0.372      -3.101       1.160\n",
       "nacis_code_cat[44, 46)    -0.7390      0.998     -0.740      0.459      -2.696       1.218\n",
       "nacis_code_cat[46, 48) -3.898e-15    2.2e-14     -0.177      0.859    -4.7e-14    3.92e-14\n",
       "nacis_code_cat[48, 50)    -0.3017      1.002     -0.301      0.763      -2.265       1.662\n",
       "nacis_code_cat[50, 51) -1.141e-15   2.37e-15     -0.482      0.630   -5.78e-15     3.5e-15\n",
       "nacis_code_cat[51, 52)     2.3662      1.005      2.355      0.019       0.397       4.336\n",
       "nacis_code_cat[52, 53)     0.7913      1.007      0.786      0.432      -1.182       2.765\n",
       "nacis_code_cat[53, 54)    -0.1870      1.003     -0.187      0.852      -2.152       1.778\n",
       "nacis_code_cat[54, 55)     0.8193      1.001      0.819      0.413      -1.142       2.781\n",
       "nacis_code_cat[55, 56)     6.4695      1.137      5.690      0.000       4.241       8.698\n",
       "nacis_code_cat[56, 61)    -0.5017      0.999     -0.502      0.615      -2.460       1.456\n",
       "nacis_code_cat[61, 62)    -0.9277      1.011     -0.918      0.359      -2.910       1.054\n",
       "nacis_code_cat[62, 71)    -0.1758      1.001     -0.176      0.861      -2.138       1.787\n",
       "nacis_code_cat[71, 72)    -0.8380      1.007     -0.833      0.405      -2.811       1.135\n",
       "nacis_code_cat[72, 81)    -1.2648      0.998     -1.267      0.205      -3.221       0.692\n",
       "nacis_code_cat[81, 83)    -1.3000      0.999     -1.302      0.193      -3.258       0.657\n",
       "nacis_code_cat[83, 92)   2.13e-15   3.81e-15      0.560      0.576   -5.33e-15    9.59e-15\n",
       "year1990                -3.83e-15   5.19e-15     -0.737      0.461    -1.4e-14    6.35e-15\n",
       "year1991                1.658e-15   1.74e-15      0.952      0.341   -1.76e-15    5.07e-15\n",
       "year1992                3.969e-16   6.46e-15      0.061      0.951   -1.23e-14    1.31e-14\n",
       "year1993                  -1.2132      1.173     -1.035      0.301      -3.512       1.085\n",
       "year1994                  -0.2682      0.630     -0.426      0.670      -1.503       0.966\n",
       "year1995                  -0.1314      0.466     -0.282      0.778      -1.044       0.781\n",
       "year1996                   0.0906      0.333      0.272      0.786      -0.562       0.744\n",
       "year1997                   0.1838      0.261      0.705      0.481      -0.327       0.695\n",
       "year1998                   0.4123      0.184      2.237      0.025       0.051       0.774\n",
       "year1999                   0.2481      0.169      1.466      0.143      -0.084       0.580\n",
       "year2000                   0.6193      0.172      3.609      0.000       0.283       0.956\n",
       "year2001                   0.0279      0.165      0.169      0.866      -0.295       0.351\n",
       "year2002                   0.3575      0.148      2.420      0.016       0.068       0.647\n",
       "year2003                   0.2876      0.149      1.924      0.054      -0.005       0.581\n",
       "year2004                   0.3198      0.136      2.360      0.018       0.054       0.585\n",
       "year2005                   0.8324      0.131      6.355      0.000       0.576       1.089\n",
       "year2006                   0.7937      0.120      6.637      0.000       0.559       1.028\n",
       "year2007                   0.9219      0.112      8.217      0.000       0.702       1.142\n",
       "year2008                   0.8160      0.106      7.723      0.000       0.609       1.023\n",
       "year2009                   0.5219      0.102      5.126      0.000       0.322       0.721\n",
       "year2010                   0.5082      0.098      5.175      0.000       0.316       0.701\n",
       "year2011                   0.6108      0.098      6.210      0.000       0.418       0.804\n",
       "year2012                   0.8147      0.097      8.383      0.000       0.624       1.005\n",
       "year2013                   0.8809      0.096      9.203      0.000       0.693       1.069\n",
       "year2014                   1.0353      0.093     11.126      0.000       0.853       1.218\n",
       "year2015                   1.0461      0.092     11.350      0.000       0.865       1.227\n",
       "year2016                   0.8004      0.092      8.740      0.000       0.621       0.980\n",
       "year2017                   0.7109      0.118      6.034      0.000       0.480       0.942\n",
       "==============================================================================\n",
       "Omnibus:                      842.453   Durbin-Watson:                   1.647\n",
       "Prob(Omnibus):                  0.000   Jarque-Bera (JB):             4493.515\n",
       "Skew:                           0.321   Prob(JB):                         0.00\n",
       "Kurtosis:                       6.481   Cond. No.                     1.03e+16\n",
       "==============================================================================\n",
       "\n",
       "Warnings:\n",
       "[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.\n",
       "[2] The smallest eigenvalue is 3.1e-22. This might indicate that there are\n",
       "strong multicollinearity problems or that the design matrix is singular.\n",
       "\"\"\""
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dummies1 = pd.get_dummies(data['naics_code_cat']).rename(columns=lambda x: 'nacis_code_cat' + str(x))\n",
    "dummies2 = pd.get_dummies(data['year']).rename(columns=lambda x: 'year' + str(x))\n",
    "X = pd.concat([data[['TargetAge','TargetAge2']],dummies1,dummies2],axis=1)\n",
    "Y=np.log(data.TotalIntangiblesPPA)\n",
    "X = sm.add_constant(X)\n",
    "selection=(pd.notnull(data.TargetAge)) &(pd.notnull(data.TotalIntangiblesPPA) ) & (data.TotalIntangiblesPPA>0)& ~(data.TargetStructureAlt=='C Corporation')\n",
    "model11 = sm.OLS(Y[selection], X[selection]).fit()\n",
    "model11.summary()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Intangible valuations multiple of Net Income\n",
    "We divide the PPA allocated to Intangible assets by Net Income in the previous year and then group by legal form and report the median. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>IntangibleToNetIncome</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>LLC</th>\n",
       "      <td>1.268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Partnership</th>\n",
       "      <td>1.522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>S Corporation</th>\n",
       "      <td>1.596</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sole Proprietorship</th>\n",
       "      <td>1.228</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     IntangibleToNetIncome\n",
       "TargetStructureAlt                        \n",
       "LLC                 1.268                 \n",
       "Partnership         1.522                 \n",
       "S Corporation       1.596                 \n",
       "Sole Proprietorship 1.228                 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "selPassThru=data['TargetStructureAlt'].isin(['S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "data['IntangibleToNetIncome']=data['TotalIntangiblesPPA']/data['NetIncome']\n",
    "temp=data[selPassThru][['IntangibleToNetIncome']].groupby(data[selPassThru].TargetStructureAlt).median()\n",
    "display(temp)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More details for Real Estate\n",
    "\n",
    "We show below that most of the transactions in \"real estate\" sector in Pratts have little or no long-term liabilities"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>TotalAssetsPPA</th>\n",
       "      <th>TotalLiabilitiesPPA</th>\n",
       "      <th>DebtAssumed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TargetStructureAlt</th>\n",
       "      <th>year</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">Partnership</th>\n",
       "      <th>1999</th>\n",
       "      <td>150000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>55000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>275000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>1047400.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"19\" valign=\"top\">S Corporation</th>\n",
       "      <th>1996</th>\n",
       "      <td>325000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1998</th>\n",
       "      <td>109400.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1999</th>\n",
       "      <td>75000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>1330000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2003</th>\n",
       "      <td>750000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2004</th>\n",
       "      <td>299000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2005</th>\n",
       "      <td>125000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2006</th>\n",
       "      <td>18108580.000</td>\n",
       "      <td>1817000.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>32440000.000</td>\n",
       "      <td>6710000.000</td>\n",
       "      <td>3426000.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>3325000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>2317000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>3263000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>4579287.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>4735000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>11658749.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>9452500.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>2693511.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>7507992.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2017</th>\n",
       "      <td>3381000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"13\" valign=\"top\">Sole Proprietorship</th>\n",
       "      <th>1997</th>\n",
       "      <td>365000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2000</th>\n",
       "      <td>240500.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2001</th>\n",
       "      <td>360000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2007</th>\n",
       "      <td>550000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2008</th>\n",
       "      <td>200000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2009</th>\n",
       "      <td>105000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010</th>\n",
       "      <td>100000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011</th>\n",
       "      <td>300000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2012</th>\n",
       "      <td>90000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2013</th>\n",
       "      <td>145000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2014</th>\n",
       "      <td>1750000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2015</th>\n",
       "      <td>250000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2016</th>\n",
       "      <td>445000.000</td>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          TotalAssetsPPA  TotalLiabilitiesPPA  DebtAssumed\n",
       "TargetStructureAlt  year                                                  \n",
       "Partnership         1999 150000.000      0.000                0.000       \n",
       "                    2009 55000.000       0.000                0.000       \n",
       "                    2010 275000.000      0.000                0.000       \n",
       "                    2013 1047400.000     0.000                0.000       \n",
       "S Corporation       1996 325000.000      0.000                0.000       \n",
       "                    1998 109400.000      0.000                0.000       \n",
       "                    1999 75000.000       0.000                0.000       \n",
       "                    2001 1330000.000     0.000                0.000       \n",
       "                    2003 750000.000      0.000                0.000       \n",
       "                    2004 299000.000      0.000                0.000       \n",
       "                    2005 125000.000      0.000                0.000       \n",
       "                    2006 18108580.000    1817000.000          0.000       \n",
       "                    2007 32440000.000    6710000.000          3426000.000 \n",
       "                    2008 3325000.000     0.000                0.000       \n",
       "                    2009 2317000.000     0.000                0.000       \n",
       "                    2010 3263000.000     0.000                0.000       \n",
       "                    2011 4579287.000     0.000                0.000       \n",
       "                    2012 4735000.000     0.000                0.000       \n",
       "                    2013 11658749.000    0.000                0.000       \n",
       "                    2014 9452500.000     0.000                0.000       \n",
       "                    2015 2693511.000     0.000                0.000       \n",
       "                    2016 7507992.000     0.000                0.000       \n",
       "                    2017 3381000.000     0.000                0.000       \n",
       "Sole Proprietorship 1997 365000.000      0.000                0.000       \n",
       "                    2000 240500.000      0.000                0.000       \n",
       "                    2001 360000.000      0.000                0.000       \n",
       "                    2007 550000.000      0.000                0.000       \n",
       "                    2008 200000.000      0.000                0.000       \n",
       "                    2009 105000.000      0.000                0.000       \n",
       "                    2010 100000.000      0.000                0.000       \n",
       "                    2011 300000.000      0.000                0.000       \n",
       "                    2012 90000.000       0.000                0.000       \n",
       "                    2013 145000.000      0.000                0.000       \n",
       "                    2014 1750000.000     0.000                0.000       \n",
       "                    2015 250000.000      0.000                0.000       \n",
       "                    2016 445000.000      0.000                0.000       "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TotalLiabilityIntensityPPA</th>\n",
       "      <th>DebtAssumedIntensity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>94.000</td>\n",
       "      <td>148.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.004</td>\n",
       "      <td>0.001</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.026</td>\n",
       "      <td>0.009</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.000</td>\n",
       "      <td>0.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>0.226</td>\n",
       "      <td>0.115</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       TotalLiabilityIntensityPPA  DebtAssumedIntensity\n",
       "count 94.000                      148.000              \n",
       "mean  0.004                       0.001                \n",
       "std   0.026                       0.009                \n",
       "min   0.000                       0.000                \n",
       "25%   0.000                       0.000                \n",
       "50%   0.000                       0.000                \n",
       "75%   0.000                       0.000                \n",
       "max   0.226                       0.115                "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "sel=selection&sel&data.TargetStructurePassThru &(data.naics_label=='Real Estate and Rental and Leasing')\n",
    "data[sel][['TotalAssetsPPA','TotalLiabilitiesPPA','DebtAssumed']].groupby([data.TargetStructureAlt,data.year]).sum()\n",
    "pd.set_option('display.max_rows', None)\n",
    "data['TotalLiabilityIntensityPPA']=data['TotalLiabilitiesPPA']/data['TotalAssetsPPA']\n",
    "data['DebtAssumedIntensity']=data['DebtAssumed']/data['TotalAssetsPPA']\n",
    "data[sel][['TotalLiabilityIntensityPPA','DebtAssumedIntensity']].describe()\n",
    "#data[sel][['TotalLiabilityIntensityPPA','IntanglibleIntensityPPA','DebtAssumed','TotalAssetsPPA','TotalLiabilitiesPPA','TargetBusinessDescription','TargetStructureAlt']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## More Details on Transport and Warehousing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "sel=selection&sel&data.TargetStructurePassThruLLC &(data.naics_label=='Transportation and Warehousing')\n",
    "columnsPPA = [s for s in data.columns if \"PPA\" in s]\n",
    "pd.set_option('display.max_columns', None)\n",
    "#data[sel][columnsPPA].groupby([data.TargetStructureAlt]).describe().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">IntanglibleIntensityPPA</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IsRoute</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>False</th>\n",
       "      <td>233.000</td>\n",
       "      <td>60.241</td>\n",
       "      <td>26.351</td>\n",
       "      <td>0.000</td>\n",
       "      <td>42.857</td>\n",
       "      <td>63.706</td>\n",
       "      <td>80.935</td>\n",
       "      <td>100.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>True</th>\n",
       "      <td>94.000</td>\n",
       "      <td>81.083</td>\n",
       "      <td>14.717</td>\n",
       "      <td>19.231</td>\n",
       "      <td>76.261</td>\n",
       "      <td>82.500</td>\n",
       "      <td>90.567</td>\n",
       "      <td>100.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        IntanglibleIntensityPPA                                                  \n",
       "                          count   mean    std    min    25%    50%    75%     max\n",
       "IsRoute                                                                          \n",
       "False   233.000                 60.241 26.351 0.000  42.857 63.706 80.935 100.000\n",
       "True    94.000                  81.083 14.717 19.231 76.261 82.500 90.567 100.000"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['IsRoute']=False\n",
    "sel2=data['TargetBusinessDescription'].str.contains(\"route\", na=False, case=False)\n",
    "data.loc[sel2,'IsRoute']=True\n",
    "data[sel][['IntanglibleIntensityPPA']].groupby(data[sel].IsRoute).describe()\n",
    "#data['IsLimo']=False\n",
    "#sel2=data['TargetBusinessDescription'].str.contains(\"limo\", na=False, case=False)\n",
    "#data.loc[sel2,'IsLimo']=True\n",
    "#data[sel][['IntanglibleIntensityPPA']].groupby(data[sel].IsLimo).describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retirement Analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"8\" halign=\"left\">IntanglibleIntensityPPA</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>count</th>\n",
       "      <th>mean</th>\n",
       "      <th>std</th>\n",
       "      <th>min</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ReasonForSale</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Not Provided</th>\n",
       "      <td>9831.000</td>\n",
       "      <td>57.332</td>\n",
       "      <td>32.287</td>\n",
       "      <td>-51.829</td>\n",
       "      <td>32.835</td>\n",
       "      <td>62.963</td>\n",
       "      <td>84.671</td>\n",
       "      <td>631.818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Others</th>\n",
       "      <td>20.000</td>\n",
       "      <td>52.076</td>\n",
       "      <td>27.240</td>\n",
       "      <td>5.682</td>\n",
       "      <td>31.667</td>\n",
       "      <td>57.157</td>\n",
       "      <td>79.895</td>\n",
       "      <td>86.792</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>health</th>\n",
       "      <td>238.000</td>\n",
       "      <td>62.107</td>\n",
       "      <td>31.552</td>\n",
       "      <td>0.000</td>\n",
       "      <td>39.602</td>\n",
       "      <td>71.202</td>\n",
       "      <td>88.056</td>\n",
       "      <td>101.651</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other interests/opportunities</th>\n",
       "      <td>465.000</td>\n",
       "      <td>60.809</td>\n",
       "      <td>31.504</td>\n",
       "      <td>-51.386</td>\n",
       "      <td>38.000</td>\n",
       "      <td>67.781</td>\n",
       "      <td>86.304</td>\n",
       "      <td>259.291</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>relocation</th>\n",
       "      <td>146.000</td>\n",
       "      <td>63.268</td>\n",
       "      <td>26.133</td>\n",
       "      <td>4.286</td>\n",
       "      <td>42.527</td>\n",
       "      <td>67.296</td>\n",
       "      <td>85.631</td>\n",
       "      <td>100.000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>retirement</th>\n",
       "      <td>154.000</td>\n",
       "      <td>52.155</td>\n",
       "      <td>28.791</td>\n",
       "      <td>0.000</td>\n",
       "      <td>28.242</td>\n",
       "      <td>55.412</td>\n",
       "      <td>73.187</td>\n",
       "      <td>100.000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                              IntanglibleIntensityPPA                                                   \n",
       "                                                count   mean    std     min    25%    50%    75%     max\n",
       "ReasonForSale                                                                                           \n",
       "Not Provided                  9831.000                57.332 32.287 -51.829 32.835 62.963 84.671 631.818\n",
       "Others                        20.000                  52.076 27.240 5.682   31.667 57.157 79.895 86.792 \n",
       "health                        238.000                 62.107 31.552 0.000   39.602 71.202 88.056 101.651\n",
       "other interests/opportunities 465.000                 60.809 31.504 -51.386 38.000 67.781 86.304 259.291\n",
       "relocation                    146.000                 63.268 26.133 4.286   42.527 67.296 85.631 100.000\n",
       "retirement                    154.000                 52.155 28.791 0.000   28.242 55.412 73.187 100.000"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>wgt_sales</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ReasonForSale</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Not Provided</th>\n",
       "      <td>53.787</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Others</th>\n",
       "      <td>0.005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>health</th>\n",
       "      <td>13.051</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>other interests/opportunities</th>\n",
       "      <td>29.199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>relocation</th>\n",
       "      <td>0.117</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>retirement</th>\n",
       "      <td>3.841</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                               wgt_sales\n",
       "ReasonForSale                           \n",
       "Not Provided                  53.787    \n",
       "Others                        0.005     \n",
       "health                        13.051    \n",
       "other interests/opportunities 29.199    \n",
       "relocation                    0.117     \n",
       "retirement                    3.841     "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Notes']=data['Notes'].str.lower()\n",
    "data['ReasonForSale']='Not Provided'\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"reason \", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='Others'\n",
    "\n",
    "selection=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "sel=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "sel=selection&sel\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"retir\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='retirement'\n",
    "\n",
    "sel2=(data['Notes'].str.contains(\"relocat\", na=False, case=False)|data['Notes'].str.contains(\"moving\", na=False, case=False))\n",
    "data.loc[sel2,'ReasonForSale']='relocation'\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"out-of -state\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='relocation'\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"out of state\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='relocation'\n",
    "\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"health\", na=False, case=False)| data['Notes'].str.contains(\"illness\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='health'\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"burnout\", na=False, case=False)|data['Notes'].str.contains(\"bur nout\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='burnout'\n",
    "\n",
    "sel2=(data['Notes'].str.contains(\"interest\", na=False, case=False))&data['Notes'].str.contains(\"other\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "\n",
    "sel2=(data['Notes'].str.contains(\"opportunit\", na=False, case=False) )&data['Notes'].str.contains(\"other\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "\n",
    "sel2=(data['Notes'].str.contains(\"opportunit\", na=False, case=False) )&data['Notes'].str.contains(\"new\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"divorce\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='divorce'\n",
    "#sel2=data['Notes'].str.contains(\"family\", na=False, case=False)& data['Notes'].str.contains(\"reason\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='family'\n",
    "\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"dissolution\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='partnership dissolution'\n",
    "\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"partnership breakup\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='partnership dissolution'\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"death\", na=False, case=False)| data['Notes'].str.contains(\"passed away\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='health'\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"died\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='health'\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"deceased\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='health'\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"career\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"a change\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"school\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='going back to school'\n",
    "\n",
    "\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"downsizing\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='downsizing'\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"other business investments\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"new\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='other interests/opportunities'\n",
    "\n",
    "\n",
    "sel2=data['Notes'].str.contains(\"ill\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "data.loc[sel2,'ReasonForSale']='health'\n",
    "\n",
    "#sel2=data['Notes'].str.contains(\"personal time\", na=False, case=False) & data['Notes'].str.contains(\"reason for\", na=False, case=False)\n",
    "#data.loc[sel2,'ReasonForSale']='more personal time'\n",
    "\n",
    "\n",
    "\n",
    "data[sel][['IntanglibleIntensityPPA']].groupby([data[sel].ReasonForSale]).describe()\n",
    "\n",
    "data[sel][['wgt_sales']].groupby([data[sel].ReasonForSale]).sum()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Category"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count   20.000\n",
       "mean    52.076\n",
       "std     27.240\n",
       "min     5.682 \n",
       "25%     31.667\n",
       "50%     57.157\n",
       "75%     79.895\n",
       "max     86.792\n",
       "Name: IntanglibleIntensityPPA, dtype: float64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#sel1\n",
    "sel1=(data.IntanglibleIntensityPPA.notnull())&~(np.isinf(data.IntanglibleIntensityPPA) )\n",
    "sel3=data['TargetStructureAlt'].isin(['C Corporation','S Corporation', 'Sole Proprietorship', 'Partnership','LLC'])\n",
    "sel2=data['ReasonForSale']=='Others'\n",
    "data[sel1&sel2&sel3]['IntanglibleIntensityPPA'].describe()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2153     net income includes other income of 1399.  the reason for selling was burnout.                                                                                     \n",
       "6360     ebt includes other income of 4739.  the reason for selling was to go into prototype work.                                                                          \n",
       "12969    reason for selling was divorce                                                                                                                                     \n",
       "13011    reason for sale: leaving business.                                                                                                                                 \n",
       "13032    ebt: includes other income of 31.  reason for sale: concentrate on second location.                                                                                \n",
       "13064    reason for sale: partnership dissolution.  payroll averages over 15/hr.  for the 96 hrs/wk. they are open.  by lowering cost seller feels business could do better.\n",
       "13200    reason for sale: one partner has moved out of area. bldg. 1800 ft.2.                                                                                               \n",
       "13211    reason for sale: tired of pizza business.                                                                                                                          \n",
       "13268    net income includes rental income of 9756 a vendor fee of 9 and a tax penalty of 3869.  the reason for selling was to start a family.                              \n",
       "13665    reason for sale was to concentrate on other business.                                                                                                              \n",
       "13778    ebt includes other income of 30841.  the reason for selling was a non-pharmacist son inherited the business.                                                       \n",
       "14202    ebt includes vendor fee income of 1064.  the reason for sale was burnout.                                                                                          \n",
       "16862    the reason for selling was a partnership dissolution.                                                                                                              \n",
       "17086    reason for sale: concentrate on developing retail outlets.                                                                                                         \n",
       "17756    the reason for sale was divorce.                                                                                                                                   \n",
       "17770    reason for sale: spend more time with family.                                                                                                                      \n",
       "21971    the reason for selling was consolidation of businesses.                                                                                                            \n",
       "23121    the reason for selling was divorce.                                                                                                                                \n",
       "23122    the reason for selling was divorce.                                                                                                                                \n",
       "24765    reason for sale was a conflict of interest.                                                                                                                        \n",
       "Name: Notes, dtype: object"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[sel1&sel2&sel3]['Notes']\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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
}