data_3.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "import findspark\n",
    "\n",
    "findspark.init()\n",
    "spark = SparkSession.builder.appName('challenge').config('spark.sql.legacy.timeParserPolicy','LEGACY').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_3 = spark.read.csv('./salary_data/data_3.csv', header=True, multiLine=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Timestamp: string (nullable = true)\n",
      " |-- Employer: string (nullable = true)\n",
      " |-- Location: string (nullable = true)\n",
      " |-- Job Title: string (nullable = true)\n",
      " |-- Years at Employer: string (nullable = true)\n",
      " |-- Years of Experience: string (nullable = true)\n",
      " |-- Annual Base Pay: string (nullable = true)\n",
      " |-- Signing Bonus: string (nullable = true)\n",
      " |-- Annual Bonus: string (nullable = true)\n",
      " |-- Annual Stock Value/Bonus: string (nullable = true)\n",
      " |-- Gender: string (nullable = true)\n",
      " |-- Additional Comments: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "data_3.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from functools import reduce\n",
    "better_names = ['timestamp','employer','location','job_title','years_at_employer','years_of_experience','annual_base_pay','signing_bonus','annual_bonus','annual_stock_value','gender','additional_comments']\n",
    "data_3 = reduce(lambda df, params: df.withColumnRenamed(*params), zip(data_3.columns, better_names), data_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- timestamp: string (nullable = true)\n",
      " |-- employer: string (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- job_title: string (nullable = true)\n",
      " |-- years_at_employer: string (nullable = true)\n",
      " |-- years_of_experience: string (nullable = true)\n",
      " |-- annual_base_pay: string (nullable = true)\n",
      " |-- signing_bonus: string (nullable = true)\n",
      " |-- annual_bonus: string (nullable = true)\n",
      " |-- annual_stock_value: string (nullable = true)\n",
      " |-- gender: string (nullable = true)\n",
      " |-- additional_comments: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "data_3.createOrReplaceTempView('data_3')\n",
    "data_3.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------------------------+------------------+\n",
      "|to_timestamp(timestamp, M/d/yyyy HH:mm:ss)|         timestamp|\n",
      "+------------------------------------------+------------------+\n",
      "|                                      null|              null|\n",
      "|                       2016-03-21 12:54:49|3/21/2016 12:54:49|\n",
      "|                       2016-03-21 12:58:52|3/21/2016 12:58:52|\n",
      "|                       2016-03-21 12:58:57|3/21/2016 12:58:57|\n",
      "|                       2016-03-21 12:58:58|3/21/2016 12:58:58|\n",
      "|                       2016-03-21 12:59:11|3/21/2016 12:59:11|\n",
      "|                       2016-03-21 12:59:30|3/21/2016 12:59:30|\n",
      "|                       2016-03-21 13:00:17|3/21/2016 13:00:17|\n",
      "|                       2016-03-21 13:00:20|3/21/2016 13:00:20|\n",
      "|                       2016-03-21 13:01:19|3/21/2016 13:01:19|\n",
      "|                       2016-03-21 13:01:19|3/21/2016 13:01:19|\n",
      "|                       2016-03-21 13:02:01|3/21/2016 13:02:01|\n",
      "|                       2016-03-21 13:02:04|3/21/2016 13:02:04|\n",
      "|                       2016-03-21 13:02:08|3/21/2016 13:02:08|\n",
      "|                       2016-03-21 13:03:03|3/21/2016 13:03:03|\n",
      "|                       2016-03-21 13:03:06|3/21/2016 13:03:06|\n",
      "|                       2016-03-21 13:03:23|3/21/2016 13:03:23|\n",
      "|                       2016-03-21 13:03:35|3/21/2016 13:03:35|\n",
      "|                       2016-03-21 13:03:52|3/21/2016 13:03:52|\n",
      "|                       2016-03-21 13:03:56|3/21/2016 13:03:56|\n",
      "+------------------------------------------+------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql('''\n",
    "    SELECT to_timestamp(timestamp, 'M/d/yyyy HH:mm:ss'), timestamp\n",
    "    FROM data_3\n",
    "''').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This dataset is by far the most difficult to work with because there are many data informed in a \"troll\" way and should be discarded for any valid analysis. Due to time constraints, this cases were not removed from the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------------------------------+\n",
      "|initcap(location)                            |\n",
      "+---------------------------------------------+\n",
      "|94102                                        |\n",
      "|Brisbane, Australia                          |\n",
      "|Alexandria, Va                               |\n",
      "|Bangalore                                    |\n",
      "|70170                                        |\n",
      "|Somerville, Ma                               |\n",
      "|Utah                                         |\n",
      "|Nigga                                        |\n",
      "|Did                                          |\n",
      "|Woburn, Ma                                   |\n",
      "|Charleston                                   |\n",
      "|Paraguay                                     |\n",
      "|Wellington                                   |\n",
      "|Russia                                       |\n",
      "|Alpharetta, Ga                               |\n",
      "|Radford, Va                                  |\n",
      "|판교                                         |\n",
      "|Remote - North America                       |\n",
      "|Phoenix                                      |\n",
      "|Kochi                                        |\n",
      "|Ithaca, Ny                                   |\n",
      "|Hogschwarts                                  |\n",
      "|Chattanooga, Tn                              |\n",
      "|Waltham                                      |\n",
      "|Menlo Park                                   |\n",
      "|Cluj-napoca, Romania                         |\n",
      "|Greenville, Sc                               |\n",
      "|Chicago Il                                   |\n",
      "|Việt Nam                                     |\n",
      "|Palo Alto                                    |\n",
      "|Ca                                           |\n",
      "|Minnesota                                    |\n",
      "|Sam Diego                                    |\n",
      "|Porto - Portugal                             |\n",
      "|New York, New York                           |\n",
      "|Madrid                                       |\n",
      "|Omaha                                        |\n",
      "|Johannesburg, South Africa                   |\n",
      "|Prague                                       |\n",
      "|Product Engineer                             |\n",
      "|Baltimore, Maryland                          |\n",
      "|Bothell                                      |\n",
      "|Sweden                                       |\n",
      "|New Hork                                     |\n",
      "|Pa                                           |\n",
      "|The Netherlands                              |\n",
      "|Rolla, Mo, Usa                               |\n",
      "|Oslo, Norway                                 |\n",
      "|Ohio                                         |\n",
      "|Des Moines, Ia                               |\n",
      "|Indianapolis, In                             |\n",
      "|Rennes, Fr                                   |\n",
      "|Glendale, Ca                                 |\n",
      "|Foster City, Ca                              |\n",
      "|Indianapolis In                              |\n",
      "|New York City, Ny                            |\n",
      "|Stuart, Fl                                   |\n",
      "|Halifax, Ns, Canada                          |\n",
      "|Moutain View                                 |\n",
      "|Perth, Western Australia                     |\n",
      "|Bellevue Wa                                  |\n",
      "|Everett                                      |\n",
      "|South Germany                                |\n",
      "|Ottawa                                       |\n",
      "|Toronto, On, Canada                          |\n",
      "|Philippines                                  |\n",
      "|Edinburgh                                    |\n",
      "|Salamanca, Spain                             |\n",
      "|Sewttle                                      |\n",
      "|Zaragoza, España                             |\n",
      "|Shibuya                                      |\n",
      "|Palo Alto, Ca                                |\n",
      "|Dallas                                       |\n",
      "|Greenville Sc                                |\n",
      "|Remote                                       |\n",
      "|Nordrhein-westfalen                          |\n",
      "|Aaaa                                         |\n",
      "|Singapore                                    |\n",
      "|Malaysia                                     |\n",
      "|Mount Laurel                                 |\n",
      "|Dallas Tx                                    |\n",
      "|음                                           |\n",
      "|Cupertino                                    |\n",
      "|Oakland                                      |\n",
      "|Turkey                                       |\n",
      "|Bc, Canada                                   |\n",
      "|Pittsburgh, Pa                               |\n",
      "|Washington Dc                                |\n",
      "|Irving, Tx                                   |\n",
      "|Germany, Frankfurt                           |\n",
      "|Davis                                        |\n",
      "|Jakarta                                      |\n",
      "|Bangalore India                              |\n",
      "|Geneva, Switzerland                          |\n",
      "|Midwest, Usa                                 |\n",
      "|Meridian, Id                                 |\n",
      "|Frankfurt                                    |\n",
      "|Toulouse, France                             |\n",
      "|Bay Area, Ca                                 |\n",
      "|Reading, Uk                                  |\n",
      "|Silicon Valley, Ca                           |\n",
      "|Hatfield, Pa                                 |\n",
      "|Buffalo, Ny                                  |\n",
      "|Montreal, Quebec, Canada                     |\n",
      "|Irvine, Ca                                   |\n",
      "|Washington, Usa                              |\n",
      "|Melbourne, Vic, Au                           |\n",
      "|Indianapolis, In, Usa                        |\n",
      "|Auckland, New Zealand                        |\n",
      "|Cambridge Ma                                 |\n",
      "|Beijing                                      |\n",
      "|Columbus Ohio                                |\n",
      "|Boulder, Colorado                            |\n",
      "|Jan Jose                                     |\n",
      "|Cleveland, Oh                                |\n",
      "|Warsaw, Poland                               |\n",
      "|Germany                                      |\n",
      "|Redwood City, Ca                             |\n",
      "|Pasadena, Ca                                 |\n",
      "|St. Paul, Mn                                 |\n",
      "|Bern                                         |\n",
      "|Europe                                       |\n",
      "|Northern Va                                  |\n",
      "|Oregon                                       |\n",
      "|Southern Sweden                              |\n",
      "|Schenectady, Ny                              |\n",
      "|Arkansas                                     |\n",
      "|Remote/everywhere/usa                        |\n",
      "|Lisbon                                       |\n",
      "|San Antonio                                  |\n",
      "|Boston Metro                                 |\n",
      "|Pleasanton                                   |\n",
      "|Raleigh                                      |\n",
      "|Sunnyvale Ca.                                |\n",
      "|Lyon, France                                 |\n",
      "|Sunnyvale, California                        |\n",
      "|Sunnyvale, Ca, Usa                           |\n",
      "|Philadelphia                                 |\n",
      "|Stockholm                                    |\n",
      "|Poughkeepsie, Ny                             |\n",
      "|Oklahoma City, Ok                            |\n",
      "|Dortmund, Germany                            |\n",
      "|Amsterdam Area (remote From Bucharest)       |\n",
      "|Hanoi, Vietnam                               |\n",
      "|Berkeley Ca                                  |\n",
      "|Jordan                                       |\n",
      "|Ankara, Turkey                               |\n",
      "|Auckland                                     |\n",
      "|Lehi, Ut                                     |\n",
      "|Constanta, Romania                           |\n",
      "|Q                                            |\n",
      "|Ruhrgebiet, Germany                          |\n",
      "|Mill Valley, Ca                              |\n",
      "|Germany, Brunswick                           |\n",
      "|Israel, Tel Aviv                             |\n",
      "|Saint-petersburg, Russia                     |\n",
      "|Boulder Co                                   |\n",
      "|Los Angeles                                  |\n",
      "|Issaquah                                     |\n",
      "|Sydney, Au                                   |\n",
      "|Dallas Texas Usa                             |\n",
      "|Glasgow                                      |\n",
      "|Louisville,ky                                |\n",
      "|Solon, Oh                                    |\n",
      "|Leeds, England                               |\n",
      "|Missor, Usa                                  |\n",
      "|Cambridge                                    |\n",
      "|Sydney, Australi                             |\n",
      "|France                                       |\n",
      "|Lexington, Ky                                |\n",
      "|Owego, Ny                                    |\n",
      "|Manila, Philippines                          |\n",
      "|Nw                                           |\n",
      "|Greece                                       |\n",
      "|Kingston, Canada                             |\n",
      "|Chiapas                                      |\n",
      "|Belgrade                                     |\n",
      "|Norwalk, Ct                                  |\n",
      "|Graz, Austria                                |\n",
      "|Grand Rapids                                 |\n",
      "|Moscow                                       |\n",
      "|Birmingham, Al                               |\n",
      "|Boston, Massachusetts                        |\n",
      "|Bengaluru, India                             |\n",
      "|Oslo                                         |\n",
      "|Sterling, Va                                 |\n",
      "|0                                            |\n",
      "|Midwest                                      |\n",
      "|Chennai                                      |\n",
      "|30040                                        |\n",
      "|Toronto, Canada                              |\n",
      "|Montreal, Canada                             |\n",
      "|Leeds, United Kingdom                        |\n",
      "|Lenexa, Ks                                   |\n",
      "|Texas                                        |\n",
      "|Ridgecrest                                   |\n",
      "|Cramlington, Uk                              |\n",
      "|Windsor, Ontario; Canada                     |\n",
      "|Greater Boston Area                          |\n",
      "|Orlando,fl                                   |\n",
      "|Lansing, Mi                                  |\n",
      "|Dallas, Tx                                   |\n",
      "|Pune, India                                  |\n",
      "|Indianapolis                                 |\n",
      "|Taiwan                                       |\n",
      "|Compton Ca                                   |\n",
      "|Chandler, Az                                 |\n",
      "|New Delhi, India                             |\n",
      "|Sfo                                          |\n",
      "|Va                                           |\n",
      "|Dublin                                       |\n",
      "|Nice, France                                 |\n",
      "|G3q3g                                        |\n",
      "|Denver, Co                                   |\n",
      "|Glasgow, Uk                                  |\n",
      "|Victoria                                     |\n",
      "|Cleveland                                    |\n",
      "|null                                         |\n",
      "|San Francisco                                |\n",
      "|Berlin                                       |\n",
      "|Nashville                                    |\n",
      "|Slovakia                                     |\n",
      "|Richmond, Va                                 |\n",
      "|San Diego                                    |\n",
      "|Fairfax, Va                                  |\n",
      "|Kuala Lumpur                                 |\n",
      "|Oklahoma City                                |\n",
      "|New Haven, Ct, Usa                           |\n",
      "|New York, Ny                                 |\n",
      "|Omaha, Ne                                    |\n",
      "|Orange County, Ca                            |\n",
      "|Vancouver Canada                             |\n",
      "|Falls Church, Va, Usa                        |\n",
      "|Arbedo                                       |\n",
      "|Bristol, Uk                                  |\n",
      "|Argentina                                    |\n",
      "|Westminster, Co                              |\n",
      "|Malaga, Spain                                |\n",
      "|St. Louis, Mo                                |\n",
      "|Central New York                             |\n",
      "|Belgium                                      |\n",
      "|Remote (midwest)                             |\n",
      "|Remote (sf Company)                          |\n",
      "|Johnston, Iowa                               |\n",
      "|Lisbon, Portugal                             |\n",
      "|Albuquerque, Nm                              |\n",
      "|Dc                                           |\n",
      "|Not Sf                                       |\n",
      "|Glasgow, Scotland                            |\n",
      "|Detroit                                      |\n",
      "|Berchtesgaden                                |\n",
      "|Houston, Tx                                  |\n",
      "|Rochester Ny                                 |\n",
      "|Cambridge, Uk                                |\n",
      "|Centennial, Co                               |\n",
      "|Portland                                     |\n",
      "|Mumbai, India                                |\n",
      "|Krakow, Poland                               |\n",
      "|Connecticut                                  |\n",
      "|Detroit, Mi                                  |\n",
      "|Englewood, Co                                |\n",
      "|Bellingham Wa                                |\n",
      "|Trento, Italy                                |\n",
      "|Bowling Green, Ky                            |\n",
      "|Tx                                           |\n",
      "|Phoenix Az                                   |\n",
      "|Eindhoven, The Netherlands                   |\n",
      "|Calgary, Alberta                             |\n",
      "|Dubai, United Arab Emirates                  |\n",
      "|London                                       |\n",
      "|Albuquerque, New Mexico                      |\n",
      "|Costa Mesa, California                       |\n",
      "|Adelaide, Au                                 |\n",
      "|Russia, Spb                                  |\n",
      "|Eire                                         |\n",
      "|San Jose, Ca                                 |\n",
      "|Rennes, France                               |\n",
      "|Nebraska                                     |\n",
      "|Bavaria                                      |\n",
      "|Mumbai                                       |\n",
      "|Boise                                        |\n",
      "|Turin, Italy                                 |\n",
      "|Vermont                                      |\n",
      "|98004                                        |\n",
      "|Bilbao, Spain                                |\n",
      "|Moscow, Russia                               |\n",
      "|St. Louis                                    |\n",
      "|55414                                        |\n",
      "|Toronto, On                                  |\n",
      "|Bethlehem, Pa                                |\n",
      "|Finland                                      |\n",
      "|São Paulo, Brazil                            |\n",
      "|Sf                                           |\n",
      "|Gilbert, Az                                  |\n",
      "|Boulder, Co                                  |\n",
      "|Des Moines, Iowa                             |\n",
      "|Londnon                                      |\n",
      "|Montreal, Qc, Canada                         |\n",
      "|Exton, Pa                                    |\n",
      "|Brisbane                                     |\n",
      "|Augusta, Me                                  |\n",
      "|Valencia, Spain                              |\n",
      "|Santa Clara                                  |\n",
      "|Louisville, Ky                               |\n",
      "|Sydney                                       |\n",
      "|Montreal                                     |\n",
      "|Herndon, Va                                  |\n",
      "|Cincinnati, Oh                               |\n",
      "|Tokyo                                        |\n",
      "|Greater Los Angeles Area                     |\n",
      "|Vienna                                       |\n",
      "|Pleasanton, Ca                               |\n",
      "|Peru                                         |\n",
      "|Edegem                                       |\n",
      "|Jblm, Wa                                     |\n",
      "|Wilmington Nc                                |\n",
      "|United States                                |\n",
      "|Hillsboro                                    |\n",
      "|Amsterdam, The Netherlands                   |\n",
      "|Boise, Id (remote, Job Is Based In Nyc)      |\n",
      "|India                                        |\n",
      "|San Bruno                                    |\n",
      "|China                                        |\n",
      "|Dfgdfg                                       |\n",
      "|Bloomington, Mn                              |\n",
      "|Nottingham Uk                                |\n",
      "|Reno Nevafa                                  |\n",
      "|Atlabta                                      |\n",
      "|Irvine                                       |\n",
      "|Leeds, West Yorkshire, United Kingdom        |\n",
      "|Salford                                      |\n",
      "|Leicester                                    |\n",
      "|Verona, Wisconsin                            |\n",
      "|Athens, Greece (remote)                      |\n",
      "|Vancouver, Ca                                |\n",
      "|Curitiba, Brazil                             |\n",
      "|Malta                                        |\n",
      "|Kitchener, On                                |\n",
      "|St Petersburg, Florida, Us                   |\n",
      "|Sunnyvale Ca                                 |\n",
      "|Oakland, Ca                                  |\n",
      "|Arlington, Va                                |\n",
      "|San Francisco Bay Area                       |\n",
      "|Bucharest                                    |\n",
      "|Foobar                                       |\n",
      "|Bosnia                                       |\n",
      "|Memphis                                      |\n",
      "|Kitchener, Canada                            |\n",
      "|高雄                                         |\n",
      "|Vancouver, B.c.                              |\n",
      "|Columbus, Oh                                 |\n",
      "|Tunis                                        |\n",
      "|Remote (poland)                              |\n",
      "|Manchester, Uk                               |\n",
      "|Salt Lake City, Utah                         |\n",
      "|Coventry, Uk                                 |\n",
      "|Boulder                                      |\n",
      "|Frankfurt Am Main, Germany                   |\n",
      "|Puerto Rico                                  |\n",
      "|Champaign, Il                                |\n",
      "|Chile                                        |\n",
      "|Montvale Nj                                  |\n",
      "|Aveiro, Portugal                             |\n",
      "|Austin                                       |\n",
      "|Wilton, Ct Usa                               |\n",
      "|Los Angles                                   |\n",
      "|Delray Beach, Fl                             |\n",
      "|Bremerhaven, Germany                         |\n",
      "|Adelaide, Australia                          |\n",
      "|Melbourne, Australia                         |\n",
      "|Virginia-washington Area                     |\n",
      "|Washington D.c.                              |\n",
      "|Huntsville, Al                               |\n",
      "|Cape Town                                    |\n",
      "|Some Dude's Anus                             |\n",
      "|Needham, Massachusetts                       |\n",
      "|Bachelor Of Science, Informathics            |\n",
      "|Bialystok, Poland                            |\n",
      "|Washington, D.c.                             |\n",
      "|Stanford, Ca                                 |\n",
      "|Telecommute                                  |\n",
      "|Oklahoma                                     |\n",
      "|Gaithersburg, Md                             |\n",
      "|Milwaukee                                    |\n",
      "|San Juan, Pr                                 |\n",
      "|Warwickshire                                 |\n",
      "|Richmond, Surrey                             |\n",
      "|Atlanta, Georgia                             |\n",
      "|Pasadena                                     |\n",
      "|Easton, Pa                                   |\n",
      "|Pittsburgh                                   |\n",
      "|Cupertino Ca                                 |\n",
      "|Palo Alto California                         |\n",
      "|Hyderabad, India                             |\n",
      "|제주                                         |\n",
      "|95814                                        |\n",
      "|Olympia, Wa                                  |\n",
      "|Sunnyvale, Ca                                |\n",
      "|Fairfax Va                                   |\n",
      "|Noida                                        |\n",
      "|Houston Tx                                   |\n",
      "|Cheonan                                      |\n",
      "|San Diego, Ca                                |\n",
      "|Cambridge, Ma                                |\n",
      "|Paris                                        |\n",
      "|98102                                        |\n",
      "|Montreal, Qc                                 |\n",
      "|Dc-va-md Area                                |\n",
      "|Redwood Shores                               |\n",
      "|Pisa, Italy                                  |\n",
      "|Kingsport, Tn                                |\n",
      "|Williamsburg                                 |\n",
      "|Waterloo, On                                 |\n",
      "|Near Canada But Not Canada                   |\n",
      "|Test                                         |\n",
      "|Lausanne, Switzerland                        |\n",
      "|Italy                                        |\n",
      "|Tampa, Fl                                    |\n",
      "|Columbus, Oh, Usa                            |\n",
      "|Poughkeepsie                                 |\n",
      "|Radnor, Pa                                   |\n",
      "|Nsw                                          |\n",
      "|Winnipeg, Mb Canada                          |\n",
      "|Chicago                                      |\n",
      "|Plano Tx                                     |\n",
      "|Romania, But I Work Remotely For A Us Company|\n",
      "|Hamburg                                      |\n",
      "|Culver City, Ca                              |\n",
      "|Reno, Nevada                                 |\n",
      "|Santa Monica, Ca                             |\n",
      "|Uk, Outside London                           |\n",
      "|Brighton, United Kingdom                     |\n",
      "|Tampa                                        |\n",
      "|San Fransico                                 |\n",
      "|Jönköping, Sweden                            |\n",
      "|Ängelholm, Sweden                            |\n",
      "|Dhaka, Bangladesh                            |\n",
      "|Lithuania                                    |\n",
      "|Austin, Texas                                |\n",
      "|Norway                                       |\n",
      "|Livermore                                    |\n",
      "|넥센                                         |\n",
      "|강남                                         |\n",
      "|Scottsdale, Az                               |\n",
      "|Calgary, Ab                                  |\n",
      "|Redmon                                       |\n",
      "|Spain                                        |\n",
      "|Edmonton, Alberta, Canada                    |\n",
      "|Newark                                       |\n",
      "|Zurich                                       |\n",
      "|Belgorod, Russia                             |\n",
      "|Bellevue, Wa, Usa                            |\n",
      "|Colorado Springs, Co                         |\n",
      "|Vancouver Bc                                 |\n",
      "|Phoenix, Arizona                             |\n",
      "|Bellingham, Wa                               |\n",
      "|Roma                                         |\n",
      "|Fort Lauderdale                              |\n",
      "|Ostrava, Czech Republic                      |\n",
      "|Ct                                           |\n",
      "|Philadelphia, Pa                             |\n",
      "|Boca Raton, Fl                               |\n",
      "|Dc Area                                      |\n",
      "|Milan                                        |\n",
      "|Krakow                                       |\n",
      "|Montréal                                     |\n",
      "|Norfolk, Va                                  |\n",
      "|Fl                                           |\n",
      "|Switzerland Zurich                           |\n",
      "|Toronto                                      |\n",
      "|Istanbul                                     |\n",
      "|Bali                                         |\n",
      "|Greensboro, Nc                               |\n",
      "|Hebron, Oh                                   |\n",
      "|Dublin Ireland                               |\n",
      "|Denmark                                      |\n",
      "|Fairport Ny                                  |\n",
      "|Remote (taiwan)                              |\n",
      "|Hursley, Uk                                  |\n",
      "|Grand Prairie, Tx                            |\n",
      "|Hillsboro, Oregon                            |\n",
      "|Chicago, Il                                  |\n",
      "|Barcelona                                    |\n",
      "|I Work Remotely                              |\n",
      "|Shanghai, China                              |\n",
      "|Milan, Italy                                 |\n",
      "|Interior Bc, Canada                          |\n",
      "|Zurich, Switzerland                          |\n",
      "|Beirut, Lebanon                              |\n",
      "|Seattle, Wa                                  |\n",
      "|Dsvao                                        |\n",
      "|Ireland                                      |\n",
      "|München                                      |\n",
      "|Long Island, New York, U.s.                  |\n",
      "|South San Francisco, Ca                      |\n",
      "|London, Uk                                   |\n",
      "|Eindhoven, Netherlands                       |\n",
      "|Guadalajara                                  |\n",
      "|Mclean, Va                                   |\n",
      "|Redmond                                      |\n",
      "|Sao Paulo                                    |\n",
      "|Il                                           |\n",
      "|Bucharest, Romania                           |\n",
      "|Birmingham, Alabama                          |\n",
      "|Broomfield, Co                               |\n",
      "|Monaco                                       |\n",
      "|Morocco                                      |\n",
      "|Kraków, Poland                               |\n",
      "|Ardmore                                      |\n",
      "|Naperville, Il                               |\n",
      "|Las Juntas                                   |\n",
      "|Silicon Valley                               |\n",
      "|Stockholm, Sweden                            |\n",
      "|Anaheim, Ca                                  |\n",
      "|97034                                        |\n",
      "|Campo Grande, Mato Grosso Do Sul, Brazil     |\n",
      "|Hong Kong                                    |\n",
      "|Ann Arbor, Michigan                          |\n",
      "|Durham, Nc                                   |\n",
      "|Amsterdam                                    |\n",
      "|Ne                                           |\n",
      "|Nijmegen                                     |\n",
      "|Buch                                         |\n",
      "|4chinz                                       |\n",
      "|Venezuela                                    |\n",
      "|Rouen, France                                |\n",
      "|Ukraine                                      |\n",
      "|Israel                                       |\n",
      "|Regina, Sk Canada                            |\n",
      "|Olathe, Ks                                   |\n",
      "|Vancouver                                    |\n",
      "|Fayetteville, Ar                             |\n",
      "|Iceland                                      |\n",
      "|Brussels                                     |\n",
      "|Khi                                          |\n",
      "|The Hague                                    |\n",
      "|Houston, Texas                               |\n",
      "|Seattel, Wa                                  |\n",
      "|New Mexico                                   |\n",
      "|Lancaster, Pa                                |\n",
      "|Wallops Island, Virginia                     |\n",
      "|College Park, Md                             |\n",
      "|Atlanta                                      |\n",
      "|Madrid (es)                                  |\n",
      "|을지로                                       |\n",
      "|Madrid, Spain                                |\n",
      "|Knoxviile, Tn                                |\n",
      "|New Orleans                                  |\n",
      "|Salt Lake City, Ut                           |\n",
      "|St Petersburg, Fl                            |\n",
      "|San Fancisco, Ca                             |\n",
      "|Ma                                           |\n",
      "|94086                                        |\n",
      "|Newcastle, Australia                         |\n",
      "|Los Angeles Ca                               |\n",
      "|Bordeaux                                     |\n",
      "|Las Vegas                                    |\n",
      "|Buckinghamshire                              |\n",
      "|Los Alamos, Nm                               |\n",
      "|Austin, Tx                                   |\n",
      "|Springfield, Mo                              |\n",
      "|Grand Rapids Mi                              |\n",
      "|Los Gatos                                    |\n",
      "|Sydney, Australia                            |\n",
      "|London, Uk (working Remote)                  |\n",
      "|Brooklyn                                     |\n",
      "|Cyprus                                       |\n",
      "|Buenos Aires                                 |\n",
      "|Redmond, Wa, Usa                             |\n",
      "|Gibraltar                                    |\n",
      "|San Mateo                                    |\n",
      "|95014                                        |\n",
      "|Folsom, Ca                                   |\n",
      "|Springsfield, Co                             |\n",
      "|Uruguay                                      |\n",
      "|Honolulu                                     |\n",
      "|Roseville, Ca                                |\n",
      "|Mexico                                       |\n",
      "|Edmonton, Ab                                 |\n",
      "|San Fransisco                                |\n",
      "|Rochester                                    |\n",
      "|Lund, Sweden                                 |\n",
      "|Nc                                           |\n",
      "|Western Maryland                             |\n",
      "|Russia, Perm                                 |\n",
      "|Hamburg, De                                  |\n",
      "|Minneapolis, Mn                              |\n",
      "|Helsinki, Finland                            |\n",
      "|San Antonio, Tx                              |\n",
      "|Richfield Mn                                 |\n",
      "|Seattle                                      |\n",
      "|Boston, Ma                                   |\n",
      "|La Mirada, Ca                                |\n",
      "|Estonia                                      |\n",
      "|Kirkland Wa Usa                              |\n",
      "|South San Francisco                          |\n",
      "|Upstate New York                             |\n",
      "|Indonesia                                    |\n",
      "|Redlands                                     |\n",
      "|Montana                                      |\n",
      "|Natick                                       |\n",
      "|St. Paul                                     |\n",
      "|Orange County                                |\n",
      "|Toronto, Ontario, Canada                     |\n",
      "|Vilnius, Lithuania                           |\n",
      "|Jersey City                                  |\n",
      "|Michigan                                     |\n",
      "|Gainesville, Fl                              |\n",
      "|Richmond, Ky                                 |\n",
      "|Virginia                                     |\n",
      "|Ann Arbor, Mi                                |\n",
      "|Waltham, Ma                                  |\n",
      "|Seattle, Usa                                 |\n",
      "|Waterloo, Ontario, Canada                    |\n",
      "|114                                          |\n",
      "|Dublin, Ireland                              |\n",
      "|Gainesville                                  |\n",
      "|W                                            |\n",
      "|Menlo Park, Ca                               |\n",
      "|Joplin Mo                                    |\n",
      "|Sunnyvale                                    |\n",
      "|Istanbul, Turkey                             |\n",
      "|Dubin                                        |\n",
      "|British Columbia                             |\n",
      "|Phoenix, Az                                  |\n",
      "|Cincinatti                                   |\n",
      "|Nrw, Germany                                 |\n",
      "|Greensboro, Nc, Usa                          |\n",
      "|Cheshire, Uk                                 |\n",
      "|New Yrok                                     |\n",
      "|East Coast (not Nyc Or Boston)               |\n",
      "|Uk                                           |\n",
      "|Regina, Sk                                   |\n",
      "|Toulouse (fr)                                |\n",
      "|Victoria, Bc                                 |\n",
      "|Basement                                     |\n",
      "|Germany (remote)                             |\n",
      "|Albany, Ny                                   |\n",
      "|Washington, Dc                               |\n",
      "|Pune                                         |\n",
      "|Delhi                                        |\n",
      "|Saskatchewan, Canada                         |\n",
      "|Not A Coast                                  |\n",
      "|Milwaukee, Wi, Us                            |\n",
      "|Stuttgart                                    |\n",
      "|North Carolina                               |\n",
      "|St Louis, Mo                                 |\n",
      "|Buffalo Ny                                   |\n",
      "|Strasbourg                                   |\n",
      "|Dc Metro Area                                |\n",
      "|Taipei                                       |\n",
      "|Appleton, Wi                                 |\n",
      "|Mountain View, Ca                            |\n",
      "|Livermore, Ca                                |\n",
      "|Melbourne, Au                                |\n",
      "|Norfolk                                      |\n",
      "|Karachi, Pakistan                            |\n",
      "|New York, Ny (nyc)                           |\n",
      "|Kirkland, Wa                                 |\n",
      "|Kansas                                       |\n",
      "|San Feancisco                                |\n",
      "|Bellinzona                                   |\n",
      "|Santa Clara, Ca                              |\n",
      "|Helsinki                                     |\n",
      "|San Mateo, Ca                                |\n",
      "|Louisville, Co                               |\n",
      "|New Jersey                                   |\n",
      "|Oakville, On., Canada                        |\n",
      "|Anacortes, Wa                                |\n",
      "|Minneapolis, Minnesota                       |\n",
      "|Chisinau, Moldova                            |\n",
      "|Shanghai，china                              |\n",
      "|Nyc                                          |\n",
      "|Norman, Ok                                   |\n",
      "|South Of France                              |\n",
      "|Berlin, Germany                              |\n",
      "|Copenhagen                                   |\n",
      "|Calgary                                      |\n",
      "|Sacramento, Ca                               |\n",
      "|Wellington, New Zealand                      |\n",
      "|Barcelona, Spain                             |\n",
      "|Maryland                                     |\n",
      "|Louisville, Ky, Usa                          |\n",
      "|Remote Us                                    |\n",
      "|Kansas City                                  |\n",
      "|San Diego Ca                                 |\n",
      "|Phils                                        |\n",
      "|Arizona                                      |\n",
      "|Bay Area                                     |\n",
      "|Cluj-napoca                                  |\n",
      "|Brighton, Uk                                 |\n",
      "|Nj, Usa                                      |\n",
      "|Windsor, Canada                              |\n",
      "|92705                                        |\n",
      "|Dfw                                          |\n",
      "|Kansai, Japan                                |\n",
      "|Oxford, Uk                                   |\n",
      "|Switzerland                                  |\n",
      "|Northern Virginia                            |\n",
      "|Sulzbach An Der Murr, Germany                |\n",
      "|Berkeley                                     |\n",
      "|Seattle, Wa Usa                              |\n",
      "|Durham                                       |\n",
      "|Redmond, Wa                                  |\n",
      "|Bangalore, India                             |\n",
      "|Eindhoven                                    |\n",
      "|Location                                     |\n",
      "|Latvia                                       |\n",
      "|Porto, Portugal                              |\n",
      "|Athens, Greece                               |\n",
      "|Abu Dhabi                                    |\n",
      "|Strasbourg France                            |\n",
      "|Paris, Fr                                    |\n",
      "|Bentonville, Ar                              |\n",
      "|1                                            |\n",
      "|Missoula                                     |\n",
      "|San Jose                                     |\n",
      "|Cambridge Uk                                 |\n",
      "|Austin Tx                                    |\n",
      "|San Franciaco                                |\n",
      "|Warren, Mi                                   |\n",
      "|Munich, Germany                              |\n",
      "|Charleston, Sc                               |\n",
      "|Lincoln, Ne                                  |\n",
      "|Redwood City                                 |\n",
      "|Melbourne                                    |\n",
      "|98105                                        |\n",
      "|Remote, Southeast Us                         |\n",
      "|Upstate, Ny                                  |\n",
      "|Southampton, Uk                              |\n",
      "|Iowa                                         |\n",
      "|Washington, Dc Area                          |\n",
      "|Madisn, Wi                                   |\n",
      "|Cologne, Germany                             |\n",
      "|İstanbul                                    |\n",
      "|Princeton, Nj                                |\n",
      "|Limerick, Ireland                            |\n",
      "|Poland (remote For Us)                       |\n",
      "|Remote (usa)                                 |\n",
      "|Chiang Mai, Thailand                         |\n",
      "|Victoria Bc                                  |\n",
      "|Canada                                       |\n",
      "|Kansas City, Mo                              |\n",
      "|Tucson                                       |\n",
      "|Yorktown,ny                                  |\n",
      "|Fremont, Ca                                  |\n",
      "|Northern Arkansas                            |\n",
      "|서울                                         |\n",
      "|Massachusetts                                |\n",
      "|Poulsbo, Wa                                  |\n",
      "|Saint Louis, Missouri                        |\n",
      "|American Fork, Ut                            |\n",
      "|Riverside                                    |\n",
      "|Chico Ca                                     |\n",
      "|Berkeley, Ca                                 |\n",
      "|Tasmania, Australia                          |\n",
      "|Guadalajara, Mexico                          |\n",
      "|Bournemouth, Uk                              |\n",
      "|Columbia, Md                                 |\n",
      "|Czech Republic                               |\n",
      "|New York City                                |\n",
      "|Minneapolis                                  |\n",
      "|Bengaluru                                    |\n",
      "|England - Manchester                         |\n",
      "|Here                                         |\n",
      "|Tallinn, Estonia                             |\n",
      "|Cologne / Germany                            |\n",
      "|Newcastle Upon Tyne, Uk                      |\n",
      "|Copenhagen, Denmark                          |\n",
      "|Houston                                      |\n",
      "|Brazil                                       |\n",
      "|Tacoma                                       |\n",
      "|Nairobi                                      |\n",
      "|Lubbock, Tx                                  |\n",
      "|Atlanta, Ga                                  |\n",
      "|Santa Barbara                                |\n",
      "|Las Vegas, Nv                                |\n",
      "|Kenya                                        |\n",
      "|Kentucky                                     |\n",
      "|Seoul                                        |\n",
      "|San Francisco, Ca                            |\n",
      "|Carson City, Nv                              |\n",
      "|Wroclaw                                      |\n",
      "|Lebanon                                      |\n",
      "|Orem, Ut                                     |\n",
      "|Lahore, Pakistan                             |\n",
      "|Sofia, Bulgaria                              |\n",
      "|Secret                                       |\n",
      "|Valencia                                     |\n",
      "|Dalas                                        |\n",
      "|Greer                                        |\n",
      "|Athens, Gr                                   |\n",
      "|Clemson, Sc                                  |\n",
      "|Slovenia                                     |\n",
      "|Rolla, Mo                                    |\n",
      "|Columbus, Ohio                               |\n",
      "|Martinez, California                         |\n",
      "|P                                            |\n",
      "|Mississippi                                  |\n",
      "|London/remote                                |\n",
      "|Redmond, Washington                          |\n",
      "|Dominican Republic                           |\n",
      "|Beijing, China                               |\n",
      "|Geneva                                       |\n",
      "|Växjö, Sweden                                |\n",
      "|Kansas City, Missouri                        |\n",
      "|Fort Collins, Co                             |\n",
      "|Karlstad, Sweden                             |\n",
      "|Miami, Fl                                    |\n",
      "|Bellevue, Wa                                 |\n",
      "|Rural Northeast Usa                          |\n",
      "|Southern California                          |\n",
      "|Bloomington, Il                              |\n",
      "|Livermore, California                        |\n",
      "|Kiev                                         |\n",
      "|Cape Town, South Africa                      |\n",
      "|Bos                                          |\n",
      "|Orlando, Fl                                  |\n",
      "|Baltimore, Md                                |\n",
      "|Rochester, Ny                                |\n",
      "|Grand Rapids, Mi                             |\n",
      "|Bordeaux, France                             |\n",
      "|Vienna, Austria                              |\n",
      "|Japan                                        |\n",
      "|Milwaukee, Wi                                |\n",
      "|St. Louis, Mo, Usa                           |\n",
      "|Usa                                          |\n",
      "|New Hampshire                                |\n",
      "|Asia                                         |\n",
      "|Birmingham, Uk                               |\n",
      "|Ontario, Canada                              |\n",
      "|Verona, Wi                                   |\n",
      "|Mountain West                                |\n",
      "|Pensacola, Fl                                |\n",
      "|Madison, Wi                                  |\n",
      "|Portland, Or                                 |\n",
      "|Dubai                                        |\n",
      "|Bonn                                         |\n",
      "|Buffalo                                      |\n",
      "|Luxembourg                                   |\n",
      "|New Zealand                                  |\n",
      "|Florida                                      |\n",
      "|Woodland Hills                               |\n",
      "|Bedford Ma                                   |\n",
      "|France - Lyon                                |\n",
      "|Frankfurt, Germany                           |\n",
      "|上海                                         |\n",
      "|Tbilisi, Georgia                             |\n",
      "|Boston Ma                                    |\n",
      "|Portland,or                                  |\n",
      "|Foster City                                  |\n",
      "|Brasil                                       |\n",
      "|Hartford, Ct                                 |\n",
      "|Plymouth, Mn                                 |\n",
      "|Richmond, Ca                                 |\n",
      "|South West Uk                                |\n",
      "|San Franciso                                 |\n",
      "|Strasbourg, France                           |\n",
      "|Ukraine, Odessa                              |\n",
      "|Nj, 30 Minutes From Nyc                      |\n",
      "|England                                      |\n",
      "|Tampa, Florida                               |\n",
      "|San Ramon, Ca                                |\n",
      "|Düsseldorf, Germany                          |\n",
      "|Vancouver, Bc                                |\n",
      "|Poland                                       |\n",
      "|Vancouver, Canada                            |\n",
      "|New Jersey Usa                               |\n",
      "|Idaho                                        |\n",
      "|Central Pennsylvania                         |\n",
      "|Portugal                                     |\n",
      "|Bangalore,india                              |\n",
      "|Australia                                    |\n",
      "|Nj                                           |\n",
      "|Manchester, Nh                               |\n",
      "|Burlington, Vt                               |\n",
      "|Tokyo, Japan                                 |\n",
      "|Minneapolis Mn                               |\n",
      "|Northern California                          |\n",
      "|Bulgaria                                     |\n",
      "|Walnut Creek Ca                              |\n",
      "|Orlando, Florida                             |\n",
      "|20008                                        |\n",
      "|South Carolina                               |\n",
      "|Didu                                         |\n",
      "|South Texas                                  |\n",
      "|Santa Monica                                 |\n",
      "|Remote (us)                                  |\n",
      "|Salt Lake City                               |\n",
      "|Columbia Mo                                  |\n",
      "|Corvallis, Or                                |\n",
      "|Alameda                                      |\n",
      "|Austria                                      |\n",
      "|Eastern Oregon                               |\n",
      "|Lexington Park, Md                           |\n",
      "|Baltimore                                    |\n",
      "|Burnaby Bc                                   |\n",
      "|Korea                                        |\n",
      "|Bala Cynwyd, Pa                              |\n",
      "|Exeter, Uk                                   |\n",
      "|Paris, France                                |\n",
      "|Ottawa, Canada                               |\n",
      "|Lowell, Ma                                   |\n",
      "|Zürich                                       |\n",
      "|Kathmandu, Nepal                             |\n",
      "|Sdf                                          |\n",
      "|California                                   |\n",
      "|Ventura, Ca                                  |\n",
      "|Serbia                                       |\n",
      "|Mtv                                          |\n",
      "|Green Bay, Wi                                |\n",
      "|Shillington Pa                               |\n",
      "|Emea                                         |\n",
      "|South Africa                                 |\n",
      "|Dayton, Oh                                   |\n",
      "|Tel Aviv, Israel                             |\n",
      "|서울시 강남구 삼성동                         |\n",
      "|Orlando                                      |\n",
      "|Bermuda                                      |\n",
      "|Newark, Nj, United States Of America         |\n",
      "|Little Falls, Nj                             |\n",
      "|San Ramon                                    |\n",
      "|Scotland                                     |\n",
      "|Esberg, Denmark                              |\n",
      "|New York                                     |\n",
      "|Jersey City, Nj                              |\n",
      "|Sf Bay Area                                  |\n",
      "|Ms                                           |\n",
      "|Hungary                                      |\n",
      "|Luxembourg, Luxembourg                       |\n",
      "|Seattle, Washington                          |\n",
      "|Guro                                         |\n",
      "|Charleston Sc                                |\n",
      "|2                                            |\n",
      "|Paloalto                                     |\n",
      "|College Station, Tx                          |\n",
      "|Halifax                                      |\n",
      "|Mountain View                                |\n",
      "|Cologne                                      |\n",
      "|Beaverton, Or                                |\n",
      "|Cupertino, Ca                                |\n",
      "|Southwest Mo                                 |\n",
      "|Winnipeg,mb                                  |\n",
      "|Atlanta, Ga (remote)                         |\n",
      "|Laurel, Md, Usa                              |\n",
      "|Poland, Warsaw                               |\n",
      "|Asd                                          |\n",
      "|Raleigh, Nc                                  |\n",
      "|Los Angeles, Ca                              |\n",
      "|Rio De Janeiro                               |\n",
      "|Mountain View, California                    |\n",
      "|Providence, Ri                               |\n",
      "|Toronto, Ontario                             |\n",
      "|Charlotte, North Carolina                    |\n",
      "|Budapest, Hungary                            |\n",
      "|Hamburg, Germany                             |\n",
      "|Sterling Va                                  |\n",
      "|Cupeettino                                   |\n",
      "|Asunción, Paraguay                           |\n",
      "|Wisconsin                                    |\n",
      "|Calgary, Canada                              |\n",
      "|Denver                                       |\n",
      "|United Kingdom                               |\n",
      "|Amsterdam Area                               |\n",
      "|Franconia, Nh                                |\n",
      "|Boston                                       |\n",
      "|Scranton, Pa                                 |\n",
      "|Munich                                       |\n",
      "|Sankt Peterburg, Russia                      |\n",
      "|St. George, Ut                               |\n",
      "|Poznan, Poland                               |\n",
      "|Bremen, Germany                              |\n",
      "|Vietnam                                      |\n",
      "|Santa Clara, Cuba                            |\n",
      "|West Lafayette, In                           |\n",
      "|San Francisco (palo Alto), Ca                |\n",
      "|Yonkers, Ny                                  |\n",
      "|Maine                                        |\n",
      "|Colorado                                     |\n",
      "|Netherlands                                  |\n",
      "|Los Gatos, Ca                                |\n",
      "|El Segundo                                   |\n",
      "|Plano, Tx                                    |\n",
      "|Provo                                        |\n",
      "|Nashville, Tn                                |\n",
      "|San Frnacisco                                |\n",
      "|Remote Usa                                   |\n",
      "|Redwood                                      |\n",
      "|Grenoble, France                             |\n",
      "|New England                                  |\n",
      "|West Yorkshire, Uk                           |\n",
      "|Shanghai                                     |\n",
      "|Casablanca, Morocco                          |\n",
      "|Lowell, Ar                                   |\n",
      "+---------------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql('''\n",
    "    SELECT DISTINCT initcap(location)\n",
    "    FROM data_3\n",
    "    \n",
    "''').show(100000, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------------+--------+\n",
      "|initcap(job_title)       |count(1)|\n",
      "+-------------------------+--------+\n",
      "|Software Engineer        |642     |\n",
      "|Senior Software Engineer |222     |\n",
      "|Software Developer       |218     |\n",
      "|Developer                |80      |\n",
      "|Web Developer            |77      |\n",
      "|Senior Developer         |56      |\n",
      "|Data Scientist           |40      |\n",
      "|Engineer                 |39      |\n",
      "|Senior Software Developer|39      |\n",
      "|Software Engineer Ii     |35      |\n",
      "|Product Manager          |33      |\n",
      "|Cto                      |33      |\n",
      "|Devops Engineer          |25      |\n",
      "|Senior Engineer          |24      |\n",
      "|Consultant               |24      |\n",
      "|Analyst                  |19      |\n",
      "|Software Architect       |18      |\n",
      "|Full Stack Developer     |17      |\n",
      "|Sr Software Engineer     |16      |\n",
      "|Front End Developer      |15      |\n",
      "+-------------------------+--------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql('''\n",
    "    SELECT initcap(job_title), count(1)\n",
    "    FROM data_3\n",
    "    GROUP BY 1\n",
    "    ORDER BY 2 DESC\n",
    "''').show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------------------+\n",
      "|annual_base_pay                     |\n",
      "+------------------------------------+\n",
      "|57670 USD                           |\n",
      "|50,000 EUR                          |\n",
      "|$128,000                            |\n",
      "|77,000                              |\n",
      "|28500                               |\n",
      "|175000                              |\n",
      "|$33,000                             |\n",
      "|57500                               |\n",
      "|66000                               |\n",
      "|46444                               |\n",
      "|$74,500                             |\n",
      "|125                                 |\n",
      "|30953                               |\n",
      "|44500                               |\n",
      "|33500                               |\n",
      "|185,000                             |\n",
      "|75000 CAD                           |\n",
      "|14000                               |\n",
      "|30 000€                             |\n",
      "|42100 USD                           |\n",
      "|53000                               |\n",
      "|112,000                             |\n",
      "|7445745                             |\n",
      "|7                                   |\n",
      "|54,000                              |\n",
      "|130,500                             |\n",
      "|159000                              |\n",
      "|79860                               |\n",
      "|51                                  |\n",
      "|36000000                            |\n",
      "|150000                              |\n",
      "|125,000                             |\n",
      "|PKR 100000                          |\n",
      "|240000                              |\n",
      "|£31300                              |\n",
      "|100,000NZD                          |\n",
      "|102,000                             |\n",
      "|20k €                               |\n",
      "|142000                              |\n",
      "|£130000                             |\n",
      "|146,000                             |\n",
      "|146900                              |\n",
      "|50225 GBP                           |\n",
      "|122,500                             |\n",
      "|92500                               |\n",
      "|87000                               |\n",
      "|81000                               |\n",
      "|15                                  |\n",
      "|-1                                  |\n",
      "|19800 euro                          |\n",
      "|232                                 |\n",
      "|79000                               |\n",
      "|37800                               |\n",
      "|25200                               |\n",
      "|61.7k €                             |\n",
      "|$48,000                             |\n",
      "|93,000                              |\n",
      "|39225                               |\n",
      "|275,000                             |\n",
      "|73000                               |\n",
      "|139000                              |\n",
      "|21000                               |\n",
      "|£80000                              |\n",
      "|100,000 USD                         |\n",
      "|x                                   |\n",
      "|80,800                              |\n",
      "|1250000                             |\n",
      "|£67000                              |\n",
      "|153000                              |\n",
      "|840000                              |\n",
      "|3600000                             |\n",
      "|93350                               |\n",
      "|103,000                             |\n",
      "|g23                                 |\n",
      "|11000                               |\n",
      "|85000 CAD                           |\n",
      "|68,000                              |\n",
      "|SGD 50,000                          |\n",
      "|1111                                |\n",
      "|110000                              |\n",
      "|55,000                              |\n",
      "|72500                               |\n",
      "|62500 USD                           |\n",
      "|1111111112221                       |\n",
      "|69                                  |\n",
      "|1500 / day corp2corp no bennies     |\n",
      "|116300                              |\n",
      "|£26000                              |\n",
      "|71174.93                            |\n",
      "|107280                              |\n",
      "|135k                                |\n",
      "|57,000                              |\n",
      "|26000                               |\n",
      "|112000                              |\n",
      "|41000 EUR                           |\n",
      "|33700                               |\n",
      "|au$80000                            |\n",
      "|$46,900                             |\n",
      "|$85,000                             |\n",
      "|42000 Euro                          |\n",
      "|36,000                              |\n",
      "|161,000                             |\n",
      "|Food                                |\n",
      "|50 000                              |\n",
      "|170k                                |\n",
      "|59800                               |\n",
      "|5000                                |\n",
      "|8,500                               |\n",
      "|NOK 450000                          |\n",
      "|123,000                             |\n",
      "|SEK 420000                          |\n",
      "|£49000                              |\n",
      "|330000                              |\n",
      "|128,000                             |\n",
      "|75000                               |\n",
      "|175,000                             |\n",
      "|104000                              |\n",
      "|€50,000                             |\n",
      "|83,000                              |\n",
      "|725000                              |\n",
      "|82000                               |\n",
      "|250,000                             |\n",
      "|26,000                              |\n",
      "|20500                               |\n",
      "|€55,000                             |\n",
      "|199,000                             |\n",
      "|109200                              |\n",
      "|122000                              |\n",
      "|90000                               |\n",
      "|123000                              |\n",
      "|120000 USD                          |\n",
      "|£62000                              |\n",
      "|120000                              |\n",
      "|£27500                              |\n",
      "|82,500 CAD                          |\n",
      "|97,000                              |\n",
      "|42347                               |\n",
      "|67000                               |\n",
      "|41600                               |\n",
      "|260,000                             |\n",
      "|120510                              |\n",
      "|€53,000                             |\n",
      "|171000                              |\n",
      "|132500                              |\n",
      "|80k                                 |\n",
      "|215000                              |\n",
      "|625K                                |\n",
      "|$34,800                             |\n",
      "|100K                                |\n",
      "|160                                 |\n",
      "|£31000                              |\n",
      "|5700                                |\n",
      "|$160,000                            |\n",
      "|129000                              |\n",
      "|205000                              |\n",
      "|108,500                             |\n",
      "|$69/hr                              |\n",
      "|RMB: 225k                           |\n",
      "|72200                               |\n",
      "|58000 EUR                           |\n",
      "|$111,000                            |\n",
      "|€54,000                             |\n",
      "|54000 EUR                           |\n",
      "|£45000                              |\n",
      "|6,031.04                            |\n",
      "|270000                              |\n",
      "|£25,000                             |\n",
      "|50,000                              |\n",
      "|45,000                              |\n",
      "|7500000                             |\n",
      "|200000000                           |\n",
      "|28800                               |\n",
      "|CAN$100000                          |\n",
      "|35000GBP                            |\n",
      "|$66,500                             |\n",
      "|64000                               |\n",
      "|119320                              |\n",
      "|€31,000                             |\n",
      "|180000 USD / yr                     |\n",
      "|€30,000                             |\n",
      "|780000                              |\n",
      "|€52,000                             |\n",
      "|£45850                              |\n",
      "|6100                                |\n",
      "|50000AUD                            |\n",
      "|80000 USD                           |\n",
      "|52                                  |\n",
      "|35                                  |\n",
      "|128500                              |\n",
      "|134000                              |\n",
      "|37500                               |\n",
      "|131,000                             |\n",
      "|$70,000                             |\n",
      "|80000 NZD                           |\n",
      "|$39,500                             |\n",
      "|£120000                             |\n",
      "|31,000                              |\n",
      "|£105,000                            |\n",
      "|0                                   |\n",
      "|49000                               |\n",
      "|2000                                |\n",
      "|10000                               |\n",
      "|36402                               |\n",
      "|ZAR 260000                          |\n",
      "|81500                               |\n",
      "|$100,000                            |\n",
      "|$30/hr                              |\n",
      "|£27,200                             |\n",
      "|3300                                |\n",
      "|113,000                             |\n",
      "|110,000                             |\n",
      "|185000                              |\n",
      "|105,000                             |\n",
      "|33000                               |\n",
      "|143000                              |\n",
      "|€27,000                             |\n",
      "|320000                              |\n",
      "|£80.000                             |\n",
      "|138600                              |\n",
      "|193000                              |\n",
      "|$28,198                             |\n",
      "|48,800                              |\n",
      "|300000                              |\n",
      "|£35000                              |\n",
      "|2400e                               |\n",
      "|20K                                 |\n",
      "|110                                 |\n",
      "|56000                               |\n",
      "|122500                              |\n",
      "|104,000                             |\n",
      "|21027                               |\n",
      "|29K                                 |\n",
      "|£30,000                             |\n",
      "|~70000?                             |\n",
      "|null                                |\n",
      "|145000                              |\n",
      "|23000                               |\n",
      "|124000                              |\n",
      "|78500                               |\n",
      "|156000000                           |\n",
      "|12222                               |\n",
      "|46150                               |\n",
      "|115,800                             |\n",
      "|$117,000                            |\n",
      "|USD$120000                          |\n",
      "|42600                               |\n",
      "|SEK 380000                          |\n",
      "|£31,000                             |\n",
      "|27,000                              |\n",
      "|$35,000                             |\n",
      "|325000                              |\n",
      "|36000                               |\n",
      "|£38500                              |\n",
      "|15,00,000 Rs                        |\n",
      "|PKR 600000                          |\n",
      "|7000                                |\n",
      "|22300                               |\n",
      "|78500 CAD                           |\n",
      "|75,000                              |\n",
      "|£42,000                             |\n",
      "|20,000                              |\n",
      "|96                                  |\n",
      "|195,500                             |\n",
      "|54100                               |\n",
      "|77957.56                            |\n",
      "|40000 GBP                           |\n",
      "|27600                               |\n",
      "|111000                              |\n",
      "|1234555                             |\n",
      "|$90/hr                              |\n",
      "|8200                                |\n",
      "|€84,000                             |\n",
      "|130,000                             |\n",
      "|70,000.00                           |\n",
      "|275000                              |\n",
      "|13000                               |\n",
      "|86000000                            |\n",
      "|131000                              |\n",
      "|48000 USD                           |\n",
      "|44000                               |\n",
      "|900USD                              |\n",
      "|156800                              |\n",
      "|SGD 103000                          |\n",
      "|101,500                             |\n",
      "|51,000                              |\n",
      "|105900                              |\n",
      "|19000                               |\n",
      "|23000 USD (NET)                     |\n",
      "|80000                               |\n",
      "|90,000                              |\n",
      "|100k£                               |\n",
      "|162000                              |\n",
      "|700000                              |\n",
      "|163000                              |\n",
      "|87250                               |\n",
      "|95000                               |\n",
      "|EUR 5000                            |\n",
      "|110000 USD                          |\n",
      "|46100                               |\n",
      "|HK$180000                           |\n",
      "|€34,000                             |\n",
      "|1520                                |\n",
      "|139,000                             |\n",
      "|55k                                 |\n",
      "|99KBRL ~27.2KUSD by today's rate    |\n",
      "|52,500                              |\n",
      "|1,000,000 USD                       |\n",
      "|15200                               |\n",
      "|5010                                |\n",
      "|405000                              |\n",
      "|89000                               |\n",
      "|160,000                             |\n",
      "|167,000                             |\n",
      "|£20000                              |\n",
      "|13200                               |\n",
      "|25,000                              |\n",
      "|87,500                              |\n",
      "|188,000                             |\n",
      "|183000                              |\n",
      "|30000 euros                         |\n",
      "|27000                               |\n",
      "|3609                                |\n",
      "|£83,000                             |\n",
      "|33600                               |\n",
      "|575000 INR                          |\n",
      "|78000                               |\n",
      "|140                                 |\n",
      "|121000                              |\n",
      "|147000                              |\n",
      "|£50,000                             |\n",
      "|131                                 |\n",
      "|5,40,000 INR                        |\n",
      "|169000                              |\n",
      "|82,000                              |\n",
      "|129857                              |\n",
      "|350000                              |\n",
      "|99500                               |\n",
      "|195000                              |\n",
      "|86,000                              |\n",
      "|£70000                              |\n",
      "|CHF 60000                           |\n",
      "|160000CAD                           |\n",
      "|125000 CAD                          |\n",
      "|131500                              |\n",
      "|$56,000                             |\n",
      "|124,000                             |\n",
      "|46999                               |\n",
      "|120                                 |\n",
      "|€60,000                             |\n",
      "|40000 euro                          |\n",
      "|58000 USD                           |\n",
      "|50,00,000 INR                       |\n",
      "|43800                               |\n",
      "|34200euros                          |\n",
      "|29500                               |\n",
      "|43000                               |\n",
      "|123530                              |\n",
      "|3.5                                 |\n",
      "|374000                              |\n",
      "|48000GBP                            |\n",
      "|66625                               |\n",
      "|46500 USD                           |\n",
      "|12000                               |\n",
      "|about $2                            |\n",
      "|60 000 CAD                          |\n",
      "|$114,500                            |\n",
      "|39000                               |\n",
      "|101,000                             |\n",
      "|130                                 |\n",
      "|44,000                              |\n",
      "|40500                               |\n",
      "|JPY 7,000,000                       |\n",
      "|43000 EUR                           |\n",
      "|236000                              |\n",
      "|24073                               |\n",
      "|10300                               |\n",
      "|$23,000 USD                         |\n",
      "|181000                              |\n",
      "|90,000 USD                          |\n",
      "|31000 USD                           |\n",
      "|67750                               |\n",
      "|137000                              |\n",
      "|118000                              |\n",
      "|267,000                             |\n",
      "|€84,480                             |\n",
      "|197400                              |\n",
      "|40000                               |\n",
      "|$50,000                             |\n",
      "|119500                              |\n",
      "|32400                               |\n",
      "|JPY 10,000,000                      |\n",
      "|60,000                              |\n",
      "|62000                               |\n",
      "|38400                               |\n",
      "|57900                               |\n",
      "|105000 CAD                          |\n",
      "|ZAR 675,000                         |\n",
      "|89000nzd                            |\n",
      "|130000 AUD                          |\n",
      "|136                                 |\n",
      "|56500 usd                           |\n",
      "|£61000                              |\n",
      "|€19,800                             |\n",
      "|1000                                |\n",
      "|82500                               |\n",
      "|92,000                              |\n",
      "|71000                               |\n",
      "|17000                               |\n",
      "|8675309                             |\n",
      "|1000000                             |\n",
      "|6600eur                             |\n",
      "|94,000                              |\n",
      "|12,000                              |\n",
      "|46,000                              |\n",
      "|1200                                |\n",
      "|NZ$80000                            |\n",
      "|162,000                             |\n",
      "|310000                              |\n",
      "|290000                              |\n",
      "|89,000                              |\n",
      "|64400                               |\n",
      "|108 AUD                             |\n",
      "|86200                               |\n",
      "|112168                              |\n",
      "|155,000                             |\n",
      "|$34,000                             |\n",
      "|€28.40                              |\n",
      "|141000                              |\n",
      "|133000                              |\n",
      "|102,000 CDN                         |\n",
      "|36276                               |\n",
      "|36000 USD                           |\n",
      "|3000usd / month                     |\n",
      "|92040                               |\n",
      "|6320                                |\n",
      "|43000 rubles                        |\n",
      "|€67,500                             |\n",
      "|90                                  |\n",
      "|€20,000                             |\n",
      "|93,500                              |\n",
      "|147,000                             |\n",
      "|37000                               |\n",
      "|58000                               |\n",
      "|90,000 CAD                          |\n",
      "|€66k                                |\n",
      "|100000                              |\n",
      "|$32/hr                              |\n",
      "|88,000                              |\n",
      "|24000 USD                           |\n",
      "|23200                               |\n",
      "|1234                                |\n",
      "|¥144000≈$20921                      |\n",
      "|57516                               |\n",
      "|16543                               |\n",
      "|£25000 ($36000)                     |\n",
      "|262000                              |\n",
      "|99000                               |\n",
      "|42500                               |\n",
      "|61000                               |\n",
      "|120,000                             |\n",
      "|$120,000                            |\n",
      "|98,000                              |\n",
      "|$30,500                             |\n",
      "|89950                               |\n",
      "|€72,000                             |\n",
      "|23                                  |\n",
      "|105000                              |\n",
      "|88000                               |\n",
      "|41500 GBP                           |\n",
      "|102                                 |\n",
      "|520万                               |\n",
      "|145,000                             |\n",
      "|$80,000                             |\n",
      "|49,300                              |\n",
      "|$37,594                             |\n",
      "|HUF 6000000                         |\n",
      "|€47,000                             |\n",
      "|$83,000                             |\n",
      "|187468                              |\n",
      "|4900                                |\n",
      "|166500                              |\n",
      "|167000                              |\n",
      "|€22,500                             |\n",
      "|137,500                             |\n",
      "|133030                              |\n",
      "|33333                               |\n",
      "|190000                              |\n",
      "|21,496.10                           |\n",
      "|510000                              |\n",
      "|63,000                              |\n",
      "|65,000                              |\n",
      "|24000                               |\n",
      "|88800                               |\n",
      "|$46,500                             |\n",
      "|50/hour                             |\n",
      "|55000                               |\n",
      "|108500                              |\n",
      "|356000                              |\n",
      "|57200                               |\n",
      "|17500                               |\n",
      "|£34,000                             |\n",
      "|56500                               |\n",
      "|£41000                              |\n",
      "|56800                               |\n",
      "|EUR 10000                           |\n",
      "|84000                               |\n",
      "|205,000                             |\n",
      "|72000 $AUD                          |\n",
      "|USD 81200                           |\n",
      "|135000                              |\n",
      "|~66 000 (250 000 PLN)               |\n",
      "|1231231                             |\n",
      "|73k USD                             |\n",
      "|100,000                             |\n",
      "|79,000                              |\n",
      "|187500                              |\n",
      "|$21,500                             |\n",
      "|75600                               |\n",
      "|€78,000                             |\n",
      "|110000-120000                       |\n",
      "|62200                               |\n",
      "|123123                              |\n",
      "|€38,000                             |\n",
      "|INR 1100000                         |\n",
      "|5300                                |\n",
      "|95680 USD                           |\n",
      "|1312                                |\n",
      "|15k (now)                           |\n",
      "|29,000                              |\n",
      "|75,000 CAD                          |\n",
      "|5200                                |\n",
      "|-                                   |\n",
      "|20500 GBP                           |\n",
      "|189000                              |\n",
      "|CAD $60000                          |\n",
      "|7.2M JPY                            |\n",
      "|40                                  |\n",
      "|51k USD                             |\n",
      "|$110,000                            |\n",
      "|70000 EUR                           |\n",
      "|164000                              |\n",
      "|£35,000                             |\n",
      "|59280                               |\n",
      "|199000                              |\n",
      "|5043                                |\n",
      "|95,000                              |\n",
      "|£37000                              |\n",
      "|114000                              |\n",
      "|£29000                              |\n",
      "|1500000 Rs                          |\n",
      "|128000                              |\n",
      "|€33,000                             |\n",
      "|64,000                              |\n",
      "|31000                               |\n",
      "|£18,000                             |\n",
      "|39000 EUR                           |\n",
      "|55 k€                               |\n",
      "|PLN 12000                           |\n",
      "|72000                               |\n",
      "|111360                              |\n",
      "|135                                 |\n",
      "|48,000                              |\n",
      "|323232                              |\n",
      "|83500                               |\n",
      "|153020                              |\n",
      "|113500                              |\n",
      "|52000 EUR                           |\n",
      "|125000                              |\n",
      "|206000                              |\n",
      "|A                                   |\n",
      "|4000                                |\n",
      "|22000                               |\n",
      "|164000 USD                          |\n",
      "|240,000                             |\n",
      "|159500                              |\n",
      "|190,000                             |\n",
      "|90k€                                |\n",
      "|115                                 |\n",
      "|$60,000                             |\n",
      "|68500                               |\n",
      "|38000                               |\n",
      "|42,000                              |\n",
      "|Rs. 2,300,000                       |\n",
      "|1400                                |\n",
      "|55000000                            |\n",
      "|$188,000                            |\n",
      "|€45,000                             |\n",
      "|20000                               |\n",
      "|£42000 / $60300                     |\n",
      "|65124                               |\n",
      "|101500 AUD                          |\n",
      "|62250                               |\n",
      "|41500                               |\n",
      "|148,000                             |\n",
      "|9000                                |\n",
      "|178000                              |\n",
      "|48000                               |\n",
      "|40,000                              |\n",
      "|5.5 Million JPY                     |\n",
      "|57,300                              |\n",
      "|$9,250,000                          |\n",
      "|126,000                             |\n",
      "|$3,400                              |\n",
      "|43200                               |\n",
      "|92,500                              |\n",
      "|1.3                                 |\n",
      "|92                                  |\n",
      "|38,000                              |\n",
      "|$35 per hour                        |\n",
      "|165,000                             |\n",
      "|23423                               |\n",
      "|51192                               |\n",
      "|86500                               |\n",
      "|72,000                              |\n",
      "|114500                              |\n",
      "|117                                 |\n",
      "|91,000                              |\n",
      "|45k                                 |\n",
      "|$97,000                             |\n",
      "|75000000                            |\n",
      "|225000                              |\n",
      "|80001                               |\n",
      "|42715                               |\n",
      "|66500                               |\n",
      "|92700                               |\n",
      "|450000                              |\n",
      "|89250                               |\n",
      "|85,000                              |\n",
      "|67500                               |\n",
      "|109,000                             |\n",
      "|75,000 EUR                          |\n",
      "|€42,000                             |\n",
      "|$132,000                            |\n",
      "|73,500                              |\n",
      "|231241                              |\n",
      "|550000                              |\n",
      "|$235,000                            |\n",
      "|67200                               |\n",
      "|35900                               |\n",
      "|€58k                                |\n",
      "|16800                               |\n",
      "|30000                               |\n",
      "|$58,000                             |\n",
      "|31200                               |\n",
      "|28,000                              |\n",
      "|10468 - really                      |\n",
      "|¥4,600,000                          |\n",
      "|96750                               |\n",
      "|$17,264                             |\n",
      "|275K                                |\n",
      "|560000                              |\n",
      "|114                                 |\n",
      "|3700000                             |\n",
      "|60,500                              |\n",
      "|33                                  |\n",
      "|€62,500                             |\n",
      "|£30k                                |\n",
      "|28359                               |\n",
      "|€26,000                             |\n",
      "|250000                              |\n",
      "|48500                               |\n",
      "|35000                               |\n",
      "|110,406.48                          |\n",
      "|51,500                              |\n",
      "|555                                 |\n",
      "|48723                               |\n",
      "|$80,600                             |\n",
      "|$43,000                             |\n",
      "|77,060                              |\n",
      "|115,000                             |\n",
      "|70000                               |\n",
      "|72600                               |\n",
      "|£46,000                             |\n",
      "|£43000                              |\n",
      "|31/ hr                              |\n",
      "|150                                 |\n",
      "|40400                               |\n",
      "|90000 Euro                          |\n",
      "|€40,000                             |\n",
      "|22700                               |\n",
      "|$15,000                             |\n",
      "|108000 AUD                          |\n",
      "|90000 CAD                           |\n",
      "|CHF 130000                          |\n",
      "|99,480                              |\n",
      "|170                                 |\n",
      "|81,000                              |\n",
      "|192000                              |\n",
      "|131400                              |\n",
      "|165000                              |\n",
      "|107500                              |\n",
      "|$84,500 AUD                         |\n",
      "|82,500                              |\n",
      "|120000000                           |\n",
      "|66000 EURO after TAX                |\n",
      "|42000000                            |\n",
      "|92000                               |\n",
      "|113000                              |\n",
      "|27,557.60                           |\n",
      "|60000                               |\n",
      "|30 000                              |\n",
      "|140k                                |\n",
      "|€70,000                             |\n",
      "|30000 pounds                        |\n",
      "|81120                               |\n",
      "|69,500                              |\n",
      "|96000                               |\n",
      "|76000                               |\n",
      "|150,000                             |\n",
      "|68250                               |\n",
      "|240                                 |\n",
      "|450USD                              |\n",
      "|124000USD                           |\n",
      "|2000000                             |\n",
      "|250,000 yen                         |\n",
      "|130000                              |\n",
      "|1575                                |\n",
      "|162500                              |\n",
      "|86400                               |\n",
      "|800000                              |\n",
      "|1 rare pepe                         |\n",
      "|$130,000                            |\n",
      "|USD15,900                           |\n",
      "|85000NZD                            |\n",
      "|27300                               |\n",
      "|54000                               |\n",
      "|50000                               |\n",
      "|127000                              |\n",
      "|126500                              |\n",
      "|107000                              |\n",
      "|95,000.00                           |\n",
      "|£60000                              |\n",
      "|112k                                |\n",
      "|116000                              |\n",
      "|93500                               |\n",
      "|105,500                             |\n",
      "|DKK 1000000                         |\n",
      "|$73,000                             |\n",
      "|88650                               |\n",
      "|22310                               |\n",
      "|216000 CNY                          |\n",
      "|18000 EUR                           |\n",
      "|£100000                             |\n",
      "|$24/hr                              |\n",
      "|60000PLN (circa 16000$)             |\n",
      "|52200                               |\n",
      "|€43,000                             |\n",
      "|136000                              |\n",
      "|53500                               |\n",
      "|$62,000                             |\n",
      "|188000                              |\n",
      "|104500                              |\n",
      "|47000                               |\n",
      "|Rrr                                 |\n",
      "|61,000                              |\n",
      "|26200                               |\n",
      "|80,000                              |\n",
      "|151000                              |\n",
      "|60650                               |\n",
      "|30k Eur                             |\n",
      "|106,000                             |\n",
      "|£75000                              |\n",
      "|$210,000                            |\n",
      "|117500                              |\n",
      "|16000                               |\n",
      "|£55000                              |\n",
      "|165,000 AUD                         |\n",
      "|210,000                             |\n",
      "|13500                               |\n",
      "|45600                               |\n",
      "|103000                              |\n",
      "|43,000                              |\n",
      "|70,000                              |\n",
      "|146000                              |\n",
      "|35,00,000                           |\n",
      "|63000                               |\n",
      "|200,000                             |\n",
      "|71,000                              |\n",
      "|£34000                              |\n",
      "|1                                   |\n",
      "|45000                               |\n",
      "|119000                              |\n",
      "|11250                               |\n",
      "|94000                               |\n",
      "|45000 USD                           |\n",
      "|4700                                |\n",
      "|98000                               |\n",
      "|302095                              |\n",
      "|70200                               |\n",
      "|€25,000                             |\n",
      "|324000                              |\n",
      "|46000                               |\n",
      "|216000                              |\n",
      "|€46,000                             |\n",
      "|no                                  |\n",
      "|R$ 72k                              |\n",
      "|1400000                             |\n",
      "|Testing                             |\n",
      "|$36,000                             |\n",
      "|£38000                              |\n",
      "|£39000                              |\n",
      "|230000                              |\n",
      "|129500                              |\n",
      "|62424                               |\n",
      "|10600                               |\n",
      "|30,187                              |\n",
      "|106500                              |\n",
      "|23000 gbp                           |\n",
      "|5160                                |\n",
      "|51750                               |\n",
      "|229000                              |\n",
      "|93000                               |\n",
      "|j                                   |\n",
      "|180000                              |\n",
      "|7500                                |\n",
      "|111111                              |\n",
      "|$1,500                              |\n",
      "|€20,400                             |\n",
      "|103595                              |\n",
      "|28000                               |\n",
      "|€75,000                             |\n",
      "|985,000                             |\n",
      "|500000                              |\n",
      "|74400                               |\n",
      "|3700                                |\n",
      "|7950                                |\n",
      "|66111                               |\n",
      "|$95,000                             |\n",
      "|140,000                             |\n",
      "|$84,000                             |\n",
      "|32900                               |\n",
      "|$62,125                             |\n",
      "|39810                               |\n",
      "|51000 USD                           |\n",
      "|168000                              |\n",
      "|900000                              |\n",
      "|4400000                             |\n",
      "|95520                               |\n",
      "|158000                              |\n",
      "|$145,000                            |\n",
      "|66738                               |\n",
      "|600000                              |\n",
      "|41000                               |\n",
      "|€32,000                             |\n",
      "|9999999999                          |\n",
      "|115000                              |\n",
      "|50000 EUR                           |\n",
      "|48100                               |\n",
      "|134,000                             |\n",
      "|14110.88                            |\n",
      "|$116,000                            |\n",
      "|12618                               |\n",
      "|10                                  |\n",
      "|56000 Eur                           |\n",
      "|85000                               |\n",
      "|$165,000                            |\n",
      "|18000                               |\n",
      "|60 000 €                            |\n",
      "|73500                               |\n",
      "|165                                 |\n",
      "|135,000                             |\n",
      "|116500                              |\n",
      "|$12,000                             |\n",
      "|36.5K USD / YEAR                    |\n",
      "|25000                               |\n",
      "|180,000                             |\n",
      "|43140                               |\n",
      "|37,500                              |\n",
      "|69000                               |\n",
      "|173,000                             |\n",
      "|135500                              |\n",
      "|68000                               |\n",
      "|106000                              |\n",
      "|15000                               |\n",
      "|34500                               |\n",
      "|46500                               |\n",
      "|70k                                 |\n",
      "|108000                              |\n",
      "|144,000                             |\n",
      "|£65,000                             |\n",
      "|34000                               |\n",
      "|400000                              |\n",
      "|20000 USD                           |\n",
      "|This                                |\n",
      "|98,400                              |\n",
      "|54500                               |\n",
      "|70000$CAD                           |\n",
      "|65                                  |\n",
      "|135, 000                            |\n",
      "|99000 AUD                           |\n",
      "|a trap a day makes the boner go away|\n",
      "|14500                               |\n",
      "|156000                              |\n",
      "|157500                              |\n",
      "|52000                               |\n",
      "|195,000                             |\n",
      "|£36,000                             |\n",
      "|55k - 60k                           |\n",
      "|102500                              |\n",
      "|30,000                              |\n",
      "|220000                              |\n",
      "|1600000                             |\n",
      "|$960000HKD                          |\n",
      "|£76500                              |\n",
      "|4500                                |\n",
      "|60k/yr                              |\n",
      "|90500                               |\n",
      "|38 210                              |\n",
      "|170,000                             |\n",
      "|71,200                              |\n",
      "|$110,00                             |\n",
      "|32000                               |\n",
      "|65000                               |\n",
      "|73200                               |\n",
      "|SGD 45600                           |\n",
      "|100250                              |\n",
      "|€68,000                             |\n",
      "|£40000                              |\n",
      "|7777                                |\n",
      "|a                                   |\n",
      "|155000                              |\n",
      "|6000                                |\n",
      "|87500                               |\n",
      "|198,000 USD                         |\n",
      "|86000                               |\n",
      "|8000                                |\n",
      "|$30,000                             |\n",
      "|80400                               |\n",
      "|106200                              |\n",
      "|41,000                              |\n",
      "|123                                 |\n",
      "|138000                              |\n",
      "|166000                              |\n",
      "|120500                              |\n",
      "|109000                              |\n",
      "|95000 AUD                           |\n",
      "|63000 USD                           |\n",
      "|€48,000                             |\n",
      "|137500                              |\n",
      "|108,000                             |\n",
      "|97000                               |\n",
      "|90000 BRL / 22500 USD               |\n",
      "|97500                               |\n",
      "|40 hr                               |\n",
      "|260000                              |\n",
      "|6800                                |\n",
      "|£34400                              |\n",
      "|$75,000                             |\n",
      "|169,000                             |\n",
      "|59100                               |\n",
      "|37600                               |\n",
      "|₹420000                             |\n",
      "|45000 EUR                           |\n",
      "|176000                              |\n",
      "|77000                               |\n",
      "|60,200                              |\n",
      "|79,000.00                           |\n",
      "|111500                              |\n",
      "|6400                                |\n",
      "|25600                               |\n",
      "|110 GBP                             |\n",
      "|$40,000                             |\n",
      "|129,000                             |\n",
      "|40k                                 |\n",
      "|54000eur                            |\n",
      "|61500                               |\n",
      "|74000                               |\n",
      "|£65000                              |\n",
      "|982032                              |\n",
      "|152000                              |\n",
      "|60528                               |\n",
      "|9022                                |\n",
      "|10000000                            |\n",
      "|$93,000                             |\n",
      "|63k EUR                             |\n",
      "|72                                  |\n",
      "|137                                 |\n",
      "|83000                               |\n",
      "|148000                              |\n",
      "|198000                              |\n",
      "|75000Aud                            |\n",
      "|89900                               |\n",
      "|$50.00 hour                         |\n",
      "|4600                                |\n",
      "|200000                              |\n",
      "|100,000,000,000,003.00              |\n",
      "|210000                              |\n",
      "|AUD $125,000                        |\n",
      "|77600                               |\n",
      "|$1,000,000                          |\n",
      "|€65,000                             |\n",
      "|HAVE                                |\n",
      "|32k                                 |\n",
      "|1800                                |\n",
      "|333333                              |\n",
      "|67,000                              |\n",
      "|51000                               |\n",
      "|102000                              |\n",
      "|113375                              |\n",
      "|$90,000                             |\n",
      "|336000 RUB (~4800USD)               |\n",
      "|52500                               |\n",
      "|58,000                              |\n",
      "|R$176000                            |\n",
      "|140000                              |\n",
      "|91000                               |\n",
      "|126000                              |\n",
      "|$140,000                            |\n",
      "|13530                               |\n",
      "|50000000                            |\n",
      "|NZ$75,000                           |\n",
      "|112500                              |\n",
      "|$10,000                             |\n",
      "|2                                   |\n",
      "|75,000 USD                          |\n",
      "|8400                                |\n",
      "|32524                               |\n",
      "|10280000                            |\n",
      "|100k                                |\n",
      "|$34,500                             |\n",
      "|720000                              |\n",
      "|65,850                              |\n",
      "|6000000yen                          |\n",
      "|62,000                              |\n",
      "|£50000                              |\n",
      "|420420                              |\n",
      "|10800                               |\n",
      "|23 000$                             |\n",
      "|11990                               |\n",
      "|117000                              |\n",
      "|157000                              |\n",
      "|77700                               |\n",
      "|₹1200000                            |\n",
      "|68609                               |\n",
      "|€35,000                             |\n",
      "|€1,800                              |\n",
      "|5000000                             |\n",
      "|£44000                              |\n",
      "|42000                               |\n",
      "|101000                              |\n",
      "|690000                              |\n",
      "|170000                              |\n",
      "|59000                               |\n",
      "|93600                               |\n",
      "|144000                              |\n",
      "|110k CAD                            |\n",
      "|secret                              |\n",
      "|1200000                             |\n",
      "|37k€                                |\n",
      "|50                                  |\n",
      "|73000 USD                           |\n",
      "|PLN 64000                           |\n",
      "|57000                               |\n",
      "|200000 USD                          |\n",
      "|£27000                              |\n",
      "|42342                               |\n",
      "|32500                               |\n",
      "|£37,000 GBP                         |\n",
      "|182000                              |\n",
      "|160000                              |\n",
      "|24,395.80                           |\n",
      "|32,000                              |\n",
      "|85,000$ CAD                         |\n",
      "|132000                              |\n",
      "|28 000                              |\n",
      "|84,500                              |\n",
      "|102,500                             |\n",
      "|$32,000                             |\n",
      "+------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql('''\n",
    "    SELECT DISTINCT annual_base_pay\n",
    "    FROM data_3\n",
    "''').show(100000, truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+\n",
      "|col    |\n",
      "+-------+\n",
      "|AWAY   |\n",
      "|DKK    |\n",
      "|KUSD   |\n",
      "|REALLY |\n",
      "|NZD    |\n",
      "|HUF    |\n",
      "|GBP    |\n",
      "|CHF    |\n",
      "|RUB    |\n",
      "|BRL    |\n",
      "|TODAY  |\n",
      "|BENNIES|\n",
      "|TESTING|\n",
      "|TRAP   |\n",
      "|CAD    |\n",
      "|MILLION|\n",
      "|KBRL   |\n",
      "|CNY    |\n",
      "|EUR    |\n",
      "|RRR    |\n",
      "|BONER  |\n",
      "|RUBLES |\n",
      "|TAX    |\n",
      "|ZAR    |\n",
      "|FOOD   |\n",
      "|MONTH  |\n",
      "|PKR    |\n",
      "|EURO   |\n",
      "|ABOUT  |\n",
      "|NET    |\n",
      "|NOK    |\n",
      "|AUD    |\n",
      "|POUNDS |\n",
      "|CDN    |\n",
      "|PER    |\n",
      "|SECRET |\n",
      "|JPY    |\n",
      "|PEPE   |\n",
      "|HKD    |\n",
      "|YEAR   |\n",
      "|INR    |\n",
      "|RARE   |\n",
      "|RMB    |\n",
      "|CAN    |\n",
      "|HOUR   |\n",
      "|DAY    |\n",
      "|CIRCA  |\n",
      "|PLN    |\n",
      "|YEN    |\n",
      "|NOW    |\n",
      "|THE    |\n",
      "|CORP   |\n",
      "|USD    |\n",
      "|SEK    |\n",
      "|EUROS  |\n",
      "|HAVE   |\n",
      "|AFTER  |\n",
      "|MAKES  |\n",
      "|RATE   |\n",
      "|THIS   |\n",
      "|SGD    |\n",
      "+-------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql('''\n",
    "    SELECT DISTINCT explode(regexp_extract_all(upper(annual_base_pay), '([A-Z]{3,})'))\n",
    "    FROM data_3\n",
    "''').show(10000, truncate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In an attempt to remove at least the currencies that were not fake. A list of valid ones were extracted from the `annual_base_pay` column and used to eliminate (turn the column to NULL) invalid information. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+--------+\n",
      "|     annual_base_pay|currency|\n",
      "+--------------------+--------+\n",
      "|    36.5K USD / YEAR|     USD|\n",
      "|           62500 USD|     USD|\n",
      "|          SEK 380000|     SEK|\n",
      "|60000PLN (circa 1...|     PLN|\n",
      "|           48000 USD|     USD|\n",
      "|          120000 USD|     USD|\n",
      "|         $23,000 USD|     USD|\n",
      "|         ZAR 675,000|     ZAR|\n",
      "|          NOK 450000|     NOK|\n",
      "|          75,000 CAD|     CAD|\n",
      "|           58000 EUR|     EUR|\n",
      "|       1,000,000 USD|     USD|\n",
      "|           45000 USD|     USD|\n",
      "|           57670 USD|     USD|\n",
      "|          50,000 EUR|     EUR|\n",
      "|90000 BRL / 22500...|     BRL|\n",
      "|             110 GBP|     GBP|\n",
      "|           124000USD|     USD|\n",
      "|            EUR 5000|     EUR|\n",
      "|         DKK 1000000|     DKK|\n",
      "|          PKR 100000|     PKR|\n",
      "|          82,500 CAD|     CAD|\n",
      "|          CAN$100000|     CAN|\n",
      "|           51000 USD|     USD|\n",
      "|~66 000 (250 000 ...|     PLN|\n",
      "|           50000 EUR|     EUR|\n",
      "|            110k CAD|     CAD|\n",
      "|          USD$120000|     USD|\n",
      "|          SEK 420000|     SEK|\n",
      "|            35000GBP|     GBP|\n",
      "|           54000 EUR|     EUR|\n",
      "|          90,000 CAD|     CAD|\n",
      "|           41500 GBP|     GBP|\n",
      "|            50000AUD|     AUD|\n",
      "|           43000 EUR|     EUR|\n",
      "|          108000 AUD|     AUD|\n",
      "|         102,000 CDN|     CDN|\n",
      "|           41000 EUR|     EUR|\n",
      "|           80000 NZD|     NZD|\n",
      "|           20500 GBP|     GBP|\n",
      "|           85000 CAD|     CAD|\n",
      "|         HUF 6000000|     HUF|\n",
      "|          101500 AUD|     AUD|\n",
      "|         $84,500 AUD|     AUD|\n",
      "|           70000$CAD|     CAD|\n",
      "|           80000 USD|     USD|\n",
      "|           50000 EUR|     EUR|\n",
      "|         £37,000 GBP|     GBP|\n",
      "|          90,000 USD|     USD|\n",
      "|           90000 CAD|     CAD|\n",
      "|          CHF 130000|     CHF|\n",
      "|          130000 AUD|     AUD|\n",
      "|              900USD|     USD|\n",
      "|          100,000NZD|     NZD|\n",
      "|           SGD 45600|     SGD|\n",
      "|          CAD $60000|     CAD|\n",
      "|         165,000 AUD|     AUD|\n",
      "|           58000 USD|     USD|\n",
      "|          105000 CAD|     CAD|\n",
      "|           USD15,900|     USD|\n",
      "|          60 000 CAD|     CAD|\n",
      "|          72000 $AUD|     AUD|\n",
      "|           95000 AUD|     AUD|\n",
      "|        AUD $125,000|     AUD|\n",
      "|       JPY 7,000,000|     JPY|\n",
      "|         85,000$ CAD|     CAD|\n",
      "|           75000 CAD|     CAD|\n",
      "|         100,000 USD|     USD|\n",
      "|          110000 USD|     USD|\n",
      "|          200000 USD|     USD|\n",
      "|         198,000 USD|     USD|\n",
      "|           36000 USD|     USD|\n",
      "|       50,00,000 INR|     INR|\n",
      "|          75,000 USD|     USD|\n",
      "|           RMB: 225k|     RMB|\n",
      "|           99000 AUD|     AUD|\n",
      "|          216000 CNY|     CNY|\n",
      "|            85000NZD|     NZD|\n",
      "|          PKR 600000|     PKR|\n",
      "|           63000 USD|     USD|\n",
      "|           24000 USD|     USD|\n",
      "|          $960000HKD|     HKD|\n",
      "|336000 RUB (~4800...|     RUB|\n",
      "|          SGD 103000|     SGD|\n",
      "|           20000 USD|     USD|\n",
      "|           42100 USD|     USD|\n",
      "|           52000 EUR|     EUR|\n",
      "|66000 EURO after TAX|     EUR|\n",
      "|           18000 EUR|     EUR|\n",
      "|          SGD 50,000|     SGD|\n",
      "|          ZAR 260000|     ZAR|\n",
      "|           50225 GBP|     GBP|\n",
      "|           70000 EUR|     EUR|\n",
      "|     23000 USD (NET)|     USD|\n",
      "|           50000 EUR|     EUR|\n",
      "|           41000 EUR|     EUR|\n",
      "|           73000 USD|     USD|\n",
      "|           31000 USD|     USD|\n",
      "|            48000GBP|     GBP|\n",
      "|           39000 EUR|     EUR|\n",
      "|           USD 81200|     USD|\n",
      "|           CHF 60000|     CHF|\n",
      "|     5.5 Million JPY|     JPY|\n",
      "|             63k EUR|     EUR|\n",
      "|           40000 GBP|     GBP|\n",
      "|           160000CAD|     CAD|\n",
      "|         INR 1100000|     INR|\n",
      "|99KBRL ~27.2KUSD ...|     BRL|\n",
      "|           PLN 64000|     PLN|\n",
      "|           45000 EUR|     EUR|\n",
      "|          125000 CAD|     CAD|\n",
      "|            7.2M JPY|     JPY|\n",
      "|           PLN 12000|     PLN|\n",
      "|          575000 INR|     INR|\n",
      "|          164000 USD|     USD|\n",
      "|           46500 USD|     USD|\n",
      "|           EUR 10000|     EUR|\n",
      "|           95680 USD|     USD|\n",
      "|             108 AUD|     AUD|\n",
      "|     180000 USD / yr|     USD|\n",
      "|      JPY 10,000,000|     JPY|\n",
      "|        5,40,000 INR|     INR|\n",
      "|              450USD|     USD|\n",
      "|           78500 CAD|     CAD|\n",
      "|             73k USD|     USD|\n",
      "|          75,000 EUR|     EUR|\n",
      "|             51k USD|     USD|\n",
      "+--------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "identified_currencies = ['AUD','BRL','CAD','CAN','CDN','CHF','CNY','DKK','EUR','GBP','HKD','HUF','INR','JPY','NOK','NZD','PKR','PLN','RMB','RUB','SEK','SGD','USD','YEN','ZAR']\n",
    "currencies_regex = '|'.join(identified_currencies)\n",
    "\n",
    "spark.sql(f'''\n",
    "    SELECT annual_base_pay, regexp_extract(annual_base_pay, '({currencies_regex})') AS currency\n",
    "    FROM data_3\n",
    "    WHERE length(regexp_extract(annual_base_pay, '({currencies_regex})')) > 0\n",
    "''').show(100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+\n",
      "|currency|\n",
      "+--------+\n",
      "|    null|\n",
      "|       ¥|\n",
      "|        |\n",
      "|       £|\n",
      "|       €|\n",
      "+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "currency_symbols_regex = '[¥€£]'\n",
    "\n",
    "spark.sql(f'''\n",
    "    SELECT DISTINCT regexp_extract(annual_base_pay, '({currency_symbols_regex})') AS currency\n",
    "    FROM data_3\n",
    "''').show(100000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+--------+-----------------+-----------------------+\n",
      "|final_salary|currency|salary           |annual_base_pay        |\n",
      "+------------+--------+-----------------+-----------------------+\n",
      "|null        |NOK     | 450000          |NOK 450000             |\n",
      "|null        |        |-                |-                      |\n",
      "|null        |        |70000?           |~70000?                |\n",
      "|null        |null    |null             |null                   |\n",
      "|null        |EUR     |30000OS          |30000 euros            |\n",
      "|null        |USD     |36.5000/YEAR     |36.5K USD / YEAR       |\n",
      "|null        |BRL     |90000/22500      |90000 BRL / 22500 USD  |\n",
      "|null        |null    |null             |null                   |\n",
      "|null        |        |G23              |g23                    |\n",
      "|null        |        |15000(NOW)       |15k (now)              |\n",
      "|null        |        |ABOUT2           |about $2               |\n",
      "|null        |        |110000-120000    |110000-120000          |\n",
      "|null        |PKR     | 100000          |PKR 100000             |\n",
      "|null        |        |32/HR            |$32/hr                 |\n",
      "|null        |        |J                |j                      |\n",
      "|null        |PLN     |66000(250000)    |~66 000 (250 000 PLN)  |\n",
      "|null        |        |55000-60000      |55k - 60k              |\n",
      "|null        |null    |null             |null                   |\n",
      "|null        |PLN     |60000(CIRCA16000)|60000PLN (circa 16000$)|\n",
      "|null        |        |HAVE             |HAVE                   |\n",
      "+------------+--------+-----------------+-----------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT CASE WHEN regexp_like(salary, '^[0-9.,]+$')\n",
    "                THEN regexp_replace(salary, '[.,]', '') || if(regexp_like(salary, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                ELSE NULL \n",
    "                END AS final_salary,\n",
    "                currency,\n",
    "                salary,\n",
    "                annual_base_pay\n",
    "    FROM (\n",
    "        SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~ ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary,\n",
    "               regexp_extract(upper(annual_base_pay), '({currencies_regex}|{currency_symbols_regex})') AS currency,\n",
    "               annual_base_pay \n",
    "        FROM data_3\n",
    "    )\n",
    "    ORDER BY 1\n",
    "''').show(truncate=False) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "One valid case of `annual_base_pay` value was found on `annual_base_pay = 'NOK 450000'`. The problem was that the RegEx used could not remove the whitespace from the string due to the fact that it is actually a different symbol on Unicode notation (`u'\\xa0'`). This symbol was added manually to the RegEx, eliminating 16 of false-negative cases on the dataset, but further \"whitespace\" symbols could be present."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+--------+-------+---------------+\n",
      "|final_salary|currency|salary |annual_base_pay|\n",
      "+------------+--------+-------+---------------+\n",
      "|null        |NOK     | 450000|NOK 450000     |\n",
      "+------------+--------+-------+---------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT CASE WHEN regexp_like(salary, '^[0-9.,]+$')\n",
    "                THEN regexp_replace(salary, '[.,]', '') || if(regexp_like(salary, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                ELSE NULL \n",
    "                END AS final_salary,\n",
    "                currency,\n",
    "                salary,\n",
    "                annual_base_pay\n",
    "    FROM (\n",
    "        SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~ ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary,\n",
    "               regexp_extract(upper(annual_base_pay), '({currencies_regex}|{currency_symbols_regex})') AS currency,\n",
    "               annual_base_pay \n",
    "        FROM data_3\n",
    "    )\n",
    "    WHERE currency = 'NOK'\n",
    "    ORDER BY 1\n",
    "''').show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+--------+\n",
      "|(final_salary IS NULL)|count(1)|\n",
      "+----------------------+--------+\n",
      "|true                  |87      |\n",
      "|false                 |3689    |\n",
      "+----------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT final_salary IS NULL, count(1)\n",
    "    FROM (\n",
    "        SELECT CASE WHEN regexp_like(salary, '^[0-9.,]+$')\n",
    "                    THEN regexp_replace(salary, '[.,]', '') || if(regexp_like(salary, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                    ELSE NULL \n",
    "                    END AS final_salary\n",
    "        FROM (\n",
    "            SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~ ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary\n",
    "            FROM data_3\n",
    "            WHERE annual_base_pay IS NOT NULL\n",
    "        )\n",
    "    )\n",
    "    GROUP BY 1\n",
    "''').show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(final_salary=None, currency='NOK', salary='\\xa0450000', annual_base_pay='NOK\\xa0450000')]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT CASE WHEN regexp_like(salary, '^[0-9.,]+$')\n",
    "                THEN regexp_replace(salary, '[.,]', '') || if(regexp_like(salary, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                ELSE NULL \n",
    "                END AS final_salary,\n",
    "                currency,\n",
    "                salary,\n",
    "                annual_base_pay\n",
    "    FROM (\n",
    "        SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~ ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary,\n",
    "               regexp_extract(upper(annual_base_pay), '({currencies_regex}|{currency_symbols_regex})') AS currency,\n",
    "               annual_base_pay \n",
    "        FROM data_3\n",
    "    )\n",
    "    WHERE currency = 'NOK'\n",
    "''').collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "unicode_whitespace = u'\\xa0'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------+--------+------+---------------+\n",
      "|final_salary|currency|salary|annual_base_pay|\n",
      "+------------+--------+------+---------------+\n",
      "|45000000    |NOK     |450000|NOK 450000     |\n",
      "+------------+--------+------+---------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT CASE WHEN regexp_like(salary, '^[0-9.,]+$')\n",
    "                THEN regexp_replace(salary, '[.,]', '') || if(regexp_like(salary, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                ELSE NULL \n",
    "                END AS final_salary,\n",
    "                currency,\n",
    "                salary,\n",
    "                annual_base_pay\n",
    "    FROM (\n",
    "        SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~{unicode_whitespace} ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary,\n",
    "               regexp_extract(upper(annual_base_pay), '({currencies_regex}|{currency_symbols_regex})') AS currency,\n",
    "               annual_base_pay \n",
    "        FROM data_3\n",
    "    )\n",
    "    WHERE currency = 'NOK'\n",
    "''').show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------------------+--------+\n",
      "|(final_salary IS NULL)|count(1)|\n",
      "+----------------------+--------+\n",
      "|true                  |71      |\n",
      "|false                 |3705    |\n",
      "+----------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT final_salary IS NULL, count(1)\n",
    "    FROM (\n",
    "        SELECT CASE WHEN regexp_like(salary, '^[0-9.,]+$')\n",
    "                    THEN regexp_replace(salary, '[.,]', '') || if(regexp_like(salary, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                    ELSE NULL \n",
    "                    END AS final_salary\n",
    "        FROM (\n",
    "            SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~{unicode_whitespace} ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary\n",
    "            FROM data_3\n",
    "            WHERE annual_base_pay IS NOT NULL\n",
    "        )\n",
    "    )\n",
    "    GROUP BY 1\n",
    "''').show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------------------------------------+----------------------------+\n",
      "|annual_base_pay                     |salary                      |\n",
      "+------------------------------------+----------------------------+\n",
      "|$24/hr                              |24/HR                       |\n",
      "|36.5K USD / YEAR                    |36.5000/YEAR                |\n",
      "|55k - 60k                           |55000-60000                 |\n",
      "|j                                   |J                           |\n",
      "|15k (now)                           |15000(NOW)                  |\n",
      "|60000PLN (circa 16000$)             |60000(CIRCA16000)           |\n",
      "|110000-120000                       |110000-120000               |\n",
      "|1 rare pepe                         |1RAREPEPE                   |\n",
      "|~70000?                             |70000?                      |\n",
      "|30000 euros                         |30000OS                     |\n",
      "|90000 BRL / 22500 USD               |90000/22500                 |\n",
      "|g23                                 |G23                         |\n",
      "|about $2                            |ABOUT2                      |\n",
      "|1500 / day corp2corp no bennies     |1500/DAYCORP2CORPNOBENNIES  |\n",
      "|$32/hr                              |32/HR                       |\n",
      "|~66 000 (250 000 PLN)               |66000(250000)               |\n",
      "|HAVE                                |HAVE                        |\n",
      "|no                                  |NO                          |\n",
      "|Rrr                                 |RRR                         |\n",
      "|40000 euro                          |40000O                      |\n",
      "|A                                   |A                           |\n",
      "|-                                   |-                           |\n",
      "|31/ hr                              |31/HR                       |\n",
      "|60k/yr                              |60000/YR                    |\n",
      "|$35 per hour                        |35PERHOUR                   |\n",
      "|-1                                  |-1                          |\n",
      "|secret                              |SECRET                      |\n",
      "|R$ 72k                              |R72000                      |\n",
      "|£42000 / $60300                     |42000/60300                 |\n",
      "|NZ$75,000                           |NZ75,000                    |\n",
      "|42000 Euro                          |42000O                      |\n",
      "|$30/hr                              |30/HR                       |\n",
      "|43000 rubles                        |43000LES                    |\n",
      "|30000 pounds                        |30000POUNDS                 |\n",
      "|40 hr                               |40HR                        |\n",
      "|$30/hr                              |30/HR                       |\n",
      "|NZ$80000                            |NZ80000                     |\n",
      "|$69/hr                              |69/HR                       |\n",
      "|$90/hr                              |90/HR                       |\n",
      "|₹1200000                            |₹1200000                    |\n",
      "|Testing                             |TESTING                     |\n",
      "|15,00,000 Rs                        |15,00,000RS                 |\n",
      "|RMB: 225k                           |:225000                     |\n",
      "|Rs. 2,300,000                       |RS.2,300,000                |\n",
      "|19800 euro                          |19800O                      |\n",
      "|336000 RUB (~4800USD)               |336000(4800)                |\n",
      "|HK$180000                           |HK180000                    |\n",
      "|2400e                               |2400E                       |\n",
      "|a trap a day makes the boner go away|ATRAPADAYMAKESTHEBONERGOAWAY|\n",
      "|66000 EURO after TAX                |66000OAFTERTAX              |\n",
      "|x                                   |X                           |\n",
      "|23000 USD (NET)                     |23000(NET)                  |\n",
      "|£25000 ($36000)                     |25000(36000)                |\n",
      "|Food                                |FOOD                        |\n",
      "|10468 - really                      |10468-REALLY                |\n",
      "|1500000 Rs                          |1500000RS                   |\n",
      "|5.5 Million JPY                     |5.5000000ILLION             |\n",
      "|99KBRL ~27.2KUSD by today's rate    |9900027.2000BYTODAY'SRATE   |\n",
      "|₹420000                             |₹420000                     |\n",
      "|34200euros                          |34200OS                     |\n",
      "|au$80000                            |AU80000                     |\n",
      "|$50.00 hour                         |50.00HOUR                   |\n",
      "|50/hour                             |50/HOUR                     |\n",
      "|This                                |THIS                        |\n",
      "|180000 USD / yr                     |180000/YR                   |\n",
      "|520万                               |520万                       |\n",
      "|3000usd / month                     |3000/MONTH                  |\n",
      "|90000 Euro                          |90000O                      |\n",
      "|¥144000≈$20921                      |144000≈20921                |\n",
      "|R$176000                            |R176000                     |\n",
      "|a                                   |A                           |\n",
      "+------------------------------------+----------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT *\n",
    "    FROM (\n",
    "        SELECT annual_base_pay, regexp_replace(regexp_replace(regexp_replace(upper(annual_base_pay), '{currencies_regex}|{currency_symbols_regex}|[$~{unicode_whitespace} ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS salary\n",
    "        FROM data_3\n",
    "    )\n",
    "    WHERE regexp_like(salary, '^[0-9.,]+$') = FALSE\n",
    "''').show(100, truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------+--------+\n",
      "|(final_bonus IS NULL)|count(1)|\n",
      "+---------------------+--------+\n",
      "|true                 |184     |\n",
      "|false                |2485    |\n",
      "+---------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT final_bonus IS NULL, count(1)\n",
    "    FROM (\n",
    "        SELECT CASE WHEN regexp_like(bonus, '^[0-9.,]+$')\n",
    "                    THEN regexp_replace(bonus, '[.,]', '') || if(regexp_like(bonus, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                    ELSE NULL \n",
    "                    END AS final_bonus\n",
    "        FROM (\n",
    "            SELECT regexp_replace(regexp_replace(regexp_replace(upper(annual_bonus), '{currencies_regex}|{currency_symbols_regex}|[$~{unicode_whitespace} ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS bonus\n",
    "            FROM data_3\n",
    "            WHERE annual_bonus IS NOT NULL\n",
    "        )\n",
    "    )\n",
    "    GROUP BY 1\n",
    "''').show(truncate=False) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------+--------------------------------------------------------+\n",
      "|final_bonus|annual_bonus                                            |\n",
      "+-----------+--------------------------------------------------------+\n",
      "|null       |10%                                                     |\n",
      "|null       |15000 (Just for first 4 months as a hire)               |\n",
      "|null       |10%                                                     |\n",
      "|null       |2 - 3%                                                  |\n",
      "|null       |10%                                                     |\n",
      "|null       |2%                                                      |\n",
      "|null       |25%                                                     |\n",
      "|null       |$0-$50,000                                              |\n",
      "|null       |1%                                                      |\n",
      "|null       |variable, around 10% of base salary (6000$)             |\n",
      "|null       |15%                                                     |\n",
      "|null       |0-25%                                                   |\n",
      "|null       |14000-20000                                             |\n",
      "|null       |NA                                                      |\n",
      "|null       |5%                                                      |\n",
      "|null       |pepe stock                                              |\n",
      "|null       |1000-5000                                               |\n",
      "|null       |7%                                                      |\n",
      "|null       |10% of base                                             |\n",
      "|null       |$243 monthly salary increment                           |\n",
      "|null       |10%                                                     |\n",
      "|null       |a box of lube                                           |\n",
      "|null       |10%                                                     |\n",
      "|null       |10%                                                     |\n",
      "|null       |10% of Salary                                           |\n",
      "|null       |10%                                                     |\n",
      "|null       |I don't do anal                                         |\n",
      "|null       |3%                                                      |\n",
      "|null       |na                                                      |\n",
      "|null       |O                                                       |\n",
      "|null       |no                                                      |\n",
      "|null       |5%                                                      |\n",
      "|null       |N/a                                                     |\n",
      "|null       |7%                                                      |\n",
      "|null       |5%                                                      |\n",
      "|null       |<=15% of salary                                         |\n",
      "|null       |10%                                                     |\n",
      "|null       |Profit Share Bonus                                      |\n",
      "|null       |~10%                                                    |\n",
      "|null       |No                                                      |\n",
      "|null       |None                                                    |\n",
      "|null       |RELATIONS                                               |\n",
      "|null       |15%                                                     |\n",
      "|null       |-                                                       |\n",
      "|null       |15% (Profit Sharing)                                    |\n",
      "|null       |ha                                                      |\n",
      "|null       |10%                                                     |\n",
      "|null       |35k-52k (based on company performance)                  |\n",
      "|null       |up to 10%                                               |\n",
      "|null       |up to 15%                                               |\n",
      "|null       |5000-10000                                              |\n",
      "|null       |O                                                       |\n",
      "|null       |32,000 cash                                             |\n",
      "|null       |5%                                                      |\n",
      "|null       |10%                                                     |\n",
      "|null       |10% but that NEVER happens                              |\n",
      "|null       |5-10%                                                   |\n",
      "|null       |10%                                                     |\n",
      "|null       |3%                                                      |\n",
      "|null       |5-10k                                                   |\n",
      "|null       |10% of salary                                           |\n",
      "|null       |up to 10%                                               |\n",
      "|null       |18-22% of salary                                        |\n",
      "|null       |n/a                                                     |\n",
      "|null       |5%                                                      |\n",
      "|null       |8%                                                      |\n",
      "|null       |0-15%                                                   |\n",
      "|null       |Unknown                                                 |\n",
      "|null       |10%                                                     |\n",
      "|null       |Yes                                                     |\n",
      "|null       |None                                                    |\n",
      "|null       |5%                                                      |\n",
      "|null       |TBD                                                     |\n",
      "|null       |15% of base pay                                         |\n",
      "|null       |3%                                                      |\n",
      "|null       |10%                                                     |\n",
      "|null       |~4%                                                     |\n",
      "|null       |20%                                                     |\n",
      "|null       |1%                                                      |\n",
      "|null       |15%                                                     |\n",
      "|null       |300 pounds                                              |\n",
      "|null       |25,000 vested over 4 years                              |\n",
      "|null       |¯\\_(ツ)_/¯                                              |\n",
      "|null       |N/A                                                     |\n",
      "|null       |5%                                                      |\n",
      "|null       |20000 2nd year                                          |\n",
      "|null       |10%                                                     |\n",
      "|null       |Varies on performance, up to 15% of annual for exceeding|\n",
      "|null       |5%                                                      |\n",
      "|null       |5%                                                      |\n",
      "|null       |ca. 15000                                               |\n",
      "|null       |<= 15%                                                  |\n",
      "|null       |depending on performance                                |\n",
      "|null       |10%                                                     |\n",
      "|null       |-                                                       |\n",
      "|null       |1%                                                      |\n",
      "|null       |20%                                                     |\n",
      "|null       |10-20%                                                  |\n",
      "|null       |10%                                                     |\n",
      "|null       |20%                                                     |\n",
      "|null       |20%                                                     |\n",
      "|null       |6%                                                      |\n",
      "|null       |10%                                                     |\n",
      "|null       |10%                                                     |\n",
      "|null       |50% target                                              |\n",
      "|null       |28000*2                                                 |\n",
      "|null       |2%                                                      |\n",
      "|null       |10-20% Base                                             |\n",
      "|null       |?                                                       |\n",
      "|null       |up to 20% yearly salary                                 |\n",
      "|null       |?                                                       |\n",
      "|null       |n/a                                                     |\n",
      "|null       |10%                                                     |\n",
      "|null       |10%                                                     |\n",
      "|null       |3%                                                      |\n",
      "|null       |₹0                                                      |\n",
      "|null       |10%                                                     |\n",
      "|null       |0-12750NZD                                              |\n",
      "|null       |0% or 1% (most years 0%)                                |\n",
      "|null       |15%                                                     |\n",
      "|null       |10%                                                     |\n",
      "|null       |None                                                    |\n",
      "|null       |up to 13% of Base Pay                                   |\n",
      "|null       |HK$15000                                                |\n",
      "|null       |?                                                       |\n",
      "|null       |-                                                       |\n",
      "|null       |?                                                       |\n",
      "|null       |N/A                                                     |\n",
      "|null       |10-20% Annual Pay                                       |\n",
      "|null       |11%                                                     |\n",
      "|null       |n/a                                                     |\n",
      "|null       |Varies                                                  |\n",
      "|null       |8-10%                                                   |\n",
      "|null       |10%                                                     |\n",
      "|null       |?                                                       |\n",
      "|null       |5%                                                      |\n",
      "|null       |50%                                                     |\n",
      "|null       |up to 9600                                              |\n",
      "|null       |3%                                                      |\n",
      "|null       |25%                                                     |\n",
      "|null       |1.5 Million JPY                                         |\n",
      "|null       |8%                                                      |\n",
      "|null       |10%                                                     |\n",
      "|null       |3%                                                      |\n",
      "|null       |None                                                    |\n",
      "|null       |10%                                                     |\n",
      "|null       |10%                                                     |\n",
      "|null       |3%                                                      |\n",
      "|null       |3%                                                      |\n",
      "|null       |2-3000                                                  |\n",
      "|null       |10%                                                     |\n",
      "|null       |8%                                                      |\n",
      "|null       |none                                                    |\n",
      "|null       |depends on business / ~ $800                            |\n",
      "|null       |-                                                       |\n",
      "|null       |Variable                                                |\n",
      "|null       |Up to 10,000                                            |\n",
      "|null       |1%                                                      |\n",
      "|null       |3-4%                                                    |\n",
      "|null       |10-15%                                                  |\n",
      "|null       |1000-10000 (Varies, improving now)                      |\n",
      "|null       |20%                                                     |\n",
      "|null       |Between 1 and 3%                                        |\n",
      "|null       |£0-£5000                                                |\n",
      "|null       |Performance pay                                         |\n",
      "|null       |34万                                                    |\n",
      "|null       |nothing                                                 |\n",
      "|null       |8-12k                                                   |\n",
      "|null       |6%                                                      |\n",
      "|null       |0 to 3 extra salaries (1950)                            |\n",
      "|null       |10%                                                     |\n",
      "|null       |11%                                                     |\n",
      "|null       |n/a                                                     |\n",
      "|null       |5-10%                                                   |\n",
      "|null       |3%                                                      |\n",
      "|null       |NA                                                      |\n",
      "|null       |5%                                                      |\n",
      "|null       |¥12000≈1743                                             |\n",
      "|null       |R$0                                                     |\n",
      "|null       |variable                                                |\n",
      "|null       |Variable                                                |\n",
      "|null       |0-10%                                                   |\n",
      "|null       |10%                                                     |\n",
      "|null       |Might vary from 5k to 15k                               |\n",
      "+-----------+--------------------------------------------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT CASE WHEN regexp_like(bonus, '^[0-9.,]+$')\n",
    "                THEN regexp_replace(bonus, '[.,]', '') || if(regexp_like(bonus, '^[0-9.,]+?[.,][0-9]{2}$'), '', '00')\n",
    "                ELSE NULL \n",
    "                END AS final_bonus,\n",
    "                annual_bonus\n",
    "    FROM (\n",
    "        SELECT annual_bonus, regexp_replace(regexp_replace(regexp_replace(upper(annual_bonus), '{currencies_regex}|{currency_symbols_regex}|[$~{unicode_whitespace} ]', ''), '(?<=[0-9])(k|K)', '000'), '(?<=[0-9])(m|M)', '000000') AS bonus\n",
    "        FROM data_3\n",
    "        WHERE annual_bonus IS NOT NULL\n",
    "    )\n",
    "    WHERE regexp_like(bonus, '^[0-9.,]+$') = FALSE\n",
    "''').show(200, truncate=False) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`years_of_experience` column is not a range-based value, but a free-form text value, so it could not be directly converted to a numerical representation. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "|years_of_experience|\n",
      "+-------------------+\n",
      "|null               |\n",
      "|-1                 |\n",
      "|-10                |\n",
      "|-114               |\n",
      "|-13                |\n",
      "|-16                |\n",
      "|-3                 |\n",
      "|-6                 |\n",
      "|-9001              |\n",
      "|0                  |\n",
      "|0 (Just graduated) |\n",
      "|0.2                |\n",
      "|0.5                |\n",
      "|0.6                |\n",
      "|0.75               |\n",
      "|0.9                |\n",
      "|1                  |\n",
      "|1 of employment    |\n",
      "|1.2                |\n",
      "|1.25               |\n",
      "+-------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT DISTINCT years_of_experience\n",
    "    FROM data_3\n",
    "    ORDER BY 1\n",
    "''').show(truncate=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Also, many values where clearly invalid due to the fact of being negatives, and were removed (turned to NULL)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-----------------------------------------+--------+\n",
      "|(CAST(years_of_experience AS DOUBLE) > 0)|count(1)|\n",
      "+-----------------------------------------+--------+\n",
      "|null                                     |5       |\n",
      "|true                                     |3317    |\n",
      "|false                                    |120     |\n",
      "+-----------------------------------------+--------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT CAST(years_of_experience AS DOUBLE) > 0, count(1)\n",
    "    FROM data_3\n",
    "    WHERE years_of_experience IS NOT NULL\n",
    "    GROUP BY 1\n",
    "''').show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------------------+\n",
      "|years_of_experience|\n",
      "+-------------------+\n",
      "|1 of employment    |\n",
      "|<1                 |\n",
      "|0 (Just graduated) |\n",
      "|10+ years          |\n",
      "|5 years            |\n",
      "+-------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark.sql(f'''\n",
    "    SELECT years_of_experience\n",
    "    FROM data_3\n",
    "    WHERE years_of_experience IS NOT NULL\n",
    "    AND CAST(years_of_experience AS DOUBLE) IS NULL\n",
    "''').show(truncate=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- timestamp: timestamp (nullable = true)\n",
      " |-- annual_salary: decimal(15,2) (nullable = true)\n",
      " |-- annual_bonus: decimal(15,2) (nullable = true)\n",
      " |-- currency: string (nullable = true)\n",
      " |-- years_experience: double (nullable = true)\n",
      " |-- location: string (nullable = true)\n",
      " |-- job_title: string (nullable = true)\n",
      "\n",
      "+-------------------+-------------+------------+--------+----------------+-------------------+--------------------+\n",
      "|          timestamp|annual_salary|annual_bonus|currency|years_experience|           location|           job_title|\n",
      "+-------------------+-------------+------------+--------+----------------+-------------------+--------------------+\n",
      "|               null|         null|        null|    null|            null|               null|                null|\n",
      "|2016-03-21 12:54:49|    122000.00|        null|        |            18.0|        Raleigh, NC|  Software Developer|\n",
      "|2016-03-21 12:58:52|    125000.00|        0.00|        |            13.0|  San Francisco, CA|    Systems Engineer|\n",
      "|2016-03-21 12:58:57|     83000.00|       50.00|        |            null|       San Diego CA|    Systems engineer|\n",
      "|2016-03-21 12:58:58|     65000.00|     5000.00|        |            15.0|    Bentonville, AR|    Senior Developer|\n",
      "|2016-03-21 12:59:11|     86000.00|     6000.00|        |             4.0|      Cleveland, OH|   Software Engineer|\n",
      "|2016-03-21 12:59:30|    200000.00|    40000.00|        |            10.0|      Palo Alto, CA| Director of Product|\n",
      "|2016-03-21 13:00:17|    105000.00|     8500.00|        |             4.0|            Waltham|                 MTS|\n",
      "|2016-03-21 13:00:20|         null|        null|    null|            null|               null|                null|\n",
      "|2016-03-21 13:01:19|         1.00|        null|        |            null|               null|                  zz|\n",
      "|2016-03-21 13:01:19|     90500.00|        0.00|        |             2.0|Copenhagen, Denmark|  Software Developer|\n",
      "|2016-03-21 13:02:01|    105000.00|    20000.00|        |            null|               null|Sr Operations Eng...|\n",
      "|2016-03-21 13:02:04|     80000.00|        null|        |            null|               null|                asdf|\n",
      "|2016-03-21 13:02:08|    110000.00|     7000.00|        |             4.0|          Cupertino|   Software Engineer|\n",
      "|2016-03-21 13:03:03|    115000.00|        null|        |            10.0|         Austin, TX|     DevOps Engineer|\n",
      "|2016-03-21 13:03:06|     40000.00|      500.00|        |             5.0|     Eastern Oregon|       IT Technician|\n",
      "|2016-03-21 13:03:23|     30000.00|        null|        |            null|               null|   Software engineer|\n",
      "|2016-03-21 13:03:35|     45000.00|     1500.00|        |             4.0|        Madison, WI|    Lead Interviewer|\n",
      "|2016-03-21 13:03:52|    135000.00|        0.00|        |             8.0|      New York City|Senior software e...|\n",
      "|2016-03-21 13:03:56|    135000.00|     7000.00|        |            10.0|      Milwaukee, WI|   Software Engineer|\n",
      "+-------------------+-------------+------------+--------+----------------+-------------------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "def outer_monetary_query_expression(column: str):\n",
    "    return f'''\n",
    "        CAST(\n",
    "            CAST(CASE WHEN regexp_like({column}, '^[0-9.,]+$')\n",
    "                      THEN regexp_replace({column}, '[.,]', '') || if(regexp_like({column}, '^[0-9.,]+?[.,][0-9]{{2}}$'), '', '00')\n",
    "                      ELSE NULL \n",
    "                 END\n",
    "                 AS LONG\n",
    "            ) / 100\n",
    "            AS DECIMAL(15,2)\n",
    "        )\n",
    "    '''\n",
    "\n",
    "def inner_monetary_query_expression(column: str):\n",
    "    return f\"regexp_replace(regexp_replace(regexp_replace(upper({column}), '{currencies_regex}|{currency_symbols_regex}|[$~{unicode_whitespace} ]', ''), '(?<=[0-9])(K)', '000'), '(?<=[0-9])(M)', '000000') AS {column}\"\n",
    "\n",
    "\n",
    "inner_annual_bonus_expr = inner_monetary_query_expression('annual_bonus')\n",
    "outer_annual_bonus_expr = outer_monetary_query_expression('annual_bonus')\n",
    "\n",
    "inner_annual_base_pay_expr = inner_monetary_query_expression('annual_base_pay')\n",
    "outer_annual_base_pay_expr = outer_monetary_query_expression('annual_base_pay')\n",
    "\n",
    "inner_years_of_experience_expr = \"CAST(years_of_experience AS DOUBLE) AS years_of_experience\"\n",
    "outer_years_of_experience_expr = 'if(years_of_experience >= 0, years_of_experience, NULL)'\n",
    "\n",
    "timestamp_expr = \"to_timestamp(timestamp, 'M/d/yyyy HH:mm:ss') AS timestamp\" \n",
    "location_expr = 'trim(location) AS location'\n",
    "job_title_expr = 'trim(job_title) AS job_title'\n",
    "currency_expr = f\"regexp_extract(upper(annual_base_pay), '({currencies_regex}|{currency_symbols_regex})') AS currency\"\n",
    "\n",
    "final_df = spark.sql(f'''\n",
    "    SELECT timestamp,\n",
    "           {outer_annual_base_pay_expr} AS annual_salary,\n",
    "           {outer_annual_bonus_expr} AS annual_bonus,\n",
    "           currency,\n",
    "           {outer_years_of_experience_expr} years_experience,\n",
    "           location,\n",
    "           job_title\n",
    "    FROM (\n",
    "        SELECT {inner_annual_bonus_expr},\n",
    "               {inner_annual_base_pay_expr},\n",
    "               {inner_years_of_experience_expr},\n",
    "               {timestamp_expr},\n",
    "               {location_expr},\n",
    "               {job_title_expr},\n",
    "               {currency_expr}\n",
    "        FROM data_3\n",
    "    )\n",
    "''')\n",
    "\n",
    "final_df.printSchema()\n",
    "\n",
    "final_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df.write.parquet('./optimized_data/data_3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.13 ('data-engineer-Ul4bSUME-py3.9')",
   "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.9.13"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "cfa400a1a75c09257029da6d859c42e9a2035576761aa7a6b0c02641f1b90159"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}