Track variants over time

Turn sampling dates into weekly time series, and combine several aggregates into a single result.

In this tutorial, we will count sequences per week to follow how variants replaced each other, and use joins and unions to put several of these counts into one table. It builds on filter(), group() and order(), which the basic queries tutorial introduces.

Loading the data

We will use a table of 1,000 SARS-CoV-2 sequences that RhyDB indexes in your browser. Click the button to load it:

Loading RhyDB…

The table is called data. Each row is one sequence; date holds its sampling date, country the country it was sampled in, and who_clade its WHO variant label, such as Delta or Omicron.

Weekly counts

The table also stores date_year and date_month, but there is no column for the week. map() derives one: isoWeek() turns a date into its ISO 8601 week, written like 2021-W49. Weeks are written with two digits, so ordering them as strings also orders them in time.

Add a week column, then group by it to count the sequences per week:

data.map({ week := date.isoWeek() }).group(by := { week }, aggs := { count := count() }).order(by := { week })

The first row has a null week: four sequences have no sampling date, and their week is null too. Filter them out before mapping:

data.filter(isNotNull(date)).map({ week := date.isoWeek() }).group(by := { week }, aggs := { count := count() }).order(by := { week })

Add who_clade to the grouping fields to get one row per week and variant. Restricted to December 2021 and January 2022, this shows Omicron taking over from Delta:

data
.filter(date.between('2021-12-01'::date, '2022-01-31'::date))
.map({ week := date.isoWeek() })
.group(by := { week, who_clade }, aggs := { count := count() })
.order(by := { week, count.desc() })

In which week was Omicron first sequenced more often than Delta? And on which date was the last Delta sequence in the dataset sampled, and in which ISO week does it fall? (Hint: sort by date.desc().)

In 2021-W50, with 5 Omicron and 4 Delta sequences. The last Delta sequences were sampled on 2 January 2022, which still belongs to week 2021-W52: an ISO week belongs to the year that contains its Thursday, so the first days of January can fall into the last week of the previous year.

Loading query editor…

Put two counts side by side with join

A weekly count of one variant says little without the total number of sequences in that week. RhyDB’s map() cannot divide one column by another, but join() can put both counts next to each other in one row.

join() combines two pipelines on a condition such as week = w. The two inputs must have different column names, so the second pipeline names its week column w. By default, a join keeps only the rows that have a match on both sides.

Count all sequences per month in Germany in 2021, and join the number of Delta sequences in that month. type := left keeps every month of the left side, also when it has no Delta sequences; their delta is null:

data
.filter(country = 'Germany' && date_year = 2021)
.group(by := { date_month }, aggs := { total := count() })
.join(
  data
    .filter(country = 'Germany' && date_year = 2021 && who_clade = 'Delta')
    .map({ m := date_month })
    .group(by := { m }, aggs := { delta := count() }),
  date_month = m,
  type := left
)
.project({ date_month, total, delta })
.order(by := { date_month })

Without type := left, the join is an inner join and only keeps the weeks present on both sides. Use it to find the weeks in which both Alpha and Delta were sequenced:

data
.filter(who_clade = 'Alpha')
.map({ week := date.isoWeek() })
.group(by := { week }, aggs := { alpha := count() })
.join(
  data
    .filter(who_clade = 'Delta')
    .map({ w := date.isoWeek() })
    .group(by := { w }, aggs := { delta := count() }),
  week = w
)
.project({ week, alpha, delta })
.order(by := { week })

type := leftAnti does the opposite: it keeps the rows of the left side that have no match and only returns the left columns. Starting from the weekly counts of 2021, how many weeks had sequences but not a single Omicron sequence? (Hint: a second group() with by := {} counts the rows of the joined result.)

49 weeks.

Loading query editor…

Stack results with unionall

Where join() adds columns, unionall() adds rows: it appends one result to another. Both must have the same columns in the same order, with the same types.

Build the weekly counts for Switzerland and Germany separately, label each with a constant column, and stack them into one table:

data
.filter(country = 'Switzerland')
.map({ week := date.isoWeek() })
.group(by := { week }, aggs := { count := count() })
.map({ place := 'Switzerland' })
.unionall(
  data
    .filter(country = 'Germany')
    .map({ week := date.isoWeek() })
    .group(by := { week }, aggs := { count := count() })
    .map({ place := 'Germany' })
)
.order(by := { week })

The same table is easier to get with country as a second grouping field. Write that query and compare the results. unionall() is useful when the stacked parts need different filters or aggregations, such as a weekly series next to a single overall total.

Loading query editor…