How I can insert values a dataframe to another dataframe-CodePudding

A similar to my data is:

dat1<-read.table (text=" ID Rat Garden  Class   Time1   Time2   Time3
1   12  12  0   15  16  20
1   13  0   1   NA  NA  NA      
2   13  11  0   18  12  16
2   9   0   1   NA  NA  NA      
1   6   13  0   17  14  14
1   7   0   2   NA   NA  NA     
2   4   14  0   17  16  12
2   3   0   2   NA  NA  NA      

", header=TRUE)
 
dat2<-read.table (text=" ID Value1  Value2
1   6   7
2   5   4
", header=TRUE)

I want to insert the values of dat2 to dat1 in the Time1 column. In front of numbers 1 and 2 in the class column. I get the following outcome.

ID  Rat Garden  Class   Time1   Time2   Time3
1   12  12  0   15  16  20
1   13  0   1   6       
2   13  11  0   18  12  16
2   9   0   1   5       
1   6   13  0   17  14  14
1   7   0   2   7       
2   4   14  0   17  16  12
2   3   0   2   4

CodePudding user response：

We may group by 'ID', and replace the 'Time1' where the NA values occur with the unlisted 'dat2' 'Value' columns where the ID matches

library(dplyr)
dat1 %>% 
   group_by(ID) %>%
   mutate(Time1 = replace(Time1, is.na(Time1), 
     unlist(dat2[-1][dat2$ID == cur_group()$ID,]))) %>%
   ungroup

-output

# A tibble: 8 × 7
     ID   Rat Garden Class Time1 Time2 Time3
  <int> <int>  <int> <int> <int> <int> <int>
1     1    12     12     0    15    16    20
2     1    13      0     1     6    NA    NA
3     2    13     11     0    18    12    16
4     2     9      0     1     5    NA    NA
5     1     6     13     0    17    14    14
6     1     7      0     2     7    NA    NA
7     2     4     14     0    17    16    12
8     2     3      0     2     4    NA    NA

CodePudding user response：

Here is a wild ride: First we pull the values as a vector from dat2. Then we put alternating an NA into the vector until it gets column length of dat1 and finally we use coalesce after cbind:

library(dplyr)
library(tidyr)

vector <- dat2 %>% 
  pivot_longer(-ID) %>% 
  arrange(name) %>% 
  pull(value)
      
col_x <- c(sapply(vector, c, rep(NA, 1)))

cbind(dat1, col_x) %>% 
  mutate(col_x = lag(col_x)) %>% 
  mutate(Time1= coalesce(Time1, col_x), .keep="unused")

 ID Rat Garden Class Time1 Time2 Time3
1  1  12     12     0    15    16    20
2  1  13      0     1     6    NA    NA
3  2  13     11     0    18    12    16
4  2   9      0     1     5    NA    NA
5  1   6     13     0    17    14    14
6  1   7      0     2     7    NA    NA
7  2   4     14     0    17    16    12
8  2   3      0     2     4    NA    NA