Data_Jobs_Cleanup.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Importing libraries and tools\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from matplotlib import pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Final output data to be used for vizualization\n",
    "output_data_file = \"output/data.csv\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Reading csv files\n",
    "analyst_df = pd.read_csv(\"Data_Analyst.csv\",encoding = \"ISO-8859-1\")\n",
    "engineer_df = pd.read_csv(\"Data_Engineer.csv\",encoding = \"ISO-8859-1\")\n",
    "scientist_df = pd.read_csv(\"DataScientist.csv\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Sample cleanup for one of our datasets (data scientist)\n",
    "df = scientist_df[\"Salary Estimate\"].str.split(\"(\", n = 1, expand = True) \n",
    "scientist_df[\"Salary Estimate\"]= df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Splitting salary estimate column to min and max\n",
    "df_1 = scientist_df[\"Salary Estimate\"].str.split(\"-\", n = 1, expand = True)\n",
    "scientist_df[\"Salary Estimate Min\"]= df_1[0]\n",
    "scientist_df[\"Salary Estimate Max\"]= df_1[1]\n",
    "\n",
    "df_2 = scientist_df[\"Salary Estimate Min\"].str.split(\"$\", n = 1, expand = True)\n",
    "scientist_df[\"Salary Estimate Min\"]= df_2[1]\n",
    "\n",
    "df_3 = scientist_df[\"Salary Estimate Max\"].str.split(\"$\", n = 1, expand = True)\n",
    "scientist_df[\"Salary Estimate Max\"]= df_3[1]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Converting units and data typesof salary column\n",
    "scientist_df['Salary Estimate Max'] =  scientist_df['Salary Estimate Max'].str.replace('K','000',regex = False)\n",
    "scientist_df['Salary Estimate Min'] =  scientist_df['Salary Estimate Min'].str.replace('K','000',regex = False)\n",
    "\n",
    "#Replacing -1s with 0s\n",
    "scientist_df.replace({\"-1\": \"0\"}, inplace=True)\n",
    "\n",
    "#Dropping rows with outlier values\n",
    "scientist_df.drop(scientist_df.index[scientist_df['Salary Estimate Max'] == '53 Per Hour'], inplace = True)\n",
    "scientist_df.drop(scientist_df.index[scientist_df['Salary Estimate Max'] == '27 Per Hour'], inplace = True)\n",
    "scientist_df.drop(scientist_df.index[scientist_df['Salary Estimate Max'] == '26 Per Hour'], inplace = True)\n",
    "\n",
    "#Converting to integer\n",
    "scientist_df['Salary Estimate Max'] = scientist_df['Salary Estimate Max'].astype(int)\n",
    "scientist_df['Salary Estimate Min'] = scientist_df['Salary Estimate Min'].astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>Unnamed: 0</th>\n",
       "      <th>index</th>\n",
       "      <th>Job Title</th>\n",
       "      <th>Salary Estimate</th>\n",
       "      <th>Job Description</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Location</th>\n",
       "      <th>Headquarters</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Competitors</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Senior Data Scientist</td>\n",
       "      <td>$111K-$181K</td>\n",
       "      <td>ABOUT HOPPER\\n\\nAt Hopper, we’re on a mission ...</td>\n",
       "      <td>3.5</td>\n",
       "      <td>Hopper</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>Montreal, Canada</td>\n",
       "      <td>501 to 1000 employees</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Travel Agencies</td>\n",
       "      <td>Travel &amp; Tourism</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>Data Scientist, Product Analytics</td>\n",
       "      <td>$111K-$181K</td>\n",
       "      <td>At Noom, we use scientifically proven methods ...</td>\n",
       "      <td>4.5</td>\n",
       "      <td>Noom US</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>1001 to 5000 employees</td>\n",
       "      <td>2008</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Health, Beauty, &amp; Fitness</td>\n",
       "      <td>Consumer Services</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>Data Science Manager</td>\n",
       "      <td>$111K-$181K</td>\n",
       "      <td>Decode_M\\n\\nhttps://www.decode-m.com/\\n\\nData ...</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>Decode_M</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>1 to 50 employees</td>\n",
       "      <td>-1</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>True</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>Data Analyst</td>\n",
       "      <td>$111K-$181K</td>\n",
       "      <td>Sapphire Digital seeks a dynamic and driven mi...</td>\n",
       "      <td>3.4</td>\n",
       "      <td>Sapphire Digital</td>\n",
       "      <td>Lyndhurst, NJ</td>\n",
       "      <td>Lyndhurst, NJ</td>\n",
       "      <td>201 to 500 employees</td>\n",
       "      <td>2019</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Internet</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>Zocdoc, Healthgrades</td>\n",
       "      <td>0</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Director, Data Science</td>\n",
       "      <td>$111K-$181K</td>\n",
       "      <td>Director, Data Science - (200537)\\nDescription...</td>\n",
       "      <td>3.4</td>\n",
       "      <td>United Entertainment Group</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>51 to 200 employees</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Advertising &amp; Marketing</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>BBDO, Grey Group, Droga5</td>\n",
       "      <td>0</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0  index                          Job Title Salary Estimate  \\\n",
       "0           0      0              Senior Data Scientist    $111K-$181K    \n",
       "1           1      1  Data Scientist, Product Analytics    $111K-$181K    \n",
       "2           2      2               Data Science Manager    $111K-$181K    \n",
       "3           3      3                       Data Analyst    $111K-$181K    \n",
       "4           4      4             Director, Data Science    $111K-$181K    \n",
       "\n",
       "                                     Job Description  Rating  \\\n",
       "0  ABOUT HOPPER\\n\\nAt Hopper, we’re on a mission ...     3.5   \n",
       "1  At Noom, we use scientifically proven methods ...     4.5   \n",
       "2  Decode_M\\n\\nhttps://www.decode-m.com/\\n\\nData ...    -1.0   \n",
       "3  Sapphire Digital seeks a dynamic and driven mi...     3.4   \n",
       "4  Director, Data Science - (200537)\\nDescription...     3.4   \n",
       "\n",
       "                 Company Name       Location      Headquarters  \\\n",
       "0                      Hopper   New York, NY  Montreal, Canada   \n",
       "1                     Noom US   New York, NY      New York, NY   \n",
       "2                    Decode_M   New York, NY      New York, NY   \n",
       "3            Sapphire Digital  Lyndhurst, NJ     Lyndhurst, NJ   \n",
       "4  United Entertainment Group   New York, NY      New York, NY   \n",
       "\n",
       "                     Size  Founded  Type of ownership  \\\n",
       "0   501 to 1000 employees     2007  Company - Private   \n",
       "1  1001 to 5000 employees     2008  Company - Private   \n",
       "2       1 to 50 employees       -1            Unknown   \n",
       "3    201 to 500 employees     2019  Company - Private   \n",
       "4     51 to 200 employees     2007  Company - Private   \n",
       "\n",
       "                    Industry                  Sector  \\\n",
       "0            Travel Agencies        Travel & Tourism   \n",
       "1  Health, Beauty, & Fitness       Consumer Services   \n",
       "2                          0                       0   \n",
       "3                   Internet  Information Technology   \n",
       "4    Advertising & Marketing       Business Services   \n",
       "\n",
       "                    Revenue               Competitors Easy Apply  \\\n",
       "0  Unknown / Non-Applicable                         0          0   \n",
       "1  Unknown / Non-Applicable                         0          0   \n",
       "2  Unknown / Non-Applicable                         0       True   \n",
       "3  Unknown / Non-Applicable      Zocdoc, Healthgrades          0   \n",
       "4  Unknown / Non-Applicable  BBDO, Grey Group, Droga5          0   \n",
       "\n",
       "   Salary Estimate Min  Salary Estimate Max  \n",
       "0               111000               181000  \n",
       "1               111000               181000  \n",
       "2               111000               181000  \n",
       "3               111000               181000  \n",
       "4               111000               181000  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Removing unwanted characters from column\n",
    "split_string = scientist_df[\"Company Name\"].str.split(\"\\n\", n = 1, expand = True) \n",
    "scientist_df[\"Company Name\"] =split_string[0]\n",
    "scientist_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Parsing the job title column to extract the needed job titles for analysis\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('Data Analyst'), 'Job Title'] = \"Data Analyst\"\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('Scientist'), 'Job Title'] = \"Data Scientist\"\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('Engineer'), 'Job Title'] = \"Data Engineer\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Replacing values\n",
    "scientist_df['Job Title'] = scientist_df['Job Title'].replace({'Self Employed': 'Self-Employed', 'Self': 'Self-Employed'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Using keywords we consolidated all jobs that had to do with data science into one universal name\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('Science'), 'Job Title'] = \"Data Scientist\"\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('SCIENCE'), 'Job Title'] = \"Data Scientist  \"\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('SCIENTIST'), 'Job Title'] = \"Data Scientist\"\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('science'), 'Job Title'] = \"Data Scientist\"\n",
    "scientist_df.loc[scientist_df['Job Title'].str.contains('scientist'), 'Job Title'] = \"Data Scientist\"\n",
    "\n",
    "#Only show Data Scientists\n",
    "scientist_df = scientist_df.loc[scientist_df['Job Title'] == 'Data Scientist',:]"
   ]
  },
  {
   "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>Job Title</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "      <th>Location</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Competitors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Hopper</td>\n",
       "      <td>3.5</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>501 to 1000 employees</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Travel Agencies</td>\n",
       "      <td>Travel &amp; Tourism</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Noom US</td>\n",
       "      <td>4.5</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>1001 to 5000 employees</td>\n",
       "      <td>2008</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Health, Beauty, &amp; Fitness</td>\n",
       "      <td>Consumer Services</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Decode_M</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>1 to 50 employees</td>\n",
       "      <td>-1</td>\n",
       "      <td>Unknown</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>True</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>United Entertainment Group</td>\n",
       "      <td>3.4</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>51 to 200 employees</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Advertising &amp; Marketing</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>BBDO, Grey Group, Droga5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>IFG Companies</td>\n",
       "      <td>2.9</td>\n",
       "      <td>111000</td>\n",
       "      <td>181000</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>201 to 500 employees</td>\n",
       "      <td>1985</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Insurance Carriers</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>Colony Specialty, Markel, RLI</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Job Title                Company Name  Rating  Salary Estimate Min  \\\n",
       "0  Data Scientist                      Hopper     3.5               111000   \n",
       "1  Data Scientist                     Noom US     4.5               111000   \n",
       "2  Data Scientist                    Decode_M    -1.0               111000   \n",
       "4  Data Scientist  United Entertainment Group     3.4               111000   \n",
       "5  Data Scientist               IFG Companies     2.9               111000   \n",
       "\n",
       "   Salary Estimate Max      Location                    Size  Founded  \\\n",
       "0               181000  New York, NY   501 to 1000 employees     2007   \n",
       "1               181000  New York, NY  1001 to 5000 employees     2008   \n",
       "2               181000  New York, NY       1 to 50 employees       -1   \n",
       "4               181000  New York, NY     51 to 200 employees     2007   \n",
       "5               181000  New York, NY    201 to 500 employees     1985   \n",
       "\n",
       "   Type of ownership                   Industry             Sector  \\\n",
       "0  Company - Private            Travel Agencies   Travel & Tourism   \n",
       "1  Company - Private  Health, Beauty, & Fitness  Consumer Services   \n",
       "2            Unknown                          0                  0   \n",
       "4  Company - Private    Advertising & Marketing  Business Services   \n",
       "5  Company - Private         Insurance Carriers          Insurance   \n",
       "\n",
       "                    Revenue Easy Apply                    Competitors  \n",
       "0  Unknown / Non-Applicable          0                              0  \n",
       "1  Unknown / Non-Applicable          0                              0  \n",
       "2  Unknown / Non-Applicable       True                              0  \n",
       "4  Unknown / Non-Applicable          0       BBDO, Grey Group, Droga5  \n",
       "5  Unknown / Non-Applicable          0  Colony Specialty, Markel, RLI  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Creating a new dataframe with selected columns\n",
    "scientist_df = scientist_df[['Job Title','Company Name','Rating','Salary Estimate Min','Salary Estimate Max','Location','Size','Founded','Type of ownership','Industry','Sector','Revenue','Easy Apply','Competitors']]\n",
    "scientist_df['Salary Estimate Min'].astype(int)\n",
    "scientist_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preping the dataframes to be merged\n",
    "analyst_df = analyst_df[['Job_Title','Company_Name','Rating','Minimum_Salary','Maximum_Salary','Location','Size','Founded','Ownership_Type','Industry','Sector','Revenue','Easy_Apply','Competitors']]\n",
    "\n",
    "analyst_df = analyst_df.rename(columns = {'Job_Title': 'Job Title', \n",
    "                                          'Company_Name': 'Company Name',\n",
    "                                          'Minimum_Salary': 'Salary Estimate Min', \n",
    "                                          'Maximum_Salary': 'Salary Estimate Max', \n",
    "                                        \n",
    "                                          'Ownership_Type': 'Type of ownership', \n",
    "                                          'Easy_Apply': 'Easy Apply'}, inplace = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Preping the dataframes to be merged\n",
    "engineer_df = engineer_df[['Job_Title','Company_Name','Rating','Minimum_Salary','Maximum_Salary','Location','Size','Founded','Ownership_Type','Industry','Sector','Revenue','Easy_Apply','Competitors']]\n",
    "engineer_df = engineer_df.rename(columns = {'Job_Title': 'Job Title', \n",
    "                                          'Company_Name': 'Company Name',\n",
    "                                          'Minimum_Salary': 'Salary Estimate Min', \n",
    "                                          'Maximum_Salary': 'Salary Estimate Max', \n",
    "                                          'Ownership_Type': 'Type of ownership', \n",
    "                                          'Easy_Apply': 'Easy Apply'}, inplace = False)"
   ]
  },
  {
   "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>Job Title</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "      <th>Location</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Competitors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Sagence</td>\n",
       "      <td>4.5</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>1 to 50 employees</td>\n",
       "      <td>2009</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Consulting</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>$10 to $25 million (USD)</td>\n",
       "      <td>0</td>\n",
       "      <td>WCI Consulting, PwC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Enterprise Integration</td>\n",
       "      <td>3.4</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>51 to 200 employees</td>\n",
       "      <td>1998</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>$25 to $50 million (USD)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Maestro Technologies</td>\n",
       "      <td>5.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>51 to 200 employees</td>\n",
       "      <td>2003</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>$5 to $10 million (USD)</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Jane Street</td>\n",
       "      <td>4.8</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>501 to 1000 employees</td>\n",
       "      <td>2000</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Investment Banking &amp; Asset Management</td>\n",
       "      <td>Finance</td>\n",
       "      <td>Unknown / Non-Applicable</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>GNY Insurance Companies</td>\n",
       "      <td>3.7</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>201 to 500 employees</td>\n",
       "      <td>1914</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Insurance Carriers</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>$100 to $500 million (USD)</td>\n",
       "      <td>True</td>\n",
       "      <td>Travelers, Chubb, Crum &amp; Forster</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       Job Title             Company Name  Rating  Salary Estimate Min  \\\n",
       "0  Data Engineer                  Sagence     4.5                80000   \n",
       "1  Data Engineer   Enterprise Integration     3.4                80000   \n",
       "2  Data Engineer     Maestro Technologies     5.0                80000   \n",
       "3  Data Engineer              Jane Street     4.8                80000   \n",
       "4  Data Engineer  GNY Insurance Companies     3.7                80000   \n",
       "\n",
       "   Salary Estimate Max      Location                   Size  Founded  \\\n",
       "0             150000.0  New York, NY      1 to 50 employees     2009   \n",
       "1             150000.0  New York, NY    51 to 200 employees     1998   \n",
       "2             150000.0  New York, NY    51 to 200 employees     2003   \n",
       "3             150000.0  New York, NY  501 to 1000 employees     2000   \n",
       "4             150000.0  New York, NY   201 to 500 employees     1914   \n",
       "\n",
       "   Type of ownership                               Industry  \\\n",
       "0  Company - Private                             Consulting   \n",
       "1  Company - Private                            IT Services   \n",
       "2  Company - Private                            IT Services   \n",
       "3  Company - Private  Investment Banking & Asset Management   \n",
       "4  Company - Private                     Insurance Carriers   \n",
       "\n",
       "                   Sector                     Revenue Easy Apply  \\\n",
       "0       Business Services    $10 to $25 million (USD)          0   \n",
       "1  Information Technology    $25 to $50 million (USD)          0   \n",
       "2  Information Technology     $5 to $10 million (USD)          0   \n",
       "3                 Finance    Unknown / Non-Applicable          0   \n",
       "4               Insurance  $100 to $500 million (USD)       True   \n",
       "\n",
       "                        Competitors  \n",
       "0               WCI Consulting, PwC  \n",
       "1                                 0  \n",
       "2                                 0  \n",
       "3                                 0  \n",
       "4  Travelers, Chubb, Crum & Forster  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Consolidating three dataframes into one dataframe\n",
    "frames = [engineer_df, analyst_df,scientist_df]\n",
    "data = pd.concat(frames)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Double checking we got all of the -1s out\n",
    "data.replace({-1: 0}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "#More cleanup\n",
    "data.loc[data['Revenue'].str.contains('Unknown / Non-Applicable'), 'Revenue'] = '0'"
   ]
  },
  {
   "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>Job Title</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "      <th>Location</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Competitors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Sagence</td>\n",
       "      <td>4.5</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>1 to 50 employees</td>\n",
       "      <td>2009</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Consulting</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>25000000</td>\n",
       "      <td>0</td>\n",
       "      <td>WCI Consulting, PwC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Enterprise Integration</td>\n",
       "      <td>3.4</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>51 to 200 employees</td>\n",
       "      <td>1998</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>50000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Maestro Technologies</td>\n",
       "      <td>5.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>51 to 200 employees</td>\n",
       "      <td>2003</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>10000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Jane Street</td>\n",
       "      <td>4.8</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>501 to 1000 employees</td>\n",
       "      <td>2000</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Investment Banking &amp; Asset Management</td>\n",
       "      <td>Finance</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>GNY Insurance Companies</td>\n",
       "      <td>3.7</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>201 to 500 employees</td>\n",
       "      <td>1914</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Insurance Carriers</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>500000000</td>\n",
       "      <td>True</td>\n",
       "      <td>Travelers, Chubb, Crum &amp; Forster</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Nationwide Children's Hospital</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>10000+ employees</td>\n",
       "      <td>1892</td>\n",
       "      <td>Nonprofit Organization</td>\n",
       "      <td>Health Care Services &amp; Hospitals</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>2000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3893</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>5001 to 10000 employees</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3895</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>5001 to 10000 employees</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3902</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Varsity Tutors</td>\n",
       "      <td>4.2</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>201 to 500 employees</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Internet</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3908</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>5001 to 10000 employees</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6596 rows × 14 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Job Title                    Company Name  Rating  \\\n",
       "0      Data Engineer                         Sagence     4.5   \n",
       "1      Data Engineer          Enterprise Integration     3.4   \n",
       "2      Data Engineer            Maestro Technologies     5.0   \n",
       "3      Data Engineer                     Jane Street     4.8   \n",
       "4      Data Engineer         GNY Insurance Companies     3.7   \n",
       "...              ...                             ...     ...   \n",
       "3884  Data Scientist  Nationwide Children's Hospital     3.7   \n",
       "3893  Data Scientist                             UCB     3.7   \n",
       "3895  Data Scientist                             UCB     3.7   \n",
       "3902  Data Scientist                  Varsity Tutors     4.2   \n",
       "3908  Data Scientist                             UCB     3.7   \n",
       "\n",
       "      Salary Estimate Min  Salary Estimate Max      Location  \\\n",
       "0                   80000             150000.0  New York, NY   \n",
       "1                   80000             150000.0  New York, NY   \n",
       "2                   80000             150000.0  New York, NY   \n",
       "3                   80000             150000.0  New York, NY   \n",
       "4                   80000             150000.0  New York, NY   \n",
       "...                   ...                  ...           ...   \n",
       "3884                55000             112000.0  Columbus, OH   \n",
       "3893                55000             112000.0    Slough, OH   \n",
       "3895                55000             112000.0    Slough, OH   \n",
       "3902                55000             112000.0  Columbus, OH   \n",
       "3908                55000             112000.0    Slough, OH   \n",
       "\n",
       "                         Size  Founded       Type of ownership  \\\n",
       "0           1 to 50 employees     2009       Company - Private   \n",
       "1         51 to 200 employees     1998       Company - Private   \n",
       "2         51 to 200 employees     2003       Company - Private   \n",
       "3       501 to 1000 employees     2000       Company - Private   \n",
       "4        201 to 500 employees     1914       Company - Private   \n",
       "...                       ...      ...                     ...   \n",
       "3884         10000+ employees     1892  Nonprofit Organization   \n",
       "3893  5001 to 10000 employees        0        Company - Public   \n",
       "3895  5001 to 10000 employees        0        Company - Public   \n",
       "3902     201 to 500 employees     2007       Company - Private   \n",
       "3908  5001 to 10000 employees        0        Company - Public   \n",
       "\n",
       "                                   Industry                     Sector  \\\n",
       "0                                Consulting          Business Services   \n",
       "1                               IT Services     Information Technology   \n",
       "2                               IT Services     Information Technology   \n",
       "3     Investment Banking & Asset Management                    Finance   \n",
       "4                        Insurance Carriers                  Insurance   \n",
       "...                                     ...                        ...   \n",
       "3884       Health Care Services & Hospitals                Health Care   \n",
       "3893              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "3895              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "3902                               Internet     Information Technology   \n",
       "3908              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "\n",
       "          Revenue Easy Apply                       Competitors  \n",
       "0       25000000           0               WCI Consulting, PwC  \n",
       "1       50000000           0                                 0  \n",
       "2       10000000           0                                 0  \n",
       "3               0          0                                 0  \n",
       "4      500000000        True  Travelers, Chubb, Crum & Forster  \n",
       "...           ...        ...                               ...  \n",
       "3884  2000000000           0                                 0  \n",
       "3893  5000000000           0                            AbbVie  \n",
       "3895  5000000000           0                            AbbVie  \n",
       "3902            0          0                                 0  \n",
       "3908  5000000000           0                            AbbVie  \n",
       "\n",
       "[6596 rows x 14 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Splitting and formating the revenue column\n",
    "df_7 = data[\"Revenue\"].str.split(\"(\", n = 1, expand = True)\n",
    "data[\"Revenue\"] = df_7[0]\n",
    "\n",
    "data['Revenue'] =  data['Revenue'].str.replace(' million','000000',regex = False)\n",
    "data['Revenue'] =  data['Revenue'].str.replace(' billion','000000000',regex = False)\n",
    "\n",
    "df_8 = data[\"Revenue\"].str.split(\"to\", n = 1, expand = True)\n",
    "data[\"Revenue\"] = df_8[1]\n",
    "\n",
    "df_9 = data[\"Revenue\"].str.split(\"$\", n = 1, expand = True)\n",
    "data[\"Revenue\"] = df_9[1]\n",
    "\n",
    "\n",
    "data[\"Revenue\"] = data[\"Revenue\"].replace([None], \"0\")\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating a new column with States\n",
    "df_4 = data[\"Location\"].str.split(\",\", n = 1, expand = True)\n",
    "df_4\n",
    "data['State'] = df_4[1]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Catagorizing the Size column by our defined market segments\n",
    "data.loc[data['Size'].str.contains('10000+ employees'), 'Size'] = \"Enterprise(5,000+ Employees)\"\n",
    "data.loc[data['Size'].str.contains('1 to 50'), 'Size'] = \"VSB (<50 Employees)\"\n",
    "data.loc[data['Size'].str.contains('5001 to 10000'), 'Size'] = \"Enterprise(5,000+ Employees)\"\n",
    "data.loc[data['Size'].str.contains('51 to 200'), 'Size'] = \"SMB(51-500 Employees)\"\n",
    "data.loc[data['Size'].str.contains('10000+'), 'Size'] = \"Enterprise(5,000+ Employees)\"\n",
    "data.loc[data['Size'].str.contains('501 to 1000'), 'Size'] = \"Mid-Market(501-5,000 Employees)\"\n",
    "data.loc[data['Size'].str.contains('501 to 1000'), 'Size'] = \"Mid-Market(501-5,000 Employees)\"\n",
    "data.loc[data['Size'].str.contains('501 to 1000'), 'Size'] = \"Mid-Market(501-5,000 Employees)\""
   ]
  },
  {
   "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>Job Title</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "      <th>Location</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Competitors</th>\n",
       "      <th>State</th>\n",
       "      <th>Salary Estimate Average</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Sagence</td>\n",
       "      <td>4.5</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Consulting</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>25000000</td>\n",
       "      <td>0</td>\n",
       "      <td>WCI Consulting, PwC</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Enterprise Integration</td>\n",
       "      <td>3.4</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>SMB(51-500 Employees)</td>\n",
       "      <td>1998</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>50000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Maestro Technologies</td>\n",
       "      <td>5.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>SMB(51-500 Employees)</td>\n",
       "      <td>2003</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>10000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Jane Street</td>\n",
       "      <td>4.8</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>Mid-Market(501-5,000 Employees)</td>\n",
       "      <td>2000</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Investment Banking &amp; Asset Management</td>\n",
       "      <td>Finance</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>GNY Insurance Companies</td>\n",
       "      <td>3.7</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>1914</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Insurance Carriers</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>500000000</td>\n",
       "      <td>True</td>\n",
       "      <td>Travelers, Chubb, Crum &amp; Forster</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Nationwide Children's Hospital</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>1892</td>\n",
       "      <td>Nonprofit Organization</td>\n",
       "      <td>Health Care Services &amp; Hospitals</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>2000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3893</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3895</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3902</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Varsity Tutors</td>\n",
       "      <td>4.2</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Internet</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3908</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5000000000</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6596 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Job Title                    Company Name  Rating  \\\n",
       "0      Data Engineer                         Sagence     4.5   \n",
       "1      Data Engineer          Enterprise Integration     3.4   \n",
       "2      Data Engineer            Maestro Technologies     5.0   \n",
       "3      Data Engineer                     Jane Street     4.8   \n",
       "4      Data Engineer         GNY Insurance Companies     3.7   \n",
       "...              ...                             ...     ...   \n",
       "3884  Data Scientist  Nationwide Children's Hospital     3.7   \n",
       "3893  Data Scientist                             UCB     3.7   \n",
       "3895  Data Scientist                             UCB     3.7   \n",
       "3902  Data Scientist                  Varsity Tutors     4.2   \n",
       "3908  Data Scientist                             UCB     3.7   \n",
       "\n",
       "      Salary Estimate Min  Salary Estimate Max      Location  \\\n",
       "0                   80000             150000.0  New York, NY   \n",
       "1                   80000             150000.0  New York, NY   \n",
       "2                   80000             150000.0  New York, NY   \n",
       "3                   80000             150000.0  New York, NY   \n",
       "4                   80000             150000.0  New York, NY   \n",
       "...                   ...                  ...           ...   \n",
       "3884                55000             112000.0  Columbus, OH   \n",
       "3893                55000             112000.0    Slough, OH   \n",
       "3895                55000             112000.0    Slough, OH   \n",
       "3902                55000             112000.0  Columbus, OH   \n",
       "3908                55000             112000.0    Slough, OH   \n",
       "\n",
       "                                 Size  Founded       Type of ownership  \\\n",
       "0                 VSB (<50 Employees)     2009       Company - Private   \n",
       "1               SMB(51-500 Employees)     1998       Company - Private   \n",
       "2               SMB(51-500 Employees)     2003       Company - Private   \n",
       "3     Mid-Market(501-5,000 Employees)     2000       Company - Private   \n",
       "4                 VSB (<50 Employees)     1914       Company - Private   \n",
       "...                               ...      ...                     ...   \n",
       "3884     Enterprise(5,000+ Employees)     1892  Nonprofit Organization   \n",
       "3893     Enterprise(5,000+ Employees)        0        Company - Public   \n",
       "3895     Enterprise(5,000+ Employees)        0        Company - Public   \n",
       "3902              VSB (<50 Employees)     2007       Company - Private   \n",
       "3908     Enterprise(5,000+ Employees)        0        Company - Public   \n",
       "\n",
       "                                   Industry                     Sector  \\\n",
       "0                                Consulting          Business Services   \n",
       "1                               IT Services     Information Technology   \n",
       "2                               IT Services     Information Technology   \n",
       "3     Investment Banking & Asset Management                    Finance   \n",
       "4                        Insurance Carriers                  Insurance   \n",
       "...                                     ...                        ...   \n",
       "3884       Health Care Services & Hospitals                Health Care   \n",
       "3893              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "3895              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "3902                               Internet     Information Technology   \n",
       "3908              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "\n",
       "          Revenue Easy Apply                       Competitors State  \\\n",
       "0       25000000           0               WCI Consulting, PwC    NY   \n",
       "1       50000000           0                                 0    NY   \n",
       "2       10000000           0                                 0    NY   \n",
       "3               0          0                                 0    NY   \n",
       "4      500000000        True  Travelers, Chubb, Crum & Forster    NY   \n",
       "...           ...        ...                               ...   ...   \n",
       "3884  2000000000           0                                 0    OH   \n",
       "3893  5000000000           0                            AbbVie    OH   \n",
       "3895  5000000000           0                            AbbVie    OH   \n",
       "3902            0          0                                 0    OH   \n",
       "3908  5000000000           0                            AbbVie    OH   \n",
       "\n",
       "      Salary Estimate Average  \n",
       "0                    115000.0  \n",
       "1                    115000.0  \n",
       "2                    115000.0  \n",
       "3                    115000.0  \n",
       "4                    115000.0  \n",
       "...                       ...  \n",
       "3884                  83500.0  \n",
       "3893                  83500.0  \n",
       "3895                  83500.0  \n",
       "3902                  83500.0  \n",
       "3908                  83500.0  \n",
       "\n",
       "[6596 rows x 16 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Creating a new column that calculates average salary\n",
    "data['Salary Estimate Average'] = (data['Salary Estimate Max'] + data['Salary Estimate Min']) / 2\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Converting revenue column to more usable data\n",
    "data['Revenue'] = data['Revenue'].astype(float)\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 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>Job Title</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "      <th>Location</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Competitors</th>\n",
       "      <th>State</th>\n",
       "      <th>Salary Estimate Average</th>\n",
       "      <th>Revenue (millions)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Sagence</td>\n",
       "      <td>4.5</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Consulting</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>2.500000e+07</td>\n",
       "      <td>0</td>\n",
       "      <td>WCI Consulting, PwC</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>2.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Enterprise Integration</td>\n",
       "      <td>3.4</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>SMB(51-500 Employees)</td>\n",
       "      <td>1998</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>5.000000e+07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>5.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Maestro Technologies</td>\n",
       "      <td>5.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>SMB(51-500 Employees)</td>\n",
       "      <td>2003</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>1.000000e+07</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Jane Street</td>\n",
       "      <td>4.8</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>Mid-Market(501-5,000 Employees)</td>\n",
       "      <td>2000</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Investment Banking &amp; Asset Management</td>\n",
       "      <td>Finance</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>GNY Insurance Companies</td>\n",
       "      <td>3.7</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>1914</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Insurance Carriers</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>5.000000e+08</td>\n",
       "      <td>True</td>\n",
       "      <td>Travelers, Chubb, Crum &amp; Forster</td>\n",
       "      <td>NY</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>50.0</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",
       "      <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>3884</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Nationwide Children's Hospital</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>1892</td>\n",
       "      <td>Nonprofit Organization</td>\n",
       "      <td>Health Care Services &amp; Hospitals</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>2.000000e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>200.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3893</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5.000000e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3895</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5.000000e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3902</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Varsity Tutors</td>\n",
       "      <td>4.2</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Internet</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3908</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>5.000000e+09</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "      <td>OH</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>500.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6596 rows × 17 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Job Title                    Company Name  Rating  \\\n",
       "0      Data Engineer                         Sagence     4.5   \n",
       "1      Data Engineer          Enterprise Integration     3.4   \n",
       "2      Data Engineer            Maestro Technologies     5.0   \n",
       "3      Data Engineer                     Jane Street     4.8   \n",
       "4      Data Engineer         GNY Insurance Companies     3.7   \n",
       "...              ...                             ...     ...   \n",
       "3884  Data Scientist  Nationwide Children's Hospital     3.7   \n",
       "3893  Data Scientist                             UCB     3.7   \n",
       "3895  Data Scientist                             UCB     3.7   \n",
       "3902  Data Scientist                  Varsity Tutors     4.2   \n",
       "3908  Data Scientist                             UCB     3.7   \n",
       "\n",
       "      Salary Estimate Min  Salary Estimate Max      Location  \\\n",
       "0                   80000             150000.0  New York, NY   \n",
       "1                   80000             150000.0  New York, NY   \n",
       "2                   80000             150000.0  New York, NY   \n",
       "3                   80000             150000.0  New York, NY   \n",
       "4                   80000             150000.0  New York, NY   \n",
       "...                   ...                  ...           ...   \n",
       "3884                55000             112000.0  Columbus, OH   \n",
       "3893                55000             112000.0    Slough, OH   \n",
       "3895                55000             112000.0    Slough, OH   \n",
       "3902                55000             112000.0  Columbus, OH   \n",
       "3908                55000             112000.0    Slough, OH   \n",
       "\n",
       "                                 Size  Founded       Type of ownership  \\\n",
       "0                 VSB (<50 Employees)     2009       Company - Private   \n",
       "1               SMB(51-500 Employees)     1998       Company - Private   \n",
       "2               SMB(51-500 Employees)     2003       Company - Private   \n",
       "3     Mid-Market(501-5,000 Employees)     2000       Company - Private   \n",
       "4                 VSB (<50 Employees)     1914       Company - Private   \n",
       "...                               ...      ...                     ...   \n",
       "3884     Enterprise(5,000+ Employees)     1892  Nonprofit Organization   \n",
       "3893     Enterprise(5,000+ Employees)        0        Company - Public   \n",
       "3895     Enterprise(5,000+ Employees)        0        Company - Public   \n",
       "3902              VSB (<50 Employees)     2007       Company - Private   \n",
       "3908     Enterprise(5,000+ Employees)        0        Company - Public   \n",
       "\n",
       "                                   Industry                     Sector  \\\n",
       "0                                Consulting          Business Services   \n",
       "1                               IT Services     Information Technology   \n",
       "2                               IT Services     Information Technology   \n",
       "3     Investment Banking & Asset Management                    Finance   \n",
       "4                        Insurance Carriers                  Insurance   \n",
       "...                                     ...                        ...   \n",
       "3884       Health Care Services & Hospitals                Health Care   \n",
       "3893              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "3895              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "3902                               Internet     Information Technology   \n",
       "3908              Biotech & Pharmaceuticals  Biotech & Pharmaceuticals   \n",
       "\n",
       "           Revenue Easy Apply                       Competitors State  \\\n",
       "0     2.500000e+07          0               WCI Consulting, PwC    NY   \n",
       "1     5.000000e+07          0                                 0    NY   \n",
       "2     1.000000e+07          0                                 0    NY   \n",
       "3     0.000000e+00          0                                 0    NY   \n",
       "4     5.000000e+08       True  Travelers, Chubb, Crum & Forster    NY   \n",
       "...            ...        ...                               ...   ...   \n",
       "3884  2.000000e+09          0                                 0    OH   \n",
       "3893  5.000000e+09          0                            AbbVie    OH   \n",
       "3895  5.000000e+09          0                            AbbVie    OH   \n",
       "3902  0.000000e+00          0                                 0    OH   \n",
       "3908  5.000000e+09          0                            AbbVie    OH   \n",
       "\n",
       "      Salary Estimate Average  Revenue (millions)  \n",
       "0                    115000.0                 2.5  \n",
       "1                    115000.0                 5.0  \n",
       "2                    115000.0                 1.0  \n",
       "3                    115000.0                 0.0  \n",
       "4                    115000.0                50.0  \n",
       "...                       ...                 ...  \n",
       "3884                  83500.0               200.0  \n",
       "3893                  83500.0               500.0  \n",
       "3895                  83500.0               500.0  \n",
       "3902                  83500.0                 0.0  \n",
       "3908                  83500.0               500.0  \n",
       "\n",
       "[6596 rows x 17 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['Revenue (millions)'] = data['Revenue'] / 10000000\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>Job Title</th>\n",
       "      <th>Company Name</th>\n",
       "      <th>Rating</th>\n",
       "      <th>Salary Estimate Average</th>\n",
       "      <th>Salary Estimate Min</th>\n",
       "      <th>Salary Estimate Max</th>\n",
       "      <th>Location</th>\n",
       "      <th>State</th>\n",
       "      <th>Size</th>\n",
       "      <th>Founded</th>\n",
       "      <th>Type of ownership</th>\n",
       "      <th>Industry</th>\n",
       "      <th>Sector</th>\n",
       "      <th>Revenue (millions)</th>\n",
       "      <th>Easy Apply</th>\n",
       "      <th>Competitors</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Sagence</td>\n",
       "      <td>4.5</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>2009</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Consulting</td>\n",
       "      <td>Business Services</td>\n",
       "      <td>2.5</td>\n",
       "      <td>0</td>\n",
       "      <td>WCI Consulting, PwC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Enterprise Integration</td>\n",
       "      <td>3.4</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>SMB(51-500 Employees)</td>\n",
       "      <td>1998</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Maestro Technologies</td>\n",
       "      <td>5.0</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>SMB(51-500 Employees)</td>\n",
       "      <td>2003</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>IT Services</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>Jane Street</td>\n",
       "      <td>4.8</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>Mid-Market(501-5,000 Employees)</td>\n",
       "      <td>2000</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Investment Banking &amp; Asset Management</td>\n",
       "      <td>Finance</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Data Engineer</td>\n",
       "      <td>GNY Insurance Companies</td>\n",
       "      <td>3.7</td>\n",
       "      <td>115000.0</td>\n",
       "      <td>80000</td>\n",
       "      <td>150000.0</td>\n",
       "      <td>New York, NY</td>\n",
       "      <td>NY</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>1914</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Insurance Carriers</td>\n",
       "      <td>Insurance</td>\n",
       "      <td>50.0</td>\n",
       "      <td>True</td>\n",
       "      <td>Travelers, Chubb, Crum &amp; Forster</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3884</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Nationwide Children's Hospital</td>\n",
       "      <td>3.7</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>1892</td>\n",
       "      <td>Nonprofit Organization</td>\n",
       "      <td>Health Care Services &amp; Hospitals</td>\n",
       "      <td>Health Care</td>\n",
       "      <td>200.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3893</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3895</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3902</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>Varsity Tutors</td>\n",
       "      <td>4.2</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Columbus, OH</td>\n",
       "      <td>OH</td>\n",
       "      <td>VSB (&lt;50 Employees)</td>\n",
       "      <td>2007</td>\n",
       "      <td>Company - Private</td>\n",
       "      <td>Internet</td>\n",
       "      <td>Information Technology</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3908</th>\n",
       "      <td>Data Scientist</td>\n",
       "      <td>UCB</td>\n",
       "      <td>3.7</td>\n",
       "      <td>83500.0</td>\n",
       "      <td>55000</td>\n",
       "      <td>112000.0</td>\n",
       "      <td>Slough, OH</td>\n",
       "      <td>OH</td>\n",
       "      <td>Enterprise(5,000+ Employees)</td>\n",
       "      <td>0</td>\n",
       "      <td>Company - Public</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>Biotech &amp; Pharmaceuticals</td>\n",
       "      <td>500.0</td>\n",
       "      <td>0</td>\n",
       "      <td>AbbVie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6596 rows × 16 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           Job Title                    Company Name  Rating  \\\n",
       "0      Data Engineer                         Sagence     4.5   \n",
       "1      Data Engineer          Enterprise Integration     3.4   \n",
       "2      Data Engineer            Maestro Technologies     5.0   \n",
       "3      Data Engineer                     Jane Street     4.8   \n",
       "4      Data Engineer         GNY Insurance Companies     3.7   \n",
       "...              ...                             ...     ...   \n",
       "3884  Data Scientist  Nationwide Children's Hospital     3.7   \n",
       "3893  Data Scientist                             UCB     3.7   \n",
       "3895  Data Scientist                             UCB     3.7   \n",
       "3902  Data Scientist                  Varsity Tutors     4.2   \n",
       "3908  Data Scientist                             UCB     3.7   \n",
       "\n",
       "      Salary Estimate Average  Salary Estimate Min  Salary Estimate Max  \\\n",
       "0                    115000.0                80000             150000.0   \n",
       "1                    115000.0                80000             150000.0   \n",
       "2                    115000.0                80000             150000.0   \n",
       "3                    115000.0                80000             150000.0   \n",
       "4                    115000.0                80000             150000.0   \n",
       "...                       ...                  ...                  ...   \n",
       "3884                  83500.0                55000             112000.0   \n",
       "3893                  83500.0                55000             112000.0   \n",
       "3895                  83500.0                55000             112000.0   \n",
       "3902                  83500.0                55000             112000.0   \n",
       "3908                  83500.0                55000             112000.0   \n",
       "\n",
       "          Location State                             Size  Founded  \\\n",
       "0     New York, NY    NY              VSB (<50 Employees)     2009   \n",
       "1     New York, NY    NY            SMB(51-500 Employees)     1998   \n",
       "2     New York, NY    NY            SMB(51-500 Employees)     2003   \n",
       "3     New York, NY    NY  Mid-Market(501-5,000 Employees)     2000   \n",
       "4     New York, NY    NY              VSB (<50 Employees)     1914   \n",
       "...            ...   ...                              ...      ...   \n",
       "3884  Columbus, OH    OH     Enterprise(5,000+ Employees)     1892   \n",
       "3893    Slough, OH    OH     Enterprise(5,000+ Employees)        0   \n",
       "3895    Slough, OH    OH     Enterprise(5,000+ Employees)        0   \n",
       "3902  Columbus, OH    OH              VSB (<50 Employees)     2007   \n",
       "3908    Slough, OH    OH     Enterprise(5,000+ Employees)        0   \n",
       "\n",
       "           Type of ownership                               Industry  \\\n",
       "0          Company - Private                             Consulting   \n",
       "1          Company - Private                            IT Services   \n",
       "2          Company - Private                            IT Services   \n",
       "3          Company - Private  Investment Banking & Asset Management   \n",
       "4          Company - Private                     Insurance Carriers   \n",
       "...                      ...                                    ...   \n",
       "3884  Nonprofit Organization       Health Care Services & Hospitals   \n",
       "3893        Company - Public              Biotech & Pharmaceuticals   \n",
       "3895        Company - Public              Biotech & Pharmaceuticals   \n",
       "3902       Company - Private                               Internet   \n",
       "3908        Company - Public              Biotech & Pharmaceuticals   \n",
       "\n",
       "                         Sector  Revenue (millions) Easy Apply  \\\n",
       "0             Business Services                 2.5          0   \n",
       "1        Information Technology                 5.0          0   \n",
       "2        Information Technology                 1.0          0   \n",
       "3                       Finance                 0.0          0   \n",
       "4                     Insurance                50.0       True   \n",
       "...                         ...                 ...        ...   \n",
       "3884                Health Care               200.0          0   \n",
       "3893  Biotech & Pharmaceuticals               500.0          0   \n",
       "3895  Biotech & Pharmaceuticals               500.0          0   \n",
       "3902     Information Technology                 0.0          0   \n",
       "3908  Biotech & Pharmaceuticals               500.0          0   \n",
       "\n",
       "                           Competitors  \n",
       "0                  WCI Consulting, PwC  \n",
       "1                                    0  \n",
       "2                                    0  \n",
       "3                                    0  \n",
       "4     Travelers, Chubb, Crum & Forster  \n",
       "...                                ...  \n",
       "3884                                 0  \n",
       "3893                            AbbVie  \n",
       "3895                            AbbVie  \n",
       "3902                                 0  \n",
       "3908                            AbbVie  \n",
       "\n",
       "[6596 rows x 16 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Final columns to work with\n",
    "data = data[['Job Title','Company Name','Rating','Salary Estimate Average','Salary Estimate Min','Salary Estimate Max','Location','State','Size','Founded','Type of ownership','Industry','Sector','Revenue (millions)','Easy Apply','Competitors']]\n",
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_csv(\"output/data.csv\", index = False)"
   ]
  },
  {
   "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}