I want to amend several columns in a consecutive way based on serial ID without rbind as columns headings are not identical

user_8376001 0
Viewed 30

Instead of copy and paste corresponding columns into excel, I want to amend several columns in a consecutive way based on serial ID named addr.

Assume my data sets are like these

df1 <- data.frame(addr=c('a','b','c','d'), 
                  num = c(1,2,3,4),
                  x=c(1, NA,4,5));df1

df2 <- data.frame(addr=c('e','f','g'), 
                  num=c(100,200,500));df2

var<-intersect(names(df), names(df2));var
combined.df<-merge(x = df1, y = df2, by = var, all=T);combined.df

df3 <- data.frame(addr=c('e','f','g'), 
                  x=c(5,7,NA));df3

var<-intersect(names(df3), names(combined.df));var
combined.df<-merge(x = combined.df, y = df3, by = var, all=T);combined.df

The current output is

  addr  x num
1    a  1   1
2    b NA   2
3    c  4   3
4    d  5   4
5    e  5  NA
6    e NA 100
7    f  7  NA
8    f NA 200
9    g NA 500

The desired output is

  addr  x num
1    a  1   1
2    b NA   2
3    c  4   3
4    d  5   4
5    e  5  100
6    f  7  200
7    g NA  500

i.e.: Overwrite empty columns without deleting prior full cells

Any advice will be greatly appreciated

dataframe r merge join
1 Answers

If we want to automate using a for loop, place the datasets in a list except the first one, then create a copy of the first dataset as 'out', loop over the sequence of the list, merge the first one i.e 'out' with the corresponding list elements, specify the by as intersect of names of both datasets and update by assigning (<-) back to the 'out'

out <- df1
lst1 <- list(df2, df3)
for(i in seq_along(lst1)) {
  out <- merge(out, lst1[[i]],
       by = intersect(names(out), names(lst1[[i]])), all = TRUE)
 }

Then, we change the output by grouping over the 'addr', and summarise across all other columns by removing the NA if there exist a non-NA element

library(dplyr)
out %>% 
    group_by(addr) %>%
    summarise(across(everything(),
            ~ if(all(is.na(.))) NA_real_ else .[!is.na(.)]), .groups = 'drop')

-output

#  addr      x   num
#  <chr> <dbl> <dbl>
#1 a         1     1
#2 b        NA     2
#3 c         4     3
#4 d         5     4
#5 e         5   100
#6 f         7   200
#7 g        NA   500
user_37322710
Related