I need to merge columns by date into one column

[dortiz]

Hi all,
I need to change a dataframe that is has a variable arranged by date like this:
ID Var_Day1 Var_Day2 Var_Day3
1 2 66 222
2 5 55 333
3 6 44 555
4 3 33 444
5 4 22 333

into a new dataframe with one column for the variable for all days. Something like this:
ID Day Var
1 1 2
2 1 5
3 1 6
4 1 3
5 1 4
1 2 66
2 2 55
3 2 44
4 2 33
5 2 22
1 3 222
2 3 333
3 3 555
4 3 444
5 3 333


I tried the package reshape and function melt but I’m stuck. Here is my code:
install.packages("reshape")
library ("reshape")
data<-read.csv("data by day.csv",header=T)
newdata <- melt(data, id=c("id"))

Any suggestion?
Many thanks,
Diego

 

[trinker]

Here is a reshape2 approach as well as my preferred tidyr+dplyr approach:

First read in your data (please use code tags to display data in the future as well as for code. http://www.talkstats.com/showthread.php/29338-How-to-use-Code-tags):

dat <- read.table(text="ID,	Var_Day1,	Var_Day2,	Var_Day3
1,	2,	66,	222
2,	5,	55,	333
3,	6,	44,	555
4,	3,	33,	444
5,	4,	22,	333", header=TRUE, sep=",")

library(reshape2)

dat2 <- melt(dat, "ID", variable.name="Day", value.name="Var")
dat2[["Day"]] <- as.numeric(gsub("Var_Day", "", dat2[["Day"]]))

library(dplyr); library(tidyr)


dat %>%
    gather(Day, Var, -ID) %>%
    mutate(Day = as.numeric(gsub("Var_Day", "", Day)))

Bonus data.table approach:

library(data.table)

dat2 <- melt(as.data.table(dat), "ID", variable.name="Day", value.name="Var")
dat2[, Day := as.numeric(gsub("Var_Day", "", Day))]
dat2

 

[dortiz]

Thanks trinker. What if I have more than one variable that have "day" columns? Is it still possible to melt both variables independently so that I end up with one column for each one? I tried creating a subset for each variable, working with each one independently and finally I merged both subsets. It worked fine but I wonder if there is a way to avoid the subset step as I have several variables repeated in time. thanks

 

[trinker]

can you ask with an example dataset?

 

[dortiz]

sure, here it is.

this is the data:

data <- read.table(text="ID_1,ID_2,growth_day1,growth_day2,growth_day3,height_day1,height_day2,height_day3
geno1,534,13.2,17.7,36.6,21.9,24.9,35.7
geno2,597,12.9,16.1,36,23,25.3,37.3
geno3,655,13.3,16.9,35.9,22.6,25.5,37.1
geno4,548,12.6,16.1,35.7,22.4,24.4,37
geno5,656,13.3,16.8,36.6,22.8,25.5,38.2
geno6,656,10.9,16.4,36.1,22.7,24.5,36.5
geno7,533,12.6,16.1,36.2,22.9,25.3,35.6
geno8,533,13.3,17.6,36.6,22.7,24.6,37.4", header=TRUE, sep=",")

And the code:

install.packages("reshape2")
library(reshape2)

growth<-data[,c("ID_1","ID_2","growth_day1","growth_day2","growth_day3")]
m.growth<-melt(growth, id= c("ID_1","ID_2"), variable.name="Day", value.name="growth")
m.growth[["Day"]] <- as.numeric(gsub("growth_day", "", m.growth[["Day"]]))

height<-data[,c("ID_1","ID_2","height_day1","height_day2","height_day3")]
m.height<-melt(height, id= c("ID_1","ID_2"), variable.name="Day", value.name="height")
m.height[["Day"]] <- as.numeric(gsub("height_day", "", m.height[["Day"]]))


final.data<- Reduce(function(x, y) merge(x, y, all=TRUE), list(m.growth,m.height))

 

[trinker]

Here's one possible approach using tidyr. reshape 2 can do this as well but I am more used to tidyr now:

library(tidyr); library(dplyr)

data %>%
    gather(var, val, -c(ID_1, ID_2)) %>%     ## grab all repeated measures as one column
    separate(var, c("var", "Day"), "_") %>%  ## split the key (variable and Day)
    spread(var, val)                         ## spread value on the var column

##     ID_1 ID_2  Day growth height
## 1  geno1  534 day1   13.2   21.9
## 2  geno1  534 day2   17.7   24.9
## 3  geno1  534 day3   36.6   35.7
## 4  geno2  597 day1   12.9   23.0
## 5  geno2  597 day2   16.1   25.3
## 6  geno2  597 day3   36.0   37.3
## 7  geno3  655 day1   13.3   22.6
## 8  geno3  655 day2   16.9   25.5
## 9  geno3  655 day3   35.9   37.1
## 10 geno4  548 day1   12.6   22.4
## 11 geno4  548 day2   16.1   24.4
## 12 geno4  548 day3   35.7   37.0
## 13 geno5  656 day1   13.3   22.8
## 14 geno5  656 day2   16.8   25.5
## 15 geno5  656 day3   36.6   38.2
## 16 geno6  656 day1   10.9   22.7
## 17 geno6  656 day2   16.4   24.5
## 18 geno6  656 day3   36.1   36.5
## 19 geno7  533 day1   12.6   22.9
## 20 geno7  533 day2   16.1   25.3
## 21 geno7  533 day3   36.2   35.6
## 22 geno8  533 day1   13.3   22.7
## 23 geno8  533 day2   17.6   24.6
## 24 geno8  533 day3   36.6   37.4

 

[trinker]

PS the way you just did the question is perfect. Give data, and an explanation, and desired output.

 

[dortiz]

looks much more simple like that. Thanks, Trinker!