R:Paste unique values of one column with data header and transpose it

Question

I need some help with my data. I am going to explain it better using an example. Here is the dput() data:

data <- structure(list(SEQ = c(1, 2, 2, 2, 2, 2), 
                       PR = structure(c(1L, 2L, 3L, 4L, 5L, 8L), .Label = c("AHE", 
                                                                                     "AHE", "BHE", "BTH", "CHE", "CTH", "DHE", 
                                                                                     "DS", "DTH"), class = "factor"), mittel = c(1.33, 
                                                                                                                                         2, 0.17, 0.33, 0, 0), max = c(1.33, 2, 0.17, 0.33, 0, 0), 
                       s = c(NaN, NaN, NaN, NaN, NaN, NaN), n = c(1L, 1L, 1L, 1L, 
                                                                  1L, 1L)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
                                                                                                                                    -6L), .Names = c("SEQ", "PR", "mittel", "max", "s", 
                                                                                                                                                     "n"))

It looks like this exactly:

     SEQ     PR mittel   max     s     n
1      1    AHE   1.33  1.33   NaN     1
2      2    AHE   2.00  2.00   NaN     1
3      2    BHE   0.17  0.17   NaN     1
4      2    BTH   0.33  0.33   NaN     1
5      2    CHE   0.00  0.00   NaN     1
6      2     DS   0.00  0.00   NaN     1

I would like to paste the unique values of column PR with the column names: mittel, max, s and n. Then transpose the table and use the SEQ column as header for the transposed table. It would look smthg like this:

           1      2
AHE_mittel 1.33  2.0
AHE_max    1.33  2.0
AHE_s      NaN   NaN
AHE_n      1     1
...

[ANSWER]

I would like to thank everyone for help! I have noticed (thank to other users) that it is sort of a duplicate question. At the end i have used this code using dcast:

data <- melt(data, id=c(1:2))
data$id <- paste(data$PR, data$variable, sep="_")
data <- dcast(data, ...~SEQ, median)

Cheers

score 1 · Accepted Answer · answered Aug 03 '16 at 11:08

Using ddply and nested functions:

DF <- structure(list(SEQ = c(1, 2, 2, 2, 2, 2), 
                       PR = structure(c(1L, 2L, 3L, 4L, 5L, 8L), .Label = c("AHE", 
                                                                                     "AHE", "BHE", "BTH", "CHE", "CTH", "DHE", 
                                                                                     "DS", "DTH"), class = "factor"), mittel = c(1.33, 
                                                                                                                                         2, 0.17, 0.33, 0, 0), max = c(1.33, 2, 0.17, 0.33, 0, 0), 
                       s = c(NaN, NaN, NaN, NaN, NaN, NaN), n = c(1L, 1L, 1L, 1L, 
                                                                  1L, 1L)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
                                                                                                                                    -6L), .Names = c("SEQ", "PR", "mittel", "max", "s", 
                                                                                                                                                     "n"))



#Convert PR to character class
DF$PR = as.character(DF$PR)                                                                                                                                                  



#For each unique PR, tranpose each of the columns (mittel,max,s,n) and rbind the rows

transposeDF = ddply(DF,.(PR),function(x) do.call(rbind,lapply(3:ncol(x),function(z) t(x[,z]) )))

#Assign new name

transposeDF$newPR = paste0(transposeDF$PR,"_",colnames(DF)[3:ncol(DF)])


#>transposeDF
#    PR    1   2      newPR
#1  AHE 1.33   2 AHE_mittel
#2  AHE 1.33   2    AHE_max
#3  AHE  NaN NaN      AHE_s
#4  AHE 1.00   1      AHE_n
#5  BHE 0.17  NA BHE_mittel
#6  BHE 0.17  NA    BHE_max
#7  BHE  NaN  NA      BHE_s
#8  BHE 1.00  NA      BHE_n
#9  BTH 0.33  NA BTH_mittel
#10 BTH 0.33  NA    BTH_max
#11 BTH  NaN  NA      BTH_s
#12 BTH 1.00  NA      BTH_n
#13 CHE 0.00  NA CHE_mittel
#14 CHE 0.00  NA    CHE_max
#15 CHE  NaN  NA      CHE_s
#16 CHE 1.00  NA      CHE_n
#17  DS 0.00  NA  DS_mittel
#18  DS 0.00  NA     DS_max
#19  DS  NaN  NA       DS_s
#20  DS 1.00  NA       DS_n

score 0 · Answer 2 · answered Aug 03 '16 at 11:03

0

Here you go:

library(tidyr)
library(dplyr)

mod <- data%>%
  gather(val,val2, mittel:n) %>%
  select(PR, val, val2, SEQ)

mod <- data.frame(id = paste0(mod$PR,"_",mod$val), select(mod, val2, SEQ))

answered Aug 03 '16 at 11:03

tia_0

412
1
3
11

Your way is very interesting, however it helps me only with the first part and not the second: transposing the table.I got my answer using `melt` and `dcast` function, but thanks anyway – Mal_a Aug 03 '16 at 11:15

R:Paste unique values of one column with data header and transpose it

2 Answers2