R: (un)reduce dataframe

user_7707288 0
Viewed 70

I have the following fake dataset. For a period of time every day (dates) the status (status) of all elements (id) was logged.

df <- data.frame( id = c(1, 1, 1, 1, 1,  2, 2, 2, 2, 2,  3, 3, 3, 3, 3,  4, 4, 4, 4, 4),
                  dates = c("2021-01-01",
                           "2021-01-02",
                           "2021-01-03",
                           "2021-01-04",
                           "2021-01-05",
                           
                           "2021-01-01",
                           "2021-01-02",
                           "2021-01-03",
                           "2021-01-04",
                           "2021-01-05",
                           
                           "2021-01-01",
                           "2021-01-02",
                           "2021-01-03",
                           "2021-01-04",
                           "2021-01-05",
                           
                           "2021-01-01",
                           "2021-01-02",
                           "2021-01-03",
                           "2021-01-04",
                           "2021-01-05"),
                 
                 status = c("A", "A", "A", "B", "C",
                            "A", "A", "B", "C", "C",
                            "A", "B", "C", "D", "E",
                            "A", "B", "B", "B", "B")
                 ) 

> df
   id      dates status
1   1 2021-01-01      A
2   1 2021-01-02      A
3   1 2021-01-03      A
4   1 2021-01-04      B
5   1 2021-01-05      C
6   2 2021-01-01      A
7   2 2021-01-02      A
8   2 2021-01-03      B
9   2 2021-01-04      C
10  2 2021-01-05      C
11  3 2021-01-01      A
12  3 2021-01-02      B
13  3 2021-01-03      C
14  3 2021-01-04      D
15  3 2021-01-05      E
16  4 2021-01-01      A
17  4 2021-01-02      B
18  4 2021-01-03      B
19  4 2021-01-04      B
20  4 2021-01-05      B

Unfortunately, in order to save space that dataframe was reduced: If on two subsequent days the status was the same, the second entry was dropped. It is assumed, that the status stays the same until it changes again, so the actual dataset looks like this:

> df %>% group_by(id) %>%
+   mutate(dupl = duplicated(status, 2)) %>%
+   ungroup() %>%
+   filter(dupl == FALSE) %>%
+   select(-dupl)
# A tibble: 13 x 3
      id dates      status
   <dbl> <chr>      <chr> 
 1     1 2021-01-01 A     
 2     1 2021-01-04 B     
 3     1 2021-01-05 C     
 4     2 2021-01-01 A     
 5     2 2021-01-03 B     
 6     2 2021-01-04 C     
 7     3 2021-01-01 A     
 8     3 2021-01-02 B     
 9     3 2021-01-03 C     
10     3 2021-01-04 D     
11     3 2021-01-05 E     
12     4 2021-01-01 A     
13     4 2021-01-02 B

My question now is: How can I go back to that first (complete) version of the dataset again? The time period is always the same for all ids (2021-01-01 to 2021-01-05)

tidyr dplyr tidyverse r
5 Answers
library(tidyverse)

# the reduced version can be created like this instead
df_reduced <- df %>% 
  mutate(dates = lubridate::ymd(dates)) %>% 
  distinct(id, status, .keep_all = TRUE)

For problems like this i would look at the functions in tidyr related to missing values. We can generate the full sequence of id/dates combinations with expand, and then fill in the NA values with fill(status, .direction = "down").

df_reduced %>% 
  expand(id, dates = full_seq(dates, 1)) %>% 
  left_join(df_reduced) %>% 
  group_by(id) %>% 
  fill(status, .direction = "down")

#> Joining, by = c("id", "dates")
#> # A tibble: 20 x 3
#> # Groups:   id [4]
#>       id dates      status
#>    <dbl> <chr>      <chr> 
#>  1     1 2021-01-01 A     
#>  2     1 2021-01-02 A     
#>  3     1 2021-01-03 A     
#>  4     1 2021-01-04 B     
#>  5     1 2021-01-05 C     
#>  6     2 2021-01-01 A     
#>  7     2 2021-01-02 A     
#>  8     2 2021-01-03 B     
#>  9     2 2021-01-04 C     
#> 10     2 2021-01-05 C     
#> 11     3 2021-01-01 A     
#> 12     3 2021-01-02 B     
#> 13     3 2021-01-03 C     
#> 14     3 2021-01-04 D     
#> 15     3 2021-01-05 E     
#> 16     4 2021-01-01 A     
#> 17     4 2021-01-02 B     
#> 18     4 2021-01-03 B     
#> 19     4 2021-01-04 B     
#> 20     4 2021-01-05 B

Created on 2021-07-06 by the reprex package (v1.0.0)

user_77787870

Understand that you are looking for a tidyverse solution. Just adding a data.table approach for reference:

dts <- seq(as.IDate("2021-01-01"), as.IDate("2021-01-05"), by="1 day")
DT[CJ(id=id, dates=dts, unique=TRUE), on=.NATURAL, roll=TRUE]

data:

library(data.table)
DT <- fread("
id dates      status
1 2021-01-01 A     
1 2021-01-04 B     
1 2021-01-05 C     
2 2021-01-01 A     
2 2021-01-03 B     
2 2021-01-04 C     
3 2021-01-01 A     
3 2021-01-02 B     
3 2021-01-03 C     
3 2021-01-04 D     
3 2021-01-05 E     
4 2021-01-01 A     
4 2021-01-02 B")
user_19894800

Here's another approach

seq_upto <- function(a, b) {
  head(seq.Date(a, b, by="1 day"), -1)
}

df2_collapsed %>% 
  group_by(id) %>% 
  mutate(start = lubridate::ymd(dates)) %>% 
  mutate(end = lead(start, default=as.Date("2021-01-05") + lubridate::days(1))) %>% 
  rowwise() %>% 
  mutate(dates = list(seq_upto(start, end))) %>% 
  ungroup %>% 
  select(-start, -end) %>% 
  tidyr::unnest(dates)

Basically you we create dates ranges for each of the IDs and their status. We then create sequences based on those lists and unlist the sequences to expand the rows.

user_23720640

This can be easily accomplished in 2 steps

df %>% 
  complete(nesting(id), dates = seq.Date(min(.$dates), max(.$dates), 1)) %>%
  fill(status)

# A tibble: 20 x 3
      id dates      status
   <dbl> <date>     <chr> 
 1     1 2021-01-01 A     
 2     1 2021-01-02 A     
 3     1 2021-01-03 A     
 4     1 2021-01-04 B     
 5     1 2021-01-05 C     
 6     2 2021-01-01 A     
 7     2 2021-01-02 A     
 8     2 2021-01-03 B     
 9     2 2021-01-04 C     
10     2 2021-01-05 C     
11     3 2021-01-01 A     
12     3 2021-01-02 B     
13     3 2021-01-03 C     
14     3 2021-01-04 D     
15     3 2021-01-05 E     
16     4 2021-01-01 A     
17     4 2021-01-02 B     
18     4 2021-01-03 B     
19     4 2021-01-04 B     
20     4 2021-01-05 B
user_28848590 
df_reduce %>% 
  mutate(dates = ymd(dates)) %>% 
  complete(dates = seq(from = as.Date("2021-01-01"), by = "day", length.out = 5), nesting(id)) %>% 
  arrange(id) %>% 
  group_by(id) %>% 
  fill(status, .direction = "downup") %>% 
  ungroup()

# A tibble: 20 x 3
   dates         id status
   <date>     <dbl> <chr> 
 1 2021-01-01     1 A     
 2 2021-01-02     1 A     
 3 2021-01-03     1 A     
 4 2021-01-04     1 B     
 5 2021-01-05     1 C     
 6 2021-01-01     2 A     
 7 2021-01-02     2 A     
 8 2021-01-03     2 B     
 9 2021-01-04     2 C     
10 2021-01-05     2 C     
11 2021-01-01     3 A     
12 2021-01-02     3 B     
13 2021-01-03     3 C     
14 2021-01-04     3 D     
15 2021-01-05     3 E     
16 2021-01-01     4 A     
17 2021-01-02     4 B     
18 2021-01-03     4 B     
19 2021-01-04     4 B     
20 2021-01-05     4 B
user_120254830
Related