<p>For a data.frame named <code>foo</code>:</p>

<pre><code>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()
</code></pre>

CC BY-SA 4.0
2020-06-09T21:26:21.887Z
2020-06-09T21:26:21.887Z
Devon Ryan
<p>For that purpose, you may also use the <strong>reshape2</strong> 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.:</p>

<pre><code>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
</code></pre>

<p>Then, load and convert the data in R:</p>

<pre><code>library(reshape2)
a&lt;-read.table("test")
dcast(a, V1~V2, value.var = "V3")
</code></pre>

CC BY-SA 4.0
2020-06-10T05:25:01.953Z
2020-06-10T05:25:01.953Z
thomas duge de bernonville
<p>I have a dataframe with two columns: <code>$1 = transcript ID</code>, <code>$2 = expression counts (tpm)</code>. This comes from a merging of several kallisto counts (if it matters). </p>

<p>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). </p>

<p>Sample input:</p>

<pre><code>$ 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
</code></pre>

<p>Sample output:</p>

<pre><code>$ 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
</code></pre>

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

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

<r><text-processing><merge>
merge rows (to columns) in R dataframe based on IDs
# merge rows (to columns) in R dataframe based on IDs

(Asked by: username_0 on 2020-06-09)

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

**Tags:** r, text-processing, merge

---

## 2 answers

### Answer by username_1 on 2020-06-09. Score: 1 | Accepted answer

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 by username_2 on 2020-06-10. Score: 1

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")


---

