Convert tibble to time series

Question

I tried to download data on covid provided by the Economist's Github repository.

library(readr)
library(knitr)
myfile <- "https://raw.githubusercontent.com/TheEconomist/covid-19-excess-deaths-tracker/master/output-data/excess-deaths/all_weekly_excess_deaths.csv"
test <- read_csv(myfile)

What I get is a tibble data frame and I am unable to easily access the data stored in that tibble. I would like to look at one column, say test$covid_deaths_per_100k and re-shape that into a matrix or ts object with rows referring to time and columns referring to countries.

I tried it manually, but I failed. Then I tried with the tsibble package and failed again:

tsibble(test[c("covid_deaths_per_100k","country")],index=test$start_date)
Error: Must extract column with a single valid subscript.
x Subscript `var` has the wrong type `date`.
ℹ It must be numeric or character.

So, I guess the problem is that the data are stacked by countries and hence the time index is duplicated. I would need some of these magic pipe functions to make this work? Is there an easy way to do that, perhaps without piping?

Answer 1

A valid tsibble must have distinct rows identified by key and index:

as_tsibble(test,index = start_date,key=c(country,region))
# A tsibble: 11,715 x 17 [1D]
# Key:       country, region [176]
   country   region    region_code start_date end_date    days  year  week population total_deaths
   <chr>     <chr>     <chr>       <date>     <date>     <dbl> <dbl> <dbl>      <dbl>        <dbl>
 1 Australia Australia 0           2020-01-01 2020-01-07     7  2020     1   25734100         2497
 2 Australia Australia 0           2020-01-08 2020-01-14     7  2020     2   25734100         2510
 3 Australia Australia 0           2020-01-15 2020-01-21     7  2020     3   25734100         2501
 4 Australia Australia 0           2020-01-22 2020-01-28     7  2020     4   25734100         2597
 5 Australia Australia 0           2020-01-29 2020-02-04     7  2020     5   25734100         2510
 6 Australia Australia 0           2020-02-05 2020-02-11     7  2020     6   25734100         2530
 7 Australia Australia 0           2020-02-12 2020-02-18     7  2020     7   25734100         2613
 8 Australia Australia 0           2020-02-19 2020-02-25     7  2020     8   25734100         2608
 9 Australia Australia 0           2020-02-26 2020-03-03     7  2020     9   25734100         2678
10 Australia Australia 0           2020-03-04 2020-03-10     7  2020    10   25734100         2602
# ... with 11,705 more rows, and 7 more variables: covid_deaths <dbl>, expected_deaths <dbl>,
#   excess_deaths <dbl>, non_covid_deaths <dbl>, covid_deaths_per_100k <dbl>,
#   excess_deaths_per_100k <dbl>, excess_deaths_pct_change <dbl>

Answer 2

ts works best with monthly, quarterly or annual series. Here we show a few approaches.

1) monthly This creates a monthly zoo object z from the indicated test columns splitting by country and aggregating to produce a monthly time series. It then creates a ts object from that.

library(zoo)

z <- read.zoo(test[c("start_date", "country", "covid_deaths")], 
  split = "country", FUN = as.yearmon, aggregate = sum)
as.ts(z)

2) weekly To create a weekly ts object with frequency 53

to_weekly <- function(x) {       
      yr <- as.integer(as.yearmon(x))
      wk <- as.integer(format(as.Date(x), "%U"))
      yr + wk/53
}
z <- read.zoo(test[c("start_date", "country", "covid_deaths")], 
  split = "country", FUN = to_weekly, aggregate = sum)  
as.ts(z)

3) daily If you want a series where the times are dates then omit the FUN argument and use zoo directly.

z <- read.zoo(test[c("end_date", "country", "covid_deaths")], 
  split = "country", aggregate = sum)