Extracting time period word frequencies

set wkcase

(ie whether working / accessing data locally or on the cluster)

args <- commandArgs(trailingOnly = TRUE)
if(length(args) != 0) {
    } else {
        source(paste0(here::here(), "/params/extract_word_freq.R"))
    }

Connect to database

Mount volume if required. Set up output directory (out_dir)

switch(wkcase,
       "smb_local" = {
         mount_ooominds1_volume() 
         db <- dbConnect(RSQLite::SQLite(), 
                         dbname = "~/../../Volumes/ooominds1/Shared/corpus.byu.edu/a1517_now/now_db")
         out_dir <- "~/../../Volumes/ooominds1/Shared/corpus.byu.edu/extraction_out/data/"
       },
       "local_local" = {
         mount_ooominds1_volume()
         db <- dbConnect(RSQLite::SQLite(), 
                         dbname = paste0(here::here(), "/data/now_db/now_db"))
         out_dir <- "~/../../Volumes/ooominds1/Shared/corpus.byu.edu/extraction_out/data/"
       },
        "smb_sharc" = {
         db <- dbConnect(RSQLite::SQLite(), 
                         dbname =  "shared/ooominds1/Shared/corpus.byu.edu/a1517_now/now_db")
         out_dir <- "shared/ooominds1/Shared/corpus.byu.edu/extraction_out/data/"
       }
)

dbListTables(db)

[1] "corpus"      "del_lexicon" "lexicon"     "samples"     "sources"    
[6] "targets"    

dbListFields(db, "sources")

[1] "textID"    "ID"        "date"      "country"   "source"    "url"      
[7] "textTitle"

get a list of unique sources in the database

source_list <- dbGetQuery(db, 'SELECT DISTINCT source FROM sources') %>% arrange(source)

head(source_list, 50)

# A tibble: 50 x 1
                                            source
                                             <chr>
 1                                'Bourne this way
 2                                (e) Science News
 3 (e) Science News (press release) (registration)
 4                                             @U2
 5                                           /FILM
 6                                     10 Connects
 7                             100 Mile Free Press
 8                       100 Mile House Free Press
 9                         100.7 WZLX Classic Rock
10                                         1011now
# ... with 40 more rows

I’ve also created a csv containing the names of the target publications we are interested in extracting article data from.

target_sources <- read_csv(paste0(here::here(), "/data/target_sources.csv"))

target_sources

# A tibble: 31 x 2
             source country
              <chr>   <chr>
 1       Daily Mail      UK
 2     The Guardian      UK
 3  The Independent      UK
 4  Financial Times      UK
 5      Independent      UK
 6            Metro      UK
 7 Evening Standard      UK
 8     The National      UK
 9       Daily Star      UK
10      The Courier      UK
# ... with 21 more rows

First, I check that all target sources match a source in the source_list df.

stopifnot(all(target_sources$source %in% source_list$source))

I then write the df of target_sources to a table in the database.

dbWriteTable(db, "targets", target_sources, overwrite = T)

This allows fast extraction of article level metadata from the sources table by using an inner join query. Using this query I extract information on all available articles from the target sources into target_subset tbl.

target_subset <- dbGetQuery(db, "select sources.* from targets inner join sources on sources.source = targets.source;") %>% filter(country %in% c("GB", "US"))

I’ve written target_subset to file to do some initial exploration of article level data availability from the different time periods discussed (see report: r01-article_availability)

target_subset %>% write_csv(paste0(here::here(),"/data/target_subset.csv"))

get word counts

At this stage, the sampled corpus is cleaned of stop words, is trimmed to words with word frequencies equal or greater than minfreq (defaults to 5) and calculates nn, counts per source per time_p.

In column total_n I also calculate the total word count per source per time_p which is then used to calculate cpm, count per million words. This is for your reference only at the minute as, my understanding is that, the shrinkage applied later does not require scaling of the data (shrinkage itself scales the data to between 0-1) so I’m currently just using the raw counts.

corpus_counts <- get_word_counts(corpus_spls)

Joining, by = "word"
Joining, by = "word"

corpus_counts

# A tibble: 1,139,985 x 6
       word  time_p     source    nn total_n   cpm
      <chr>   <chr>      <chr> <int>   <int> <int>
 1   people us_elec Daily Mail  3534  896505  3941
 2     time us_elec Daily Mail  3316  896505  3698
 3    share us_elec Daily Mail  2911  896505  3247
 4   people   weeks Daily Mail  2625  694983  3777
 5   people  brexit Daily Mail  2516  613985  4097
 6     time   weeks Daily Mail  2491  694983  3584
 7     told us_elec Daily Mail  2358  896505  2630
 8   people  contin Daily Mail  2313  600013  3854
 9     home us_elec Daily Mail  2267  896505  2528
10 children us_elec Daily Mail  2165  896505  2414
# ... with 1,139,975 more rows

widen df

Finally, I reshape the df into a wide format, with each source in a column and each row a word contained in the overall sampled corpus (ie the complete word list across all time_p and sources).

wide_counts <- widen_word_counts(corpus_counts, value = "nn")

wide_counts %>% order_wide %>% head(150) 

# A tibble: 150 x 33
# Groups:   time_p [4]
     word  time_p `Baltimore Sun` `Chicago Tribune` `Daily Mail`
   <fctr>   <chr>           <dbl>             <dbl>        <dbl>
 1 people us_elec              36                37         3534
 2 people   weeks              75               103         2625
 3 people  brexit              64               388         2516
 4 people  contin              50                96         2313
 5   time us_elec              31                32         3316
 6   time   weeks              51                72         2491
 7   time  contin              22                65         2155
 8   time  brexit              40               276         2115
 9 police  brexit             141               464         1996
10  world   weeks               5                48         1436
# ... with 140 more rows, and 28 more variables: `Daily Star` <dbl>,
#   `Dallas Morning News` <dbl>, `Evening Standard` <dbl>, `Financial
#   Times` <dbl>, `Los Angeles Times` <dbl>, Metro <dbl>, `Milwaukee
#   Journal Sentinel` <dbl>, `Minneapolis Star Tribune` <dbl>, `National
#   Geographic` <dbl>, `New York Daily News` <dbl>, `New York
#   Magazine` <dbl>, `New York Post` <dbl>, Politico <dbl>, `The
#   Atlantic` <dbl>, `The Courier` <dbl>, `The Guardian` <dbl>, `The
#   Independent` <dbl>, `The National` <dbl>, `The New Yorker` <dbl>, `The
#   Seattle Times` <dbl>, `The Week Magazine` <dbl>, TIME <dbl>, `Wall
#   Street Journal` <dbl>, `Washington Times` <dbl>, Independent <dbl>,
#   `The Sun` <dbl>, `The Times` <dbl>, `Washington Post` <dbl>

write out data

Create out_dir directory (if it doesn’t exist already)

dir.create(out_dir, recursive = T)

Warning in dir.create(out_dir, recursive = T): '/Users/Anna/../../Volumes/
ooominds1/Shared/corpus.byu.edu/extraction_out/data' already exists

Split wide df into the four time_p and write to csv individually.

split(wide_shrunk, factor(wide_shrunk$time_p)) %>% map(~write_csv(.x, paste0(out_dir, unique(.x$time_p), "_freqs.csv")))

Write the full wide_shrunk_freqs and the long corpus_counts dfs to .csv

write_csv(wide_shrunk_freqs, paste0(out_dir, "all_freqs.csv"))
write_csv(corpus_counts, paste0(out_dir, "all_counts_long.csv")

Reminder of where the files are:

out_dir

[1] "~/../../Volumes/ooominds1/Shared/corpus.byu.edu/extraction_out/data/"

Extract code from .Rmd into an .R rscript ro run on the cluster.

knitr::purl(paste0(here::here(),"/analysis/extract_word_freq.Rmd"), paste0(here::here(),"/code/m04_extract_word_freq.R"), documentation = 0)

  |                                                                       
  |                                                                 |   0%
  |                                                                       
  |.                                                                |   1%
  |                                                                       
  |..                                                               |   3%
  |                                                                       
  |..                                                               |   4%
  |                                                                       
  |...                                                              |   5%
  |                                                                       
  |....                                                             |   6%
  |                                                                       
  |.....                                                            |   8%
  |                                                                       
  |......                                                           |   9%
  |                                                                       
  |.......                                                          |  10%
  |                                                                       
  |.......                                                          |  11%
  |                                                                       
  |........                                                         |  13%
  |                                                                       
  |.........                                                        |  14%
  |                                                                       
  |..........                                                       |  15%
  |                                                                       
  |...........                                                      |  16%
  |                                                                       
  |............                                                     |  18%
  |                                                                       
  |............                                                     |  19%
  |                                                                       
  |.............                                                    |  20%
  |                                                                       
  |..............                                                   |  22%
  |                                                                       
  |...............                                                  |  23%
  |                                                                       
  |................                                                 |  24%
  |                                                                       
  |................                                                 |  25%
  |                                                                       
  |.................                                                |  27%
  |                                                                       
  |..................                                               |  28%
  |                                                                       
  |...................                                              |  29%
  |                                                                       
  |....................                                             |  30%
  |                                                                       
  |.....................                                            |  32%
  |                                                                       
  |.....................                                            |  33%
  |                                                                       
  |......................                                           |  34%
  |                                                                       
  |.......................                                          |  35%
  |                                                                       
  |........................                                         |  37%
  |                                                                       
  |.........................                                        |  38%
  |                                                                       
  |..........................                                       |  39%
  |                                                                       
  |..........................                                       |  41%
  |                                                                       
  |...........................                                      |  42%
  |                                                                       
  |............................                                     |  43%
  |                                                                       
  |.............................                                    |  44%
  |                                                                       
  |..............................                                   |  46%
  |                                                                       
  |..............................                                   |  47%
  |                                                                       
  |...............................                                  |  48%
  |                                                                       
  |................................                                 |  49%
  |                                                                       
  |.................................                                |  51%
  |                                                                       
  |..................................                               |  52%
  |                                                                       
  |...................................                              |  53%
  |                                                                       
  |...................................                              |  54%
  |                                                                       
  |....................................                             |  56%
  |                                                                       
  |.....................................                            |  57%
  |                                                                       
  |......................................                           |  58%
  |                                                                       
  |.......................................                          |  59%
  |                                                                       
  |.......................................                          |  61%
  |                                                                       
  |........................................                         |  62%
  |                                                                       
  |.........................................                        |  63%
  |                                                                       
  |..........................................                       |  65%
  |                                                                       
  |...........................................                      |  66%
  |                                                                       
  |............................................                     |  67%
  |                                                                       
  |............................................                     |  68%
  |                                                                       
  |.............................................                    |  70%
  |                                                                       
  |..............................................                   |  71%
  |                                                                       
  |...............................................                  |  72%
  |                                                                       
  |................................................                 |  73%
  |                                                                       
  |.................................................                |  75%
  |                                                                       
  |.................................................                |  76%
  |                                                                       
  |..................................................               |  77%
  |                                                                       
  |...................................................              |  78%
  |                                                                       
  |....................................................             |  80%
  |                                                                       
  |.....................................................            |  81%
  |                                                                       
  |.....................................................            |  82%
  |                                                                       
  |......................................................           |  84%
  |                                                                       
  |.......................................................          |  85%
  |                                                                       
  |........................................................         |  86%
  |                                                                       
  |.........................................................        |  87%
  |                                                                       
  |..........................................................       |  89%
  |                                                                       
  |..........................................................       |  90%
  |                                                                       
  |...........................................................      |  91%
  |                                                                       
  |............................................................     |  92%
  |                                                                       
  |.............................................................    |  94%
  |                                                                       
  |..............................................................   |  95%
  |                                                                       
  |...............................................................  |  96%
  |                                                                       
  |...............................................................  |  97%
  |                                                                       
  |................................................................ |  99%
  |                                                                       
  |.................................................................| 100%

[1] "/Users/Anna/Documents/workflows/RSE_clients/news-scrape/code/m04_extract_word_freq.R"

readLines(paste0(here::here(),"/code/m04_extract_word_freq.R")) %>% 
  gsub("##", "", .) %>% 
  writeLines(paste0(here::here(),"/code/m04_extract_word_freq.R"))