Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rearranging Data frame in R

Tags:

r

transform

So I have an R output that looks like this

Time    50  100  150  200  250  300  350
Carla  1.2  1.8  2.2  2.3  3.0  2.5  1.8
Mace   1.5  1.1  1.9  2.0  3.6  3.0  2.5
Lea    1.7  1.6  2.3  2.7  2.6  2.2  2.6
Karen  1.3  1.7  1.9  2.2  3.2  1.5  1.9

I want it to look like this

Time  Score   Name
50    1.2     Carla
50    1.5     Mace
50    1.7     Lea
50    1.3     Karen
100   1.8     Carla
100   1.5     Mace
100   1.7     Lea
100   1.3     Karen

How can I transform it to this? Thank you

like image 746
Sarah May Muholland Avatar asked Jul 05 '13 04:07

Sarah May Muholland


2 Answers

Two methods, neither requiring external packages:

# get the data, the number labelled columns get automatically renamed
# to say 'X50' instead of '50'
test <- read.table(text="Time   50   100    150  200 250 300 350
Carla  1.2   1.8   2.2  2.3 3.0 2.5 1.8
Mace   1.5   1.1   1.9  2.0 3.6 3.0 2.5
Lea    1.7   1.6   2.3  2.7 2.6 2.2 2.6
Karen  1.3   1.7   1.9  2.2 3.2 1.5 1.9",header=TRUE)

Method 1: as.data.frame.table

rownames(test) <- test$Time
result <- setNames(as.data.frame.table(as.matrix(test[-1])),c("Name","Time","Score"))
result$Time <- gsub("X","",result$Time)

> head(result)
   Name Time Score
1 Carla   50   1.2
2  Mace   50   1.5
3   Lea   50   1.7
4 Karen   50   1.3
5 Carla  100   1.8
6  Mace  100   1.1

Method 2: base R reshape

result <- reshape(test, idvar="Time", varying=-1, direction="long", sep="")
names(result) <- c("Name","Time","Score")

> head(result)
           Name Time Score
Carla.50  Carla   50   1.2
Mace.50    Mace   50   1.5
Lea.50      Lea   50   1.7
Karen.50  Karen   50   1.3
Carla.100 Carla  100   1.8
Mace.100   Mace  100   1.1
like image 93
thelatemail Avatar answered Nov 14 '22 23:11

thelatemail


I copy your dataset directly from here and processed it a bit:

library(reshape2)

data <- read.fwf("data.txt",widths=c(7,5,7,5,4,4,4,3))
data <- data.frame(lapply(data,function(x){gsub(" ","",x)}),stringsAsFactors=FALSE)
data <- data.frame(lapply(data,as.character),stringsAsFactors=FALSE)
names(data) <- data[1,]
data <- data[-1,]

data <- melt(data,id.vars=c("Time"))
names(data) <- c("Name","Time","Score")
data <- data[,c("Time","Score","Name")]
like image 35
lokheart Avatar answered Nov 14 '22 22:11

lokheart