How can I use columns as entries in rows?

Question

in the attached picture you can see two dataframes. I want to transform the first dataframe to the second one. I feel like there is probably an easy solution, I just don't know it.

Two Dataframes

Note that this example is created in Excel, the "real work" will be done in R.

Please do not post an image of code/data/errors: it cannot be copied or searched (SEO), it breaks screen-readers, and it may not fit well on some mobile devices. Ref: https://meta.stackoverflow.com/a/285557 (and https://xkcd.com/2116/). Please just include the code, console output, or data (e.g., `dput(head(x))` or `data.frame(...)`) directly. — r2evans, Jun 29 '20 at 16:34
[`tidyr::pivot_longer`](https://tidyr.tidyverse.org/reference/pivot_longer.html) — r2evans, Jun 29 '20 at 16:35

score 2 · Answer 1 · answered Jun 29 '20 at 16:36

This is easily accomplished with tidyr::pivot_longer().

textFile <- "Volume,Gene1,Gene2,Gene3,Gene4,Gene5
Set 1,1,2,3,4,5
Set 2,6,7,8,9,10
Set 3,11,12,13,14,15"

df <- read.csv(text = textFile,header=TRUE)

library(tidyr)
df %>% pivot_longer(.,-Volume,names_to = "Gene",values_to="Count")

...and the output:

# A tibble: 15 x 3
   Volume Gene  Count
   <chr>  <chr> <int>
 1 Set 1  Gene1     1
 2 Set 1  Gene2     2
 3 Set 1  Gene3     3
 4 Set 1  Gene4     4
 5 Set 1  Gene5     5
 6 Set 2  Gene1     6
 7 Set 2  Gene2     7
 8 Set 2  Gene3     8
 9 Set 2  Gene4     9
10 Set 2  Gene5    10
11 Set 3  Gene1    11
12 Set 3  Gene2    12
13 Set 3  Gene3    13
14 Set 3  Gene4    14
15 Set 3  Gene5    15

score 1 · Accepted Answer · answered Jun 29 '20 at 16:34

1

You need pivot_longer.

library(tidyr)

df %>%
  pivot_longer(cols=starts_with("Gene"), names_to="Gene", values_to="Count")

answered Jun 29 '20 at 16:34

Martin Gal

16,640
5
21
39

How can I use columns as entries in rows?

2 Answers2