Início / coding / Perguntas / 77746012
Accepted
Shan Cheung
Asked: 2024-01-02 20:12:39 +0800 CST

Agrupar e contar data em R

  • 772

Tenho um conjunto de dados que descreve o número de dias (BedDays) que cada paciente permanece internado em um hospital:

my_df<-data.frame(RecordID=c("1","2","3","4","5","6","7","8","9","10"),
              StartDate=c("2020-03-02","2020-03-05"
                          ,"2020-03-08","2020-03-11"
                          ,"2020-03-14","2020-03-17"
                          ,"2020-03-20","2020-03-23"
                          ,"2020-03-26","2020-03-29"),
              BedDays=c(12,3,2,11,1,10,18,10,3,6))

E gostaria de contar o número de leitos usados ​​a cada dia. Eu gostaria que meus resultados fossem:

Resultados esperados

Até agora, só consigo pensar no uso de arrays e loops. Use o array para listar as datas em que cada paciente ficou no hospital e, em seguida, use o loop para examinar os arrays e contar os pacientes no hospital em um determinado dia. Esta será a contagem de leitos utilizados em um determinado dia. Isso parece complicado.

Existe alguma função/pacote inteligente que possa fazer essa transformação facilmente?

4 respostas

  1. Acho que a chave é "expandir" BedDayspara uma sequência de dias e depois contar esses dias.

    library(tidyverse)

my_df %>% 
  mutate(StartDate = as.Date(StartDate)) %>% 
  reframe(duration = seq(StartDate, StartDate + BedDays - 1, by = 1), .by = RecordID) %>% 
  count(duration)

     duration n
1  2020-03-02 1
2  2020-03-03 1
3  2020-03-04 1
4  2020-03-05 2
5  2020-03-06 2
6  2020-03-07 2
7  2020-03-08 2
8  2020-03-09 2
9  2020-03-10 1
10 2020-03-11 2
11 2020-03-12 2
12 2020-03-13 2
13 2020-03-14 2
14 2020-03-15 1
15 2020-03-16 1
16 2020-03-17 2
17 2020-03-18 2
18 2020-03-19 2
19 2020-03-20 3
20 2020-03-21 3
21 2020-03-22 2
22 2020-03-23 3
23 2020-03-24 3
24 2020-03-25 3
25 2020-03-26 4
26 2020-03-27 3
27 2020-03-28 3
28 2020-03-29 3
29 2020-03-30 3
30 2020-03-31 3
31 2020-04-01 3
32 2020-04-02 2
33 2020-04-03 2
34 2020-04-04 1
35 2020-04-05 1
36 2020-04-06 1
    • 4

  2. O código abaixo começa dividindo os dados por data, depois cria data.frames com tantas linhas quanto BedDays, reúne todos os dados temporários e agrega os dados para ter contagens de leitos por data.

    my_df<-data.frame(RecordID=c("1","2","3","4","5","6","7","8","9","10"),
                  StartDate=c("2020-03-02","2020-03-05"
                              ,"2020-03-08","2020-03-11"
                              ,"2020-03-14","2020-03-17"
                              ,"2020-03-20","2020-03-23"
                              ,"2020-03-26","2020-03-29"),
                  BedDays=c(12,3,2,11,1,10,18,10,3,6))

my_df$StartDate <- as.Date(my_df$StartDate)

my_df |>
  split(my_df$StartDate) |>
  lapply(\(x) {
    StartDate <- x$StartDate + seq.int(x$BedDays) - 1L
    data.frame(StartDate, NumBeds = 1L)
  }) |> 
  do.call(rbind, args = _) |>
  aggregate(NumBeds ~ StartDate, data = _, sum)
#>     StartDate NumBeds
#> 1  2020-03-02       1
#> 2  2020-03-03       1
#> 3  2020-03-04       1
#> 4  2020-03-05       2
#> 5  2020-03-06       2
#> 6  2020-03-07       2
#> 7  2020-03-08       2
#> 8  2020-03-09       2
#> 9  2020-03-10       1
#> 10 2020-03-11       2
#> 11 2020-03-12       2
#> 12 2020-03-13       2
#> 13 2020-03-14       2
#> 14 2020-03-15       1
#> 15 2020-03-16       1
#> 16 2020-03-17       2
#> 17 2020-03-18       2
#> 18 2020-03-19       2
#> 19 2020-03-20       3
#> 20 2020-03-21       3
#> 21 2020-03-22       2
#> 22 2020-03-23       3
#> 23 2020-03-24       3
#> 24 2020-03-25       3
#> 25 2020-03-26       4
#> 26 2020-03-27       3
#> 27 2020-03-28       3
#> 28 2020-03-29       3
#> 29 2020-03-30       3
#> 30 2020-03-31       3
#> 31 2020-04-01       3
#> 32 2020-04-02       2
#> 33 2020-04-03       2
#> 34 2020-04-04       1
#> 35 2020-04-05       1
#> 36 2020-04-06       1

    Criado em 02/01/2024 com reprex v2.0.2

    • 3

  3. Uma data.tableopção

    > library(data.table)

> setDT(my_df)[, .(Date = as.IDate(StartDate) + seq(BedDays)-1), RecordID][, .N, Date]
          Date N
 1: 2020-03-02 1
 2: 2020-03-03 1
 3: 2020-03-04 1
 4: 2020-03-05 2
 5: 2020-03-06 2
 6: 2020-03-07 2
 7: 2020-03-08 2
 8: 2020-03-09 2
 9: 2020-03-10 1
10: 2020-03-11 2
11: 2020-03-12 2
12: 2020-03-13 2
13: 2020-03-14 2
14: 2020-03-15 1
15: 2020-03-16 1
16: 2020-03-17 2
17: 2020-03-18 2
18: 2020-03-19 2
19: 2020-03-20 3
20: 2020-03-21 3
21: 2020-03-22 2
22: 2020-03-23 3
23: 2020-03-24 3
24: 2020-03-25 3
25: 2020-03-26 4
26: 2020-03-27 3
27: 2020-03-28 3
28: 2020-03-29 3
29: 2020-03-30 3
30: 2020-03-31 3
31: 2020-04-01 3
32: 2020-04-02 2
33: 2020-04-03 2
34: 2020-04-04 1
35: 2020-04-05 1
36: 2020-04-06 1
          Date N
    • 3
  4. Best Answer

    Aqui está uma combinação com uncounte count:

    library(dplyr)
library(tidyr)

my_df %>% 
  uncount(BedDays) %>% 
  mutate(StartDate = StartDate + (row_number() - 1), .by = RecordID) %>% 
  count(StartDate)

    saída:

    
    StartDate n
1  2020-03-02 1
2  2020-03-03 1
3  2020-03-04 1
4  2020-03-05 2
5  2020-03-06 2
6  2020-03-07 2
7  2020-03-08 2
8  2020-03-09 2
9  2020-03-10 1
10 2020-03-11 2
11 2020-03-12 2
12 2020-03-13 2
13 2020-03-14 2
14 2020-03-15 1
15 2020-03-16 1
16 2020-03-17 2
17 2020-03-18 2
18 2020-03-19 2
19 2020-03-20 3
20 2020-03-21 3
21 2020-03-22 2
22 2020-03-23 3
23 2020-03-24 3
24 2020-03-25 3
25 2020-03-26 4
26 2020-03-27 3
27 2020-03-28 3
28 2020-03-29 3
29 2020-03-30 3
30 2020-03-31 3
31 2020-04-01 3
32 2020-04-02 2
33 2020-04-03 2
34 2020-04-04 1
35 2020-04-05 1
36 2020-04-06 1
    • 2

relate perguntas