Keep original ID column while creating data frame with date availabilities

Question

Via you guys I already got a great code for getting a dataframe of available dates based on the amount of participants per date: Occurence of certain date is more than x times, get next available date

However, I just noticed that within this dataframe, I am not keeping the original IDs of my participants but replacing them with row names in ascending order. However, I still need the IDs to check which participants belongs to which date. This is the code used to create this dataframe:

df1 <- 
  T0range %>% 
  pivot_longer(-Included.y) %>% 
  select(-Included.y) %>% 
  mutate(name = factor(name, levels = paste0("V", 1:14), ordered = TRUE))%>% 
  group_by(value) %>%
  arrange(value, name) %>% 
  slice_head(n = 12)%>% 
  rowid_to_column(var = "Included.y") %>% 
  filter(Included.y <= length(T0range$Included.y)) %>% 
  pivot_wider(names_from = name, values_from = value)

T0range looks like this:

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")

head(T0range)
  Included.y         V1         V2         V3         V4         V5         V6   V7         V8         V9        V10        V11        V12        V13  V14
1          3       <NA>       <NA> 2021-11-29 2021-11-30 2021-12-01 2021-12-02 <NA>       <NA>       <NA> 2021-12-06 2021-12-07 2021-12-08 2021-12-09 <NA>
2          4       <NA> 2021-09-06 2021-09-07 2021-09-08 2021-09-09       <NA> <NA>       <NA> 2021-09-13 2021-09-14 2021-09-15 2021-09-16       <NA> <NA>
3          5 2021-09-13 2021-09-14 2021-09-15 2021-09-16       <NA>       <NA> <NA> 2021-09-20 2021-09-21 2021-09-22 2021-09-23       <NA>       <NA> <NA>
4          6 2021-11-15 2021-11-16 2021-11-17 2021-11-18       <NA>       <NA> <NA> 2021-11-22 2021-11-23 2021-11-24 2021-11-25       <NA>       <NA> <NA>
5          8 2021-08-02 2021-08-03 2021-08-04 2021-08-05       <NA>       <NA> <NA> 2021-08-09 2021-08-10 2021-08-11 2021-08-12       <NA>       <NA> <NA>
6          9       <NA> 2021-11-08 2021-11-09 2021-11-10 2021-11-11       <NA> <NA>       <NA> 2021-11-15 2021-11-16 2021-11-17 2021-11-18       <NA> <NA>

I cannot seem to find a way to save the 'Included.y' column in the df1 and I think that it would be relatively easy... so i really hope that you can help me out!

Thanks so much in advance

You should use dput(head(T0range)) to provide the code to create the sample data head(T0range), which will be more convient for us to help you. — Peace Wang
Hi Peace Wang, i added the dput (for the whole df). Hope this helps! — Debbie Oomen

Ronak Shah Ronak Shah · Accepted Answer · 2021-04-13T13:37:25

You can try :

 library(dplyr)
 library(tidyr)
 
T0Range %>% 
  pivot_longer(-Included.y) %>% 
  mutate(name = factor(name, levels = paste0("V", 1:14), ordered = TRUE))%>% 
  arrange(value, name) %>% 
  group_by(value) %>%
  slice_head(n = 12) %>% 
  pivot_wider(names_from = name, values_from = value) -> df1

df1

Keep original ID column while creating data frame with date availabilities

1 Answers