Calculating average measurement in utero (inspired by a question on RLadies Slack)

in-utero-average.R

library(tidyverse)
packageVersion("dplyr")
library(lubridate)
library(glue)


set.seed(1234)


children <- tribble(
  ~id, ~birth_year, ~birth_month, ~birth_day, ~locality, 
  1, 2000, 11, 1, "a",
  2, 2001,  3, 4, "b"
)

children

vals <- tibble(
  date = rep(seq(ymd("1999-01-01"), ymd("2001-12-01"), by = "month"), 2),
  value = rnorm(36*2),
  locality = c(rep("a", 36), rep("b", 36)),
  month_year = glue("{year(date)}-{month(date)}") # create a character string for year-month combo
)

vals

children %>%
  mutate(
    iu_end_date = ymd(glue("{birth_year}-{birth_month}-{birth_day}")), # birth date
    iu_begin_date = iu_end_date - weeks(40) # assuming 40 weeks in utero
  ) %>%
  select(-starts_with("birth_")) %>% # remove columns that are no longer useful
  pivot_longer(cols = -c(id, locality), names_to = "date_type", values_to = "date") %>% # one row per date
  group_by(id) %>%
  complete(date = seq.Date(min(date), max(date), by = "month")) %>% # add a row for each month between io begin and end
  arrange(id, date) %>%
  select(-date_type) %>% # remove columns that are no longer useful
  fill(locality) %>%
  mutate(month_year = glue("{year(date)}-{month(date)}")) %>% # create a character string for year-month combo
  left_join(vals, by = c("month_year", "locality")) %>% # join by year-month and locality
  summarise(mean_value = mean(value), .groups = "drop")