Meta-data

I decided to start with the slightly harder task of cleaning the episode meta-data (number, title and airdate) first.

Having removed the header row (tab[-1]), I converted the vector to a dataframe and extracted every odd row (once I removed the header, the meta-data went from being on every even row to every odd row), making use of the slice() and row_number() functions from dplyr.

After this, I removed information on the proverbs from the live shows, which are presented in a second table at the end of the scraped webpage, by removing all rows after (and including) the row containing the header for that table.

I then needed to deal with the fact that three variables were contained in a single column in the dataframe. Fortunately, the variables were consistently seperated by a common delimiter (“\n\n”) which allowed me to use separate() from tidyr to split the single column into three new variables.

Finally, I removed all quotation marks from the three new variables,I appreciate this could be done in one step using dplyr::across(), but R kept hanging when I tried it and it wasn’t the focus of this project, so I moved on! and removed the trailing newline charater from airdate.

meta <- tab[-1] %>%
  # Convert vector to dataframe
  data.frame(number = ., stringsAsFactors = FALSE) %>%
  # Extract every odd row
  slice(which(row_number() %% 2 == 1)) %>%
  # Remove live shows
  slice(1:which(.$number == "Show No.\n\nTitle\n\nRelease Date\n") - 1) %>%
  # Split into component parts
  separate(number,
           c("number", "title", "airdate"),
           sep = '\n\n') %>%
  # Remove quotes from all variables
  mutate(
    title = stringr::str_replace_all(title, '\"', ""),
    number = stringr::str_replace_all(number, '\"', ""),
    airdate = stringr::str_replace_all(airdate, '\"', "")
  ) %>%
  # Remove trailing newline character from airdate/
  mutate(airdate = stringr::str_replace(airdate, "\n", ""))

head(meta)

##   number                   title          airdate
## 1      1                   Pilot     15 June 2012
## 2      2              Glow Cloud      1 July 2012
## 3      3      Station Management     15 July 2012
## 4      4             PTA Meeting    1 August 2012
## 5      5 The Shape in Grove Park   15 August 2012
## 6      6          The Drawbridge 1 September 2012

Proverb text

I used a similar approach to clean the proverb data, taking every even row from the tab vector and again removing those proverbs from the live shows. Out of interest, I also created two additional variables, which count the number of characters and words in each proverb.

Once I had this done, I removed any leading and trailing newline characters (I needed to retain internal newline characters as some proverbs are list-like) and create a unique numeric ID for each episode, as the number variable often had part indicators (19A and 19B). Finally, I merged the proverb data with the meta-data obtained above to create the final dataset.

proverb_df <- tab[-1] %>%
  # Convert vector to dataframe
  data.frame(proverb = ., stringsAsFactors = FALSE) %>%
  # Extract every even row
  slice(which(row_number() %% 2 == 0)) %>%
  # Remove proverbs from live shows
  slice(1:which(.$proverb == "1\n\n\"Condos\"\n\n25 September 2013\n") -
          1) %>%
  # Generate variables with # of character/words for each proverb
  mutate(
    proverb_len_char = nchar(proverb),
    proverb_len_word = str_count(proverb, '\\w+')
  ) %>%
  # Clear random newlines at start and end of proverbs
  mutate(
    proverb = str_replace(proverb, "\n\n$", ""),
    proverb = str_replace(proverb, "\n$", ""),
    proverb = str_replace(proverb, "^\n", "")
  ) %>%
  # Add unique id
  mutate(id = seq_along(1:n())) %>%
  # Bind to metadata and reorder
  cbind(meta, .) %>%
  select(id, everything()) %>%
  tibble()
  

head(proverb_df)

## # A tibble: 6 x 7
##      id number title   airdate  proverb        proverb_len_char proverb_len_word
##   <int> <chr>  <chr>   <chr>    <chr>                     <int>            <int>
## 1     1 1      Pilot   15 June~ Look to the n~               72               13
## 2     2 2      Glow C~ 1 July ~ Men are from ~               90               15
## 3     3 3      Statio~ 15 July~ There's a spe~               66               13
## 4     4 4      PTA Me~ 1 Augus~ What has four~              141               30
## 5     5 5      The Sh~ 15 Augu~ A million dol~               64               13
## 6     6 6      The Dr~ 1 Septe~ Lost? Confuse~               72               12