I have two datasets in R (these tables below are just smaller versions) that I would like to combine into a new data frame.
> meetingtime2
#two columns of datetime that class=factor
ST ET
1 2014-12-22 07:00:00 2014-12-22 07:30:00
2 2014-12-22 07:30:00 2014-12-22 08:00:00
3 2014-12-22 08:00:00 2014-12-22 08:30:00
4 2014-12-22 08:30:00 2014-12-22 09:00:00
5 2014-12-22 09:00:00 2014-12-22 09:30:00
> roomdata2
#three columns; Room=factor, Capacity=integer, Video Conference=numeric
Room Capacity Video.Conference
1 0M02A 16 1
2 0M03A 8 0
3 0M03B 12 1
The desired output would be a 15 row by 5 column matrix. In easy speak the output is every time slot for every room.
#the following is a MANUALLY created output of what the first few rows should look like
Room Capacity Video.Conference ST ET
1 0M02A 16 1 2014-12-22 07:00:00 2014-12-22 07:30:00
2 0M02A 16 1 2014-12-22 07:30:00 2014-12-22 08:00:00
3 0M02A 16 1 2014-12-22 08:00:00 2014-12-22 08:30:00
4 0M02A 16 1 2014-12-22 08:30:00 2014-12-22 09:00:00
5 0M02A 16 1 2014-12-22 09:00:00 2014-12-22 09:30:00
6 0M03A 16 1 2014-12-22 07:00:00 2014-12-22 07:30:00
7 0M03A 16 1 2014-12-22 07:30:00 2014-12-22 08:00:00
#and so forth to 15 rows.
I've tried using a nested loop
#note, the code is written so I can apply to a bigger (1000's of rows) dataset
>mylist<-list()
>for(i in 1:(nrow(roomdata2)))
+{ for(j in 1:(nrow(meetingtime2)))
+mylist[[j]]<- data.frame(roomdata2[i,1],roomdata2[i,2],roomdata2[i,3],
+meetingtime2[j,1],meetingtime2[j,2])
}
>df<-do.call("rbind",mylist)
>df
The output I get. I'm getting all the timeslots for the last room, just not the preceding rooms
roomdata2.i..1. roomdata2.i..2. roomdata2.i..3. meetingtime2.j..1. meetingtime2.j..2.
1 0M03B 12 1 2014-12-22 07:00:00 2014-12-22 07:30:00
2 0M03B 12 1 2014-12-22 07:30:00 2014-12-22 08:00:00
3 0M03B 12 1 2014-12-22 08:00:00 2014-12-22 08:30:00
4 0M03B 12 1 2014-12-22 08:30:00 2014-12-22 09:00:00
5 0M03B 12 1 2014-12-22 09:00:00 2014-12-22 09:30:00
I know my code is far from correct and is giving me the final iteration of the loop.
The other way I looked at this was a continuous print function for each iteration
>for(i in 1:(nrow(roomdata2)))
>for(j in 1:(nrow(meetingtime2)))
>print(paste(roomdata2[i,1],roomdata2[i,2],roomdata2[i,3],
+meetingtime2[j,1],meetingtime2[j,2]))
the output
[1] "0M02A 16 1 2014-12-22 07:00:00 2014-12-22 07:30:00"
[1] "0M02A 16 1 2014-12-22 07:30:00 2014-12-22 08:00:00"
[1] "0M02A 16 1 2014-12-22 08:00:00 2014-12-22 08:30:00"
[1] "0M02A 16 1 2014-12-22 08:30:00 2014-12-22 09:00:00"
[1] "0M02A 16 1 2014-12-22 09:00:00 2014-12-22 09:30:00"
[1] "0M03A 8 0 2014-12-22 07:00:00 2014-12-22 07:30:00"
[1] "0M03A 8 0 2014-12-22 07:30:00 2014-12-22 08:00:00"
[1] "0M03A 8 0 2014-12-22 08:00:00 2014-12-22 08:30:00"
[1] "0M03A 8 0 2014-12-22 08:30:00 2014-12-22 09:00:00"
[1] "0M03A 8 0 2014-12-22 09:00:00 2014-12-22 09:30:00"
[1] "0M03B 12 1 2014-12-22 07:00:00 2014-12-22 07:30:00"
[1] "0M03B 12 1 2014-12-22 07:30:00 2014-12-22 08:00:00"
[1] "0M03B 12 1 2014-12-22 08:00:00 2014-12-22 08:30:00"
[1] "0M03B 12 1 2014-12-22 08:30:00 2014-12-22 09:00:00"
[1] "0M03B 12 1 2014-12-22 09:00:00 2014-12-22 09:30:00"
#however the values are not separated, they are just in one set of string for each row.
The desired result is a table like directly above, but instead a dataframe with each value in a seperate column (each date & time set together in one column).
I've looked into lists,lapply,foreach but I just can't wrap my head around the solution. Any help would be appreciated, I'm a beginner so I'm keen to learn.
Cheers * the dputs
>dput(meetingtime2)
structure(list(ST = structure(1:5, .Label = c("22/12/2014 7:00", "22/12/2014 7:30", "22/12/2014 8:00", "22/12/2014 8:30", "22/12/2014 9:00" ), class = "factor"), ET = structure(1:5, .Label = c("22/12/2014 7:30", "22/12/2014 8:00", "22/12/2014 8:30", "22/12/2014 9:00", "22/12/2014 9:30" ), class = "factor")), .Names = c("ST", "ET"), row.names = c(NA, -5L), class = "data.frame")
>dput(roomdata2)
structure(list(Room = structure(1:3, .Label = c("0M02A", "0M03A", "0M03B"), class = "factor"), Capacity = c(16L, 8L, 12L), Video.Conference = c(1L, 0L, 1L)), .Names = c("Room", "Capacity", "Video.Conference"), row.names = c(NA, -3L), class = "data.frame")
Using your data:
meetingtime2 <- read.csv(text = "ST,ET
2014-12-22 07:00:00,2014-12-22 07:30:00
2014-12-22 07:30:00,2014-12-22 08:00:00
2014-12-22 08:00:00,2014-12-22 08:30:00
2014-12-22 08:30:00,2014-12-22 09:00:00
2014-12-22 09:00:00,2014-12-22 09:30:00")
roomdata2 <- read.csv(text = "Room,Capacity,Video_Conference
0M02A,16,1
0M03A,8,0
0M03B,12,1")
Then merge handily returns the Cartesian product, because none of the columns match.
merge(meetingtime2, roomdata2)[, c(3:5, 1:2)]
## Room Capacity Video_Conference ST ET
## 1 0M02A 16 1 2014-12-22 07:00:00 2014-12-22 07:30:00
## 2 0M02A 16 1 2014-12-22 07:30:00 2014-12-22 08:00:00
## 3 0M02A 16 1 2014-12-22 08:00:00 2014-12-22 08:30:00
## 4 0M02A 16 1 2014-12-22 08:30:00 2014-12-22 09:00:00
## 5 0M02A 16 1 2014-12-22 09:00:00 2014-12-22 09:30:00