Salaries Prep.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sbn\n",
    "import re\n",
    "from matplotlib import pyplot\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load the CSV and Name the Columns (I wasn't sure what a few of these were so I just dropped 'em')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = pd.read_csv('./assets/salaries.csv', names=['date','tmp0','title','tmp','tmp2','location','tmp23','tmp3','tmp34','tmp4','org_size','org_size_bucket','salary','tmp5','tmp6','experience', 'tmp7','tmp8','industry','tmp9', 'comments', 'tmp10'])\n",
    "df = pd.read_csv('./assets/salaries.csv')\n",
    "\n",
    "# df[['title','location','salary','experience']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>Timestamp</th>\n",
       "      <th>Title</th>\n",
       "      <th>Location</th>\n",
       "      <th>Remote?</th>\n",
       "      <th>Org Size</th>\n",
       "      <th>Salary</th>\n",
       "      <th>Years of experience</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Additional comments</th>\n",
       "      <th>Additional perks?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9/9/2020 22:17:07</td>\n",
       "      <td>Head of &lt;redacted&gt; technologies</td>\n",
       "      <td>St. Paul, Minnesota</td>\n",
       "      <td>No</td>\n",
       "      <td>51 - 500</td>\n",
       "      <td>US$165,000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9/20/2020 12:19:13</td>\n",
       "      <td>Senior Software Engineer</td>\n",
       "      <td>Porto Alegre/RS - Brazil</td>\n",
       "      <td>No</td>\n",
       "      <td>&gt; 5000</td>\n",
       "      <td>US$ 18400 / year</td>\n",
       "      <td>17</td>\n",
       "      <td>Mediatech</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9/19/2020 18:50:46</td>\n",
       "      <td>Senior Site Reliability Engineer</td>\n",
       "      <td>Toronto, Ontario</td>\n",
       "      <td>No</td>\n",
       "      <td>501 - 1000</td>\n",
       "      <td>CAD $174,000</td>\n",
       "      <td>21</td>\n",
       "      <td>Software/SaaS</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Options, hiring bonus, stock purchase plan, an...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9/9/2020 20:25:55</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>Chantilly, VA</td>\n",
       "      <td>No</td>\n",
       "      <td>&gt; 5000</td>\n",
       "      <td>98000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9/19/2020 21:01:23</td>\n",
       "      <td>Web Developer</td>\n",
       "      <td>Canberra, ACT</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11- 50</td>\n",
       "      <td>$97,294</td>\n",
       "      <td>13</td>\n",
       "      <td>Marketing</td>\n",
       "      <td>The tech industry does exist outside of the US...</td>\n",
       "      <td>No</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2046</th>\n",
       "      <td>2/23/2021 21:37:14</td>\n",
       "      <td>Software Engineer</td>\n",
       "      <td>Chicago Illinois</td>\n",
       "      <td>No</td>\n",
       "      <td>51 - 500</td>\n",
       "      <td>115000</td>\n",
       "      <td>10</td>\n",
       "      <td>RegTech, FinTech</td>\n",
       "      <td>Black woman</td>\n",
       "      <td>5k</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2047</th>\n",
       "      <td>2/23/2021 21:41:54</td>\n",
       "      <td>Senior Application Architect</td>\n",
       "      <td>Northern NJ</td>\n",
       "      <td>No</td>\n",
       "      <td>51 - 500</td>\n",
       "      <td>175000</td>\n",
       "      <td>24</td>\n",
       "      <td>Consulting</td>\n",
       "      <td>NaN</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2048</th>\n",
       "      <td>2/23/2021 21:54:05</td>\n",
       "      <td>Senior Software Engineer</td>\n",
       "      <td>Atlanta</td>\n",
       "      <td>No</td>\n",
       "      <td>&gt; 5000</td>\n",
       "      <td>130000</td>\n",
       "      <td>7</td>\n",
       "      <td>Automotive</td>\n",
       "      <td>Every salary bump has been diagonal move. I've...</td>\n",
       "      <td>13000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2049</th>\n",
       "      <td>2/23/2021 21:56:53</td>\n",
       "      <td>Design Lead</td>\n",
       "      <td>Remote</td>\n",
       "      <td>Yes</td>\n",
       "      <td>11- 50</td>\n",
       "      <td>65000</td>\n",
       "      <td>7</td>\n",
       "      <td>Software consultancy</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nope</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2050</th>\n",
       "      <td>2/23/2021 21:57:28</td>\n",
       "      <td>Senior Software Engineer</td>\n",
       "      <td>Minneapolis, Minnesota</td>\n",
       "      <td>No</td>\n",
       "      <td>&gt; 5000</td>\n",
       "      <td>115,000</td>\n",
       "      <td>6</td>\n",
       "      <td>Retail</td>\n",
       "      <td>I am underpaid 🤷</td>\n",
       "      <td>5% bonus target</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2051 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               Timestamp                             Title  \\\n",
       "0      9/9/2020 22:17:07   Head of <redacted> technologies   \n",
       "1     9/20/2020 12:19:13          Senior Software Engineer   \n",
       "2     9/19/2020 18:50:46  Senior Site Reliability Engineer   \n",
       "3      9/9/2020 20:25:55                 Software Engineer   \n",
       "4     9/19/2020 21:01:23                     Web Developer   \n",
       "...                  ...                               ...   \n",
       "2046  2/23/2021 21:37:14                 Software Engineer   \n",
       "2047  2/23/2021 21:41:54      Senior Application Architect   \n",
       "2048  2/23/2021 21:54:05          Senior Software Engineer   \n",
       "2049  2/23/2021 21:56:53                       Design Lead   \n",
       "2050  2/23/2021 21:57:28          Senior Software Engineer   \n",
       "\n",
       "                      Location Remote?    Org Size            Salary  \\\n",
       "0          St. Paul, Minnesota      No    51 - 500        US$165,000   \n",
       "1     Porto Alegre/RS - Brazil      No      > 5000  US$ 18400 / year   \n",
       "2             Toronto, Ontario      No  501 - 1000     CAD $174,000    \n",
       "3                Chantilly, VA      No      > 5000             98000   \n",
       "4                Canberra, ACT     Yes      11- 50           $97,294   \n",
       "...                        ...     ...         ...               ...   \n",
       "2046          Chicago Illinois      No    51 - 500            115000   \n",
       "2047               Northern NJ      No    51 - 500            175000   \n",
       "2048                   Atlanta      No      > 5000            130000   \n",
       "2049                    Remote     Yes      11- 50             65000   \n",
       "2050    Minneapolis, Minnesota      No      > 5000           115,000   \n",
       "\n",
       "     Years of experience               Industry  \\\n",
       "0                    NaN                    NaN   \n",
       "1                     17             Mediatech    \n",
       "2                     21          Software/SaaS   \n",
       "3                    NaN                    NaN   \n",
       "4                     13              Marketing   \n",
       "...                  ...                    ...   \n",
       "2046                  10       RegTech, FinTech   \n",
       "2047                  24             Consulting   \n",
       "2048                   7             Automotive   \n",
       "2049                   7  Software consultancy    \n",
       "2050                   6                 Retail   \n",
       "\n",
       "                                    Additional comments  \\\n",
       "0                                                   NaN   \n",
       "1                                                   NaN   \n",
       "2                                                   NaN   \n",
       "3                                                   NaN   \n",
       "4     The tech industry does exist outside of the US...   \n",
       "...                                                 ...   \n",
       "2046                                       Black woman    \n",
       "2047                                                NaN   \n",
       "2048  Every salary bump has been diagonal move. I've...   \n",
       "2049                                                NaN   \n",
       "2050                                   I am underpaid 🤷   \n",
       "\n",
       "                                      Additional perks?  \n",
       "0                                                   NaN  \n",
       "1                                                   NaN  \n",
       "2     Options, hiring bonus, stock purchase plan, an...  \n",
       "3                                                   NaN  \n",
       "4                                                    No  \n",
       "...                                                 ...  \n",
       "2046                                                 5k  \n",
       "2047                                                 no  \n",
       "2048                                              13000  \n",
       "2049                                               Nope  \n",
       "2050                                    5% bonus target  \n",
       "\n",
       "[2051 rows x 10 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Turn 100k into 100,000 and remove commas\n",
    "cleaned_salary = df['Salary'].str.replace('k','000').str.replace(',','')\n",
    "# extract a number from the salaries if possible. Still won't work if people wrote like \"one hundred thousand\" \n",
    "# I guess that might be one change you could make to the survey is specify this field is in dollars and only allow a numeric input?\n",
    "cleaned_salary = cleaned_salary.str.extract('(\\d+)', expand=False)\n",
    "cleaned_salary = cleaned_salary.astype(float)\n",
    "\n",
    "# extract a numeric from the experience answer too if possible\n",
    "cleaned_experience = df['Years of experience'].str.extract('(\\d+)').astype(float)\n",
    "\n",
    "df['salary'] = cleaned_salary\n",
    "df['experience'] = cleaned_experience\n",
    "\n",
    "\n",
    "# Take the first pre-comma chunk of text for location (so \"Los Angeles, CA\" and \"Los Angeles\" bot become \"Los Angeles\")\n",
    "df['location'] = df['Location'].str.split(',').str[0]\n",
    "# Remove non-alpha characters and lowercase to try and make these location names more standard\n",
    "df['location'] = df['location'].fillna('').apply(lambda x: re.sub(\"[^a-zA-Z ]+\", '', x, flags=re.UNICODE)).str.lower()\n",
    "# Just fix DC manually\n",
    "df.loc[df['location'] == 'washington', 'location'] = 'washington dc'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Drop Some Bad Outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df[df['salary'] < 1000000]\n",
    "df = df[df['experience'] < 100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# this doesnt cover a lot of these titles, but you get the idea\n",
    "def title_substrings(title, substrings):\n",
    "    for substring in substrings:\n",
    "        if substring in title:\n",
    "            return True\n",
    "    return False\n",
    "\n",
    "def title_function(title):\n",
    "    title = title.lower()\n",
    "    if title_substrings(title, ['data', 'artificail', 'ml', 'scien']):\n",
    "        return 'Data'\n",
    "    elif title_substrings(title, ['front', 'javascript', 'react', 'ui']):\n",
    "        return 'Front End'\n",
    "    elif title_substrings(title, ['back']):\n",
    "        return 'Back End'\n",
    "    elif title_substrings(title, ['full','web', 'rails']):\n",
    "        return 'Full Stack'\n",
    "    elif title_substrings(title, ['mobile','ios', 'android']):\n",
    "        return 'Mobile'\n",
    "    elif title_substrings(title, ['ops','system', 'security', 'tool', 'network']):\n",
    "        return 'Ops and Security'\n",
    "    elif title_substrings(title, ['ux','user', 'design']):\n",
    "        return 'UX and Design'\n",
    "    elif title_substrings(title, ['manag','director', 'arch', 'president', 'cto', 'ceo', 'head', 'gm', 'coo', 'vp', 'chief']):\n",
    "        return 'Management and Leadership'\n",
    "    elif title_substrings(title, ['market', 'content', 'writ']):\n",
    "        return 'Marketing and Content'\n",
    "    elif title_substrings(title, ['test', 'quality', 'qa']):\n",
    "        return 'QA and Testing'\n",
    "    elif title_substrings(title, ['support']):\n",
    "        return 'Support'\n",
    "    elif title_substrings(title, ['product']):\n",
    "        return 'Product'\n",
    "    elif title_substrings(title, ['engineer', 'software', 'dev', 'program', 'tech']):\n",
    "        return 'General Engineering'\n",
    "    else:\n",
    "        return 'Unknown'\n",
    "\n",
    "df['title_categories'] = df['Title'].fillna('').apply(title_function)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# I don't know what a lot of these titles are, these are the titles I didn't categorize in any way..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['SWE Intern', 'Community Advocate', 'Applications Analyst',\n",
       "       'Solutions Analyst', 'Junior Salesforce Administrator ',\n",
       "       'Lead Analyst', 'Lead SRE', 'Advanced Physicist',\n",
       "       'Sr Business Analyis Consultant', 'Sr. SWE', 'Senior Consultant ',\n",
       "       'Senior Business Analyst ', 'Red Team Lead', 'Associate ',\n",
       "       'Senior SDM', 'Senior Sdet', 'SRE', 'Jr. Salesforce Administrator',\n",
       "       'Senior FP&A Analyst', 'Staff 1', 'Senior Digital Strategist ',\n",
       "       'ERP Consultant', 'Sales Strategy', 'Social Media Specialist',\n",
       "       'Sr Analyst', 'Sr SDE', 'Team Lead', 'Senior Financial Analyst',\n",
       "       'Lead Producer', 'z/os scheduller', 'Business Analyst ',\n",
       "       'Senior SOC Analyst', 'CIO', 'Sr Sales Operations Analyst',\n",
       "       'Salesforce Consultant', 'Senior SRE',\n",
       "       'Senior Partnerships Associate', 'Server Storage Administrator',\n",
       "       'Founder', 'Business consultant', 'Infrastructure Analyst',\n",
       "       'Infosec.Consultant', 'Analytic Consultant', 'Consultant ',\n",
       "       'Senior Business Intelligence Consultant', 'Group leader',\n",
       "       'Quantitative Solutions', 'Account Executive', 'Project Analyst',\n",
       "       'Project manger', 'Senior Account Executivf ',\n",
       "       'Senior consultant ', 'IT Business Analyst ', 'Cryptographer',\n",
       "       'Senior Account Exec', 'Poop Picker', 'Analyst ',\n",
       "       'Senior Associate ', 'Executive Assistant', 'Analytics Lead',\n",
       "       'Analyst', 'Sr. Consultant', 'ICT4', nan, 'warlord', 'CISO',\n",
       "       'Statistician', 'Progrqm Analyst', 'Team leader', 'Client Partner',\n",
       "       'Banana eater', 'Computational Biology', 'Threat hunter', 'L7',\n",
       "       'Fraud analyst ', 'Scrum Master', 'IT Specialist III', 'SDE 2',\n",
       "       'Senior Consultant', 'prout', 'Associate', 'Just lookin',\n",
       "       'Incident Response Consultant', 'Agile Consultant',\n",
       "       'Operations Analyst', 'Does it matter', 'Consultant', 'GIS analy6',\n",
       "       'MD', 'Red Team Operator', 'Lead Consultant, Corporate Strategy',\n",
       "       'Digital Forensics Analyst', 'Executive Compensation Specialist',\n",
       "       'DBA', 'Account Executive ', 'Senior SWE', 'Python evangelist',\n",
       "       'Principal Solutions Consultant', 'Business analyst',\n",
       "       'PhD candidate', 'Independent Consultant', 'Trainer',\n",
       "       'senior soft eng', 'Business Intelligence Analyst ',\n",
       "       'SWE - Machine Learning', 'Threat Hunter', 'Cartographer + GIS  ',\n",
       "       'Senior Associate', 'Ers', 'JR Business Analysts ',\n",
       "       'Experience Lead', 'Senior consultant'], dtype=object)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['title_categories'] == 'Unknown']['Title'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('./assets/cleaned_data.csv')"
   ]
  },
  {
   "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.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}