How to sort out these data using two data frames in R-CodePudding

I have a sample of my two data frames as follows:

df1<-read.table (text=" ID  A   B   C   D   E   F   

A11 NA  Yes NA  M   NA  NA  
A12 Yes No  A1  Z   Q1  Yes 

", header=TRUE)

  

  df2<-read.table (text=" Name  A11 A12

    Rose    1   1
    Helen   0   0
    Move    1   1

    ", header=TRUE)

I want to get the following table:

Name    A11 Yes M   A12 Yes No  A1  Z   Q1  Yes
Rose    1   1   1   1   1   1   1   1   1   1
Helen   0   0   0   0   0   0   0   0   0   0
Move    1   1   1   1   1   1   1   1   1   1

The logic is that in df1, I want to get the values of A,B,C,D,F and G as columns in the table requested. So we look at ID in df1 and A11, A12 in df2; for example, in df1, the A11 ID is NA, Yes,...No. so in the outcome, they are considered as headings, but I don't need to have missings as columns. So in df2, when A11 is 1, all columns get 1. If it is zero, all columns get 0.

I hope my explanation is clear. If not happy to explain it better.

CodePudding user response：

I would do it like this:

# list of non-NA values in each row of df1
c.names <- apply(df1, 1, function(row) list(row[!is.na(row)]))
# drop one list level
c.names <- unlist(c.names, recursive=F)
names(c.names) <- df1[, 1]
# copy columns as necessary
df3 <- df2[, c('Name', rep(names(c.names), sapply(c.names, length)))]
names(df3)[-1] <- unlist(c.names)
#    Name A11 Yes M A12 Yes No A1 Z Q1 Yes
# 1  Rose   1   1 1   1   1  1  1 1  1   1
# 2 Helen   0   0 0   0   0  0  0 0  0   0
# 3  Move   1   1 1   1   1  1  1 1  1   1

Since there is no simplify argument for apply, we don't know if the returned object will be a list or a matrix. It would be a matrix if the number of non-NA values was the same for all rows. Otherwise, it would be a list. That's why I used list inside apply – to make sure it always returns a list. This list is, however, one level too deep so I had to drop one level as a next step.