dry_cleaning.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "fa72067f-3cb2-4ba7-9ac0-64b9b37fa53b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Defaulting to user installation because normal site-packages is not writeable\n",
      "Requirement already satisfied: polars in /home/anopsy/.local/lib/python3.10/site-packages (0.20.31)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install --upgrade polars"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "d9505bff-f0b4-40da-92f1-54aa46fa533b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "9d994e33-4019-4893-949d-0e099f23b424",
   "metadata": {},
   "outputs": [],
   "source": [
    "#not paarsing dates so I can first perform string operation on it\n",
    "df_old = pl.read_csv(\"data/9june2023.csv\", try_parse_dates=False)\n",
    "df_new = pl.read_csv(\"data/2april2024.csv\", try_parse_dates=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "0401bf47-b6f1-444d-9244-0b3e7e404745",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_old = df_old.with_columns(sample_date = pl.lit(\"2023\"))\n",
    "df_new = df_new.with_columns(sample_date = pl.lit(\"2024\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "714137be-2189-49e6-8dfc-6622cdc42409",
   "metadata": {},
   "outputs": [],
   "source": [
    "whole_df = df_old.vstack(df_new)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "7380ec3c-d8e4-4172-ba59-606445da6a98",
   "metadata": {},
   "outputs": [],
   "source": [
    "ex_rates = pl.read_csv(\"data/ex_rate.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2dde9e1-2809-4294-8386-f1062d968217",
   "metadata": {},
   "source": [
    "### 1. Removing duplicates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "8a902730-b7d6-48b8-9df5-6b95588a40d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "whole_df = whole_df.unique()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "df66594d-17ed-4e83-a06d-2739bfd8bd74",
   "metadata": {},
   "source": [
    "### 2. Cleaning comp_est"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fd92cba7-5be5-41a3-8b76-85f6e3850133",
   "metadata": {},
   "outputs": [],
   "source": [
    "def split_ranges(df, col):\n",
    "    '''\n",
    "    calculates the length of a col_est, splits the longer str's from comp_est if they're an amalgamation of compensation ranges\n",
    "    computes mean of the two numbers\n",
    "    and saves it in a new column\n",
    "    '''\n",
    "    df = df.with_columns(len_comp = pl.col(col).str.len_chars())\n",
    "    df = df.with_columns(est = pl.when((pl.col(\"len_comp\")>10) & (pl.col(col).str.contains(\"[123456789]+[0]+[123456789]+[0]+\")))\n",
    "            .then(pl.col(col).str.extract_all(\"[123456789]+[0]+\"))\n",
    "            .otherwise(pl.concat_list(col, col))\n",
    "    )\n",
    "    df = df.with_columns(est_float = pl.col('est').cast(pl.List(pl.Float64)))\n",
    "    df = df.with_columns(compensation = pl.col('est_float').list.mean())\n",
    "    return df\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "9ec2b827-ee71-4091-b457-1cbf49310eb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "def currency_exchange(df, rates_df):\n",
    "    '''\n",
    "    joins the main df with a currency exchange rates table\n",
    "    calculates the compensation in dolars\n",
    "    '''\n",
    "    df = df.join(rates_df, how='left', on='country', coalesce=True)\n",
    "    df = df.with_columns(comp_dol = pl.when(pl.col('rate').is_not_null())\n",
    "                                        .then(pl.col('compensation')/pl.col('rate'))\n",
    "                                        .otherwise(pl.col('compensation')))\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "2698f9d8-5818-4393-8db7-8a018a55a5a6",
   "metadata": {},
   "outputs": [],
   "source": [
    "def hourly_yearly(df, col, lower, upper):\n",
    "    '''\n",
    "    assumes that values between lower and upper\n",
    "    are hourly wages and based on that calculates the yearly wage\n",
    "    '''\n",
    "    df = df.with_columns(pl.when((pl.col(col)>lower) & (pl.col(col)<upper))\n",
    "                            .then(pl.col(col)*40*52)\n",
    "                            .otherwise(pl.col(col))\n",
    "    )\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "da43bd6a-726e-46bf-80db-a4a98726bc53",
   "metadata": {},
   "outputs": [],
   "source": [
    "def monthly_yearly(df, col, lower, upper):\n",
    "    '''\n",
    "    assumes that values between lower and upper\n",
    "    are hourly wages and based on that calculates the yearly wage\n",
    "    '''\n",
    "    df = df.with_columns(pl.when((pl.col(col)>lower) & (pl.col(col)<upper))\n",
    "                            .then(pl.col(col)*12)\n",
    "                            .otherwise(pl.col(col))\n",
    "    )\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "aaeebddb-7382-47b0-86c3-bac583d9409e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def remove_comp_outliers(df, col, lower, upper):\n",
    "    '''\n",
    "    assumes extreme compensation numbers (0-9 and > 1_000_000) are a mistake\n",
    "    and replaces them with a mean of compensation column\n",
    "    '''\n",
    "    mean_val = df.filter(pl.col(col)>10, pl.col(col)<upper).select(col).mean()\n",
    "    df = df.with_columns(comp_dol = pl.when((pl.col(col)<lower) | (pl.col(col)>upper))\n",
    "                                        .then(mean_val)\n",
    "                                        .otherwise(pl.col(col))\n",
    "    )\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4bfc767-db36-4945-9d83-db7ba838e99c",
   "metadata": {},
   "source": [
    "### 3. Missing categorical to \"unknown\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "920e847c-737b-42f8-8cfb-7034472edd54",
   "metadata": {},
   "outputs": [],
   "source": [
    "def replace_missing(df, list_col, replace_with = \"unknown\"):\n",
    "    '''\n",
    "    replaces missing/null values with \"unknown\" category\n",
    "    '''\n",
    "    for col in list_col:\n",
    "        df = df.with_columns(pl.col(col).fill_null(replace_with))\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dd18d92d-6410-41a5-b942-ddbf69a8d45c",
   "metadata": {},
   "source": [
    "### 4. Cleaning job_names & language"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8c0f5a77-3739-47ff-8e08-62227f363673",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_jobnames(df):\n",
    "    '''\n",
    "    replaces \"sr.\" and \"jr.\" with respectively \"senior\" and \"junior\"\n",
    "    '''\n",
    "    df = df.with_columns(pl.col(\"job_name\").str.to_lowercase().str.replace(\"sr.\", \"senior\").str.replace(\"jr.\", \"junior\"))\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "a12282be-079b-4b02-9851-e24727c362fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "def clean_language(df):\n",
    "    '''\n",
    "    takes first two character from a column\n",
    "    '''\n",
    "    df = df.with_columns(pl.col('language').str.head(2))\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2fb149b-bb27-4d0f-a4fa-ac42bb7b38c0",
   "metadata": {},
   "source": [
    "### 5. Creating separate df for tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3e0d99ee-14fb-4136-8380-884062d4c966",
   "metadata": {},
   "outputs": [],
   "source": [
    "def tags_df(df, list_cols):\n",
    "    '''\n",
    "    combines tags cols into one columns of strings\n",
    "    '''\n",
    "    for col in list_cols:\n",
    "        df = df.with_columns(\n",
    "            pl.col(col).str.extract_all(r\"\\w+\").cast(pl.List(pl.String))\n",
    "        )\n",
    "    df = df.with_columns(all_tags=pl.col(list_cols[0])\n",
    "                    .list.concat(list_cols[1:])\n",
    "                    .list.unique()\n",
    "                    .list.join(\" \"))\n",
    "\n",
    "    return df\n",
    "            \n",
    "   "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d5d1d87-070a-49e5-9546-a800e7c5656e",
   "metadata": {},
   "source": [
    "### 6. Adding a column of job_type (lead vs IC)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d6726cb4-4940-44fa-a8ef-b892d990e889",
   "metadata": {},
   "outputs": [],
   "source": [
    "def lead_ic(df):\n",
    "    '''\n",
    "    adds a column stating if a job is a leading position or IC\n",
    "    '''\n",
    "    df = df.with_columns(\n",
    "        job_type=pl.when(pl.col(\"seniority\").is_in(ic))\n",
    "                .then(pl.lit(\"ic\"))\n",
    "                .when(pl.col(\"seniority\").is_in(lead))\n",
    "                .then(pl.lit(\"lead\"))\n",
    "                .otherwise(pl.lit(\"unclear\"))\n",
    "    )\n",
    "    return df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c711469-a1bb-4e65-9c9f-2e67ffdcb90f",
   "metadata": {},
   "source": [
    "### 7. Casting data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "618cf2ec-97ba-44f3-9ee8-9e9d0d97c6fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "selected_columns= ['job_name','hours','remote','company_name','education','seniority',\n",
    "                'language','city','country','job_published_at','sample_date','comp_dol','all_tags']\n",
    "cat_cols = ['hours','remote','company_name','education','seniority', 'language','city','country', 'sample_date', 'job_type']\n",
    "str_col = ['job_name', 'all_tags']\n",
    "data_col = ['job_published_at']\n",
    "num_col = ['comp_dol']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "a81a184c-27c5-46c1-9257-b7c5a248b6c5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def cast_dtypes(df, selected):\n",
    "    '''\n",
    "    changes the dtypes of specified columns\n",
    "    '''\n",
    "    clean_df = df.select(selected)\n",
    "    for col in selected:\n",
    "        if col in cat_cols:\n",
    "            clean_df = clean_df.with_columns(pl.col(col).cast(pl.Categorical))\n",
    "        if col in data_col:\n",
    "            clean_df = clean_df.with_columns(pl.col(col).str.to_datetime().cast(pl.Date))\n",
    "        if col in num_col:\n",
    "            clean_df = clean_df.with_columns(pl.col(col).cast(pl.Int64))\n",
    "\n",
    "    return clean_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4297fe22-ab1a-4532-91e3-c55de28737a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "whole_df = split_ranges(whole_df, \"comp_est\")\n",
    "whole_df = currency_exchange(whole_df, ex_rates)\n",
    "whole_df = hourly_yearly(whole_df, 'comp_dol', 10, 200)\n",
    "whole_df = monthly_yearly(whole_df, \"comp_dol\", 1_000, 10_000)\n",
    "whole_df = remove_comp_outliers(whole_df, \"comp_dol\", 10, 1_000_000)\n",
    "whole_df = replace_missing(whole_df, ['hours','remote','education','language'], replace_with=\"Unclear\")\n",
    "whole_df = replace_missing(whole_df , ['seniority'], replace_with=\"Unclear Seniority\")\n",
    "whole_df = clean_language(whole_df)\n",
    "whole_df = clean_jobnames(whole_df)\n",
    "whole_df = tags_df(whole_df,[\"tags_matched\", \"tag_categories\", \"categories\"])\n",
    "whole_df = lead_ic(whole_df)\n",
    "whole_df = cast_dtypes(whole_df, selected_columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f88d82f3-01d4-48e9-94e6-676e147d8f84",
   "metadata": {},
   "source": [
    "### 8. Setting data range/ Removing date outliers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7cc0e1be-1a28-4515-aecf-52b18bcd3b61",
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df = whole_df.filter(pl.col('job_published_at').is_between(datetime(2020, 12, 31), datetime(2024, 4, 2)))\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99be7b88-c8d6-4d5c-bd80-03d5d7ff8a2b",
   "metadata": {},
   "source": [
    "### 9. Saving csv's "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "459481ab-e0a1-4695-9973-08e58d0a40f0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['job_name',\n",
       " 'hours',\n",
       " 'remote',\n",
       " 'company_name',\n",
       " 'education',\n",
       " 'seniority',\n",
       " 'language',\n",
       " 'city',\n",
       " 'country',\n",
       " 'job_published_at',\n",
       " 'sample_date',\n",
       " 'comp_dol',\n",
       " 'all_tags']"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df = clean_df.rename({\"company_name\" : \"company\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "f8cf2c86-42a2-4d37-a082-1ab0cbb38fe5",
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df.write_csv(\"data/analysis_df.csv\", separator=\",\") #df for analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "d00fd562-f23f-4028-8c59-ff69d543ca5e",
   "metadata": {},
   "outputs": [],
   "source": [
    "comp_df = clean_df.drop_nulls(subset = [\"comp_dol\", \"all_tags\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "d25ca558-47de-4213-8075-9a187f96b0cc",
   "metadata": {},
   "outputs": [],
   "source": [
    "comp_df.write_csv(\"data/comp_df.csv\", separator=\",\") #df for modelling"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}