creating days spent variable

General
#1

i have five columns. person_id, in_date, out_date, week_rounded (rounded to the first sunday of each week), and house. it is a long dataset, and I provided a subset below. the in_date and out_date are the same for each visit spell at someone's house. I am quite stuck..

how do i make a new variable that calculates total days spent for a given week at someone's house? (note the weeks begin on Sunday)

Reproducible data example:

structure(list(person_id = c("A", "A", "A", "A", "A", "A", "A",
"A", "A"), in_date = structure(c(1635984000, 1635984000, 1635984000,
1635984000, 1658534400, 1658534400, 1658966400, 1658966400, 1658966400
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), out_date = structure(c(1658534400,
1658534400, 1658534400, 1658534400, 1658966400, 1658966400, 1660089600,
1660089600, 1660089600), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
week_rounded = structure(c(1656201600, 1656806400, 1657411200,
1658016000, 1658016000, 1658620800, 1658620800, 1659225600,
1659830400), class = c("POSIXct", "POSIXt"), tzone = "UTC"),
house = c("Janelle", "Janelle", "Janelle", "Janelle", "Lisa",
"Lisa", "Bob", "Bob", "Bob"), days_spent_in_week = c(NA,
NA, NA, NA, NA, NA, NA, NA, NA)), class = c("tbl_df", "tbl",
"data.frame"), row.names = c(NA, -9L))

please help me fill the days_spent_in_week variable using tidyverse. I am very stuck!

#2

Hi @help, maybe you need something like that:

data <- structure(list(
  person_id = c("A", "A", "A", "A", "A", "A", "A", "A", "A"),
  in_date = structure(c(1635984000, 1635984000, 1635984000, 1635984000, 1658534400, 1658534400, 1658966400, 1658966400, 1658966400),
             class = c("POSIXct", "POSIXt"), tzone = "UTC"),
  out_date = structure(c(1658534400, 1658534400, 1658534400, 1658534400, 1658966400, 1658966400, 1660089600, 1660089600, 1660089600),
           class = c("POSIXct", "POSIXt"), tzone = "UTC"),
  week_rounded = structure(c(1656201600, 1656806400, 1657411200, 1658016000, 1658016000, 1658620800, 1658620800, 1659225600, 1659830400),
          class = c("POSIXct", "POSIXt"), tzone = "UTC"),
  house = c("Janelle", "Janelle", "Janelle", "Janelle", "Lisa", "Lisa", "Bob", "Bob", "Bob"),
  days_spent_in_week = c(NA, NA, NA, NA, NA, NA, NA, NA, NA)
), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, -9L))

data_1 <- data %>%
  group_by(person_id, house, week_rounded) %>%
  mutate(days_spent_in_week = as.numeric(difftime(out_date, in_date, units = "days")) + 1) # add 1 to include both the start and end dates

data_1 
# person_id in_date             out_date            week_rounded        house   days_spent_in_week
# <chr>     <dttm>              <dttm>              <dttm>              <chr>                <dbl>
# 1 A         2021-11-04 00:00:00 2022-07-23 00:00:00 2022-06-26 00:00:00 Janelle                262
# 2 A         2021-11-04 00:00:00 2022-07-23 00:00:00 2022-07-03 00:00:00 Janelle                262
# 3 A         2021-11-04 00:00:00 2022-07-23 00:00:00 2022-07-10 00:00:00 Janelle                262
# 4 A         2021-11-04 00:00:00 2022-07-23 00:00:00 2022-07-17 00:00:00 Janelle                262
# 5 A         2022-07-23 00:00:00 2022-07-28 00:00:00 2022-07-17 00:00:00 Lisa                     6
# 6 A         2022-07-23 00:00:00 2022-07-28 00:00:00 2022-07-24 00:00:00 Lisa                     6
# 7 A         2022-07-28 00:00:00 2022-08-10 00:00:00 2022-07-24 00:00:00 Bob                     14
# 8 A         2022-07-28 00:00:00 2022-08-10 00:00:00 2022-07-31 00:00:00 Bob                     14
# 9 A         2022-07-28 00:00:00 2022-08-10 00:00:00 2022-08-07 00:00:00 Bob                     14

# Created on 2023-08-18 by the reprex package (v2.0.1)