Use an if inside loop to replace the data between two dataframe-CodePudding

I have two files and want to transfer date from one to other after doing a test

File1:

ID, X1, X2, X3
2000, 1, 2, 3
2001, 3, 4, 5
1999, 2, 5, 6
2003, 3, 5, 4

File2:

ID, X1, X2, X3,
2000,
2001,
2002,
2003,

Result file will be like:

1999 "There is an error"

File2:

ID, X1, X2, X3
2000, 1, 2, 3
2001, 3, 4, 5
2002, Na, Na, Na
2003, 3, 5, 4

I tried to use for loop with if, Unfortunately, it doesn't work:

for(j in length(1: nrows(file1){
   for(i in length(1: nrows(file2){
     if( file1&ID[j]>= file2&ID[j 1]){
      print(j, ' wrong value')
     esle 
     file2[i,]<- file1[j,]
break

It would be very nice if I can get some ideas, codes how I can get something similar to result file

I hope I can find the right code to solve this problem

CodePudding user response：

No need to iterate using loops, you can simply use right_join from dplyr package

df1 %>% 
  right_join(df2, by="ID") %>% 
  arrange(ID)
    ID X1 X2 X3
1 2000  1  2  3
2 2001  3  4  5
3 2002 NA NA NA
4 2003  3  5  4

Sample data

df1 <- structure(list(ID = c(2000L, 2001L, 1999L, 2003L), X1 = c(1L, 
3L, 2L, 3L), X2 = c(2L, 4L, 5L, 5L), X3 = c(3L, 5L, 6L, 4L)), class = "data.frame", row.names = c(NA, 
-4L))

df2 <- structure(list(ID = 2000:2003), class = "data.frame", row.names = c(NA, 
-4L))

CodePudding user response：

Using data.table

library(data.table)
setDT(df2)[df1, names(df1)[-1] := mget(paste0("i.", names(df1)[-1])), on = .(ID)]

-output

> df2
     ID X1 X2 X3
1: 2000  1  2  3
2: 2001  3  4  5
3: 2002 NA NA NA
4: 2003  3  5  4

CodePudding user response：

Here is a slightly different approach which does not give the exact expected output: Note that year 1999 is kept in the dataframe:

coalesce_by_column <- function(df) {
  return(coalesce(df[1], df[2]))
}

bind_rows(df1, df2) %>% 
  group_by(ID) %>%
  summarise_all(coalesce_by_column)

     ID    X1    X2    X3
  <int> <int> <int> <int>
1  1999     2     5     6
2  2000     1     2     3
3  2001     3     4     5
4  2002    NA    NA    NA
5  2003     3     5     4