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

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-22 10:18:09Z

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?

Collectives™ on Stack Overflow

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

1 Answer 1

Update

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update

2 Comments

Linked

Related