Occurence of certain date is more than x times, get next available date

1
votes

I have a dataframe containing 15 columns, 1 column is the participant ID and the 14 columns of possible appointment dates per participant (excluding holidays and weekends):

  Included.Participant         V1         V2         V3         V4         V5   V6   V7         V8         V9        V10        V11        V12  V13  V14
1                    1 2021-03-22 2021-03-23 2021-03-24 2021-03-25 2021-03-26 <NA> <NA> 2021-03-29 2021-03-30 2021-03-31 2021-04-01 2021-04-02 <NA> <NA>
2                    2 2021-03-22 2021-03-23 2021-03-24 2021-03-25 2021-03-26 <NA> <NA> 2021-03-29 2021-03-30 2021-03-31 2021-04-01 2021-04-02 <NA> <NA>
3                    3 2021-03-22 2021-03-23 2021-03-24 2021-03-25 2021-03-26 <NA> <NA> 2021-03-29 2021-03-30 2021-03-31 2021-04-01 2021-04-02 <NA> <NA>
4                    4 2021-03-22 2021-03-23 2021-03-24 2021-03-25 2021-03-26 <NA> <NA> 2021-03-29 2021-03-30 2021-03-31 2021-04-01 2021-04-02 <NA> <NA>
5                    5 2021-03-22 2021-03-23 2021-03-24 2021-03-25 2021-03-26 <NA> <NA> 2021-03-29 2021-03-30 2021-03-31 2021-04-01 2021-04-02 <NA> <NA>
6                    6 2021-03-22 2021-03-23 2021-03-24 2021-03-25 2021-03-26 <NA> <NA> 2021-03-29 2021-03-30 2021-03-31 2021-04-01 2021-04-02 <NA> <NA>

Per date, 3 participants can be added. If a maximum of 3 has been reached, it should move to the next available date for the fourth participant. So in this example the desired output would be:

      Included.Participant         V1
1                    1 2021-03-22
2                    2 2021-03-22
3                    3 2021-03-22
4                    4 2021-03-23
5                    5 2021-03-23
6                    6 2021-03-23

If there is no possible date left then column V1 can stay empty.

I cannot seem to figure out how to get the desired output.. I really hope you can help out

Many thanks!

dput:

structure(list(Included.y = c(1L, 2L, 3L, 4L, 7L, 8L, 9L, 10L, 
11L, 12L, 13L, 14L, 15L, 16L, 17L, 18L, 19L, 20L, 21L, 22L), 
    V1 = structure(c(18870, NA, 18848, NA, NA, NA, NA, NA, 18806, 
    18799, 18835, 18841, NA, NA, 18912, 18954, NA, 18842, NA, 
    NA), class = "Date"), V2 = structure(c(18871, NA, 18849, 
    NA, NA, NA, NA, 18876, 18807, 18800, 18836, 18842, NA, NA, 
    18913, 18955, NA, 18843, NA, NA), class = "Date"), V3 = structure(c(18872, 
    18904, 18850, 18897, 18967, NA, 18883, 18877, 18808, 18801, 
    18837, 18843, 18890, NA, 18914, 18956, 18953, 18844, NA, 
    18869), class = "Date"), V4 = structure(c(NA, 18905, 18851, 
    18898, 18968, 18953, 18884, 18878, 18809, 18802, NA, 18844, 
    18891, 18967, NA, NA, 18954, NA, 18925, 18870), class = "Date"), 
    V5 = structure(c(NA, 18906, NA, 18899, 18969, 18954, 18885, 
    18879, NA, NA, NA, NA, 18892, 18968, NA, NA, 18955, NA, 18926, 
    18871), class = "Date"), V6 = structure(c(NA, 18907, NA, 
    18900, 18970, 18955, 18886, NA, NA, NA, NA, NA, 18893, 18969, 
    NA, NA, 18956, NA, 18927, 18872), class = "Date"), V7 = structure(c(18876, 
    NA, NA, NA, NA, 18956, NA, NA, NA, NA, 18841, NA, NA, 18970, 
    18918, 18960, NA, 18848, 18928, NA), class = "Date"), V8 = structure(c(18877, 
    NA, 18855, NA, NA, NA, NA, NA, 18813, 18806, 18842, 18848, 
    NA, NA, 18919, 18961, NA, 18849, NA, NA), class = "Date"), 
    V9 = structure(c(18878, NA, 18856, NA, NA, NA, NA, 18883, 
    18814, 18807, 18843, 18849, NA, NA, 18920, 18962, NA, 18850, 
    NA, NA), class = "Date"), V10 = structure(c(18879, 18911, 
    18857, 18904, 18974, NA, 18890, 18884, 18815, 18808, 18844, 
    18850, 18897, NA, 18921, 18963, 18960, 18851, NA, 18876), class = "Date"), 
    V11 = structure(c(NA, 18912, 18858, 18905, 18975, 18960, 
    18891, 18885, 18816, 18809, NA, 18851, 18898, 18974, NA, 
    NA, 18961, NA, 18932, 18877), class = "Date"), V12 = structure(c(NA, 
    18913, NA, 18906, 18976, 18961, 18892, 18886, NA, NA, NA, 
    NA, 18899, 18975, NA, NA, 18962, NA, 18933, 18878), class = "Date"), 
    V13 = structure(c(NA, 18914, NA, 18907, 18977, 18962, 18893, 
    NA, NA, NA, NA, NA, 18900, 18976, NA, NA, 18963, NA, 18934, 
    18879), class = "Date"), V14 = structure(c(18883, NA, NA, 
    NA, NA, 18963, NA, NA, NA, NA, 18848, NA, NA, 18977, 18925, 
    18967, NA, 18855, 18935, NA), class = "Date")), row.names = c(NA, 
20L), class = "data.frame")
1
rdataframe
It would really help test and verify a solution if you could paste your data as objects into the question: use dput(your_dataframe). - Peter
Thank you for the tip, I edited my question with the dput - Debbie Oomen
Once a date is filled with three participants say 2021-02-23 in V1 is the same date: 2021-02-23 in V2 still available for other participants? - Peter
See my comment underneath your answer. Hope this clears this up - Debbie Oomen
Hi, these are not dates based on preferencees. These are all the possibilities for appointments for that participant so we do not have to consider which dates are in which column number. Hope this helps - Debbie Oomen

1 Answers

1
votes

Updated code following clarification allocation of participants to same dates in different columns:

library(dplyr)
library(tidyr)
library(tibble)


df1 <- 
  df %>% 
  pivot_longer(-Included.Participant) %>% 
  select(-Included.Participant) %>% 
  mutate(name = factor(name, levels = paste0("V", 1:14), ordered = TRUE))%>% 
  group_by(value) %>%
  arrange(value, name) %>% 
  slice_head(n = 3)%>% 
  rowid_to_column(var = "Included.Participant") %>% 
  filter(Included.Participant <= 20) %>% 
  pivot_wider(names_from = name, values_from = value)
  • output
head(df1, 10)
#> # A tibble: 10 x 5
#>    Included.Participant V1         V2         V3         V4        
#>                   <int> <date>     <date>     <date>     <date>    
#>  1                    1 2021-03-22 NA         NA         NA        
#>  2                    2 2021-03-22 NA         NA         NA        
#>  3                    3 2021-03-22 NA         NA         NA        
#>  4                    4 2021-03-23 NA         NA         NA        
#>  5                    5 2021-03-23 NA         NA         NA        
#>  6                    6 2021-03-23 NA         NA         NA        
#>  7                    7 NA         2021-03-24 NA         NA        
#>  8                    8 NA         2021-03-24 NA         NA        
#>  9                    9 NA         2021-03-24 NA         NA        
#> 10                   10 NA         NA         2021-03-25 NA