ch21-ownership-management-quality/ch21-wms-01-dataprep.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Prepared for Gabor's Data Analysis\n",
    "\n",
    "### Data Analysis for Business, Economics, and Policy\n",
    "by Gabor Bekes and  Gabor Kezdi\n",
    " \n",
    "Cambridge University Press 2021\n",
    "\n",
    "**[gabors-data-analysis.com ](https://gabors-data-analysis.com/)**\n",
    "\n",
    " License: Free to share, modify and use for educational purposes. \n",
    " Not to be used for commercial purposes.\n",
    "\n",
    "### CHAPTER 21\n",
    "**CH20A Founder/family ownership and quality of management**\n",
    "\n",
    " using the wms-management dataset\n",
    " \n",
    " version 1.0 2021-05-05"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import warnings\n",
    "\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from plotnine import *\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Current script folder\n",
    "current_path = os.getcwd()\n",
    "dirname = current_path.split(\"da_case_studies\")[0]\n",
    "\n",
    "# location folders\n",
    "data_in = dirname + \"da_data_repo/wms-management-survey/clean/\"\n",
    "data_out = dirname + \"da_case_studies/ch21-ownership-management-quality/\"\n",
    "output = dirname + \"da_case_studies/ch21-ownership-management-quality/output/\"\n",
    "\n",
    "func = dirname + \"da_case_studies/ch00-tech-prep/\"\n",
    "sys.path.append(func)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Import the prewritten helper functions\n",
    "from py_helper_functions import *\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PART I\n",
    "\n",
    "### Data prep"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(data_in + \"wms_da_textbook-xsec.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ownership: define founder/family owned and drop ownership that's missing or not relevant\n",
    "# Ownership\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Freq</th>\n",
       "      <th>Percent</th>\n",
       "      <th>Cum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ownership</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Dispersed Shareholders</th>\n",
       "      <td>3772</td>\n",
       "      <td>26.42</td>\n",
       "      <td>26.42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Family owned, CEO unknown</th>\n",
       "      <td>176</td>\n",
       "      <td>1.23</td>\n",
       "      <td>27.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Family owned, external CEO</th>\n",
       "      <td>446</td>\n",
       "      <td>3.12</td>\n",
       "      <td>30.78</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Family owned, family CEO</th>\n",
       "      <td>2370</td>\n",
       "      <td>16.60</td>\n",
       "      <td>47.38</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Founder owned, CEO unknown</th>\n",
       "      <td>124</td>\n",
       "      <td>0.87</td>\n",
       "      <td>48.25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Founder owned, external CEO</th>\n",
       "      <td>339</td>\n",
       "      <td>2.37</td>\n",
       "      <td>50.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Founder owned, founder CEO</th>\n",
       "      <td>2427</td>\n",
       "      <td>17.00</td>\n",
       "      <td>67.62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Government</th>\n",
       "      <td>261</td>\n",
       "      <td>1.83</td>\n",
       "      <td>69.45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Other</th>\n",
       "      <td>968</td>\n",
       "      <td>6.78</td>\n",
       "      <td>76.23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private Equity/Venture Capital</th>\n",
       "      <td>492</td>\n",
       "      <td>3.45</td>\n",
       "      <td>79.67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Private Individuals</th>\n",
       "      <td>2885</td>\n",
       "      <td>20.21</td>\n",
       "      <td>99.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>17</td>\n",
       "      <td>0.12</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                Freq  Percent     Cum\n",
       "ownership                                            \n",
       "Dispersed Shareholders          3772    26.42   26.42\n",
       "Family owned, CEO unknown        176     1.23   27.65\n",
       "Family owned, external CEO       446     3.12   30.78\n",
       "Family owned, family CEO        2370    16.60   47.38\n",
       "Founder owned, CEO unknown       124     0.87   48.25\n",
       "Founder owned, external CEO      339     2.37   50.62\n",
       "Founder owned, founder CEO      2427    17.00   67.62\n",
       "Government                       261     1.83   69.45\n",
       "Other                            968     6.78   76.23\n",
       "Private Equity/Venture Capital   492     3.45   79.67\n",
       "Private Individuals             2885    20.21   99.88\n",
       "NaN                               17     0.12  100.00"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ownership = (\n",
    "    data.groupby(\"ownership\", dropna=False)\n",
    "    .agg(Freq=(\"firmid\", \"count\"))\n",
    "    .assign(Percent=lambda x: 100 * x / x.sum())\n",
    ")\n",
    "ownership[\"Cum\"] = ownership[\"Percent\"].cumsum()\n",
    "ownership.round(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define foundfam owned\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"foundfam_owned\"] = np.where(\n",
    "    (data[\"ownership\"] == \"Family owned, external CEO\")\n",
    "    | (data[\"ownership\"] == \"Family owned, family CEO\")\n",
    "    | (data[\"ownership\"] == \"Family owned, CEO unknown\")\n",
    "    | (data[\"ownership\"] == \"Founder owned, external CEO\")\n",
    "    | (data[\"ownership\"] == \"Founder owned, CEO unknown\")\n",
    "    | (data[\"ownership\"] == \"Founder owned, founder CEO\"),\n",
    "    1,\n",
    "    np.where(data[\"ownership\"].isnull(), np.nan, 0),\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Foundfam owned\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>Freq</th>\n",
       "      <th>Percent</th>\n",
       "      <th>Cum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>foundfam_owned</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0.0</th>\n",
       "      <td>8378</td>\n",
       "      <td>58.68</td>\n",
       "      <td>58.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1.0</th>\n",
       "      <td>5882</td>\n",
       "      <td>41.20</td>\n",
       "      <td>99.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>17</td>\n",
       "      <td>0.12</td>\n",
       "      <td>100.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Freq  Percent     Cum\n",
       "foundfam_owned                       \n",
       "0.0             8378    58.68   58.68\n",
       "1.0             5882    41.20   99.88\n",
       "NaN               17     0.12  100.00"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ownership = (\n",
    "    data.groupby(\"foundfam_owned\", dropna=False)\n",
    "    .agg(Freq=(\"firmid\", \"count\"))\n",
    "    .assign(Percent=lambda x: 100 * x / x.sum())\n",
    ")\n",
    "ownership[\"Cum\"] = ownership[\"Percent\"].cumsum()\n",
    "ownership.round(2)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Proportion of managers/non-managers with a college degree\n",
    "# need correction: -44 means do not know, -99 means missing\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"degree_m\"] = data[\"degree_m\"] / 100\n",
    "data[\"degree_nm\"] = data[\"degree_nm\"] / 100\n",
    "data.loc[data[\"degree_m\"] < 0, \"degree_m\"] = None\n",
    "data.loc[data[\"degree_nm\"] < 0, \"degree_nm\"] = None\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Generate bins from degree_nm\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>0%</th>\n",
       "      <th>10%</th>\n",
       "      <th>25%</th>\n",
       "      <th>50%</th>\n",
       "      <th>75%</th>\n",
       "      <th>90%</th>\n",
       "      <th>100%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>degree_nm</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.05</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            0%  10%  25%   50%   75%  90%  100%\n",
       "degree_nm  0.0  0.0  0.0  0.05  0.15  0.3   1.0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.DataFrame(\n",
    "    data[\"degree_nm\"].describe(percentiles=[0, 0.1, 0.25, 0.5, 0.75, 0.9, 1])\n",
    ").T.iloc[:, 4:-1]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "data[\"degree_nm_bins\"] = pd.cut(\n",
    "    data[\"degree_nm\"], bins=[0, 0.001, 0.05, 0.20, 1.01], right=False\n",
    ")\n",
    "data[\"degree_nm_sq\"] = data[\"degree_nm\"] ** 2\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>min</th>\n",
       "      <th>max</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>degree_nm_bins</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>[0.0, 0.001)</th>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>3974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[0.001, 0.05)</th>\n",
       "      <td>0.01</td>\n",
       "      <td>0.04</td>\n",
       "      <td>1939</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[0.05, 0.2)</th>\n",
       "      <td>0.05</td>\n",
       "      <td>0.19</td>\n",
       "      <td>4207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>[0.2, 1.01)</th>\n",
       "      <td>0.20</td>\n",
       "      <td>1.00</td>\n",
       "      <td>2679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NaN</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 min   max     n\n",
       "degree_nm_bins                  \n",
       "[0.0, 0.001)    0.00  0.00  3974\n",
       "[0.001, 0.05)   0.01  0.04  1939\n",
       "[0.05, 0.2)     0.05  0.19  4207\n",
       "[0.2, 1.01)     0.20  1.00  2679\n",
       "NaN              NaN   NaN     0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.groupby(\"degree_nm_bins\", dropna=False).agg(\n",
    "    min=(\"degree_nm\", \"min\"), max=(\"degree_nm\", max), n=(\"degree_nm\", \"count\")\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Take log of employment\n",
    "data[\"lnemp\"] = np.log(data[\"emp_firm\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Competition\n",
    "data[\"competition\"] = data[\"competition\"].str.strip()\n",
    "data[\"compet_weak\"] = (data[\"competition\"] == \"0 competitors\") | (\n",
    "    data[\"competition\"] == \"1-4 competitors\"\n",
    ")\n",
    "data[\"compet_moder\"] = data[\"competition\"] == \"5-9 competitors\"\n",
    "data[\"compet_strong\"] = data[\"competition\"] == \"10+ competitors\"\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Industry in 2 digits\n",
    "\n",
    "industry_names = [\n",
    "    \"food\",\n",
    "    \"tobacco\",\n",
    "    \"textile\",\n",
    "    \"apparel\",\n",
    "    \"lumber\",\n",
    "    \"furniture\",\n",
    "    \"paper\",\n",
    "    \"printing\",\n",
    "    \"chemical\",\n",
    "    \"petrol\",\n",
    "    \"rubber\",\n",
    "    \"leather\",\n",
    "    \"glass\",\n",
    "    \"primary_metal\",\n",
    "    \"fabricated_metal\",\n",
    "    \"ind_machinery\",\n",
    "    \"electronic\",\n",
    "    \"transport\",\n",
    "    \"instrument\",\n",
    "    \"misc_manuf\",\n",
    "]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "_, index = pd.factorize(data[\"sic\"], sort=True)\n",
    "dic = dict(zip(index, industry_names))\n",
    "data[\"industry\"] = data.loc[data[\"sic\"].notnull(), \"sic\"].apply(lambda x: dic[x])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Country as factor\n",
    "data[\"countrycode\"] = data[\"cty\"]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# age\n",
    "data[\"age_unknown\"] = data[\"firmage\"].isnull()\n",
    "data[\"age_young\"] = data[\"firmage\"] < 30\n",
    "data[\"age_old\"] = data[\"firmage\"] > 80\n",
    "data[\"age_mid\"] = (\n",
    "    (data[\"age_young\"] == False)\n",
    "    & (data[\"age_old\"] == False)\n",
    "    & (data[\"age_unknown\"] == False)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(14277, 258)"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Dispersed Shareholders            3772\n",
       "Private Individuals               2885\n",
       "Founder owned, founder CEO        2427\n",
       "Family owned, family CEO          2370\n",
       "Other                              968\n",
       "Private Equity/Venture Capital     492\n",
       "Family owned, external CEO         446\n",
       "Founder owned, external CEO        339\n",
       "Government                         261\n",
       "Family owned, CEO unknown          176\n",
       "Founder owned, CEO unknown         124\n",
       "Name: ownership, dtype: int64"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.ownership.value_counts()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SAMPLE SELECTION\n",
    " Keep observations with:\n",
    "     Non-employee/Research/Gov/Other type of ownership\n",
    "     non-missing variables "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.loc[\n",
    "    (data[\"ownership\"] != \"Government\") & (data[\"ownership\"] != \"Other\")\n",
    "].dropna(subset=[\"ownership\"])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.dropna(\n",
    "    subset=[\n",
    "        \"ownership\",\n",
    "        \"management\",\n",
    "        \"foundfam_owned\",\n",
    "        \"degree_nm\",\n",
    "        \"competition\",\n",
    "        \"industry\",\n",
    "        \"countrycode\",\n",
    "        \"lnemp\",\n",
    "    ]\n",
    ")\n"
   ]
  },
  {
   "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 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>std</th>\n",
       "      <th>min</th>\n",
       "      <th>1%</th>\n",
       "      <th>50%</th>\n",
       "      <th>99%</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>emp_firm</th>\n",
       "      <td>11672.0</td>\n",
       "      <td>797.526474</td>\n",
       "      <td>3347.958543</td>\n",
       "      <td>1.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>260.0</td>\n",
       "      <td>7000.0</td>\n",
       "      <td>176000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            count        mean          std  min    1%    50%     99%       max\n",
       "emp_firm  11672.0  797.526474  3347.958543  1.0  50.0  260.0  7000.0  176000.0"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Summary of num. of employment\n",
    "pd.DataFrame(data[\"emp_firm\"].describe(percentiles=[0.01, 0.5, 0.99])).T\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "113"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Drop tiny and large firms\n",
    "\n",
    "data.loc[data[\"emp_firm\"] < 50].shape[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "142"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.loc[data[\"emp_firm\"] > 5000].shape[0]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = data.loc[(data[\"emp_firm\"] > 50) | (data[\"emp_firm\"] < 5000)]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(data_out + \"wms_da_textbook-work.csv\")\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.8.10 ('da_case_studies-TgTp0_0o')",
   "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.8.10"
  },
  "vscode": {
   "interpreter": {
    "hash": "c01754e8627d0ea60fdf9a984fbf743943cf4db47884120dd04bfc512143b077"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}