DataCheckpoint_Group050_WI24.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**If you lost points on the last checkpoint you can get them back by responding to TA/IA feedback**  \n",
    "\n",
    "Update/change the relevant sections where you lost those points, make sure you respond on GitHub Issues to your TA/IA to call their attention to the changes you made here.\n",
    "\n",
    "Please update your Timeline... no battle plan survives contact with the enemy, so make sure we understand how your plans have changed."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# COGS 108 - Data Checkpoint"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Names\n",
    "\n",
    "Inna Amogolonova\\\n",
    "Dawson Tam\\\n",
    "Kate Zhou\\\n",
    "Tyler May\\\n",
    "Charvi Shukla"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Research Question"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "-  Include a specific, clear data science question.\n",
    "-  Make sure what you're measuring (variables) to answer the question is clear\n",
    "\n",
    "What is your research question? Include the specific question you're setting out to answer. This question should be specific, answerable with data, and clear. A general question with specific subquestions is permitted. (1-2 sentences)\n",
    "\n",
    "\n",
    "\n",
    "**How do the top three most prevalent skills required for data science jobs (from Linkedin) in the United States for 2024 align with the educational content provided by Coursera courses, measured by percentage of courses that teach the relevant skills among all Coursera data science courses?**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Background and Prior Work"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "- Include a general introduction to your topic\n",
    "- Include explanation of what work has been done previously\n",
    "- Include citations or links to previous work\n",
    "\n",
    "This section will present the background and context of your topic and question in a few paragraphs. Include a general introduction to your topic and then describe what information you currently know about the topic after doing your initial research. Include references to other projects who have asked similar questions or approached similar problems. Explain what others have learned in their projects.\n",
    "\n",
    "Find some relevant prior work, and reference those sources, summarizing what each did and what they learned. Even if you think you have a totally novel question, find the most similar prior work that you can and discuss how it relates to your project.\n",
    "\n",
    "References can be research publications, but they need not be. Blogs, GitHub repositories, company websites, etc., are all viable references if they are relevant to your project. It must be clear which information comes from which references. (2-3 paragraphs, including at least 2 references)\n",
    "\n",
    " **Use inline citation through HTML footnotes to specify which references support which statements** \n",
    "\n",
    "For example: After government genocide in the 20th century, real birds were replaced with surveillance drones designed to look just like birds.<a name=\"cite_ref-1\"></a>[<sup>1</sup>](#cite_note-1) Use a minimum of 2 or 3 citations, but we prefer more.<a name=\"cite_ref-2\"></a>[<sup>2</sup>](#cite_note-2) You need enough to fully explain and back up important facts. \n",
    "\n",
    "Note that if you click a footnote number in the paragraph above it will transport you to the proper entry in the footnotes list below.  And if you click the ^ in the footnote entry, it will return you to the place in the main text where the footnote is made.\n",
    "\n",
    "To understand the HTML here, `<a name=\"#...\"> </a>` is a tag that allows you produce a named reference for a given location.  Markdown has the construciton `[text with hyperlink](#named reference)` that will produce a clickable link that transports you the named reference.\n",
    "\n",
    "1. <a name=\"cite_note-1\"></a> [^](#cite_ref-1) Lorenz, T. (9 Dec 2021) Birds Aren’t Real, or Are They? Inside a Gen Z Conspiracy Theory. *The New York Times*. https://www.nytimes.com/2021/12/09/technology/birds-arent-real-gen-z-misinformation.html \n",
    "2. <a name=\"cite_note-2\"></a> [^](#cite_ref-2) Also refs should be important to the background, not some randomly chosen vaguely related stuff. Include a web link if possible in refs as above.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we are approaching graduation, many of us undergraduate students are concerned about finding a job after we graduate. Many students worry if the skills and experiences gained in college will be enough to find a job in their desired field. In addition to that, people without a technical degree wonder if it’s possible to learn the required skills through means other than college. According to a blog by Rice University <a name=\"cite_ref-1\"></a>[<sup>1</sup>](#cite_note-1), both technical and non-technical skills are crucial for data scientists in the modern world. For technical skills the most popular listed on there are advanced math (calculus, linear algebra), programming and machine learning. This begs the question: is it possible to learn those skills independently through online platforms in order to land a job in the field?. \n",
    "\n",
    "According to this article <a name=\"cite_ref-2\"></a>[<sup>2</sup>](#cite_note-2), \"many [tech] companies have been de-prioritizing their degree requirements, especially if candidates can demonstrate they have the necessary skills to thrive in the role\". With the huge rise of MOOCs (Massive Open Online Courses) over the past few years, it might be possible to learn the necessary skills online in order to get the desired job in the field. \"MOOCs have the potential to be as effective as classroom learning\", according to a meta-analysis of effectiveness of online education <a name=\"cite_ref-3\"></a>[<sup>3</sup>](#cite_note-3), but best results come from blending classroom education with MOOCs. Generally MOOCs are effective for learners because they allow them to access a massive amount of new skills and often are free or much cheaper than college classes are. The rise of MOOCs since 2012 has created a new interesting avenue for acquiring education and work-force skills and needs to be further researched on how the quality of knowledge compares to more traditional methods. Nonetheless, having those resources might be helpful when looking to apply for jobs, especially in technical fields like data science. In this project we will explore how accessible relevant courses are by looking at how many Coursera data science courses align with current data science job market skill demands.\n",
    "\n",
    "\n",
    "1. <a name=\"cite_note-1\"></a> [^](#cite_ref-1) (22 Dec 2022) 12 Essential Skills Required for Data Scientists in 2023. *RICE University*. https://csweb.rice.edu/academics/graduate-programs/online-mds/blog/data-science-skills\n",
    "2. <a name=\"cite_note-2\"></a> [^](#cite_ref-2) Brian T. Horowitz (28 Aug 2023) Do You Need a Four-Year Degree to Get a Job in Tech? *DICE*. https://www.dice.com/career-advice/do-you-need-a-four-year-degree-to-get-a-job-in-tech\n",
    "3. <a name=\"cite_note-3\"></a> [^](#cite_ref-3) Justin M. Weinhardt, Traci Sitzmann (June 2019) Revolutionizing training and education? Three questions regarding massive open online courses (MOOCs). *Human Resource Management Review*. https://www.sciencedirect.com/science/article/abs/pii/S1053482218303814\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Hypothesis\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "- Include your team's hypothesis\n",
    "- Ensure that this hypothesis is clear to readers\n",
    "- Explain why you think this will be the outcome (what was your thinking?)\n",
    "\n",
    "What is your main hypothesis/predictions about what the answer to your question is? Briefly explain your thinking. (2-3 sentences)\n",
    "\n",
    "**Hypothesis:** \\\n",
    "There is a strong alignment between the top three most prevalent skills required for data science jobs (from Linkedin) in 2024 and the educational content of Coursera's data science courses, with at least 70% of Coursera's data science courses covering these skills.\n",
    "\n",
    "**Reasoning:** \\\n",
    "Big online educational companies such as Coursera actively keep track of skills that are heavily demanded in industry, and add new courses that train the workforce, and fulfill this high demand. Furthermore, the increase in Industry-academia partnerships (IBM and Coursera, Google and Udacity, AWS and edX), will also contribute to a greater number of courses that align with skill requirements in job listings. \n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data overview\n",
    "\n",
    "For each dataset include the following information\n",
    "- Dataset #1\n",
    "  - **Dataset Name:** Data Science Job Postings & Skills (2024)\n",
    "  - **Link to the dataset:** https://www.kaggle.com/datasets/asaniczka/data-science-job-postings-and-skills\n",
    "  - **Number of observations:** 12217 observations\n",
    "  - **Number of variables:** 16 variables \n",
    "- Dataset #2 \n",
    "  - **Dataset Name:** Coursera Dataset\n",
    "  - **Link to the dataset:** https://www.kaggle.com/datasets/elvinrustam/coursera-dataset\n",
    "  - **Number of observations:** 8369 observations \n",
    "  - **Number of variables:** 12 variables\n",
    "\n",
    "Now write 2 - 5 sentences describing each dataset here. Include a short description of the important variables in the dataset; what the metrics and datatypes are, what concepts they may be proxies for. Include information about how you would need to wrangle/clean/preprocess the dataset\n",
    "\n",
    "**Dataset #1: Data Science Job Postings & Skills (2024)** \\\n",
    "This is a 20 megabyte dataset consisting of data on data science job listings from Linkedin. Here are some useful variables that we will be utilizing:\n",
    "- `Job Title`: String, title of the job posting\n",
    "- `Company`: String, name of the company offering a job\n",
    "- `Search Country`: String, country where the job is searched\n",
    "- `Job Summary`: String, job summary\n",
    "- `Job Type`: String, nature of the job (on-site, remote, etc)\n",
    "- `Job Skills`: String, skills extracted using AI from the job description\n",
    "\n",
    "**Cleaning process:** \n",
    "\n",
    "We first exracted all useful columns from the three `.csv` files containing `job_postings`, `summaries` and `skills`. Each of these contained a column for a `job_link` which was used to join the three columns. We also only kept jobs from the United States. We checked for rows containing null values, but there were none in the dataset. We standardized the column names (so that there are no trailing white spaces and upper case characters). The `skill` column entries were split into arrays based on a comma delimiter, transforming a string list of skills. Finally, we wrote a standardize function to standardize the text in the `summary` column. We converted text to lowercase, replaced new lines and tabs with spaces, and removing special characters while preserving basic punctuation -- this is essential for doing any form of text analysis.\n",
    "\n",
    "**Concepts:** \n",
    "\n",
    "The skills series will be useful for extracting the top three skills in the data science field. Information about what skills are currently trending in the Data Science industry can also be extracted from the summary column. The country series was used to filter out only the United States positions. The `job_level` can be used towards further research, to see if there are specific skills that \"medium\" or \"senior\" level jobs require. \n",
    "\n",
    "**Dataset #2: Coursera Dataset** \\\n",
    "This is a 4 megabyte dataset of Coursera's course offerings. Here are some useful variables that we will be utilizing:\n",
    "- `Course Title`: String, indicating the name of the course\n",
    "- `Rating`: Float, average rating provided by users that have completed the course.\n",
    "- `What you will learn:` String, describing the objective of the coure\n",
    "- `Skill gain`: String, detailing specific skills acquired through the course\n",
    "- `Keyword`: String, a tag used to classify courses based on themes or areas of interest. Eg: \"Arts and Humanities\"\n",
    "- `Number of Reviews`: int, count of number of users that have submitted a review for the course.\n",
    "\n",
    "**Cleaning process:**\n",
    "\n",
    "We first selected all relevant columns from the dataset, and checkef if any rows have null values. We removed reows containing non-ASCII characters since they indicate that the entries are in languages other than English or with special characters. Columns were renamed to have standardized names. We then standardize `level` column to have standardized values such as 'beginner', 'intermediate', 'advanced', and 'N/A' for not specified. Entries in the `rating` and `num_review` columns were converted to numeric types to ensure that only qunatitative data was included. The dataset was then filtered to remove courses not related to technology (removed humanities, health, business etc related courses). Then, we standardized the keywords to have shorter acronyms, and removed white spaces. Similar to the Jobs dataset, the `skill` column entries, initially presented as comma-separated strings, were split into arrays. Finally `jobs_data` was sorted by the number of reviews and then by rating, prioritizing courses with higher engagement and potentially higher relevance or quality. \n",
    "\n",
    "**Concepts:**\n",
    "\n",
    "The `num_reviews` variable and `rating` proxies for engagement and popularity of a course. The `skills` column, containing multiple arrays of skills can be used to determine popularity and availability of skills. The `keyword` variable can be used to see what department offers the most relevant skills needed as a qualification for a Data Science job in 2024.\n",
    "\n",
    "\n",
    "**If you plan to use multiple datasets, add a few sentences about how you plan to combine these datasets.**\n",
    "\n",
    "We combined multiple datasets to form our `jobs_data` dataset by using the `.join()` method. Since the datasets correlated with each other by a unique link, this was a relatively straightforward task. We will cross reference out second dataset (Coursera dataset) with our first dataset (Jobs dataset) by creating a count of how many relevant skills courses there are on Coursera. First we will determine what are the three most prevalent skills and then, one by one, we will cross reference what is the percentage of courses that cover a speecific skill among all technical courses on the platform. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset #1: Data Science Job Postings & Skills (2024)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "# importing all CSV files in the Job postings dataset\n",
    "job_posting = pd.read_csv('job-postings/job_postings.csv')   \n",
    "job_summaries = pd.read_csv('job-postings/job_summary.csv')   \n",
    "job_skills = pd.read_csv('job-postings/job_skills.csv')   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>job_title</th>\n",
       "      <th>company</th>\n",
       "      <th>job_level</th>\n",
       "      <th>country</th>\n",
       "      <th>summary</th>\n",
       "      <th>skill</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Senior Machine Learning Engineer</td>\n",
       "      <td>Jobs for Humanity</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>company description jobs for humanity is partn...</td>\n",
       "      <td>[Machine Learning, Programming, Python, Scala,...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Principal Software Engineer, ML Accelerators</td>\n",
       "      <td>Aurora</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>who we are aurora nasdaq aur is delivering the...</td>\n",
       "      <td>[C++, Python, PyTorch, TensorFlow, MXNet, CUDA...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Senior ETL Data Warehouse Specialist</td>\n",
       "      <td>Adame Services LLC</td>\n",
       "      <td>Associate</td>\n",
       "      <td>United States</td>\n",
       "      <td>location new york city, ny position summary ou...</td>\n",
       "      <td>[ETL, Data Integration, Data Transformation, D...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Senior Data Warehouse Developer / Architect</td>\n",
       "      <td>Morph Enterprise</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>responsibilities candidate must have significa...</td>\n",
       "      <td>[Data Lakes, Data Bricks, Azure Data Factory P...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Lead Data Engineer</td>\n",
       "      <td>Dice</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>dice is the leading career destination for tec...</td>\n",
       "      <td>[Java, Scala, Python, RDBMS, NoSQL, Redshift, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12210</th>\n",
       "      <td>Data Entry Clerk - Excel</td>\n",
       "      <td>Corestaff Services</td>\n",
       "      <td>Associate</td>\n",
       "      <td>United States</td>\n",
       "      <td>benefits  medical, dental, and vision coverage...</td>\n",
       "      <td>[Data Entry, Microsoft Excel, VLOOKUP, PivotTa...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12213</th>\n",
       "      <td>Corporate AML Alert Investigation Specialist</td>\n",
       "      <td>Glacier Bancorp, Inc.</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>about the role please note review of applicant...</td>\n",
       "      <td>[Investigation, Antimoney laundering, Fraud, B...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12214</th>\n",
       "      <td>Senior Data Scientist</td>\n",
       "      <td>Highnote</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>about highnote founded in 2020 by a team of le...</td>\n",
       "      <td>[Data Science, Quantitative Modeling, SQL, Dat...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12215</th>\n",
       "      <td>Senior Data Engineer</td>\n",
       "      <td>CompSource Mutual Insurance Company</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>are you an experienced data engineer in oklaho...</td>\n",
       "      <td>[Data Engineering, Data Quality, SQL, Python, ...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12216</th>\n",
       "      <td>Medical Technologist, MLS or MLT</td>\n",
       "      <td>Community Health Systems</td>\n",
       "      <td>Mid senior</td>\n",
       "      <td>United States</td>\n",
       "      <td>job description weekend night shift position f...</td>\n",
       "      <td>[Medical Technology, MLS, Microbiology, Clinic...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10291 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          job_title  \\\n",
       "0                  Senior Machine Learning Engineer   \n",
       "1      Principal Software Engineer, ML Accelerators   \n",
       "2              Senior ETL Data Warehouse Specialist   \n",
       "3       Senior Data Warehouse Developer / Architect   \n",
       "4                                Lead Data Engineer   \n",
       "...                                             ...   \n",
       "12210                      Data Entry Clerk - Excel   \n",
       "12213  Corporate AML Alert Investigation Specialist   \n",
       "12214                         Senior Data Scientist   \n",
       "12215                          Senior Data Engineer   \n",
       "12216              Medical Technologist, MLS or MLT   \n",
       "\n",
       "                                   company   job_level        country  \\\n",
       "0                        Jobs for Humanity  Mid senior  United States   \n",
       "1                                   Aurora  Mid senior  United States   \n",
       "2                       Adame Services LLC   Associate  United States   \n",
       "3                         Morph Enterprise  Mid senior  United States   \n",
       "4                                     Dice  Mid senior  United States   \n",
       "...                                    ...         ...            ...   \n",
       "12210                   Corestaff Services   Associate  United States   \n",
       "12213                Glacier Bancorp, Inc.  Mid senior  United States   \n",
       "12214                             Highnote  Mid senior  United States   \n",
       "12215  CompSource Mutual Insurance Company  Mid senior  United States   \n",
       "12216             Community Health Systems  Mid senior  United States   \n",
       "\n",
       "                                                 summary  \\\n",
       "0      company description jobs for humanity is partn...   \n",
       "1      who we are aurora nasdaq aur is delivering the...   \n",
       "2      location new york city, ny position summary ou...   \n",
       "3      responsibilities candidate must have significa...   \n",
       "4      dice is the leading career destination for tec...   \n",
       "...                                                  ...   \n",
       "12210  benefits  medical, dental, and vision coverage...   \n",
       "12213  about the role please note review of applicant...   \n",
       "12214  about highnote founded in 2020 by a team of le...   \n",
       "12215  are you an experienced data engineer in oklaho...   \n",
       "12216  job description weekend night shift position f...   \n",
       "\n",
       "                                                   skill  \n",
       "0      [Machine Learning, Programming, Python, Scala,...  \n",
       "1      [C++, Python, PyTorch, TensorFlow, MXNet, CUDA...  \n",
       "2      [ETL, Data Integration, Data Transformation, D...  \n",
       "3      [Data Lakes, Data Bricks, Azure Data Factory P...  \n",
       "4      [Java, Scala, Python, RDBMS, NoSQL, Redshift, ...  \n",
       "...                                                  ...  \n",
       "12210  [Data Entry, Microsoft Excel, VLOOKUP, PivotTa...  \n",
       "12213  [Investigation, Antimoney laundering, Fraud, B...  \n",
       "12214  [Data Science, Quantitative Modeling, SQL, Dat...  \n",
       "12215  [Data Engineering, Data Quality, SQL, Python, ...  \n",
       "12216  [Medical Technology, MLS, Microbiology, Clinic...  \n",
       "\n",
       "[10291 rows x 6 columns]"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Get relevant columns from the job postings csv file\n",
    "job_desciptors = ['job_link','job_title', 'company', 'job_level', 'search_country']\n",
    "df1 = job_posting[job_desciptors]\n",
    "\n",
    "# Get relevant columns from the summaries csv file\n",
    "df2 = job_summaries[\"job_summary\"]\n",
    "\n",
    "# Get relevant columns from the skills csv file\n",
    "df3 = job_skills[\"job_skills\"]\n",
    "\n",
    "# Set 'job_link' as the index if it's not already, for the purpose of merging\n",
    "df1.set_index('job_link', inplace=True)\n",
    "df2 = job_summaries.set_index('job_link')\n",
    "df3 = job_skills.set_index('job_link')\n",
    "\n",
    "# Merge the three datasets together\n",
    "jobs_data = df1.join([df2, df3])\n",
    "jobs_data.reset_index(inplace=True)\n",
    "\n",
    "# only keep the data for the United States\n",
    "jobs_data = jobs_data[jobs_data['search_country'] == 'United States']\n",
    "\n",
    "# look for null rows and drop them\n",
    "null_rows = jobs_data[jobs_data.isnull().any(axis=1)] \n",
    "jobs_data.dropna()\n",
    "\n",
    "# the jon_link column was only required for merging. Drop after merging is done \n",
    "jobs_data = jobs_data.drop('job_link', axis=1) \n",
    "\n",
    "# change column names\n",
    "jobs_data.columns = ['job_title', 'company', 'job_level', 'country', 'summary', 'skill']\n",
    "\n",
    "# CLEANING THE 'SKILL' COLUMN\n",
    "jobs_data['skill'] = jobs_data['skill'].str.split(', ')\n",
    "\n",
    "# CLEANING THE \"SUMMARY COLUMN\"\n",
    "def standardize_summary(summary):\n",
    "    # Convert to lowercase\n",
    "    summary = summary.lower()\n",
    "    # Replace new lines and tabs with a space\n",
    "    summary = re.sub(r'\\n', ' ', summary)\n",
    "    summary = re.sub(r'\\t', ' ', summary)\n",
    "    # Remove special characters (keeping only letters, numbers, and basic punctuation)\n",
    "    summary = re.sub(r'[^a-zA-Z0-9 .,!?;]', '', summary)\n",
    "    return summary\n",
    "# Applying the standardization\n",
    "jobs_data['summary'] = jobs_data['summary'].apply(standardize_summary)\n",
    "\n",
    "jobs_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Dataset #2: Coursera Dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Course Title', 'Rating', 'Level', 'Schedule', 'What you will learn',\n",
       "       'Skill gain', 'Modules', 'Instructor', 'Offered By', 'Keyword',\n",
       "       'Course Url', 'Duration to complete (Approx.)', 'Number of Review'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# importing the Coursera dataset\n",
    "courses = pd.read_csv(\"coursera-courses/CourseraDataset-Clean.csv\")\n",
    "courses.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of null rows:  0\n",
      "Different levels of courses in this dataset:  ['Beginner level' 'Intermediate level' 'Not specified' 'Advanced level']\n",
      "Keywords in this dataset are:  ['Arts and Humanities' 'Business' 'Computer Science' 'DataScience'\n",
      " 'Health' 'Information Technology' 'Math and Logic' 'Personal Development'\n",
      " 'Physical Science and Engineering' 'Social Sciences']\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>course_title</th>\n",
       "      <th>rating</th>\n",
       "      <th>level</th>\n",
       "      <th>skill</th>\n",
       "      <th>keyword</th>\n",
       "      <th>num_review</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Programming for Everybody (Getting Started wit...</td>\n",
       "      <td>4.8</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[Python Syntax And Semantics, Basic Programmin...</td>\n",
       "      <td>CS</td>\n",
       "      <td>225830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Programming for Everybody (Getting Started wit...</td>\n",
       "      <td>4.8</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[Python Syntax And Semantics, Basic Programmin...</td>\n",
       "      <td>DS</td>\n",
       "      <td>225830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Programming for Everybody (Getting Started wit...</td>\n",
       "      <td>4.8</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[Python Syntax And Semantics, Basic Programmin...</td>\n",
       "      <td>MATH</td>\n",
       "      <td>225830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Python for Everybody Specialization</td>\n",
       "      <td>4.8</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[Json, Xml, Python Programming, Database (DBMS)]</td>\n",
       "      <td>CS</td>\n",
       "      <td>209311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Python for Everybody Specialization</td>\n",
       "      <td>4.8</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[Json, Xml, Python Programming, Database (DBMS)]</td>\n",
       "      <td>DS</td>\n",
       "      <td>209311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2970</th>\n",
       "      <td>Audit System with Spring Boot AOP</td>\n",
       "      <td>0.0</td>\n",
       "      <td>intermediate</td>\n",
       "      <td>[Application Design, Microservices, Spring Boo...</td>\n",
       "      <td>MATH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2971</th>\n",
       "      <td>CompTIA a+ Network</td>\n",
       "      <td>0.0</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[scripting, Server Administrator, Authenticati...</td>\n",
       "      <td>MATH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2972</th>\n",
       "      <td>MBA Essentials</td>\n",
       "      <td>0.0</td>\n",
       "      <td>intermediate</td>\n",
       "      <td>[Not specified]</td>\n",
       "      <td>MATH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2973</th>\n",
       "      <td>Exam Prep CKA: Certified Kubernetes Administrator</td>\n",
       "      <td>0.0</td>\n",
       "      <td>intermediate</td>\n",
       "      <td>[Docker, Kubernetes, AWS cloud]</td>\n",
       "      <td>MATH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2974</th>\n",
       "      <td>Machine Learning in Healthcare: Fundamentals &amp;...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>beginner</td>\n",
       "      <td>[Not specified]</td>\n",
       "      <td>MATH</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2975 rows × 6 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           course_title  rating         level  \\\n",
       "0     Programming for Everybody (Getting Started wit...     4.8      beginner   \n",
       "1     Programming for Everybody (Getting Started wit...     4.8      beginner   \n",
       "2     Programming for Everybody (Getting Started wit...     4.8      beginner   \n",
       "3                   Python for Everybody Specialization     4.8      beginner   \n",
       "4                   Python for Everybody Specialization     4.8      beginner   \n",
       "...                                                 ...     ...           ...   \n",
       "2970                  Audit System with Spring Boot AOP     0.0  intermediate   \n",
       "2971                                 CompTIA a+ Network     0.0      beginner   \n",
       "2972                                     MBA Essentials     0.0  intermediate   \n",
       "2973  Exam Prep CKA: Certified Kubernetes Administrator     0.0  intermediate   \n",
       "2974  Machine Learning in Healthcare: Fundamentals &...     0.0      beginner   \n",
       "\n",
       "                                                  skill keyword  num_review  \n",
       "0     [Python Syntax And Semantics, Basic Programmin...      CS      225830  \n",
       "1     [Python Syntax And Semantics, Basic Programmin...      DS      225830  \n",
       "2     [Python Syntax And Semantics, Basic Programmin...    MATH      225830  \n",
       "3      [Json, Xml, Python Programming, Database (DBMS)]      CS      209311  \n",
       "4      [Json, Xml, Python Programming, Database (DBMS)]      DS      209311  \n",
       "...                                                 ...     ...         ...  \n",
       "2970  [Application Design, Microservices, Spring Boo...    MATH           0  \n",
       "2971  [scripting, Server Administrator, Authenticati...    MATH           0  \n",
       "2972                                    [Not specified]    MATH           0  \n",
       "2973                    [Docker, Kubernetes, AWS cloud]    MATH           0  \n",
       "2974                                    [Not specified]    MATH           0  \n",
       "\n",
       "[2975 rows x 6 columns]"
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# getting the names of the columns that are needed\n",
    "course_descriptors = ['Course Title', 'Rating', 'Level', 'Skill gain', 'Keyword', 'Number of Review']\n",
    "courses_data = courses[course_descriptors]\n",
    "\n",
    "# look for null rows (if any)\n",
    "null_rows = courses_data[courses_data.isnull().any(axis=1)]    \n",
    "print(\"Number of null rows: \", len(null_rows))    # there are no null rows\n",
    "\n",
    "# Removing rows with non-ASCII characters (there were some rows with chinese letters)\n",
    "mask = pd.Series([False] * len(courses_data))\n",
    "\n",
    "# Check each column for non-ASCII characters\n",
    "for column in courses_data.columns:\n",
    "    # Update the mask to True if any column contains non-ASCII characters\n",
    "    # This regex matches any character not in the ASCII range\n",
    "    mask |= courses_data[column].astype(str).str.contains(r'[^\\x00-\\x7F]+', na=False)\n",
    "\n",
    "# Dropping rows where any column contains non-ASCII characters \n",
    "courses_data = courses_data[~mask]\n",
    "courses_data.reset_index(drop=True, inplace=True)\n",
    "\n",
    "# Renaming the column names\n",
    "courses_data.columns = ['course_title', 'rating', 'level', 'skill', 'keyword', 'num_review']\n",
    "\n",
    "# getting all level values \n",
    "print(\"Different levels of courses in this dataset: \", courses_data['level'].unique())\n",
    "\n",
    "# CLEANING THE \"LEVEL\" COLUMN \n",
    "def standardize_level(level):\n",
    "    # Convert the level to lowercase and strip leading/trailing whitespace\n",
    "    level = level.lower().strip()\n",
    "    # Map each original level to its new standardized form\n",
    "    if 'beginner level' in level:\n",
    "        output = 'beginner'\n",
    "    elif 'intermediate level' in level:\n",
    "        output = 'intermediate'\n",
    "    elif 'not specified' in level:\n",
    "        output = 'N/A'\n",
    "    elif 'advanced level' in level:\n",
    "        output = 'advanced'\n",
    "    # Otherwise, if uncaught - keep as is\n",
    "    else:\n",
    "        output = level\n",
    "    \n",
    "    return output\n",
    "    \n",
    "# Apply the function to the 'level' column\n",
    "courses_data['level'] = courses_data['level'].apply(standardize_level)\n",
    "\n",
    "\n",
    "# CLEANING THE \"RATING\" AND \"NUM_REVIEW\" COLUMN \n",
    "# ensure all numbers are floats or ints \n",
    "courses_data['rating'] = pd.to_numeric(courses_data['rating'], errors='coerce')\n",
    "courses_data['num_review'] = pd.to_numeric(courses_data['num_review'], errors='coerce')\n",
    "\n",
    "# CLEANING THE \"KEYWORDS\" COLUMN\n",
    "unique_keywords = courses_data['keyword'].unique()\n",
    "print(\"Keywords in this dataset are: \", unique_keywords)\n",
    "# removing all courses that are NOT tech related \n",
    "exclude_keywords = ['Arts and Humanities', 'Business', 'Health', 'Personal Development',\n",
    "                    'Physical Science and Engineering', 'Social Sciences']\n",
    "\n",
    "# Corrected to 'keyword' to match the renamed column name\n",
    "courses_data = courses_data[~courses_data['keyword'].isin(exclude_keywords)]\n",
    "\n",
    "def standardize_keyword(keyw):\n",
    "    # Convert the level to lowercase and strip leading/trailing whitespace\n",
    "    keyw = keyw.lower().strip()\n",
    "    # Map each original keyword to its new standardized form\n",
    "    if 'computer science' in keyw:\n",
    "        output = 'CS'\n",
    "    elif 'datascience' in keyw:\n",
    "        output = 'DS'\n",
    "    elif 'information technology' in keyw:\n",
    "        output = 'IT'\n",
    "    elif 'math and logic' in keyw:\n",
    "        output = 'MATH'\n",
    "    # Otherwise, if uncaught - keep as is\n",
    "    else:\n",
    "        output = \"N/A\"\n",
    "    \n",
    "    return output\n",
    "    \n",
    "# Apply the function to the 'level' column\n",
    "courses_data['keyword'] = courses_data['keyword'].apply(standardize_keyword)\n",
    "\n",
    "# sorting the dataset first by num_reviews, then by rating\n",
    "courses_data = courses_data.sort_values([\"num_review\", \"rating\"], ascending=False).reset_index(drop=True)\n",
    "\n",
    "\n",
    "# CLEANING THE 'SKILL' COLUMN\n",
    "courses_data['skill'] = courses_data['skill'].str.split(', ')\n",
    "\n",
    "courses_data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Ethics & Privacy"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Thoughtful discussion of ethical concerns included\n",
    "- Ethical concerns consider the whole data science process (question asked, data collected, data being used, the bias in data, analysis, post-analysis, etc.)\n",
    "- How your group handled bias/ethical concerns clearly described\n",
    "\n",
    "Acknowledge and address any ethics & privacy related issues of your question(s), proposed dataset(s), and/or analyses. Use the information provided in lecture to guide your group discussion and thinking. If you need further guidance, check out [Deon's Ethics Checklist](http://deon.drivendata.org/#data-science-ethics-checklist). In particular:\n",
    "\n",
    "- Are there any biases/privacy/terms of use issues with the data you propsed?\n",
    "- Are there potential biases in your dataset(s), in terms of who it composes, and how it was collected, that may be problematic in terms of it allowing for equitable analysis? (For example, does your data exclude particular populations, or is it likely to reflect particular human biases in a way that could be a problem?)\n",
    "- How will you set out to detect these specific biases before, during, and after/when communicating your analysis?\n",
    "- Are there any other issues related to your topic area, data, and/or analyses that are potentially problematic in terms of data privacy and equitable impact?\n",
    "- How will you handle issues you identified?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In this project we are not dealing with individuals’ data, only with open source information like LinkedIn posts and Coursera courses, which are readily available online, which eliminates our consern for personal privacy violations. However, even a data set without people’s information can have pre-existing biases. For example, most MOOCs users, including on Coursera, are from the United States. This means that we should narrow down our job postings dataset only to the United States, in order to keep the data aligned with each other. We acknowledge that this creates a bias for data science job posting for a single country, which is not representative of the whole world. \n",
    "\n",
    "Before starting our analysis we should ensure that our dataset is large enough and covers a diverse range of listings to try to extract the most reliable patterns out of the data. During our analysis we will pay close attention to the patterns in our data and how the biases in our dataset might have had an effect on them. When writing and finalizing our analysis we will discuss how biases of the dataset might have skewed our results and conclusions."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Team Expectations "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "Read over the [COGS108 Team Policies](https://github.com/COGS108/Projects/blob/master/COGS108_TeamPolicies.md) individually. Then, include your group’s expectations of one another for successful completion of your COGS108 project below. Discuss and agree on what all of your expectations are. Discuss how your team will communicate throughout the quarter and consider how you will communicate respectfully should conflicts arise. By including each member’s name above and by adding their name to the submission, you are indicating that you have read the COGS108 Team Policies, accept your team’s expectations below, and have every intention to fulfill them. These expectations are for your team’s use and benefit — they won’t be graded for their details.\n",
    "\n",
    "Our team came up with the following guidelines:\n",
    "\n",
    "- Our group is planning to meet bi-weekly to check on each other’s progress as well as to assign our next steps.\n",
    "- We plan on using Discord as our primary communication.\n",
    "- Messages on discord should be responded to within a day.\n",
    "- If any of us need to miss one of the meetings, we must make sure that we effectively communicate that in the discord.\n",
    "- Communicate if you’re not able to complete your assignment on time and ask for help in the discord.\n",
    "- Make sure to pull from the working branch on GH so that our data/progress is kept clean."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Project Timeline Proposal"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Specify your team's specific project timeline. An example timeline has been provided. Changes the dates, times, names, and details to fit your group's plan.\n",
    "\n",
    "If you think you will need any special resources or training outside what we have covered in COGS 108 to solve your problem, then your proposal should state these clearly. For example, if you have selected a problem that involves implementing multiple neural networks, please state this so we can make sure you know what you’re doing and so we can point you to resources you will need to implement your project. Note that you are not required to use outside methods.\n",
    "\n",
    "\n",
    "\n",
    "| Meeting Date  | Meeting Time| Completed Before Meeting  | Discuss at Meeting |\n",
    "|---|---|---|---|\n",
    "| 2/15  |  N/A | Pick dataset by this date  | Get approvals from everyone on the dataset. | \n",
    "| 2/20  |  1:30 - 2:30 PM | Have data cleaned by this date | Any debugging issues, final resuls | \n",
    "| 2/25  | 1:30 - 2:30 PM  | Dataset defined and described; Data cleaned  | General meeting to discuss the steps of our analysis |\n",
    "| 3/7  | 1:30 - 2:30 PM  | Analysis and results draft by this date | Discuss the results of the analysis   |\n",
    "| 3/12  | 1:30 - 2:30 PM  | Final report draft done | Proof read the final report draft. Discuss the video logistics |\n",
    "| 3/16  | 1:30 - 2:30 PM  | Video logistics done| General project review, video recording |\n",
    "| 3/20  | Before 11:59 PM  | Everything else! | Turn in Final Project & Group Project Surveys |"
   ]
  }
 ],
 "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.4"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}