R: reshape data from column to row and add additional data based on name

Question

I am looking for a way to reshape the following sample data

data <- structure(list(id = c(2L, 5L, 7L), name = structure(1:3, .Label = c("Test1","Test10", "Test8"), class = "factor"), source = structure(c(1L,3L, 2L), .Label = c("A", "T", "Z"), class = "factor")), .Names = c("id", "name", "source"), class = "data.frame", row.names = c(NA, -3L))

 id   name source
1  2  Test1      A
2  5 Test10      Z
3  7  Test8      T

into the following structure

row.names   1.1    2.1     3.1
id            2      5       7
name      Test1 Test10   Test8
source        A      Z       T

and how could I add a second data.frame say, data2 based on the name to data (only the data that contains a matching name)?

data2 <- structure(list(name = structure(1L, .Label = "adddata", class = "factor"), Test1 = 10L, Test10 = 12L, Test8 = 17L, Test12 = 7L), .Names = c("name", "Test1", "Test10", "Test8", "Test12"), class = "data.frame", row.names = c(NA, -1L))

data2
   name   Test1 Test10 Test8 Test12
1 adddata    10     12    17      7

So that in the end something like the following data.frame which contains only matching names (Test12 from data2 is left out) is the case

datanew
  row.names   1.1    2.1   3.1
1        id     2      5     7
2      name Test1 Test10 Test8
3    source     A      Z     T
4   adddata    10     12    17

EDIT

I just realised that my input data contains nested lists like this. Is there a way to implement this?

data <- structure(list(`1.1` = structure(list(id = structure(2, .Dim = c(1L, 1L)), name = structure("Test1", .Dim = c(1L, 1L)), source = structure("A", .Dim = c(1L, 1L))), .Names = c("id", "name", "source")), `2.1` = structure(list(id = structure(5, .Dim = c(1L, 1L)), name = structure("Test10", .Dim = c(1L, 1L)), source = structure("Z", .Dim = c(1L, 1L))), .Names = c("id", "name", "source")), `3.1` = structure(list(id = structure(7, .Dim = c(1L, 1L)), name = structure("Test8", .Dim = c(1L, 1L)), source = structure("T", .Dim = c(1L, 1L))), .Names = c("id", "name", "source"))), .Names = c("1.1", "2.1", "3.1"), class = "data.frame", row.names = c("id", "name", "source"))

'data.frame':   3 obs. of  3 variables:
 $ 1.1:List of 3
  ..$ id    : num [1, 1] 2
  ..$ name  : chr [1, 1] "Test1"
  ..$ source: chr [1, 1] "A"
 $ 2.1:List of 3
  ..$ id    : num [1, 1] 5
  ..$ name  : chr [1, 1] "Test10"
  ..$ source: chr [1, 1] "Z"
 $ 3.1:List of 3
  ..$ id    : num [1, 1] 7
  ..$ name  : chr [1, 1] "Test8"
  ..$ source: chr [1, 1] "T"

akrun · Accepted Answer · 2015-07-22T10:18:09.157

You could transpose the first dataset ('data') and rbind the output ('d1') with the columns that in 'data2' that we subset using the match between the column 'name' in 'data' and the column names of 'data2'

  d1 <- as.data.frame(t(data), stringsAsFactors=FALSE)
  res <- rbind(d1, setNames(data2[match(data$name, names(data2))], names(d1)))
  rownames(res)[4] <- as.character(data2$name)
  res
  #           V1     V2    V3
  #id          2      5     7
  #name    Test1 Test10 Test8
  #source      A      Z     T
  #adddata    10     12    17

Or another option is join from data.table

  library(data.table)#v1.9.5+
  DT <- setDT(data)[melt(data2, id.var='name', value.name='adddata', 
           variable.name='name')[-1], on='name', nomatch=0]
  DT
  #   id   name source adddata
  #1:  2  Test1      A      10
  #2:  5 Test10      Z      12
  #3:  7  Test8      T      17

I would keep in this format rather than transpose it as the columns are of different classes. If we transpose, the numeric and non-numeric elements get mixed together in a column and the class will be either factor or character

 t(DT)

Update

Based on the edited "data", we can unlist the list and convert to 'data.frame'. Then, we can use the steps as before.

 data <- setNames(as.data.frame(matrix(unlist(data), ncol=3, 
               byrow=TRUE)), row.names(data))
 DT <- setDT(data)[melt(data2, id.var='name', value.name='adddata', 
            variable.name='name')[-1], on='name', nomatch=0]
 DT
 #   id   name source adddata
 #1:  2  Test1      A      10
 #2:  5 Test10      Z      12
 #3:  7  Test8      T      17

thanks @akrun. I however just realised that `data` contains nested lists and is not a common data.frame (I edited the input data). Is there a way to implement this? — nebuloso, Jul 22 '15 at 09:19

R: reshape data from column to row and add additional data based on name

1 Answers1

Update