merge rows (to columns) in R dataframe based on IDs

Question

I have a dataframe with two columns: $1 = transcript ID, $2 = expression counts (tpm). This comes from a merging of several kallisto counts (if it matters).

I would like to build, in the most basic R way (no tidyverse), a dataframe with as column $1 the unique transcript IDs, and the expression countsas following columns (as many as needed).

Sample input:

$ head test1
G01000001_1 0
G01000001_1 0.00855717
G01000001_1 0.0113467
G01000001_1 0.0121086
G01000001_1 0.0121108
G01000001_1 0.0148402
G01000001_1 0.0183057
G01000001_1 0.0268394
G01000001_1 0.0298547

Sample output:

$ head -3 test2

G01000001_1 0  0.00855717 0.0113467 0.0121086 0.0121108 0.0148402 0.0183057 0.0268394 0.0298547 0.0444686 0.046795 0.0547494 0.0640871 0.0674897 0.0719834 0.082262 0.0867695 0.094905 0.10269 0.11488 0.121549 0.131143 0.141432 0.145758 0.167886 0.184162 0.192757 0.200921 0.249595 0.283296 0.430173 0.473344 0.518981 0.674573 0.701431 0.845816 2.77479 6.28394
G01000002_1 0  0.0277237 0.0294273 0.029438 0.0379258 0.0606153 0.0614727 0.069608 0.142541 0.197644 0.218152 0.22193 0.227401 0.230179 0.259295 0.279463 0.289982 0.292763 0.303268 0.305259 0.308236 0.319988 0.532091 0.632095 0.707766 0.746436 0.821887 0.822496 0.997334 1.06616 1.21752 1.48436 10.0862 2.01763 3.94584 5.02089 7.55767 8.3501
G01000003_1 0  0.00265959 0.00322424 0.00920435 0.454749 0.692183 0.709159 0.948286 1.11827 1.39854 1.39985 1.5781 1.86343 10.2554 10.7185 11.0315 14.342 15.2383 2.13068 2.41792 2.52833 2.77868 3.56981 3.84991 3.97521 4.10445 4.16344 4.26902 4.97202 47.8293 47.9114 5.21789 5.21926 5.35712 5.49942 5.7085 5.87654 50.7484 51.3721 56.7279 7.35062 7.54485 7.64363e-05 8.1367 8.34828

This could be achieved in awk, but I would really like an R solution. For the logic, here is the awk way:

sort test1 | awk '{if(a!=$1) {a=$1; printf "\n%s%s",$0,FS} else {a=$1;$1="";printf $0 }} END {printf "\n" }' > test2

Answer 1

For a data.frame named foo:

sink('some_output_file.txt')
uvals = unique(foo[,1])
for(uIDX in c(1:length(uvals))) {
    gname = uvals[uIDX]
    IDX = which(foo[,1] == gname)
    cat(sprintf("%s\t%s\n", gname, paste0(foo[IDX,2], collapse="\t")))
}
sink()

Answer 2

For that purpose, you may also use the reshape2 R package which allows you to switch wide to long format for data.frames. However, for that, you need an additional column with sample identifiers, e.g.:

G01000001_1 Sample1 0
G01000001_1 Sample2 0.00855717
G01000001_1 Sample3 0.0113467
G01000002_1 Sample1 0.0121086
G01000002_1 Sample2 0.0121108
G01000002_1 Sample3 0.0148402
G01000003_1 Sample1 0.0183057
G01000003_1 Sample2 0.0268394
G01000003_1 Sample3 0.0298547

Then, load and convert the data in R:

library(reshape2)
a<-read.table("test")
dcast(a, V1~V2, value.var = "V3")