3_employee turnover.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Employee Churn"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We got employee data from a few companies. We have data about all employees who joined from 2011/01/24 to 2015/12/13. For each employee, we also know if they are still at the company as of 2015/12/13 or they have quit. Beside that, we have general info about the employee, such as avg salary during her tenure, dept, and yrs of experience.\n",
    "\n",
    "As said above, the goal is to predict employee retention and understand its main drivers. Specifically, you should:\n",
    "\n",
    "\n",
    "1. Assume, for each company, that the headcount starts from zero on 2011/01/23. Estimate employee headcount, for each company on each day, from 2011/01/24 to 2015/12/13. That is, if by 2012/03/02 2000 people have joined company 1 and 1000 of them have already quit, then company headcount on 2012/03/02 for company 1 would be 1000. You should create a table with 3 columns: day, employee_headcount, company_id\n",
    "\n",
    "\n",
    "- What are the main factors that drive employee churn? Do they make sense? Explain your findings\n",
    "\n",
    "\n",
    "- If you could add to this data set just one variable that could help explain employee churn, what would that be?\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Checking"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 286,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   employee_id  company_id              dept  seniority    salary   join_date   quit_date\n",
      "0      13021.0           7  customer_service         28   89000.0  2014-03-24  2015-10-30\n",
      "1     825355.0           7         marketing         20  183000.0  2013-04-29  2014-04-04\n",
      "2     927315.0           4         marketing         14  101000.0  2014-10-13         NaN\n",
      "3     662910.0           7  customer_service         20  115000.0  2012-05-14  2013-06-07\n",
      "4     256971.0           2      data_science         23  276000.0  2011-10-17  2014-08-22\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pandas.set_option('display.max_columns', 10)\n",
    "pandas.set_option('display.width', 350)\n",
    "  \n",
    "#read from google drive\n",
    "data=pandas.read_csv(\".\\employee_retention.csv\")\n",
    "  \n",
    "print(data.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 287,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(24702, 7)"
      ]
     },
     "execution_count": 287,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# explore data\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([ 7,  4,  2,  9,  1,  6, 10,  5,  3,  8, 11, 12], dtype=int64)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['company_id'].unique()\n",
    "# only 12 companies in total - company 1 to company 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Question 1: Create table with employee headcount by day for all companies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 474,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1785"
      ]
     },
     "execution_count": 474,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# create the dates from 2011/01/24 to 2015/12/13\n",
    "dates = pd.date_range('2011-01-24', '2015-12-13')\n",
    "len(dates)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 476,
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = pd.DataFrame({'dates': dates})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 477,
   "metadata": {},
   "outputs": [],
   "source": [
    "#! [] is empty list, [[]] is empty df\n",
    "dates_headct_all = [[]]\n",
    "for i in np.arange(1, 13):\n",
    "    \n",
    "    data_cpn = data[data['company_id']==i]\n",
    "    \n",
    "    join_dates = data_cpn['join_date']\n",
    "    quit_dates = data_cpn['quit_date']\n",
    "    \n",
    "    join_date_ct = pd.DataFrame({'join_date': join_dates.value_counts().index,\n",
    "                'join_date_ct': join_dates.value_counts().values})\n",
    "    join_date_ct['join_date'] = pd.to_datetime(join_date_ct['join_date']) \n",
    "    \n",
    "    quit_date_ct = pd.DataFrame({'quit_date': quit_dates.value_counts().index,\n",
    "                'quit_date_ct': quit_dates.value_counts().values})\n",
    "    \n",
    "    quit_date_ct['quit_date'] = pd.to_datetime(quit_date_ct['quit_date']) \n",
    "    \n",
    "    dates_headct = pd.merge(dates, join_date_ct, how = 'left', left_on = 'dates', right_on = 'join_date')\n",
    "    \n",
    "    dates_headct = pd.merge(dates_headct, quit_date_ct, how = 'left', left_on = 'dates', right_on = 'quit_date')\n",
    "    \n",
    "    # fill NaNs with 0 in join_date_ct and quit_date_ct to work out the cumulative counts\n",
    "    dates_headct['join_date_ct'] = dates_headct['join_date_ct'].fillna(0)\n",
    "    dates_headct['quit_date_ct'] = dates_headct['quit_date_ct'].fillna(0)\n",
    "    \n",
    "    dates_headct['join_cum_count'] = dates_headct['join_date_ct'].cumsum()\n",
    "    dates_headct['quit_cum_count'] = dates_headct['quit_date_ct'].cumsum()\n",
    "    \n",
    "    dates_headct['headcount'] = dates_headct['join_cum_count'] - dates_headct['quit_cum_count']\n",
    "    \n",
    "    dates_headct['company_id'] = i \n",
    "    \n",
    "    dates_headct_all.append(dates_headct)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 478,
   "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>dates</th>\n",
       "      <th>join_date</th>\n",
       "      <th>join_date_ct</th>\n",
       "      <th>quit_date</th>\n",
       "      <th>quit_date_ct</th>\n",
       "      <th>join_cum_count</th>\n",
       "      <th>quit_cum_count</th>\n",
       "      <th>headcount</th>\n",
       "      <th>company_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>2011-02-14</td>\n",
       "      <td>2011-02-14</td>\n",
       "      <td>25.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>129.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>2011-02-15</td>\n",
       "      <td>2011-02-15</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>2011-02-16</td>\n",
       "      <td>2011-02-16</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>2011-02-17</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>2011-02-18</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>2011-02-19</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>2011-02-20</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>2011-02-21</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>132.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>2011-02-22</td>\n",
       "      <td>2011-02-22</td>\n",
       "      <td>24.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>156.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>2011-02-23</td>\n",
       "      <td>2011-02-23</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>2011-02-24</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>2011-02-25</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>2011-02-26</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>2011-02-27</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>159.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>2011-02-28</td>\n",
       "      <td>2011-02-28</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>2011-03-01</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>178.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>2011-03-02</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>181.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>2011-03-03</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>2011-03-04</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaT</td>\n",
       "      <td>0.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>182.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        dates  join_date  join_date_ct quit_date  quit_date_ct  join_cum_count  quit_cum_count  headcount  company_id\n",
       "21 2011-02-14 2011-02-14          25.0       NaT           0.0           129.0             0.0      129.0           1\n",
       "22 2011-02-15 2011-02-15           1.0       NaT           0.0           130.0             0.0      130.0           1\n",
       "23 2011-02-16 2011-02-16           2.0       NaT           0.0           132.0             0.0      132.0           1\n",
       "24 2011-02-17        NaT           0.0       NaT           0.0           132.0             0.0      132.0           1\n",
       "25 2011-02-18        NaT           0.0       NaT           0.0           132.0             0.0      132.0           1\n",
       "26 2011-02-19        NaT           0.0       NaT           0.0           132.0             0.0      132.0           1\n",
       "27 2011-02-20        NaT           0.0       NaT           0.0           132.0             0.0      132.0           1\n",
       "28 2011-02-21        NaT           0.0       NaT           0.0           132.0             0.0      132.0           1\n",
       "29 2011-02-22 2011-02-22          24.0       NaT           0.0           156.0             0.0      156.0           1\n",
       "30 2011-02-23 2011-02-23           3.0       NaT           0.0           159.0             0.0      159.0           1\n",
       "31 2011-02-24        NaT           0.0       NaT           0.0           159.0             0.0      159.0           1\n",
       "32 2011-02-25        NaT           0.0       NaT           0.0           159.0             0.0      159.0           1\n",
       "33 2011-02-26        NaT           0.0       NaT           0.0           159.0             0.0      159.0           1\n",
       "34 2011-02-27        NaT           0.0       NaT           0.0           159.0             0.0      159.0           1\n",
       "35 2011-02-28 2011-02-28          19.0       NaT           0.0           178.0             0.0      178.0           1\n",
       "36 2011-03-01        NaT           0.0       NaT           0.0           178.0             0.0      178.0           1\n",
       "37 2011-03-02 2011-03-02           3.0       NaT           0.0           181.0             0.0      181.0           1\n",
       "38 2011-03-03 2011-03-03           1.0       NaT           0.0           182.0             0.0      182.0           1\n",
       "39 2011-03-04        NaT           0.0       NaT           0.0           182.0             0.0      182.0           1"
      ]
     },
     "execution_count": 478,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# append all together - a bit strange, need to revise\n",
    "comp_headcnt_by_date = dates_headct_all[1].append([dates_headct_all[2], dates_headct_all[3], dates_headct_all[4], \n",
    "                           dates_headct_all[5] , dates_headct_all[6], dates_headct_all[7],\n",
    "                             dates_headct_all[8], dates_headct_all[9], dates_headct_all[10],\n",
    "                             dates_headct_all[11], dates_headct_all[12]])\n",
    "comp_headcnt_by_date[21:40]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 471,
   "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>dates</th>\n",
       "      <th>company_id</th>\n",
       "      <th>headcount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2011-01-24</td>\n",
       "      <td>1</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2011-01-25</td>\n",
       "      <td>1</td>\n",
       "      <td>27.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2011-01-26</td>\n",
       "      <td>1</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011-01-27</td>\n",
       "      <td>1</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2011-01-28</td>\n",
       "      <td>1</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2011-01-29</td>\n",
       "      <td>1</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2011-01-30</td>\n",
       "      <td>1</td>\n",
       "      <td>29.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2011-01-31</td>\n",
       "      <td>1</td>\n",
       "      <td>59.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2011-02-01</td>\n",
       "      <td>1</td>\n",
       "      <td>66.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2011-02-02</td>\n",
       "      <td>1</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2011-02-03</td>\n",
       "      <td>1</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2011-02-04</td>\n",
       "      <td>1</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2011-02-05</td>\n",
       "      <td>1</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2011-02-06</td>\n",
       "      <td>1</td>\n",
       "      <td>67.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2011-02-07</td>\n",
       "      <td>1</td>\n",
       "      <td>99.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        dates  company_id  headcount\n",
       "0  2011-01-24           1       25.0\n",
       "1  2011-01-25           1       27.0\n",
       "2  2011-01-26           1       29.0\n",
       "3  2011-01-27           1       29.0\n",
       "4  2011-01-28           1       29.0\n",
       "5  2011-01-29           1       29.0\n",
       "6  2011-01-30           1       29.0\n",
       "7  2011-01-31           1       59.0\n",
       "8  2011-02-01           1       66.0\n",
       "9  2011-02-02           1       67.0\n",
       "10 2011-02-03           1       67.0\n",
       "11 2011-02-04           1       67.0\n",
       "12 2011-02-05           1       67.0\n",
       "13 2011-02-06           1       67.0\n",
       "14 2011-02-07           1       99.0"
      ]
     },
     "execution_count": 471,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comp_headcnt_by_date[['dates', 'company_id', 'headcount']].head(15)"
   ]
  }
 ],
 "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}