Converting column to row while consolidating data

Question

I have a data frame like this:

feature_id  mouse_number  value
P1001       1             8.55
P1001       2             6.50
P1001       3             2.48
P1001       4             9.01
P1002       1             4.41
P1002       2             4.91
P1002       3             5.86
P1002       4             1.89
...

I want the data to read like:

mouse_number   P1001    P1002   ...
1              8.55     4.41
2              6.50     4.91
3              2.48     5.86
4              9.01     1.89

Please assist, thanks!

score 1 · Answer 1 · answered Oct 10 '17 at 15:32

df <- read.table(text="feature_id  mouse_number  value
P1001       1             8.55
           P1001       2             6.50
           P1001       3             2.48
           P1001       4             9.01
           P1002       1             4.41
           P1002       2             4.91
           P1002       3             5.86
           P1002       4             1.89",stringsAsFactors=F,header=T)

library(tidyr)
df %>% spread(feature_id,value)
# mouse_number P1001 P1002
# 1            1  8.55  4.41
# 2            2  6.50  4.91
# 3            3  2.48  5.86
# 4            4  9.01  1.89

score 1 · Answer 2 · answered Oct 10 '17 at 15:36

You can also use the reshape2 package.

library(reshape2)

dt2 <- dcast(dt, mouse_number ~ feature_id, value.var = "value")
dt2
  mouse_number P1001 P1002
1            1  8.55  4.41
2            2  6.50  4.91
3            3  2.48  5.86
4            4  9.01  1.89

DATA

dt <- read.table(text = "feature_id  mouse_number  value
P1001       1             8.55
                 P1001       2             6.50
                 P1001       3             2.48
                 P1001       4             9.01
                 P1002       1             4.41
                 P1002       2             4.91
                 P1002       3             5.86
                 P1002       4             1.89",
                 header = TRUE, stringsAsFactors = FALSE)

score 1 · Answer 3 · answered Oct 10 '17 at 17:22

Another option is dcast from data.table

library(data.table)
dcast(setDT(dt), mouse_number~feature_id)
#   mouse_number P1001 P1002
#1:            1  8.55  4.41
#2:            2  6.50  4.91
#3:            3  2.48  5.86
#4:            4  9.01  1.89

Or if there are equal number of observations, xtabs from base R can be used

xtabs(value~ mouse_number + feature_id, dt)

Converting column to row while consolidating data

3 Answers3