Insight 1.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "import os\n",
    "from spark_utils import *\n",
    "from pyspark.sql import functions as F\n",
    "\n",
    "spark = create_spark_session()\n",
    "\n",
    "bucket = 's3a://helenaudacitybucket'\n",
    "load_pw = spark.read.parquet(os.path.join(bucket, 'processed_data', 'PW'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "calculate average salary"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+----------------------+-----------------------+---------+-----+\n",
      "|       BUSINESS_NAME|PRIMARY_WORKSITE_STATE|PRIMARY_WORKSITE_COUNTY|mean_wage|count|\n",
      "+--------------------+----------------------+-----------------------+---------+-----+\n",
      "|  McLaren Bay Region|                    MI|                    BAY| 280904.0|    1|\n",
      "|Parkersburg Cardi...|                    WV|                   WOOD| 269173.0|    1|\n",
      "|CENTRAL CALIFORNI...|                    CA|                 FRESNO| 257067.0|    1|\n",
      "|       Rentlogic LLC|                    NY|               NEW YORK| 256776.0|    1|\n",
      "|       LMC US Corp. |                    NY|               NEW YORK| 256776.0|    1|\n",
      "|McIntosh Trail Co...|                    GA|                  HENRY| 255362.0|    1|\n",
      "|Mcintosh Trail Co...|                    GA|                  HENRY| 255362.0|    1|\n",
      "|BDT CAPITAL PARTN...|                    NY|               NEW YORK| 254405.0|    1|\n",
      "|Argus Information...|                    NY|            WESTCHESTER| 254405.0|    1|\n",
      "|        BCAM USA LLC|                    NY|               NEW YORK| 254405.0|    1|\n",
      "|        BDO USA, LLP|                    NY|               NEW YORK| 254405.0|    1|\n",
      "| Prosek Partners LLC|                    NY|               NEW YORK| 254405.0|    2|\n",
      "| Greenhill & Co. LLC|                    NY|               NEW YORK| 254405.0|    1|\n",
      "|  Project: Worldwide|                    NY|               NEW YORK| 254405.0|    1|\n",
      "| MEAG New York Corp.|                    NY|               NEW YORK| 254405.0|    1|\n",
      "|INVESTCORP INTERN...|                    NY|               NEW YORK| 254405.0|    3|\n",
      "|Intuition Publish...|                    NY|               NEW YORK| 254405.0|    1|\n",
      "|Allianz Global In...|                    NY|               NEW YORK| 254405.0|    1|\n",
      "|ALLIANZ GLOBAL IN...|                    NY|               NEW YORK| 254405.0|    1|\n",
      "| El Ad Holding, Inc.|                    NY|               NEW YORK| 254405.0|    1|\n",
      "+--------------------+----------------------+-----------------------+---------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "load_pw.groupBy(\"BUSINESS_NAME\", \"PRIMARY_WORKSITE_STATE\", \"PRIMARY_WORKSITE_COUNTY\").\\\n",
    "agg(F.avg('PWD_WAGE_RATE').alias('mean_wage'), \n",
    "    F.count('PWD_WAGE_RATE').alias('count')\n",
    "   ).orderBy('mean_wage', ascending=False).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "load_lca = spark.read.parquet(os.path.join(bucket, 'processed_data', 'LCA'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "editable": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------------+---------------+--------------------+-----+\n",
      "|       EMPLOYER_NAME|WORKSITE_STATE|WORKSITE_COUNTY|           mean_wage|count|\n",
      "+--------------------+--------------+---------------+--------------------+-----+\n",
      "|THE NEW YORK STEM...|            NY|       NEW YORK|              6.05E9|    1|\n",
      "|EZEN COMPUTER SER...|            IL|         DUPAGE|           4.77775E9|    1|\n",
      "|      TEKPLANT, INC.|            NY|    WESTCHESTER|1.5334023333333333E9|    3|\n",
      "|Missouri State Un...|            MO|         GREENE|          1.069985E9|    2|\n",
      "|     AssureCare, LLC|            OH|       HAMILTON|       4.752307789E8|   10|\n",
      "|       Sutter Health|            CA|     SACRAMENTO|4.1023092609090906E8|   11|\n",
      "|        NetApp, Inc.|            CA|    SANTA CLARA| 3.183381066363636E8|   22|\n",
      "|Millennium Softwa...|            GA|         FULTON|              1.25E7|    1|\n",
      "|The Solomon R. Gu...|            NY|       NEW YORK|           9536000.0|    1|\n",
      "|Precision Psychia...|            CA|         TULARE|           7543500.0|    1|\n",
      "|Independent Physi...|            GA|       SPALDING|           7500000.0|    2|\n",
      "|Cogent Healthcare...|            GA|           BIBB|           7500000.0|    1|\n",
      "|Hospitalist Medic...|            CA|    SAN JOAQUIN|   7354000.000000001|    1|\n",
      "|NATIONAL BUREAU O...|            MA| CAMBRIDGE CITY|           7266250.0|    2|\n",
      "|Parkview Health S...|            IN|          ALLEN|   7104000.000000001|    2|\n",
      "|Lonestar Hospital...|            TX|        EL PASO|           7000000.0|    1|\n",
      "|Northwest Hospita...|            NM|       DONA ANA|           7000000.0|    1|\n",
      "|Cogent Medical Ca...|            NY|         BROOME|           6867000.0|    1|\n",
      "|Hospitalist Medic...|            OH|         BUTLER|           6750000.0|    1|\n",
      "|Cogent Healthcare...|            SC|          AIKEN|   6620999.999999999|    1|\n",
      "+--------------------+--------------+---------------+--------------------+-----+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "load_lca.groupBy(\"EMPLOYER_NAME\", \"WORKSITE_STATE\", \"WORKSITE_COUNTY\").\\\n",
    "agg(F.avg('ANNUAL_INCOME').alias('mean_wage'), \n",
    "    F.count('ANNUAL_INCOME').alias('count')\n",
    "   ).orderBy('mean_wage', ascending=False).show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "editable": true
   },
   "source": [
    "*Take home message*: Doctors' compensation can be really high.\n",
    "Also, the maximum income we can see from prevailing wages dataset is generally lower than the LCA dataset.\n",
    "\n",
    "By definition, The prevailing wage is used as a measure of the minimum allowable wage to be paid by employers seeking to employ a foreign national in H-1B status. It is a calculation of the average wage rate paid by employers to similarly-employed workers in substantially comparable jobs in the geographic area of intended employment. \n",
    "\n",
    "Probably Department of Labor removed some high-income outliers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "editable": true
   },
   "outputs": [],
   "source": [
    "# alternatively, we can use\n",
    "\n",
    "# view = 'PW'\n",
    "# load_pw.createOrReplaceTempView(view)\n",
    "# output = spark.sql(\"\"\"\n",
    "# SELECT BUSINESS_NAME, PRIMARY_WORKSITE_STATE, PRIMARY_WORKSITE_COUNTY, \n",
    "# AVG(PWD_WAGE_RATE) AS AVE_WAGE, COUNT(PWD_WAGE_RATE) AS COUNT\n",
    "# FROM PW  \n",
    "# GROUP BY BUSINESS_NAME, PRIMARY_WORKSITE_STATE, PRIMARY_WORKSITE_COUNTY\n",
    "# ORDER BY AVE_WAGE DESC\n",
    "# LIMIT 10\n",
    "# \"\"\")\n",
    "# output.limit(100).toPandas()"
   ]
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}