user3354212 user3354212 - 3 months ago 7
R Question

Extract from values from different columns based on a list of indices to form a long-format dataframe in r

I have a data frame

df
and a list
mylist
:

df = read.table(text="ID location C1 C2 C3 C4 C5 C6
M01 1 A H H A A B
M02 2 A H A A A B
M03 3 A B A A A B
M04 4 H B H A A B
M05 5 H B H A A B
M06 6 A B H A A H
M07 7 A B H B A H
M08 8 A B H A A H
M09 9 A B H A A H
M10 10 B B H A A H
M11 11 A B H A A H
M12 12 A B H A A H
M13 13 A B H A A H
M14 14 B B B A A H
M15 15 B B B A A A", header=T, stringsAsFactors=F)

mylist = list(C1 = c(3, 5, 9, 10, 13), C2 = c(2) ,
C3 = c(1, 3, 13 ), C4 =c(6, 7 ), C6 = c(5, 14 ))


I would like to extract out values of columns
names(df)[-(1:2)]
based on indices in a list
mylist
and with the first two columns
names(df)[1:2]
together to form a long format data frame as:

ID location value colname
M03 3 A C1
M05 5 H C1
M09 9 A C1
M10 10 B C1
M13 13 A C1
M02 2 H C2
M01 1 H C3
M03 3 A C3
M13 13 H C3
M06 6 A C4
M07 7 B C4
M05 5 B C6
M14 14 H C6

Answer

We get the first two columns in the expected output by unlisting the 'mylist', use that as row index to extract the rows from the first row columns of 'df', the 'value' column is created by extracting the elements of corresponding columns of 'df' with the 'mylist' elements as index in Map and unlist the list output, and the final 'colname' is the replicate of the names of 'mylist' by the lengths of the list.

r1 <- transform(df[unlist(mylist), 1:2],value = unlist(Map(`[`, df[names(mylist)], mylist)), 
       colname = rep(names(mylist), lengths(mylist)))
row.names(r1) <- NULL
r1
#   ID location value colname
#1  M03        3     A      C1
#2  M05        5     H      C1
#3  M09        9     A      C1
#4  M10       10     B      C1
#5  M13       13     A      C1
#6  M02        2     H      C2
#7  M01        1     H      C3
#8  M03        3     A      C3
#9  M13       13     H      C3
#10 M06        6     A      C4
#11 M07        7     B      C4
#12 M05        5     B      C6
#13 M14       14     H      C6
Comments