How to code in R operations involving progressively increasing separate online csv files?

Question

Johns Hopkins Universty COVID-19 dataset changed its data repository structure from a single time series file to a system where each day generates a separate csv file with state numbers partitioned down by counties, plus an "unassigned" entry for each county, which I am not sure what it means. These files can be found here.

I want to generate a time series plot of the number of cases and deaths in the states of Washington v New York, which would entail adding all counties in each file separately and then plotting these sums as a unified time series.

I know that this can be done in R, but I am not sure how, and I immediately run across the hurdle of having to reference a lot of different files is I am to follow the steps mentioned in this post, for example. Further, it would be necessary to revise the code every day to add the latest file to compile.

Is there a way of streamlining this process? Sorry I am not showing my attempt, but I don't even know how to start without importing all ~50 files individually.

Start by looking up how to read all files one at a time in a loop. Then when you can do this, create and empty data frame outside of the loop, run the loop again adding a feature to row bind the file data into the data frame. If the state and county are not in the file, itself, but the file name, you will need to find a way to add those from the file name and add columns to capture this in the original data frame outside of the loop. From there you can use `Tidyverse` to aggregate & filter as desired. And if you only want NY and WA, you can build into your loop a filter for files with NY & WA — sconfluentus, Mar 29 '20 at 15:35
@sconfluentus Do you know of any posts illustrating the looping process in the first sentence of your comment? — Antoni Parellada, Mar 29 '20 at 15:38
https://stackoverflow.com/questions/11433432/how-to-import-multiple-csv-files-at-once & https://blog.exploratory.io/how-to-read-multiple-excel-or-csv-files-together-42af5d314a10 — sconfluentus, Mar 29 '20 at 15:39
@sconfluentus Thank you. Perusing the answers, it seems as though they all have the files in question in the R directory, as opposed to online. I'm sure the process of pointing to the URL of the repository is not difficult, but I don't know how to do it. — Antoni Parellada, Mar 29 '20 at 15:54
you can try this: https://unix.stackexchange.com/questions/232946/how-to-copy-all-files-from-a-directory-to-a-remote-directory-using-scp/232947 — sconfluentus, Mar 29 '20 at 15:59
you may also be able to just use the path to the folder..I have never done this remotely, but I am sure it is doable... — sconfluentus, Mar 29 '20 at 16:02
Please, no! @sconfluentus' suggestion to *"row bind the file data into the data frame"* performs horribly in the long run, succumbing to the [R Inferno](https://www.burns-stat.com/pages/Tutor/R_inferno.pdf) chapter 2, *Growing Objects*. See https://stackoverflow.com/a/24376207/3358272, and its use of a `list` (either `lapply` or otherwise) followed eventually by `do.call(rbind, list_of_frames)`. — r2evans, Mar 29 '20 at 16:14
Antoni, it should be straight-forward to use `rvest` to scrape the file names from that page. I'd think you could loop through them, and if you have not previously downloaded them, then download it. Once you have a "mirroring" portion working, then the files you need are all local and you can revert to some of the techniques shown in that link I provided previously. — r2evans, Mar 29 '20 at 16:15
@r2evans...that is fair. It is slow...but being familiar with the data at hand, having worked in epidemiology, it is NOT cumbersome...but RVEST would be better. I took the perspective that the person asking probably had limits Re experience and tried to suggest things one could find resources about easily. — sconfluentus, Mar 29 '20 at 16:34

r2evans · Accepted Answer · 2020-03-31T05:13:29.317

Try this process.

Step 1: download and cache the files (let's be fair to github):

library(rvest)

cachedir <- "cache"
if (!dir.exists(cachedir)) dir.create(cachedir)

URL <- "https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports"

html <- read_html(URL)
csvlinks <- html_nodes(html, "td span") %>%
  html_nodes("a") %>%
  html_attr("href") %>%
  grep("csv$", ., value = TRUE) %>%
  paste0("https://raw.githubusercontent.com", .) %>%
  gsub("/blob", "", .)
csvfiles <- file.path(cachedir, basename(csvlinks))
donothave <- !file.exists(csvfiles)
csvlinks <- csvlinks[donothave]
csvfiles <- csvfiles[donothave]

head(csvlinks, n=3)
# [1] "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/01-22-2020.csv"
# [2] "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/01-23-2020.csv"
# [3] "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/01-24-2020.csv"
head(csvfiles, n=3)
# [1] "cache/01-22-2020.csv" "cache/01-23-2020.csv" "cache/01-24-2020.csv"

list.files(path = cachedir)
# character(0)
ign <- Map(function(l,f) download.file(l, f, quiet=TRUE), csvlinks, csvfiles)
list.files(path = cachedir)
#  [1] "01-22-2020.csv" "01-23-2020.csv" "01-24-2020.csv" "01-25-2020.csv" "01-26-2020.csv"
#  [6] "01-27-2020.csv" "01-28-2020.csv" "01-29-2020.csv" "01-30-2020.csv" "01-31-2020.csv"
# [11] "02-01-2020.csv" "02-02-2020.csv" "02-03-2020.csv" "02-04-2020.csv" "02-05-2020.csv"
# [16] "02-06-2020.csv" "02-07-2020.csv" "02-08-2020.csv" "02-09-2020.csv" "02-10-2020.csv"
# [21] "02-11-2020.csv" "02-12-2020.csv" "02-13-2020.csv" "02-14-2020.csv" "02-15-2020.csv"
# [26] "02-16-2020.csv" "02-17-2020.csv" "02-18-2020.csv" "02-19-2020.csv" "02-20-2020.csv"
# [31] "02-21-2020.csv" "02-22-2020.csv" "02-23-2020.csv" "02-24-2020.csv" "02-25-2020.csv"
# [36] "02-26-2020.csv" "02-27-2020.csv" "02-28-2020.csv" "02-29-2020.csv" "03-01-2020.csv"
# [41] "03-02-2020.csv" "03-03-2020.csv" "03-04-2020.csv" "03-05-2020.csv" "03-06-2020.csv"
# [46] "03-07-2020.csv" "03-08-2020.csv" "03-09-2020.csv" "03-10-2020.csv" "03-11-2020.csv"
# [51] "03-12-2020.csv" "03-13-2020.csv" "03-14-2020.csv" "03-15-2020.csv" "03-16-2020.csv"
# [56] "03-17-2020.csv" "03-18-2020.csv" "03-19-2020.csv" "03-20-2020.csv" "03-21-2020.csv"
# [61] "03-22-2020.csv" "03-23-2020.csv" "03-24-2020.csv" "03-25-2020.csv" "03-26-2020.csv"
# [66] "03-27-2020.csv" "03-28-2020.csv"

Tomorrow, run this again and you should only need to download the newer file(s), all others are untouched. (This does not catch updates to previous files.)

Let's load them all.

# just in case something went wrong ...
csvfiles2 <- list.files(path = cachedir, pattern = "csv$", full.names = TRUE)
head(csvfiles2)
# [1] "cache/01-22-2020.csv" "cache/01-23-2020.csv" "cache/01-24-2020.csv"
# [4] "cache/01-25-2020.csv" "cache/01-26-2020.csv" "cache/01-27-2020.csv"
list_of_frames <- lapply(csvfiles2, read.csv, stringsAsFactors = FALSE)
str(list_of_frames[1:2])
# List of 2
#  $ :'data.frame': 38 obs. of  6 variables:
#   ..$ ï..Province.State: chr [1:38] "Anhui" "Beijing" "Chongqing" "Fujian" ...
#   ..$ Country.Region   : chr [1:38] "Mainland China" "Mainland China" "Mainland China" "Mainland China" ...
#   ..$ Last.Update      : chr [1:38] "1/22/2020 17:00" "1/22/2020 17:00" "1/22/2020 17:00" "1/22/2020 17:00" ...
#   ..$ Confirmed        : int [1:38] 1 14 6 1 NA 26 2 1 4 1 ...
#   ..$ Deaths           : int [1:38] NA NA NA NA NA NA NA NA NA NA ...
#   ..$ Recovered        : int [1:38] NA NA NA NA NA NA NA NA NA NA ...
#  $ :'data.frame': 46 obs. of  6 variables:
#   ..$ ï..Province.State: chr [1:46] "Anhui" "Beijing" "Chongqing" "Fujian" ...
#   ..$ Country.Region   : chr [1:46] "Mainland China" "Mainland China" "Mainland China" "Mainland China" ...
#   ..$ Last.Update      : chr [1:46] "1/23/20 17:00" "1/23/20 17:00" "1/23/20 17:00" "1/23/20 17:00" ...
#   ..$ Confirmed        : int [1:46] 9 22 9 5 2 32 5 3 5 1 ...
#   ..$ Deaths           : int [1:46] NA NA NA NA NA NA NA NA NA 1 ...
#   ..$ Recovered        : int [1:46] NA NA NA NA NA 2 NA NA NA NA ...

Unfortunately, not all frames are created equal.

unique(lapply(list_of_frames, colnames))
# [[1]]
# [1] "ï..Province.State" "Country.Region"    "Last.Update"      
# [4] "Confirmed"         "Deaths"            "Recovered"        
# [[2]]
# [1] "Province.State" "Country.Region" "Last.Update"    "Confirmed"     
# [5] "Deaths"         "Recovered"     
# [[3]]
# [1] "Province.State" "Country.Region" "Last.Update"    "Confirmed"     
# [5] "Deaths"         "Recovered"      "Latitude"       "Longitude"     
# [[4]]
# [1] "ï..Province.State" "Country.Region"    "Last.Update"      
# [4] "Confirmed"         "Deaths"            "Recovered"        
# [7] "Latitude"          "Longitude"        
# [[5]]
#  [1] "ï..FIPS"        "Admin2"         "Province_State" "Country_Region"
#  [5] "Last_Update"    "Lat"            "Long_"          "Confirmed"     
#  [9] "Deaths"         "Recovered"      "Active"         "Combined_Key"  
# [[6]]
#  [1] "FIPS"           "Admin2"         "Province_State" "Country_Region"
#  [5] "Last_Update"    "Lat"            "Long_"          "Confirmed"     
#  [9] "Deaths"         "Recovered"      "Active"         "Combined_Key"

So two things we need to do:

Fix where some fields are prepended with "ï..".
Deal with the fact that some frames have additional columns.

First,

list_of_frames2 <- lapply(list_of_frames, function(x) {
  colnames(x) <- gsub(".*\\.", "", colnames(x))
  x
})
unique(lapply(list_of_frames2, colnames))
# [[1]]
# [1] "State"     "Region"    "Update"    "Confirmed" "Deaths"    "Recovered"
# [[2]]
# [1] "State"     "Region"    "Update"    "Confirmed" "Deaths"    "Recovered"
# [7] "Latitude"  "Longitude"
# [[3]]
#  [1] "FIPS"           "Admin2"         "Province_State" "Country_Region"
#  [5] "Last_Update"    "Lat"            "Long_"          "Confirmed"     
#  [9] "Deaths"         "Recovered"      "Active"         "Combined_Key"

Second ... you can likely row-bind those from the first two (since some will be missing lat/lon), but the third set is just different.

lapply(list_of_frames2[60:61], head)
# [[1]]
#    State  Region              Update Confirmed Deaths Recovered Latitude
# 1  Hubei   China 2020-03-21T10:13:08     67800   3139     58946  30.9756
# 2          Italy 2020-03-21T17:43:03     53578   4825      6072  41.8719
# 3          Spain 2020-03-21T13:13:30     25374   1375      2125  40.4637
# 4        Germany 2020-03-21T20:43:02     22213     84       233  51.1657
# 5           Iran 2020-03-21T11:13:12     20610   1556      7635  32.4279
# 6 France  France 2020-03-21T20:43:02     14282    562        12  46.2276
#   Longitude
# 1  112.2707
# 2   12.5674
# 3   -3.7492
# 4   10.4515
# 5   53.6880
# 6    2.2137
# [[2]]
#    FIPS        Admin2 Province_State Country_Region   Last_Update      Lat
# 1 36061 New York City       New York             US 3/22/20 23:45 40.76727
# 2 36059        Nassau       New York             US 3/22/20 23:45 40.74067
# 3 36119   Westchester       New York             US 3/22/20 23:45 41.16278
# 4 36103       Suffolk       New York             US 3/22/20 23:45 40.88320
# 5 36087      Rockland       New York             US 3/22/20 23:45 41.15028
# 6 36071        Orange       New York             US 3/22/20 23:45 41.40337
#       Long_ Confirmed Deaths Recovered Active                Combined_Key
# 1 -73.97153      9654     63         0      0 New York City, New York, US
# 2 -73.58942      1900      4         0      0        Nassau, New York, US
# 3 -73.75742      1873      0         0      0   Westchester, New York, US
# 4 -72.80122      1034      9         0      0       Suffolk, New York, US
# 5 -74.02560       455      1         0      0      Rockland, New York, US
# 6 -74.30241       247      0         0      0        Orange, New York, US

Some some "basic" reduction and finding commonality, I believe this does a reasonable job:

renamer <- c(
  State = "Province_State",
  Region = "Country_Region",
  Update = "Last_Update",
  Latitude = "Lat",
  Longitude = "Long_"
)
list_of_frames3 <- lapply(list_of_frames2, function(x) {
  nms <- colnames(x)
  colnames(x) <- ifelse(nms %in% names(renamer), renamer[ nms ], nms)
  x
})
unique(lapply(list_of_frames3, colnames))
# [[1]]
# [1] "Province_State" "Country_Region" "Last_Update"    "Confirmed"     
# [5] "Deaths"         "Recovered"     
# [[2]]
# [1] "Province_State" "Country_Region" "Last_Update"    "Confirmed"     
# [5] "Deaths"         "Recovered"      "Lat"            "Long_"         
# [[3]]
#  [1] "FIPS"           "Admin2"         "Province_State" "Country_Region"
#  [5] "Last_Update"    "Lat"            "Long_"          "Confirmed"     
#  [9] "Deaths"         "Recovered"      "Active"         "Combined_Key"

This is better. The base-R approach would use something like do.call(rbind, list_of_frames3), but because we have difference in columns, it's best IMO to use one of dplyr::bind_rows or data.table::rbindlist:

dplyr::bind_rows(list_of_frames3) %>% str(.)
# 'data.frame': 31566 obs. of  12 variables:
#  $ Province_State: chr  "Anhui" "Beijing" "Chongqing" "Fujian" ...
#  $ Country_Region: chr  "Mainland China" "Mainland China" "Mainland China" "Mainland China" ...
#  $ Last_Update   : chr  "1/22/2020 17:00" "1/22/2020 17:00" "1/22/2020 17:00" "1/22/2020 17:00" ...
#  $ Confirmed     : int  1 14 6 1 NA 26 2 1 4 1 ...
#  $ Deaths        : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Recovered     : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Lat           : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ Long_         : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ FIPS          : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Admin2        : chr  NA NA NA NA ...
#  $ Active        : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Combined_Key  : chr  NA NA NA NA ...

data.table::rbindlist(list_of_frames3, fill = TRUE) %>% str(.)
# Classes 'data.table' and 'data.frame':    31566 obs. of  12 variables:
#  $ Province_State: chr  "Anhui" "Beijing" "Chongqing" "Fujian" ...
#  $ Country_Region: chr  "Mainland China" "Mainland China" "Mainland China" "Mainland China" ...
#  $ Last_Update   : chr  "1/22/2020 17:00" "1/22/2020 17:00" "1/22/2020 17:00" "1/22/2020 17:00" ...
#  $ Confirmed     : int  1 14 6 1 NA 26 2 1 4 1 ...
#  $ Deaths        : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Recovered     : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Lat           : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ Long_         : num  NA NA NA NA NA NA NA NA NA NA ...
#  $ FIPS          : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Admin2        : chr  NA NA NA NA ...
#  $ Active        : int  NA NA NA NA NA NA NA NA NA NA ...
#  $ Combined_Key  : chr  NA NA NA NA ...
#  - attr(*, ".internal.selfref")=<externalptr>

There are more hurdles to overcome here. Namely: verification that the renaming was correct (and that regions in the beginning are the regions in the most recent), and timestamps change format. Look at the first row from each frame:

data.table::rbindlist(
  lapply(
    split(list_of_frames3,
          sapply(list_of_frames3, function(a) paste(colnames(a), collapse=","))),
    function(x) data.table::rbindlist(lapply(x, head, n = 1))
  ),
  fill = TRUE
)
#      FIPS        Admin2 Province_State Country_Region         Last_Update      Lat     Long_ Confirmed Deaths Recovered Active                  Combined_Key
#  1: 36061 New York City       New York             US       3/22/20 23:45 40.76727 -73.97153      9654     63         0      0   New York City, New York, US
#  2: 45001     Abbeville South Carolina             US 2020-03-23 23:19:34 34.22333 -82.46171         1      0         0      0 Abbeville, South Carolina, US
#  3: 45001     Abbeville South Carolina             US 2020-03-24 23:37:31 34.22333 -82.46171         1      0         0      0 Abbeville, South Carolina, US
#  4: 45001     Abbeville South Carolina             US 2020-03-25 23:33:19 34.22333 -82.46171         3      0         0      0 Abbeville, South Carolina, US
#  5: 45001     Abbeville South Carolina             US 2020-03-26 23:48:35 34.22333 -82.46171         3      0         0      0 Abbeville, South Carolina, US
#  6: 45001     Abbeville South Carolina             US 2020-03-27 22:14:55 34.22333 -82.46171         4      0         0      0 Abbeville, South Carolina, US
#  7: 45001     Abbeville South Carolina             US 2020-03-28 23:05:37 34.22333 -82.46171         3      0         0      0 Abbeville, South Carolina, US
#  8:    NA          <NA>          Anhui Mainland China     1/22/2020 17:00       NA        NA         1     NA        NA     NA                          <NA>
#  9:    NA          <NA>          Anhui Mainland China       1/23/20 17:00       NA        NA         9     NA        NA     NA                          <NA>
# 10:    NA          <NA>          Hubei Mainland China       1/24/20 17:00       NA        NA       549     24        31     NA                          <NA>
# 11:    NA          <NA>          Hubei Mainland China       1/25/20 17:00       NA        NA       761     40        32     NA                          <NA>
# 12:    NA          <NA>          Hubei Mainland China       1/26/20 16:00       NA        NA      1058     52        42     NA                          <NA>
# 13:    NA          <NA>          Hubei Mainland China       1/27/20 23:59       NA        NA      1423     76        45     NA                          <NA>
# 14:    NA          <NA>          Hubei Mainland China       1/28/20 23:00       NA        NA      3554    125        80     NA                          <NA>
# 15:    NA          <NA>          Hubei Mainland China       1/29/20 19:30       NA        NA      3554    125        88     NA                          <NA>
# 16:    NA          <NA>          Hubei Mainland China       1/30/20 16:00       NA        NA      4903    162        90     NA                          <NA>
# 17:    NA          <NA>          Hubei Mainland China     1/31/2020 23:59       NA        NA      5806    204       141     NA                          <NA>
# 18:    NA          <NA>          Hubei Mainland China      2/1/2020 11:53       NA        NA      7153    249       168     NA                          <NA>
# 19:    NA          <NA>          Hubei Mainland China 2020-02-02T23:43:02       NA        NA     11177    350       295     NA                          <NA>
# 20:    NA          <NA>          Hubei Mainland China 2020-02-03T23:23:03       NA        NA     13522    414       386     NA                          <NA>
# 21:    NA          <NA>          Hubei Mainland China 2020-02-04T23:43:01       NA        NA     16678    479       522     NA                          <NA>
# 22:    NA          <NA>          Hubei Mainland China 2020-02-05T23:13:12       NA        NA     19665    549       633     NA                          <NA>
# 23:    NA          <NA>          Hubei Mainland China 2020-02-06T23:23:02       NA        NA     22112    618       817     NA                          <NA>
# 24:    NA          <NA>          Hubei Mainland China 2020-02-07T23:43:02       NA        NA     24953    699      1115     NA                          <NA>
# 25:    NA          <NA>          Hubei Mainland China 2020-02-08T23:33:06       NA        NA     27100    780      1439     NA                          <NA>
# 26:    NA          <NA>          Hubei Mainland China 2020-02-09T23:33:02       NA        NA     29631    871      1795     NA                          <NA>
# 27:    NA          <NA>          Hubei Mainland China 2020-02-10T23:33:02       NA        NA     31728    974      2222     NA                          <NA>
# 28:    NA          <NA>          Hubei Mainland China 2020-02-11T23:33:02       NA        NA     33366   1068      2639     NA                          <NA>
# 29:    NA          <NA>          Hubei Mainland China 2020-02-12T14:13:08       NA        NA     33366   1068      2686     NA                          <NA>
# 30:    NA          <NA>          Hubei Mainland China 2020-02-13T14:13:06       NA        NA     48206   1310      3459     NA                          <NA>
# 31:    NA          <NA>          Hubei Mainland China 2020-02-14T23:33:02       NA        NA     54406   1457      4774     NA                          <NA>
# 32:    NA          <NA>          Hubei Mainland China 2020-02-15T23:13:05       NA        NA     56249   1596      5623     NA                          <NA>
# 33:    NA          <NA>          Hubei Mainland China 2020-02-16T23:53:01       NA        NA     58182   1696      6639     NA                          <NA>
# 34:    NA          <NA>          Hubei Mainland China 2020-02-17T23:13:06       NA        NA     59989   1789      7862     NA                          <NA>
# 35:    NA          <NA>          Hubei Mainland China 2020-02-18T23:13:11       NA        NA     61682   1921      9128     NA                          <NA>
# 36:    NA          <NA>          Hubei Mainland China 2020-02-19T23:23:02       NA        NA     62031   2029     10337     NA                          <NA>
# 37:    NA          <NA>          Hubei Mainland China 2020-02-20T23:43:02       NA        NA     62442   2144     11788     NA                          <NA>
# 38:    NA          <NA>          Hubei Mainland China 2020-02-21T13:03:09       NA        NA     62662   2144     11881     NA                          <NA>
# 39:    NA          <NA>          Hubei Mainland China 2020-02-22T23:33:06       NA        NA     64084   2346     15299     NA                          <NA>
# 40:    NA          <NA>          Hubei Mainland China 2020-02-23T11:33:03       NA        NA     64084   2346     15343     NA                          <NA>
# 41:    NA          <NA>          Hubei Mainland China 2020-02-24T11:13:09       NA        NA     64287   2495     16748     NA                          <NA>
# 42:    NA          <NA>          Hubei Mainland China 2020-02-25T15:23:04       NA        NA     64786   2563     18971     NA                          <NA>
# 43:    NA          <NA>          Hubei Mainland China 2020-02-26T14:13:10       NA        NA     65187   2615     20969     NA                          <NA>
# 44:    NA          <NA>          Hubei Mainland China 2020-02-27T12:13:14       NA        NA     65596   2641     23383     NA                          <NA>
# 45:    NA          <NA>          Hubei Mainland China 2020-02-28T00:43:01       NA        NA     65914   2682     26403     NA                          <NA>
# 46:    NA          <NA>          Hubei Mainland China 2020-02-29T12:13:10       NA        NA     66337   2727     28993     NA                          <NA>
# 47:    NA          <NA>          Hubei Mainland China 2020-03-01T10:13:19 30.97560 112.27070     66907   2761     31536     NA                          <NA>
# 48:    NA          <NA>          Hubei Mainland China 2020-03-02T15:03:23 30.97560 112.27070     67103   2803     33934     NA                          <NA>
# 49:    NA          <NA>          Hubei Mainland China 2020-03-03T11:43:02 30.97560 112.27070     67217   2835     36208     NA                          <NA>
# 50:    NA          <NA>          Hubei Mainland China 2020-03-04T12:53:03 30.97560 112.27070     67332   2871     38557     NA                          <NA>
# 51:    NA          <NA>          Hubei Mainland China 2020-03-05T14:53:03 30.97560 112.27070     67466   2902     40592     NA                          <NA>
# 52:    NA          <NA>          Hubei Mainland China 2020-03-06T14:23:04 30.97560 112.27070     67592   2931     42033     NA                          <NA>
# 53:    NA          <NA>          Hubei Mainland China 2020-03-07T11:13:04 30.97560 112.27070     67666   2959     43500     NA                          <NA>
# 54:    NA          <NA>          Hubei Mainland China 2020-03-08T14:43:03 30.97560 112.27070     67707   2986     45235     NA                          <NA>
# 55:    NA          <NA>          Hubei Mainland China 2020-03-09T14:33:03 30.97560 112.27070     67743   3008     46488     NA                          <NA>
# 56:    NA          <NA>          Hubei Mainland China 2020-03-10T15:13:05 30.97560 112.27070     67760   3024     47743     NA                          <NA>
# 57:    NA          <NA>          Hubei          China 2020-03-11T10:53:02 30.97560 112.27070     67773   3046     49134     NA                          <NA>
# 58:    NA          <NA>          Hubei          China 2020-03-12T09:53:06 30.97560 112.27070     67781   3056     50318     NA                          <NA>
# 59:    NA          <NA>          Hubei          China 2020-03-13T11:09:03 30.97560 112.27070     67786   3062     51553     NA                          <NA>
# 60:    NA          <NA>          Hubei          China 2020-03-14T10:13:09 30.97560 112.27070     67790   3075     52960     NA                          <NA>
# 61:    NA          <NA>          Hubei          China 2020-03-15T18:20:18 30.97560 112.27070     67794   3085     54288     NA                          <NA>
# 62:    NA          <NA>          Hubei          China 2020-03-16T14:38:45 30.97560 112.27070     67798   3099     55142     NA                          <NA>
# 63:    NA          <NA>          Hubei          China 2020-03-17T11:53:10 30.97560 112.27070     67799   3111     56003     NA                          <NA>
# 64:    NA          <NA>          Hubei          China 2020-03-18T12:13:09 30.97560 112.27070     67800   3122     56927     NA                          <NA>
# 65:    NA          <NA>          Hubei          China 2020-03-19T10:13:14 30.97560 112.27070     67800   3130     57682     NA                          <NA>
# 66:    NA          <NA>          Hubei          China 2020-03-20T07:43:02 30.97560 112.27070     67800   3133     58382     NA                          <NA>
# 67:    NA          <NA>          Hubei          China 2020-03-21T10:13:08 30.97560 112.27070     67800   3139     58946     NA                          <NA>
#      FIPS        Admin2 Province_State Country_Region         Last_Update      Lat     Long_ Confirmed Deaths Recovered Active                  Combined_Key

I'm not going to spend time here on verification, but the timestamp thing can be corrected:

alldata <- data.table::rbindlist(list_of_frames3, fill = TRUE)

fmts <- c("%m/%d/%y %H:%M", "%m/%d/%Y %H:%M", "%Y-%m-%d %H:%M:%S", "%Y-%m-%dT%H:%M:%S")
timestamp <- rep(Sys.time()[NA], nrow(alldata))
for (fmt in fmts) {
  if (!any(isna <- is.na(timestamp))) next
  timestamp[isna] <- as.POSIXct(alldata$Last_Update[isna], format = fmt)
}
head(timestamp)
# [1] "2020-01-22 17:00:00 PST" "2020-01-22 17:00:00 PST" "2020-01-22 17:00:00 PST"
# [4] "2020-01-22 17:00:00 PST" "2020-01-22 17:00:00 PST" "2020-01-22 17:00:00 PST"
range(timestamp)
# [1] "2020-01-22 17:00:00 PST" "2020-03-28 23:11:06 PDT"

alldata$Last_Update <- timestamp
alldata[order(Last_Update),]
#           Province_State Country_Region         Last_Update Confirmed Deaths Recovered      Lat    Long_ FIPS Admin2 Active                 Combined_Key
#     1:             Anhui Mainland China 2020-01-22 17:00:00         1     NA        NA       NA       NA   NA   <NA>     NA                         <NA>
#     2:           Beijing Mainland China 2020-01-22 17:00:00        14     NA        NA       NA       NA   NA   <NA>     NA                         <NA>
#     3:         Chongqing Mainland China 2020-01-22 17:00:00         6     NA        NA       NA       NA   NA   <NA>     NA                         <NA>
#     4:            Fujian Mainland China 2020-01-22 17:00:00         1     NA        NA       NA       NA   NA   <NA>     NA                         <NA>
#     5:             Gansu Mainland China 2020-01-22 17:00:00        NA     NA        NA       NA       NA   NA   <NA>     NA                         <NA>
#    ---                                                                                                                                                  
# 31562:        Queensland      Australia 2020-03-28 23:11:06       625      1         8 -27.4698 153.0251   NA           616        Queensland, Australia
# 31563:   South Australia      Australia 2020-03-28 23:11:06       287      0         6 -34.9285 138.6007   NA           281   South Australia, Australia
# 31564:          Tasmania      Australia 2020-03-28 23:11:06        62      0         5 -42.8821 147.3272   NA            57          Tasmania, Australia
# 31565:          Victoria      Australia 2020-03-28 23:11:06       685      3       191 -37.8136 144.9631   NA           491          Victoria, Australia
# 31566: Western Australia      Australia 2020-03-28 23:11:06       278      2        28 -31.9505 115.8605   NA           248 Western Australia, Australia

Cheers!

This is fantastic! Thank you very much! I'll wait till tomorrow to see the update! Now I only need to figure out how to aggregate across csv's :-) — Antoni Parellada, Mar 29 '20 at 17:06
As the icing on the cake, could you consider including the plotting proposed in the OP? — Antoni Parellada, Mar 29 '20 at 18:13
The data looks a bit noisy in that regard, many countries/regions have multiple counts on a day. Without looking at it more closely, either some days are duplicated or perhaps they update multiple times in a day some times. To plot this will take a bit more elbow-grease in order to find what is where (I'm not intimately familiar with the data yet). — r2evans, Mar 29 '20 at 22:01
Turns out I get an error message... `> ign <- Map(download.file, csvlinks, csvfiles, quiet = TRUE) Error in mapply(FUN = f, ..., SIMPLIFY = FALSE) : zero-length inputs cannot be mixed with those of non-zero length` — Antoni Parellada, Mar 31 '20 at 05:03
That error is because you ran it already and everything worked the previous time you ran it. I have since updated the code for the `Map` error and replaced with something a little more robust. Please try now. — r2evans, Mar 31 '20 at 05:15
I posted a follow-up question [here](https://stackoverflow.com/q/60948692/4089351). — Antoni Parellada, Mar 31 '20 at 10:43

hello_friend · Answer 2 · 2020-03-31T22:55:56.563

Not a particularly nice chart but a fairly robust data pipeline:

# Install pacakges if they are not already installed:
necessary_packages <- c("rvest", "tidyverse")

# Create a vector containing the names of any packages needing installation:
new_packages <- necessary_packages[!(necessary_packages %in%
                                       installed.packages()[, "Package"])]

# If the vector has more than 0 values, install the new pacakges
# (and their) associated dependencies:
if (length(new_packages) > 0) {
  install.packages(new_packages, dependencies = TRUE)
}

# Initialise the packages in the session:
lapply(necessary_packages, require, character.only = TRUE)

# Store a scalar that's values is the github url: URL => vector:
URL <-
  "https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_daily_reports"

# Store a scalar of the directory path where files are to be stored:
# Enter your desired path here !
covid_19_csv_dir_path <- "C:/Users/.../Documents/covid_19_csvs"

# If the directory doesn't exist create it:
if (!(dir.exists(covid_19_csv_dir_path))) {
  dir.create(covid_19_csv_dir_path)
}

# Store a vector of URLs: csvlinks => character vector:
csvlinks <-
  read_html(URL) %>%
  html_nodes("a") %>%
  html_attr("href") %>%
  grep("csv$", ., value = TRUE) %>%
  paste0("https://raw.githubusercontent.com", .) %>%
  gsub("/blob", "", .)

# Store a vector of csv names: csv_names => vector
csv_names <- sub(".*\\/", "", csvlinks)

# Check if the file already exists in directory: csvs_stored_locally => vector:
csvs_stored_locally <- list.files(covid_19_csv_dir_path)

# Subset the csvlinks vector to contain those csvs that
# require downloading: csvs_to_be_stored => vector
csvs_to_be_stored <- csvlinks[!(csv_names %in% csvs_stored_locally)]

# Conditionally execute the next segment if there are csvs to store:
if (length(csvs_to_be_stored) > 0) {
  # Create a vector of the date of each version: version_dates => vector
  version_dates <- as.Date(gsub("\\.csv", "",
                                csv_names[!(csv_names %in% csvs_stored_locally)]),
                           "%m-%d-%Y")

  # Create a vector of names for each dataframe in the list: df_names => vector
  df_names <- paste0("x_", gsub("[[:punct:]]", "_", version_dates))

  # Create an empty list to store the dataframes: df_list => list
  df_list <- vector("list", length(csvs_to_be_stored))

  # Store the dataframes in the named list: df_list => list
  df_list <- lapply(seq_along(csvs_to_be_stored),
                    function(i) {
                      read.csv(csvs_to_be_stored[i], sep = ",")
                    })

  # Clean the vector names in each dataframe: cleaned_df_names_list => df_list
  df_list <- setNames(lapply(df_list,
                             function(x) {
                               names(x) <- gsub("[[:punct:]]|\\s+|.*\\.", "",
                                                trimws(names(x), "both"))
                               return(x)
                             }),
                      df_names)

  # Store the version date as a variable: df_list => list
  df_list <-
    mapply(cbind,
           "version_date" = version_dates,
           df_list,
           SIMPLIFY = FALSE)

  # Store the csv files in the directory: stdout
  lapply(seq_along(df_list),
         function(i) {
           write.csv(as.data.frame(df_list[i]),
                     paste0(covid_19_csv_dir_path, "/",
                            sub("\\/", "", csv_names[i])),
                     row.names = FALSE)
         })
}

# If there are files stored in the directory read them in:
if (length(csvs_stored_locally) > 0) {
  # Allocate some memory for a list of dataframes: ls_csvs => list
  ls_csvs <- vector("list", length(csvs_stored_locally))

  # Read the csvs as dataframes into a list and name them appropriately: ls_csvs => list
  ls_csvs <-
    setNames(lapply(seq_along(csvs_stored_locally), function(i) {
      read.csv(paste0(covid_19_csv_dir_path, "/", csvs_stored_locally[i]))
    }),
    paste0("x_", gsub(
      "[[:punct:]]", "_",
      gsub("\\.csv", "", csvs_stored_locally)
    )))
}

# If csvs have been downloaded from the github in this execution, combine
# the list of stored dataframes with the those downloaded: combined_df_list => list
if (exists("df_list") & exists("ls_csvs")) {

  # Combine the two lists: combined_df_list => list
  combined_df_list <- c(ls_csvs, df_list)

  # Remove df_list, ls_csvs variables from the global environment:
  rm(df_list, ls_csvs)

  # If ls_csvs but not df_list exists in the global environment:
} else if (exists("ls_csvs") & !(exists("df_list"))) {

  # Rename the ls_csvs list: combined_df_list => list
  combined_df_list <- ls_csvs

  # Remove ls_csv variables from the global environment:
  rm(ls_csvs)


# Otherwise:
} else{

  # Rename the df_list list: combined_df_list => list
  combined_df_list <- df_list

}

# Re-allocate some memory:
gc()

# Store a function to row-bind all dataframes in the list: rbind_all_columns => function
rbind_all_columns <- function(x, y) {
  x[, c(as.character(setdiff(colnames(y), colnames(x))))] <- NA
  y[, c(as.character(setdiff(colnames(x), colnames(y))))] <- NA
  return(rbind(x, y))
}

# Apply the function and store the result as a dataframe: df => data.frame
df <-
  Reduce(function(x, y) {
    rbind_all_columns(x, y)
  }, combined_df_list)

# Subset out New York and Washington: washington_vs_ny => data.frame 
washington_vs_ny <- df[which(df$State == "Washington" | df$State == "New York"),]

# Clean the data.frame: washington_vs_nyordered => data.frame
washington_vs_nyordered <- within(washington_vs_ny[order(washington_vs_ny$version_date),],
                             {
                               Confirmed <- ifelse(is.na(Confirmed), 0, Confirmed)
                               Deaths <- ifelse(is.na(Deaths), 0, Deaths)
                               Recovered <- ifelse(is.na(Recovered), 0, Recovered)
                             }
)[,c("version_date", "State", "Confirmed", "Deaths", "Recovered")]

# Reshape the data for charting: chart_df => data.frame: 
chart_data <- within(reshape(washington_vs_nyordered,
        direction = "long", 
        varying = c("Confirmed", "Deaths", "Recovered"),
        v.names=c("vars"),
        idvar = c("version_date", "State"),
        timevar = "vals", 
        times = c("Confirmed", "Deaths", "Recovered"),
        new.row.names = 1:(length(c("Confirmed", "Deaths", "Recovered")) * 
                             nrow(washington_vs_nyordered))
      ), {version_date <- as.Date(as.character(version_date), "%Y-%m-%d")})

# Chart the data: 
ggplot(chart_data, aes(x = version_date, y = vars, colour = vals)) +
  geom_line() + 
  facet_wrap(.~State)

How to code in R operations involving progressively increasing separate online csv files?

2 Answers2

Linked