Generating Person-Count and Person-Time Tables
Source:vignettes/count_time_tables.Rmd
count_time_tables.Rmd
Sys.setenv(OMP_THREAD_LIMIT = 1) # Reducing core use, to avoid accidental use of too many cores
library(Colossus)
library(data.table)
#>
#> Attaching package: 'data.table'
#> The following object is masked from 'package:base':
#>
#> %notin%
library(parallel)General Purpose
There are multiple reasons why individual level data may be grouped,
including visualization or performing analysis on aggregated data.
Either way the process is similar, we want to take a table and apply
categorizations and summarize events. Colossus offers two
functions designed to create grouped tables,
Event_Count_Gen() and Event_Time_Gen() which
create person-count tables and person-time tables respectively. The
following sections will cover generally how to use each function and
what different needs are met.
Person-Count Tables
A person count table is the simplest way to summarize a table. This assumes you have columns you want to break into categories and columns you want to summarize. Let us start with a basic example. Suppose we have a dataset tracking the number of apples and oranges people bought, the number of hands they used to carry their bag, and whether the bag ripped or not.
apples <- c(0, 1, 2, 3, 4, 5, 6, 2, 2, 3, 4, 2, 1, 5, 6, 4, 2)
oranges <- c(1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 1, 3, 2, 2, 1)
rip <- c(0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 1)
hands <- c(1, 1, 2, 3, 2, 1, 2, 2, 2, 2, 1, 1, 1, 1, 1, 2, 2)
table <- data.table::data.table(
apples = apples,
oranges = oranges,
rip = rip,
hands = hands
)Suppose we want to split our dataset up by the number of each fruit, count the number of ripped bags in each category, and figure out the average number of hands used. We would start by defining how we want to split the categories. There are two general ways to define the intervals, with a single string or a list of upper and lower bounds. Another option is to split by levels of a factor, by setting the categorization to “factor”.
Let us start with the string representation. The string is expected to alternate number and delimiter, either “/” or “]”, which split the upper and lower bounds for each interval. Every interval in this format includes the lower bound. The interval includes the upper bound if “]” is used, and does not otherwise. So the interval [2,5) is equivalent to “2 / 5” and the interval [5,8] is equivalent to “5 ] 8”. This method uses each triplet sequentially, so the categories will cover every value that falls between the first and last number listed. The functions assume that the bounds are strictly non-decreasing, so the upper boundary is equal to the lower boundary or greater. When the upper and lower boundary are equal, the category is upper limit inclusive. In general, data are assigned to the first category they belong to, so points on an included upper boundary will be assigned to the lower category. The string representation can name categories by including the name after the delimiter. For example, a category containing [3,5) named “middle” could be represented “3 / middle 5”. Note that the name and boundary must be split by a space, and the name cannot include “/” or ”]“.
# Suppose we want the categories to change by two:
categ <- "0/2/4/6/8"
# This would form bins like:
# [0,2), [2,4), etc.
# An entry with a value of 2 would be assigned to the second bin
# Suppose we wanted the first bin to be fully inclusive
categ <- "0]2/4/6/8"
# Now the first bin is: [0,2]
# An entry with a value of 2 would be assigned to the first bin only
# A named version might look like
categ <- "0 / low 2 / middle 4 / high 6 / extreme 8"
# This names the bins: low, middle, high, and extreme
# The default is a string representation of the binThe list notation is very similar. There are 2 values required in the list and 1 optional value. The list has to have vectors titled lower and upper, which are the lower and upper bounds for each interval. This method can include gaps. Similarly to the string version, the lower bound is always included and the upper bound is included if the upper bound entry includes “]”. The list option allows the categories to be labeled instead of being automatically numbered. A third list titled name can be provided to label each category.
# Suppose we want the categories to change by two:
categ <- list(
upper = c(2, 4, 6, 8),
lower = c(0, 2, 4, 6)
)
# The version with an inclusive upper bound would be:
categ <- list(
upper = c("2]", 4, 6, 8),
lower = c(0, 2, 4, 6)
)
# We can also name categories
categ <- list(
upper = c(2, 4, 6, 8),
lower = c(0, 2, 4, 6),
name = c("low", "middle", "high", "extreme")
)Suppose we want to break the apple column into the intervals: [0, 3), [3, 5), [5,7] and break our oranges column into the intervals: [-1,3), [3,6), [6,10) with the labels: few, good, and excessive. That would look like the following code. This splits each category into 3 levels and in total 9 combinations.
apple_category <- "0/3/5]7"
orange_category <- list(
lower = c(-1, 3, 6),
upper = c(3, 6, 10),
name = c("few", "good", "excessive")
)
# Finally we combine them into a list. The list names should be column
# names that the categories will be applied to.
categ <- list(
apples = apple_category,
oranges = orange_category
)
# We could also rename a category using `column AS new_name` in the list names.
# This is demonstrated later in the vignette.Now we define what summary variables we want. Once again, we define a list. Every item in the list is named with the column it is applied to and contains a string specifying what summary method to use. There are many options supported currently, two common for a person-event table are count and mean, which will provide the sum or the mean of the column in each category. One can also provide a new name for the summary column by listing the method as “option AS name”. Otherwise, the grouped table will use the original column name for the summary column name. Suppose we wanted to take the sum of ripped bags and name it “dropped” and take the mean of the hands column.
# We might want two events to be summarized, the 'rip' and 'hands' columns.
# The first we can take the sum of, and name the summary 'dropped'
# The second we can just take a mean of
# This creates the event list, once again with list names
# matching columns in the data.
event <- list(rip = "count AS dropped", hands = "mean")The final step is to run the function. The function returns two items, the grouped data and a summary of the category intervals. The category intervals should be checked to verify that the intervals match what were expected.
# We take our table, and apply the categorizations and event summaries
# The result has two values: 'df' and 'bounds'
# df is the final grouped table, with the categories and summaries
# bounds is a description of the category levels applied
Event_Count_Gen(table, categ, event)
#> $df
#> apples_category oranges_category COUNT dropped hands
#> <char> <char> <int> <num> <num>
#> 1: 0 - 3 excessive 1 0 2.0
#> 2: 0 - 3 few 5 2 1.2
#> 3: 0 - 3 good 2 1 2.0
#> 4: 3 - 5 few 1 0 2.0
#> 5: 3 - 5 good 4 3 2.0
#> 6: 5 - 7 excessive 2 2 1.5
#> 7: 5 - 7 few 1 1 1.0
#> 8: 5 - 7 good 1 0 1.0
#>
#> $bounds
#> $bounds$apples_category
#> [1] " [0, 3) [3, 5) [5, 7]"
#>
#> $bounds$oranges_category
#> [1] " [-1, 3) [3, 6) [6, 10)"Person-Time Tables
The previous method treats every row as being weighted the same. In
many cases, every row has some measure of time which we may want to use.
That is the fundamental difference between the person count tables and
person time tables, the inclusion of time. The code used to generate a
person time table is very similar to the person count table, with
several important differences. The first is that the data should have
time columns. Similar to the Cox model options in Colossus,
the data should have an entry column, exit column, or both. These would
denote the scenario where every interval ends at the same point, starts
at the same point, or has different start and stop times. There are two
formats of time allowed, calendar and user scale.
Calendar time data is listed in day, month, and year columns which are combined into dates. User time data is interpreted as duration observed, without any inherent units. These apply to both a person-year list, which specifies the entry and exit columns, as well as the time categories which split the data into intervals. For the calendar time scale, dates will default to 1/1/1900 if not provided. The person-year list for a calendar time-scale can also contain a unit, defaulted to years. For the following example, we will use a calendar time scale.
Calendar Time Scale
# We can start by making some fake data
# a and b are arbitrary factors
# c might be the event status we are interested in
a <- c(0, 1, 2, 3, 4, 5, 6, 2, 2, 3, 4, 2, 1, 5, 6, 4, 2)
b <- c(1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 1, 3, 2, 2, 1)
c <- c(0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 1)
# Next we define columns for the calendar dates of each interval
# In this case we are defining day, month, and year
d <- c(1, 1, 2, 2, 1, 1, 2, 2, 3, 3, 3, 4, 4, 2, 1, 1, 2)
e <- c(1, 1, 1, 2, 2, 2, 2, 1, 1, 1, 1, 2, 2, 2, 2, 1, 1)
f <- c(
1900, 1900, 1900, 1900, 1900, 1900, 1900, 1900, 1900,
1900, 1900, 1900, 1900, 1900, 1900, 1900, 1900
)
g <- c(4, 4, 4, 5, 5, 5, 5, 6, 6, 6, 5, 5, 5, 4, 4, 4, 4)
h <- c(6, 4, 4, 6, 6, 6, 4, 4, 4, 6, 6, 6, 6, 4, 4, 4, 4)
i <- c(
1901, 1902, 1903, 1904, 1905, 1906, 1907, 1903, 1904,
1903, 1904, 1910, 1903, 1904, 1903, 1904, 1910
)
# Finally we have our table that we want to group.
table <- data.table::data.table(
a = a, b = b, c = c,
d = d, e = e, f = f,
g = g, h = h, i = i
)In general, the person-year list should contain the columns used for the entry and/or exit dates as well as an optional units value. Each entry or exit variable should be a list with columns corresponding to the calendar date columns. These columns should be named ‘day’, ‘month’, or ‘year’. As already mentioned, missing entries will currently default to January 1st 1900.
# Our person-years are described by calendar dates
# We have entry and exit lists, each with the columns that
# correspond to the day/month/year.
# We also include a unit, to say we want our final person-years
# in terms of years, instead of days or months.
pyr <- list(
entry = list(year = "f", month = "e", day = "d"),
exit = list(year = "i", month = "h", day = "g"),
unit = "years"
)The second major difference is the possible use of time categories.
For a calendar time scale, time categories are either set by calendar
date or by age. Calendar time scales are set by vectors of the day,
month, and year of each interval splitting point. So intervals changing
every 2 days could be expressed by “day=c(1,3,5,…)”. Age time scales use
a day/month/year as a reference for age, and then set the age categories
using the same notation as non-time categories (either a single string
or list of upper and lower bounds). Unlike the non-time categories, we
can not name the categories and any attempt to do will be ignored and
produce a warning. When age categories are used, a new column is created
for the average age in each interval.
# A calendar category assumes we have a list with days, months, and years
# Yearly categories might look like:
calendar_categ <- list(year = c(2000, 2001, 2002))
# This will define categories going from (1/1/2000 - 12/31/2000), (1/1/2001 - 12/31/2001), etc.
# Alternatively we might want to categorize by duration since a reference date
# In this case our list has an additional value for categories, or upper/lower limits
# For an age based calendar time category, our day/month/year are reference columns not vectors of dates
# Suppose we wanted to categorize by above/below 50 years since entry
age_categ <- list(year = "f", categories = "0 / 50 / 100")
# We add this to our time categories by assigning it a name
# We can use the standard "column AS new_name" notation, new_name defaults to column_category
# How the name is used depends on the categorization
# Both will use 'new_name' as the category name
# Age based categories will use 'column' to define an average duration in the interval
time_categ <- list("calendar" = calendar_categ, "age" = age_categ)
# This will create three new columns in the dataTime categories are unique in that each row can correspond to multiple time categories, so the duration of each row in each time category is kept to distinguish which rows are only partially in each time interval. If a time category is not provided, then the full duration of each row is used for person-year calculations. Multiple categories can be used for a calendar time scale, however it should be noted that multiple calendar date based categories could be combined into a single list of categories.
# The non-time categories are defined as previously shown.
categ <- list(
a = "-1/3/5]7",
b = list(
lower = c(-1, 3, 6), upper = c(3, 6, 10),
name = c("low", "medium", "high")
)
)
# The time categories follow a similar structure.
# We have a list with names that correspond to columns,
# and entries that give category limits.
# Our calendar categories are defined between each of these dates,
# upper bound exclusive.
# Time categories make a new column for the category, which should not be in the data.
time_scale <- list(`time_bin` = list(
day = c(1, 1, 1),
month = c(1, 1, 1),
year = c(1899, 1903, 1910)
))The final difference is that person-time tables make a distinction between events and summaries. The summary list is nearly identical to the event list in the person-count function, with the exception of allowing for means to be weighted by person-years. Person-time tables also return the total intervals “at risk” in each category. Observations that are split into multiple time categories will contribute to the number at risk.
# Summaries follow the person-count table notation for events.
# We apply some summary to an existing column.
# The name should be the column, and we can use 'AS'
# to rename the summary as needed.
summary <- list(b = "weighted_mean AS b_weighted")The event list for the person-time table function is a list of event status, which occur at the end of observation. Events are assigned to the category including the end of observation. By default, the number of events in each grouping is recorded in the final table. Based on the relative row intervals and category intervals, each row can be part of multiple time categories. However, the event is only assigned to the time category containing the endpoint of the row interval. The event list or vector should only contain the event columns, optionally renamed. Note that if an event column is renamed, ‘event AS new_event’, then both versions will be available for summaries but only the new_event version will correctly split events between categories.
# Events in a person-time table are assumed to occur at the end
# of the interval.
# Events are automatically added to the summary list as a sum.
events <- list("c AS cases")Finally, the function is run. Similar to the person-count table function, the function returns the grouped table and list of category boundaries.
# We run the analysis just like the event_count data
# Here we are also setting fcount to true, to return the first at risk in each group
# We get the same returned value, the table and summary of category boundaries
print(Event_Time_Gen(table, pyr, time_scale, categ, summary, events, fcount = T))
#> $df
#> time_bin_category a_category b_category AT_RISK PYR F_AT_RISK cases
#> <char> <char> <char> <int> <num> <num> <num>
#> 1: 1 -1 - 3 high 1 2.9952088 1 0
#> 2: 1 -1 - 3 low 5 12.4791239 5 1
#> 3: 1 -1 - 3 medium 2 5.9876797 2 0
#> 4: 1 3 - 5 low 1 2.9979466 1 0
#> 5: 1 3 - 5 medium 4 11.8083504 4 0
#> 6: 1 5 - 7 high 2 5.8234086 2 0
#> 7: 1 5 - 7 low 1 2.9130732 1 0
#> 8: 1 5 - 7 medium 1 2.9103354 1 0
#> 9: 2 -1 - 3 high 1 0.2600958 0 0
#> 10: 2 -1 - 3 low 3 14.4312115 0 0
#> 11: 2 -1 - 3 medium 2 1.5167693 0 1
#> 12: 2 3 - 5 low 1 1.2566735 0 0
#> 13: 2 3 - 5 medium 4 5.7056810 0 3
#> 14: 2 5 - 7 high 2 7.6824093 0 2
#> 15: 2 5 - 7 low 1 0.2546201 0 1
#> 16: 2 5 - 7 medium 1 1.2566735 0 0
#> b_weighted
#> <num>
#> 1: 6.000000
#> 2: 1.413339
#> 3: 3.999543
#> 4: 2.000000
#> 5: 3.993276
#> 6: 6.499765
#> 7: 2.000000
#> 8: 3.000000
#> 9: 6.000000
#> 10: 1.485297
#> 11: 4.664260
#> 12: 2.000000
#> 13: 4.175144
#> 14: 6.554170
#> 15: 2.000000
#> 16: 3.000000
#>
#> $bounds
#> $bounds$time_bin_category
#> [1] " [1899-01-01 to 1902-12-31] [1903-01-01 to 1910-01-01]"
#>
#> $bounds$a_category
#> [1] " [-1, 3) [3, 5) [5, 7]"
#>
#> $bounds$b_category
#> [1] " [-1, 3) [3, 6) [6, 10)"
# We can also change the units in our person-years
pyr <- list(
entry = list(year = "f", month = "e", day = "d"),
exit = list(year = "i", month = "h", day = "g"),
unit = "days"
)
print(Event_Time_Gen(table, pyr, time_scale, categ, summary, events))
#> $df
#> time_bin_category a_category b_category AT_RISK PYR cases b_weighted
#> <char> <char> <char> <int> <num> <num> <num>
#> 1: 1 -1 - 3 high 1 1094 0 6.000000
#> 2: 1 -1 - 3 low 5 4558 1 1.413339
#> 3: 1 -1 - 3 medium 2 2187 0 3.999543
#> 4: 1 3 - 5 low 1 1095 0 2.000000
#> 5: 1 3 - 5 medium 4 4313 0 3.993276
#> 6: 1 5 - 7 high 2 2127 0 6.499765
#> 7: 1 5 - 7 low 1 1064 0 2.000000
#> 8: 1 5 - 7 medium 1 1063 0 3.000000
#> 9: 2 -1 - 3 high 1 95 0 6.000000
#> 10: 2 -1 - 3 low 3 5271 0 1.485297
#> 11: 2 -1 - 3 medium 2 554 1 4.664260
#> 12: 2 3 - 5 low 1 459 0 2.000000
#> 13: 2 3 - 5 medium 4 2084 3 4.175144
#> 14: 2 5 - 7 high 2 2806 2 6.554170
#> 15: 2 5 - 7 low 1 93 1 2.000000
#> 16: 2 5 - 7 medium 1 459 0 3.000000
#>
#> $bounds
#> $bounds$time_bin_category
#> [1] " [1899-01-01 to 1902-12-31] [1903-01-01 to 1910-01-01]"
#>
#> $bounds$a_category
#> [1] " [-1, 3) [3, 5) [5, 7]"
#>
#> $bounds$b_category
#> [1] " [-1, 3) [3, 6) [6, 10)"User Defined Time Scale
As previously mentioned, the time scale can also be a duration with arbitrary units. This is particularly important if the study is using follow-up time and there is no need to translate to calendar points. The differences start with the person-year list. In this case the entry and exit items are just columns, the duration at entry and exit of the observation.
# Repeating the same non-time categories and event statuses
a <- c(0, 1, 2, 3, 4, 5, 6, 2, 2, 3, 4, 2, 1, 5, 6, 4, 2)
b <- c(1, 2, 3, 4, 5, 6, 7, 6, 5, 4, 3, 2, 1, 3, 2, 2, 1)
c <- c(0, 1, 0, 1, 0, 1, 1, 0, 1, 1, 1, 0, 0, 0, 1, 0, 1)
# Instead of calendar dates, we may have entry and exit duration
d <- c(1, 1, 2, 2, 10, 10, 15, 15, 20, 20, 30, 30, 40, 40, 50, 50, 60)
e <- c(11, 11, 22, 22, 30, 30, 45, 45, 50, 50, 60, 60, 70, 70, 80, 80, 90)
table <- data.table::data.table(
a = a, b = b, c = c,
d = d, e = e
)
# When we have duration instead of calendar dates, our person-year
# list should only have a single entry, instead of lists of columns.
pyr <- list(entry = "d", exit = "e")Next, we define the time categories. The only accepted time category for a user defined scale is similar to the non-time categories. It is assumed to be provided as a single string or a list with upper and lower limits. While we can technically provide multiple categories, they can always be condensed into a single category, similar to the calendar categories.
# Once again, our non-time categories are defined the same
categ <- list(
"a" = "-1/-1/3/5]7",
"b AS b_bin" = list(lower = c(-1, -1, 3, 6), upper = c(-1, 3, 6, "]10"))
)
# Here we are defining the duration categories to use.
# This will create two columns, 'time' and 'time_bin'.
# 'time' is the average duration between the entry and exit duration.
# 'time_bin' is the category value.
# We can use 'time' to find the average duration in each group.
time_scale <- list("time AS time_bin" = "0 / 10 / 50 / 100")Finally, there are functionally no differences between the summaries and events available for person-time tables using calendar and user defined time scales.
summary <- list("c" = "count AS cases", "a" = "mean", "b" = "weighted_mean")
events <- list("c")
# Everything else runs the same, and gives similar outputs.
# Except now the time category boundaries are not based on dates.
print(Event_Time_Gen(table, pyr, time_scale, categ, summary, events))
#> $df
#> time_bin a_category b_bin AT_RISK PYR c a b
#> <char> <char> <char> <int> <num> <num> <num> <num>
#> 1: 1 -1 - 3 2 2 18 0 0.500000 1.500000
#> 2: 1 -1 - 3 3 1 8 0 2.000000 3.000000
#> 3: 1 3 - 5 3 2 8 0 3.500000 4.000000
#> 4: 2 -1 - 3 2 4 32 1 1.000000 1.656250
#> 5: 2 -1 - 3 3 2 42 0 2.000000 4.428571
#> 6: 2 -1 - 3 4 1 30 0 2.000000 6.000000
#> 7: 2 3 - 5 3 4 82 1 3.500000 4.000000
#> 8: 2 5 - 7 3 1 10 0 5.000000 3.000000
#> 9: 2 5 - 7 4 2 50 2 5.500000 6.600000
#> 10: 3 -1 - 3 2 3 60 1 1.666667 1.166667
#> 11: 3 3 - 5 2 1 30 0 4.000000 2.000000
#> 12: 3 3 - 5 3 2 10 2 3.500000 3.000000
#> 13: 3 5 - 7 2 1 30 1 6.000000 2.000000
#> 14: 3 5 - 7 3 1 20 0 5.000000 3.000000
#>
#> $bounds
#> $bounds$time_bin
#> [1] " [0 to 10) [10 to 50) [50 to 100)"
#>
#> $bounds$a_category
#> [1] " [-1, -1] [-1, 3) [3, 5) [5, 7]"
#>
#> $bounds$b_bin
#> [1] " [-1, -1] [-1, 3) [3, 6) [6, 10]"Additional summary options
There are multiple options for optional returned summaries available
for person-time tables. Person-time tables calculate the typical “at
risk” summary variables for any row at risk, but they also can calculate
“first at risk” and “last at risk” summary variables (named
F_AT_RISK and L_AT_RISK respectively). These
values can be summarized by setting their corresponding function
parameters, fcount and lcount to true. Note
that these are not calculated during run-time otherwise, so they can be
used for taking weighted means only if they are returned. Notably, these
summaries determine the first and last observed time category for each
subject. This means that even if a subject is observed past the last
time category, they are still considered “last at risk” during the final
time category. Similarly for the first at risk measure. These summaries
will always sum to the total subjects at risk at any point during the
time categories. These summaries can also be extended to subject IDs, if
there are multiple intervals for each subject. By passing a
studyid parameter to the person-time function, the at risk
summaries will summarize per ID, instead of per observed interval.
In addition to the summary and event methods listed in this vignette, there are 11 methods allowed by person-time tables and 7 methods allowed by person-count tables. As mentioned previously, methods can be written as “option AS name” to rename the output, but they can also be weighted by using “option BY covariate” (or both renamed and weighted by using both). Weighting can be applied to turn almost every non-weighted summary into a weighted version. Weighted summaries can be listed without a weighting column for person-time tables to default to weighting by person-years, however there is no default for person-event tables.
| Option | Description | Alternative Writing | Available for Person-Count |
|---|---|---|---|
| count, sum, rsum | Takes the sum | N/A | Yes |
| mean, rmean | Takes the mean | N/A | Yes |
| weighted_mean | Takes the weighted mean, either by person-years or by a column selected using BY |
mean BY column or
rmean BY column
|
Yes |
| weighted_sum | Takes the weighted sum, either by person-years or by a column selected using BY |
sum BY column,
rsum BY column, or count BY column
|
Yes |
| xsum | Takes the sum over the last at risk intervals | N/A | No |
| xmean | Takes the mean over the last at risk intervals | N/A | No |
| weighted_xsum | Takes the weighted sum over the last at risk intervals, either by person-years or by a column selected using BY | xsum BY column |
No |
| weighted_xmean | Takes the weighted mean over the last at risk intervals, either by person-years or by a column selected using BY | xmean BY column |
No |