# Calculate duration in a complex table_1

1 view

I have created the following data frame:

df <- data.frame("name" = c("jack", "william", "david", "john"),

"01-Jan-19" = c(NA,"A", NA,"A"),

"01-Feb-19" = c("A","A",NA,"A"),

"01-Mar-19" = c("S","A","A","A"),

"01-Apr-19" = c("A","A","A","S"),

"01-May-19" = c(NA,"A","A","A"),

"01-Jun-19" = c("A","S","A","S"),

"01-Jul-19" = c("A","S","A","S"),

"01-Aug-19" = c(NA,"S","A","A"),

"01-Sep-19" = c(NA,"S","A","S"),

"01-Oct-19" = c("S","S","A","S"),

"01-Nov-19" = c("S","S",NA,"S"),

"01-Dec-19" = c("S","S","S",NA),

"01-Jan-20" = c("S","M","A","M"),

"01-Feb-20" = c("M","M","M","M"))

For estimating the duration for each person between the first A to the last A, I was able to get that with the following piece of code:

duration <- df %>%

tidyr::pivot_longer(cols = -name, names_to = 'person', values_drop_na = TRUE) %>%

dplyr::mutate(person = dmy(sub('X', '', person))) %>%

group_by(name) %>%

dplyr::summarise(avg_duration = person[max(which(value == 'A'))] - person[min(which(value == 'A'))])

But I want to find the periods in between the two As, and also how can I eliminate the period that is with other values (anything that is not A, e.g. S, NA)?

by (108k points)

I think you need two implications for a period. Let's say, for instance, a person has the series: A, S, A, do they have 0, 1 or 2 periods with A? and what about A, S, A, A, S, A?

duration <- df %>%

tidyr::pivot_longer(cols = -name, names_to = 'date') %>%

dplyr::mutate(date = lubridate::dmy(sub('X', '', date))) %>%

group_by(name) %>%

dplyr::arrange(name, date) %>%

dplyr::mutate(duration = c(diff(date), 0)) %>%

dplyr::group_by(name, value) %>%

dplyr::summarise(summed_duration = sum(duration))

# A tibble: 15 x 3

# Groups:   name [4]

name    value summed_duration

<chr>   <chr> <drtn>

1 david   A     276 days

2 david   M       0 days

3 david   S      31 days

4 david   NA     89 days

5 jack    A     119 days

6 jack    M       0 days

7 jack    S     154 days

8 jack    NA    123 days

9 john    A     152 days

10 john    M      31 days

11 john    S     182 days

12 john    NA     31 days

13 william A     151 days

14 william M      31 days

15 william S     214 days

If you are a beginner and want to know more about R, then do check out the R programming tutorial