<p><div><div><div><div data-show-faces="false" data-height="21" data-width="100" data-layout="button_count" data-send="true" data-href="https://www.r-bloggers.com/an-overview-of-the-singapore-hiring-landscape/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://www.timlrx.com/2018/11/21/an-overview-of-the-singapore-hiring-landscape/"> R on Quasilinear Musings</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>The idea of having a 360 degree view of the entire job seeking and matching landscape has always been a dream of any labour economist. Just imagine, a dataset of CVs and job seekers matched with job advertisements and openings! The potential of such a dataset to answer existing questions on the labour market is incredible. One could investigate market power between worker and firms, information asymmetry within the matching process, or find out new growth clusters and skills needed to support these areas. So it was slightly unfortunate that I was not able to get my hands on such a dataset during my time in the government (I believe only Linkedin could capture something close to what I described).</p>
<p>A few months ago, I decided to make some steps towards creating that dream dataset. While getting information on the candidate side is near impossible, data on job openings are readily available through job portals. Over a few weekends, I wrote a scrapy bot to crawl and save job postings from one major Singapore based job portal.<a rel="nofollow" href="https://www.timlrx.com/2018/11/21/an-overview-of-the-singapore-hiring-landscape/#fn1" target="_blank"><sup>1</sup></a> Having collected a full month of data for October, I thought it was a good opportunity to carry out an exploratory analysis of the dataset. This post features that analysis along with some tidbits of the Singapore hiring landscape. If anyone is interested in studying this dataset in more detail, feel free to drop me an email!</p>
<div>
<h2>Dataset</h2>
<p>The job posting data is collected on a MySQL database and I will be using R along with the <code>dbplyr</code> package to explore the dataset. To interface with the backend database, I am using <code>DBI</code> to create a connection to it. For this post I created a slightly modified copy of the data in a table called <code>sg_jobs_tbl</code>.</p>
<p>For reference, the main fields that were scraped are the job title, posting date, company, post type (sponsored or not), occupation (called tag in the database), job description, experience required, company’s address, company’s industry and a brief overview of the company.</p>
</div>
<div>
<h2>Data Cleaning</h2>
<p>Let us begin by importing the libraries we require and setting up the database connection:</p>
<pre><code>library(DBI)
library(tidyverse)

con &lt;- dbConnect(RMariaDB::MariaDB(),
                 default.file   = paste0(path, '/.my.cnf'),
                 groups = 'jobs_db')</code></pre>
<p>To query the database, we can use the <code>dbSendQuery()</code> function and pass in a standard sql statement for it to evaluate. <code>dbFetch()</code> is used to get the results back in a dataframe. Let’s take a look at how many postings we have collected over the month of October:</p>
<pre><code>q &lt;- dbSendQuery(con, &quot;SELECT count(*) FROM sg_jobs_tbl&quot;)
dbFetch(q)</code></pre>
<pre><code>##   count(*)
## 1    55456</code></pre>
<pre><code>dbClearResult(q)</code></pre>
<p>We have more than 50,000 postings in a month, but how many of them are from recruiting agencies or direct job openings? We can filter out firms with ‘recruitment firm’ as part of their company description to derive the count of postings by actual companies.</p>
<pre><code>q &lt;- dbSendQuery(con, &quot;SELECT count(*) FROM sg_jobs_tbl where company_snapshot not like '%RECRUITMENT FIRM%'&quot;)
dbFetch(q)</code></pre>
<pre><code>##   count(*)
## 1    19327</code></pre>
<pre><code>dbClearResult(q)</code></pre>
<p>Apparently more than 50% of all job posts are by recruitment firms. This means that we have to be a little careful of the data quality since firms might spam the job board with multiple posts across the month for a single job opening.</p>
<p>One of the nice features of R is the <code>tidyverse</code> ecosystem. It provides a consistent syntax to manipulate data regardless of the backend source. So we can just use our favourite <code>dplyr</code> verbs and the package will automatically convert it to sql syntax.<a rel="nofollow" href="https://www.timlrx.com/2018/11/21/an-overview-of-the-singapore-hiring-landscape/#fn2" target="_blank"><sup>2</sup></a> In the code below, I extract the most frequent company, title combinations across the entire month:</p>
<pre><code>q &lt;- tbl(con, &quot;sg_jobs_tbl&quot;) %>%
  filter(!is.na(company), !is.na(title)) %>%
  group_by(company, title) %>%
  count() %>%
  arrange(desc(n))</code></pre>
<p>A nice aspect of writing sql statements in <code>dplyr</code> syntax is that it is lazily evaluated i.e. the code will only be run when it is explicitly required, such as a print statement. Given a <code>tbl</code> object with a <code>DBI</code> connection, <code>dplyr</code> will use <code>dbplyr</code> to generate the sql translation which we can preview by calling the <code>show_query()</code> function. Even when we ask it to print the results of the query, it only returns the first 10 observations. To return the entire query as a data frame we have to use the <code>collect()</code> function. We can take advantage of this feature by just printing the query and getting the top 10 observations:</p>
<pre><code>q</code></pre>
<pre><code>## # Source:     lazy query [?? x 3]
## # Database:   mysql 8.0.12 [root@localhost:/jobs_db]
## # Groups:     company, title
## # Ordered by: desc(n)
##    company                  title                                n        
##    </code></pre></div>