How to combine data from another dataframe by calling column names in r

Question

I have a dataframe:

df1 = read.table(text="X1   X2  X3  X4  X5  X6  X7

1   3   6   2   0   3   1

2   3   5   8   9   0   1

5   1   0   6   3   2   7", header=T, stringsAsFactors=F)

and another dataframe. The id column holds the vectors of df1 column names:

df2 = read.table(text="id

'X2 X4 X7'

'X2 X3 X4 X6'

'X3 X5 X6 X7'

'X1'

'X1 X4'", header=T, stringsAsFactors=F)

I would like to add more columns, the number of columns added would be dependent on the row numbers of df1. For each added column, will holds the pasted values for each row only taken from the columns defined by id column.
So the expected result:

df2 = read.table(text="id   V1  V2  V3

'X2 X4 X7'  '3 2 1' '3 8 1' '1 6 7'

'X2 X3 X4 X6'   '3 6 2 3'   '3 5 8 0'   '1 0 6 7'

'X3 X5 X6 X7'   '6 0 3 1'   '5 9 0 1'   '0 3 2 7'

'X1'    '1' '2' '5'

'X1 X4' '1 2'   '2 8'   '5 6'", header=T, stringsAsFactors=F)

Thanks foe help.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

This is a bit involved but seems to work

 cbind(
   df2
 , 
   t(sapply(
   #first find indexes of columns
   lapply(strsplit(df2$id, " "), match, names(df1)), 
   # now extract those values
   function(i) { apply(df1[,i, drop=F], 1, paste, collapse=" " )}))
 )

This returns

           id       1       2       3
1    X2 X4 X7   3 2 1   3 8 1   1 6 7
2 X2 X3 X4 X6 3 6 2 3 3 5 8 0 1 0 6 2
3 X3 X5 X6 X7 6 0 3 1 5 9 0 1 0 3 2 7
4          X1       1       2       5
5       X1 X4     1 2     2 8     5 6

How to combine data from another dataframe by calling column names in r

Sign up