I have a large tibble with grouped data. Each group includes 12 rows. The third column of the tibble has to have a values between 1 and 12. Most of the rows have all the rows in column 3 populated, but some do not. For those rows with missing data (NA) I like to use dplyr to insert the correct value. My challenge is that the values in column 3 are not necessarily in numeric order, so I have to find the values that do not already exist in the and then insert accordingly.
I prepared a reprex to demonstrate what I'm trying to describe. In my example group B is missing the last two values in column 3. The only values that don't already exist in column 3 are 4 and 7. Therefore I would like to find a way of determining what values can be inserted and then use mutate to update the NA value to the unused value. This is all part of my effort to meeting the data requirements of mlogit data frames.
library(tidyverse)
df_BEFORE <- tibble( key = c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B"),y=c(2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2), z=c(1,2,3,4,5,6,7,8,9,10,11,12,2,11,3,6,12,8,9,10,1,5,NA,NA))
print(df_BEFORE, n=24)
#> # A tibble: 24 x 3
#> key y z
#> <chr> <dbl> <dbl>
#> 1 A 2 1
#> 2 A 2 2
#> 3 A 2 3
#> 4 A 2 4
#> 5 A 2 5
#> 6 A 2 6
#> 7 A 2 7
#> 8 A 2 8
#> 9 A 2 9
#> 10 A 2 10
#> 11 A 2 11
#> 12 A 2 12
#> 13 B 2 2
#> 14 B 2 11
#> 15 B 2 3
#> 16 B 2 6
#> 17 B 2 12
#> 18 B 2 8
#> 19 B 2 9
#> 20 B 2 10
#> 21 B 2 1
#> 22 B 2 5
#> 23 B 2 NA
#> 24 B 2 NA
df_AFTER <- df_BEFORE %>%
group_by(key)
target_df <- tibble( key = c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B"),y=c(2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2), z=c(1,2,3,4,5,6,7,8,9,10,11,12,2,11,3,6,12,8,9,10,1,5,4,7))
print(target_df, n=24)
#> # A tibble: 24 x 3
#> key y z
#> <chr> <dbl> <dbl>
#> 1 A 2 1
#> 2 A 2 2
#> 3 A 2 3
#> 4 A 2 4
#> 5 A 2 5
#> 6 A 2 6
#> 7 A 2 7
#> 8 A 2 8
#> 9 A 2 9
#> 10 A 2 10
#> 11 A 2 11
#> 12 A 2 12
#> 13 B 2 2
#> 14 B 2 11
#> 15 B 2 3
#> 16 B 2 6
#> 17 B 2 12
#> 18 B 2 8
#> 19 B 2 9
#> 20 B 2 10
#> 21 B 2 1
#> 22 B 2 5
#> 23 B 2 4
#> 24 B 2 7
Update: I have a working solution but its ugly purrr might help
library(tidyverse)
df_BEFORE <- tibble( key = c("A","A","A","A","A","A","A","A","A","A","A","A","B","B","B","B","B","B","B","B","B","B","B","B"),y=c(2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2), z=c(1,2,3,4,5,6,7,8,9,10,11,12,2,11,3,6,12,8,9,10,1,5,NA,NA))
print(df_BEFORE, n=24)
#> # A tibble: 24 x 3
#> key y z
#> <chr> <dbl> <dbl>
#> 1 A 2 1
#> 2 A 2 2
#> 3 A 2 3
#> 4 A 2 4
#> 5 A 2 5
#> 6 A 2 6
#> 7 A 2 7
#> 8 A 2 8
#> 9 A 2 9
#> 10 A 2 10
#> 11 A 2 11
#> 12 A 2 12
#> 13 B 2 2
#> 14 B 2 11
#> 15 B 2 3
#> 16 B 2 6
#> 17 B 2 12
#> 18 B 2 8
#> 19 B 2 9
#> 20 B 2 10
#> 21 B 2 1
#> 22 B 2 5
#> 23 B 2 NA
#> 24 B 2 NA
get_index <- function(c,j){
case_when(
is.na(j) ~ 0,
j == 0 ~ 0,
j ==1 ~ setdiff(1:12,c)[1],
j ==2 ~ setdiff(1:12,c)[2],
j ==3 ~ setdiff(1:12,c)[3],
j ==4 ~ setdiff(1:12,c)[4],
j ==5 ~ setdiff(1:12,c)[5],
j ==6 ~ setdiff(1:12,c)[6],
j ==7 ~ setdiff(1:12,c)[7],
j ==8 ~ setdiff(1:12,c)[8],
j ==9 ~ setdiff(1:12,c)[9],
j ==10 ~ setdiff(1:12,c)[10],
j ==11 ~ setdiff(1:12,c)[11]
)
}
df_BEFORE %>%
group_by(key) %>%
mutate(seed = 1) %>%
mutate(flag = if_else(!is.na(z),0,seed)) %>%
mutate(j = flag + lag(flag)) %>%
mutate(temp = z) %>%
mutate(new_z = if_else(j==1,setdiff(1:12,temp)[1],
if_else(j==2,setdiff(1:12,temp)[2],
if_else(j==3,setdiff(1:12,temp)[3],
if_else(j==4,setdiff(1:12,temp)[4],
if_else(j==2,setdiff(1:12,temp)[5],
if_else(j==2,setdiff(1:12,temp)[6],
if_else(j==2,setdiff(1:12,temp)[7],
if_else(j==2,setdiff(1:12,temp)[8],
if_else(j==2,setdiff(1:12,temp)[9],
if_else(j==2,setdiff(1:12,temp)[2],0L
))))))))))) %>%
mutate(z= if_else(is.na(z),as.numeric(new_z),z)) %>%
select(-j, -new_z,-temp, -flag, -seed) %>%
print(n=24)
#> # A tibble: 24 x 3
#> # Groups: key [2]
#> key y z
#> <chr> <dbl> <dbl>
#> 1 A 2 1
#> 2 A 2 2
#> 3 A 2 3
#> 4 A 2 4
#> 5 A 2 5
#> 6 A 2 6
#> 7 A 2 7
#> 8 A 2 8
#> 9 A 2 9
#> 10 A 2 10
#> 11 A 2 11
#> 12 A 2 12
#> 13 B 2 2
#> 14 B 2 11
#> 15 B 2 3
#> 16 B 2 6
#> 17 B 2 12
#> 18 B 2 8
#> 19 B 2 9
#> 20 B 2 10
#> 21 B 2 1
#> 22 B 2 5
#> 23 B 2 4
#> 24 B 2 7
Created on 2020-05-03 by the reprex package (v0.3.0)
