Historical Job Postings

The first of these datasets are the historical job postings. So downloaded the data from 2013 to 2017 (the most recent), and unzipped the files into a folder that I’ll call rawdatapath, and I’ll put the derived data into deriveddatapath. After unzipping, we get a big JSON file for each year.

Let’s load up the data from 2013 to play around with first.

rawdatapath <- "../RawData"
deriveddatapath <- "../DerivedData"

To load the data from the JSON file, it isn’t quite a normal JSON file: it’s in the Newline delimited JSON (NDJSON) format. So we can’t use the usual jsonlite::fromJSON, but rather the jsonlite::stream_in() command.

read_and_save <- function(year) {
  
  indat <- jsonlite::stream_in(file(glue("{rawdatapath}/{year}.json")), 
                               pagesize=1000) %>% 
    bind_rows() %>% 
    mutate(id = paste(year, "_", 1:nrow(.), sep=""))
  
  saveRDS(indat, glue('{deriveddatapath}/jobads_{year}.rds'), 
          compress = FALSE)
}

read_and_save(2013)
read_and_save(2014)
read_and_save(2015)
read_and_save(2016)
read_and_save(2017)

Note that I use compress=FALSE. This is because it makes the saving and loading of the rds file much faster. Note that I also load and save, and then load the faster file, and I divide the chunks: this is so that I can run the slow conversion chunk with eval=FALSE after doing it the first time to increase the speed of re-running the notebook.

jobads <- readRDS(glue('{deriveddatapath}/jobads_2013.rds'))

Job Ontologies

The second of these data sources is the job ontologies. This is the result of heroic work by the developers at JobTech to extract a list of skills from all of these job postings. We can download the terms and save them into our raw data folder, then load and save as an uncompressed rds as above.

terms <- read_csv2(glue("{rawdatapath}/termer_koncept_kompetens.csv")) %>% 
  select(word=term, concept)

saveRDS(terms, file = glue("{deriveddatapath}/terms.rds"))

I’ve kept the word and concept columns. The concept column is what the word should have been, i.e. if the adverts misspelled something, or specified it in an unusual way (“actionscript 3” vs “action script 3”). So we’ll aim to replace the words with the concepts later on.

terms <- readRDS(glue("{deriveddatapath}/terms.rds"))

Munging

First, we want to prepare everything and make it as simple as possible to use. For text data, it’s always a good idea to bring everything to lowercase.

jobads <- jobads %>% 
  select(id, text=PLATSBESKRIVNING) %>% 
  mutate(text = stringr::str_to_lower(text)) 
  

terms <- terms %>% 
  mutate(word = stringr::str_to_lower(word),
         concept = stringr::str_to_lower(concept))

Extracting the terms

Now let’s turn the jobads into terms. I made the mistake at the start of trying to find the specific terms from all the text of the adverts using regex, which was unbelievably slow, and would have taken days.

Instead, I found that using tidytext::unnest_tokens() was substantially quicker, and then we could simply filter by the terms from the ontologies. However, it takes up a lot of memory to do this. So I made another convenience function to chunk everything into chunks of 500 adverts, so that we can unnest and filter within each chunk, and then bind it all up at the end. This was much faster, and much easier on the memory.

chunk_it_up <- function(.data, chunksize) {
  chunks <- ceiling(nrow(.data) / chunksize)
  chunkvec <- rep(1:chunks, each=chunksize)
  chunkvec <- chunkvec[1:nrow(.data)]
  
  return(chunkvec)
}

jobads <- jobads %>% 
  mutate(chunk = chunk_it_up(., 500)) %>% 
  group_by(chunk) %>% 
  nest()

And now let’s extract the terms from each advert, chunk by chunk, and then replace the terms by the concepts.

text2terms <- function(data) {
  
  data <- data %>% 
    unnest_tokens(word, text) %>% 
    filter(word %in% terms$word) %>% 
    group_by(id) %>% 
    filter(!duplicated(word)) %>% # in case the same word occurs multiple times in
                                  # one advert
    ungroup()
  return(data)
}

jobads <- jobads %>% 
  mutate(terms = map(data, ~text2terms(.x)))

jobterms <- jobads %>% 
  ungroup() %>% 
  select(terms) %>% 
  unnest(cols=terms) %>% 
  left_join(terms) %>% 
  select(-word, word=concept) # Replacing word with concept
## Joining, by = "word"

… and at the end, we replaced word with concept, so we should have resolved the inaccuracies.

Extracting the relevant adverts

Now let’s see if we can pull out which adverts contain each term. First I’ll define some functions: query_term() pulls out the advert id’s for a given term. query_terms() applies query_term() to one or multiple terms.

query_term <- function(jobterms, term) {
  
  ad_id <- jobterms %>% 
    filter(word==term) %>% 
    pull(id) %>% 
    unique(.)
  
  return(ad_id) 
}

query_terms <- function(jobterms, terms) {
  
  term_ind <- tibble(
    word = terms
  ) %>% 
    mutate(indices = map(terms, 
                         ~query_term(jobterms, .x)))
  
  ad_id <- Reduce(intersect, term_ind$indices)
  
  return(ad_id)
  
}

And now let’s test

head(query_terms(jobterms, "python"))
## [1] "2013_1356" "2013_1733" "2013_3796" "2013_3835" "2013_4102" "2013_4154"
head(query_terms(jobterms, "r"))
## [1] "2013_167"  "2013_200"  "2013_671"  "2013_989"  "2013_1704" "2013_1921"
head(query_terms(jobterms, c("python", "r")))
## [1] "2013_10220" "2013_18314" "2013_21809" "2013_51759" "2013_55334"
## [6] "2013_73348"

Great – that seems to work well!

Extracting related skills

Now, by using the ids of the adverts, we should be able to just count the occurrence of the other terms in the adverts containing the original search term. Let’s take painting as a skill: what are the most associated terms?

queryterm <- "måleri"

jobids <- query_terms(jobterms, queryterm)

assoc_terms <- jobterms %>% 
  filter(id %in% jobids) %>% 
  select(-id) %>% 
  count(word) %>% 
  arrange(desc(n)) %>% 
  filter(!word %in% queryterm) %>% 
  mutate(perc = 100*n/length(jobids)) %>% 
  select(Word = word, Percentage = perc)

head(assoc_terms)
## # A tibble: 6 x 2
##   Word        Percentage
##   <chr>            <dbl>
## 1 körkort          25.0 
## 2 svenska          17.6 
## 3 snickeri         17.3 
## 4 hantverk          6.64
## 5 rekrytering       6.64
## 6 tapetsering       6.64

And it works! And the results consist of both expected and unexpected associations, for me at least. To translate, the first three are “driving license”, “Swedish” and “carpentry”. I definitely would not have predicted driving license, but it absolutely makes sense given the nature of the job.

Conclusions

This works, but it does take some time and lots of memory to load the data, and it could probably be faster. Here is where we could do with a database! Let’s put everything into a big database, and see whether we can get everything a bit quicker that way.

Populating our database

Now we need to put some content into it. I’ll divide it into tables by years: I think doing operations across years maybe gives one a biased picture of what is related to what, and also probably means working with unwieldy datasets.

Let’s first define a function that does all the things done above, and then populates the database.

yearterms2db <- function(year, db) {
  
  jobterms <- readRDS(glue('{deriveddatapath}/jobads_{year}.rds')) %>% 
    select(id, text=PLATSBESKRIVNING) %>% 
    mutate(text = stringr::str_to_lower(text)) %>% 
    mutate(chunk = chunk_it_up(., 500)) %>% 
    group_by(chunk) %>% 
    nest() %>% 
    mutate(terms = map(data, ~text2terms(.x))) %>% 
    ungroup() %>% 
    select(terms) %>% 
    unnest(cols=terms) %>% 
    left_join(terms) %>% 
    select(-word, word=concept)
  
  dbWriteTable(db, glue("Terms_{year}"), jobterms)
}

Let’s fire it up!

years <- 2013:2017

mydb <- dbConnect(RSQLite::SQLite(), 
                  glue("{deriveddatapath}/termsdb.sqlite"))

walk(years, ~yearterms2db(.x, mydb))

dbDisconnect(mydb)

Finally, let’s also put the terms into the database. Let’s first replace the word column with the concept column though.

terms <- terms %>% 
  select(word=concept) %>% 
  distinct()

mydb <- dbConnect(RSQLite::SQLite(), 
                  glue("{deriveddatapath}/termsdb.sqlite"))

dbWriteTable(mydb, "Terms", terms)

dbDisconnect(mydb)

And let’s just make sure that all those tables made their way into the database.

dbfile <- glue("{deriveddatapath}/termsdb.sqlite")

mydb <- dbConnect(RSQLite::SQLite(), 
                  dbfile)

dbListTables(conn = mydb)
## [1] "Terms"      "Terms_2013" "Terms_2014" "Terms_2015" "Terms_2016"
## [6] "Terms_2017"
dbDisconnect(mydb)

Ok, it seems to work!

Now we just need to fix up the terms table of our database so that it contains concept instead of word.

dbfile <- glue("{deriveddatapath}/termsdb.sqlite")

mydb <- dbConnect(RSQLite::SQLite(), 
                  dbfile)

Functions for querying

query_term <- function(queryterm, year, 
                       dbfile=glue("{deriveddatapath}/termsdb.sqlite")) {
  
  # Check year
  if(!(year %in% 2013:2017)) {
    stop("Year not included in the database")
  }
  
  # Connect
  con <- dbConnect(RSQLite::SQLite(), 
                  dbfile)
  
  # Load
  terms <- tbl(con, "Terms")
  termtable <- tbl(con, glue("Terms_{year}"))
  
  # Check
  term <- stringr::str_to_lower(queryterm)
  
  checkterm <- terms %>% 
    filter(word==queryterm) %>% 
    collect()
  
  if( nrow(checkterm) == 0 ) {
    dbDisconnect(con)
    stop(paste0("Term ", queryterm, " not found"))
  }
  
  # Do
  adverts <- termtable %>% 
    filter(word==queryterm) %>% 
    pull(id)
  
  # Disconnect
  dbDisconnect(con)
  
  # Return
  return(adverts) 
}

query_terms <- function(queryterms, year, 
                       dbfile=glue("{deriveddatapath}/termsdb.sqlite")) {
  
  # Connect
  con <- dbConnect(RSQLite::SQLite(), 
                  dbfile)
  
  # Do
  adverts <- tibble(
    queryterm = queryterms
  ) %>% 
    mutate(ids = map(queryterms, 
                         ~query_term(.x, year, 
                                     dbfile)))
  
  adverts <- Reduce(intersect, adverts$ids)
  
  # Disconnect
  dbDisconnect(con)
  
  # Return
  return(adverts) 
}

str2queryterms <- function(query) {
  
  query <- gsub("; ", replacement = ";", query)
  strsplit(query, split = ";")[[1]]
  
}

query_associated <- function(query, year, 
                       dbfile=glue("{deriveddatapath}/termsdb.sqlite")) {
  
  # Connect
  con <- dbConnect(RSQLite::SQLite(), 
                  dbfile)
  
  # Clean
  queryterms <- str2queryterms(query)
  
  
  
  ids <- query_terms(queryterms, year, dbfile)
  
  assocterms <- tbl(con, paste0("Terms_", year)) %>% 
    filter(id %in% ids) %>% 
    select(-id) %>% 
    count(word) %>% 
    arrange(desc(n)) %>% 
    filter(!word %in% queryterms) %>% 
    collect() %>% 
    mutate(perc = 100*n/length(ids)) %>% 
    select(Word = word, Percentage = perc)
  
  # Disconnect
  dbDisconnect(con)
  
  return(assocterms)
  
}

Trying it out

Let’s give it a try!

query <- "r;python"

query_associated(query, 2013) %>% 
  head(10) %>% 
  kable(digits=1, caption = "2013: R and Python")

query_associated(query, 2014) %>% 
  head(10) %>% 
  kable(digits=1, caption = "2014: R and Python")

query_associated(query, 2015) %>% 
  head(10) %>% 
  kable(digits=1, caption = "2015: R and Python")

query_associated(query, 2016) %>% 
  head(10) %>% 
  kable(digits=1, caption = "2016: R and Python")

query_associated(query, 2017) %>% 
  head(10) %>% 
  kable(digits=1, caption = "2017: R and Python")

Seems to be working, and even shows a pretty good replication across years: they’re pretty consistent.

dbplyr and SQL syntax

Let’s just take a quick look at what’s going on under the hood here for the associated terms call.

queryterms <- "r;python"
year <- 2013
dbfile <- glue("{deriveddatapath}/termsdb.sqlite")

# Connect
con <- dbConnect(RSQLite::SQLite(), 
                dbfile)

# Clean
queryterms <- str2queryterms(query)



ids <- query_terms(queryterms, year, dbfile)

tbl(con, paste0("Terms_", year)) %>% 
  filter(id %in% ids) %>% 
  select(-id) %>% 
  count(word) %>% 
  arrange(desc(n)) %>% 
  filter(!word %in% queryterms) %>% 
  sql_render()
## <SQL> SELECT *
## FROM (SELECT *
## FROM (SELECT `word`, COUNT() AS `n`
## FROM (SELECT `word`
## FROM (SELECT *
## FROM `Terms_2013`
## WHERE (`id` IN ('2013_10220', '2013_18314', '2013_21809', '2013_51759', '2013_55334', '2013_73348', '2013_96893', '2013_138749', '2013_146642', '2013_147865', '2013_152414', '2013_169580', '2013_173873', '2013_176894', '2013_215573', '2013_225841', '2013_231812', '2013_236254', '2013_252987', '2013_276010', '2013_297857', '2013_315498', '2013_318950', '2013_326418', '2013_344887', '2013_364894', '2013_365606', '2013_367555', '2013_383812', '2013_387415', '2013_388891'))))
## GROUP BY `word`)
## ORDER BY `n` DESC)
## WHERE (NOT(`word` IN ('r', 'python')))
# Disconnect
dbDisconnect(con)

And, in this way, all our SQL is done automagically, and we collect() the output at the end.

It works!