CODEWITHSUNDEEP
rdplyrrowlinear-regression
tcvdb1992
tcvdb1992

Reputation: 455

Performing a simple linear regression on every 2 rows on data in a long format in R

I have repeated measurements data in a long format in R, where every row corresponds to a measurement on a continuous outcome:

library(dplyr)
library(magrittr)

mydata <- structure(list(ID = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 
3L, 3L, 3L, 3L, 3L, 4L, 4L, 5L, 5L, 5L, 6L, 6L, 6L, 6L, 6L, 7L, 
7L, 7L, 7L, 7L, 8L, 8L, 9L, 9L, 10L, 10L, 11L, 11L, 11L, 11L, 
12L, 12L, 13L, 13L, 14L, 14L, 14L, 14L, 14L, 15L, 15L, 16L, 16L, 
17L, 17L, 17L, 17L, 17L, 18L, 18L, 19L, 19L, 20L, 20L, 21L, 21L, 
22L, 22L, 22L, 22L, 22L, 23L, 23L, 24L, 24L, 24L, 24L), .Label = c("2", 
"3", "4", "7", "8", "13", "14", "20", "21", "22", "24", "25", 
"27", "29", "30", "31", "34", "36", "37", "38", "39", "40", "48", 
"49", "50", "51", "52", "54", "58", "60", "61", "65", "74", "75", 
"76", "77", "80", "81", "82", "83", "84", "86", "87", "88", "92", 
"94", "95", "96", "103", "104", "105", "114", "115", "116", "117", 
"119", "125", "126", "127", "132", "134", "135", "137", "138", 
"141", "142", "145", "152", "153", "154", "157", "159", "160", 
"162", "164", "165", "171", "172", "179", "180", "184", "185", 
"189", "194", "195", "197", "198", "202", "203", "205", "209", 
"213", "221", "253", "255", "258", "262", "271", "273", "277", 
"279", "310", "315", "320"), class = "factor"), date_measurement = structure(c(15923, 
16122, 16715, 16902, 17086, 18003, 16150, 16841, 16421, 16764, 
16951, 17135, 18011, 16622, 18247, 16582, 16752, 18045, 16729, 
16862, 17042, 17226, 18102, 16568, 16736, 16916, 17100, 18040, 
16743, 16841, 16589, 16729, 16526, 16729, 16619, 16862, 17042, 
17226, 16407, 18437, 16512, 16953, 16457, 16946, 17112, 17310, 
17989, 16573, 16841, 15923, 16752, 16505, 16729, 16909, 17107, 
18038, 16540, 16743, 15951, 16122, 16624, 18202, 16623, 18221, 
16694, 16715, 16902, 17086, 18037, 16451, 16743, 16421, 16736, 
16909, 17100), class = "Date"), ct_count = c(1L, 2L, 3L, 4L, 
5L, 6L, 1L, 2L, 1L, 2L, 3L, 4L, 5L, 1L, 2L, 1L, 2L, 3L, 1L, 2L, 
3L, 4L, 5L, 1L, 2L, 3L, 4L, 5L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 
3L, 4L, 1L, 2L, 1L, 2L, 1L, 2L, 3L, 4L, 5L, 1L, 2L, 1L, 2L, 1L, 
2L, 3L, 4L, 5L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 3L, 4L, 
5L, 1L, 2L, 1L, 2L, 3L, 4L), age = c(56.6, 57.1, 58.8, 59.3, 
59.8, 62.3, 43.2, 45.1, 52, 52.9, 53.4, 53.9, 56.3, 58.5, 63, 
57.4, 57.9, 61.4, 57.8, 58.2, 58.7, 59.2, 61.6, 52.4, 52.8, 53.3, 
53.8, 56.4, 70.8, 71.1, 61.4, 61.8, 59.2, 59.8, 61.5, 62.2, 62.7, 
63.2, 48.9, 54.5, 54.2, 55.4, 50.1, 51.4, 51.8, 52.4, 54.3, 55.4, 
56.1, 48.6, 50.9, 64.2, 64.8, 65.3, 65.8, 68.4, 68.3, 68.8, 66.7, 
67.1, 60.5, 64.8, 56.5, 60.9, 62.7, 62.8, 63.3, 63.8, 66.4, 49, 
49.8, 61, 61.8, 62.3, 62.8), continuous_outcome = c(1636.4, 544.1, 
1408, 1594.7, 1719.4, 2345.9, 115.3, 226, 2678.2, 3451.6, 3702.7, 
3632.7, 5805, 155.2, 1095, 992.2, 296.6, 2020.4, 3708.6, 2710.7, 
2934.2, 3080.4, 4489.7, 3459.4, 4965.3, 5553.1, 5037.8, 7315.7, 
29980.8, 35407.5, 2263.2, 2060.6, 3220.7, 4467.1, 5902.3, 6407.2, 
5947.1, 6271.6, 306, 689.3, 1430.6, 1672.1, 9.9, 58.7, 69.9, 
125.3, 39.5, 3842.5, 5136.3, 216.6, 332.4, 5719.3, 5386, 5490.7, 
5268.2, 6166.7, 12520.6, 12981.8, 2896.1, 2976.8, 5495.6, 6470.6, 
4235.5, 7603.5, 3887, 3344.5, 2885.7, 3324.1, 6401, 1942.2, 2000.9, 
2401.7, 2231.5, 2749.7, 2741.7)), row.names = c(NA, -75L), class = c("tbl_df", 
"tbl", "data.frame"))

To further explore the longitudinal progression of my continuous outcome over age I want to perform a simple linear regression (formula: continuous outcome ~ age) per ID and per 2 measurements/rows, and save the beta values in the dataset.

For example, the first person with ID=2 has 6 measurements on the continuous outcome, and would thus have 5 regression analyses/beta-values: (1) a regression (continuous outcome ~ age) of the first to rows, (2) a regression of row 2 and 3, (3) a regression of rows 3 and 4, etc.

I've tried to accomplish this with case_when as follows:

mydata <- 
  mydata %>% 
  group_by(ID) %>% 
  mutate(
    beta=
      case_when(
        (measurement_count==1 | measurement_count==2) ~ lm(continuous_outcome ~ age)$coef[2])) %>% 
  ungroup()

This produces a beta-value, but not the correct one. For example if I calculate the beta-values of the aformentioned regression analysis for the first two rows of ID number 2 I get (544.1-1636.4)/(57.1-56.6)=2184.6.

Any help?

Upvotes: 0

Views: 52

Answers (1)

r2evans
r2evans

Reputation: 160782

mydata %>%
  group_by(ID) %>%
  summarize(beta = zoo::rollapply(
    cbind(continuous_outcome, age), 2,
    FUN = function(z) lm(z[,1] ~ z[,2])$coefficients[2],
    by.column = FALSE)
  )
# # A tibble: 51 x 2
# # Groups:   ID [24]
#    ID       beta
#    <fct>   <dbl>
#  1 2     -2185. 
#  2 2       508. 
#  3 2       373. 
#  4 2       249. 
#  5 2       251. 
#  6 3        58.3
#  7 4       859. 
#  8 4       502. 
#  9 4      -140. 
# 10 4       905. 
# # ... with 41 more rows

If you want/need more columns, we can modify this to roll on row indices instead of just two columns:

mydata %>%
  group_by(ID) %>%
  summarize(beta = zoo::rollapply(
    row_number(), 2,
    FUN = function(ri) lm(continuous_outcome ~ age, data  =.[ri,])$coefficients[2],
    by.column = FALSE)
  )

Upvotes: 2

Related Questions