R: merging columns by a common ID without any NA on any row?

Question

Given a data with missing values, imputation is a process where the missing values are substituted with some values. The goal is to ignore the rows with missing values, denoted with NAs. Such row could be seen as a component of the data hence the process called item imputation.

Input

df1 <- data.frame(ID=c(1,2,5,6),V1=c(7,77,777,NA))
df2 <- data.frame(ID=c(1,3,5,6),V2=c(6,66,666,6666))
df3 <- data.frame(ID=c(1,3,5,6),V3=c(9,NA,999,9999))

or alternatively in CSV format where misings values are marked with NAs

data.csv      data2.csv        data3.csv

ID V1         ID V2            ID V3
1  7          1  6             1  9
2  77         2  NA            2  NA
3  NA         3  66            3  NA
4  NA         4  NA            4  NA
5  777        5  666           5  999
6  NA         6  6666          6  9999

Output

Expected result is

ID V1   V2   V3
1  7    6    9
5  777  666  999

where we we wanted just lines without any NA value.

How to merge the input data with columns V1, V2, V3 and a common column ID with no NA on a row?

---

Example solution with SQLDF to merge the columns with common ID and no NA

library(sqldf)
# Read in the data: with CSV, you can use read.csv or fread from data.table
df1 <- data.frame(ID=c(1,2,5,6),V1=c(7,77,777,NA))
df2 <- data.frame(ID=c(1,3,5,6),V2=c(6,66,666,6666))
df3 <- data.frame(ID=c(1,3,5,6),V3=c(9,NA,999,9999))
#
sqldf("SELECT a.ID, a.V1, b.V2, c.V3 FROM df1 a, df2 b, df3 c WHERE a.ID=b.ID AND b.ID=c.ID AND V1!='NA'")

resulting to

   ID   V1  V2  V3
1   1    7   6   9
2   5  777 666 999

Answer 1

Here is a base R only version, that doesn't care how many merges there are. Assumes the data frames are in list l - see edit to Q for the example data in that format:

for(i in seq_along(l[-1])) {
    if(i == 1) {
        m <- merge(l[[i]], l[[i+1]])
    } else {
        m <- merge(m, l[[i+1]])
    }
}
m <- m[!apply(is.na(m), 1, any), ]

which gives the desired output

> m
  ID  V1  V2  V3
1  1   7   6   9
2  5 777 666 999

To read the data in, something like this should work

l <- lapply(list.files(pattern = glob2rx("data*.csv")), read.table, 
            header = TRUE)

or if they really are CSV

l <- lapply(list.files(pattern = glob2rx("data*.csv")), read.csv)

then you can use the code above to process.

Answer 2

Out of respect for the artform of haiku on fine display in this question, I'm going to provide the following answer/wild-guess:

library(reshape)
dats <- lapply( dir(), read.csv )
mgd <- merge_recurse( dats, by="ID" )
na.sel <- apply( mgd, 1, function(x) any(is.na(x)) )
mgd <- mgd[!na.sel,]

Note this assumes you actually want ID==1.

Thanks to @Joris for the merge_recurse tip.