0

I have a dataframe with Date,Time and Price as the column heads. I want to create a new dataframe based on the Time, i.e. one column has all the prices at 00:00, another has all the prices at 01:00, the next has all the prices at 02:00 etc.

I have used ddply to calculate the mean of each time through;

ddply(df,.(Time),function(x) mean(x$No.Trade))

And it works fine, but I want to have a new dataframe of the lists of prices at certain times in order to complete more analysis on them.

Russia Must Remove Putin
  • 374,368
  • 89
  • 403
  • 331
aur
  • 33
  • 4
  • 1
    your question is a little vague at the moment. Do you mind updating your question with [these guidelines in mind?](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) – Chase Sep 30 '14 at 15:15
  • `df[which(df$Time==1:00),]` will return only the data from time 1:00. Is this what you are trying to do? As @Chase says, it's a bit unclear... – Joe Sep 30 '14 at 15:21
  • apologies if I wasn't clear. I have a dataframe consisting of variables of Dates, Times and Prices for a certain stock. I wish to extract the Prices at certain times/days to form a new variable. For instance, a variable of all the prices at 01:00, or a variable for all the prices at 12:00. – aur Sep 30 '14 at 17:04
  • The data looks like; Date Time Price 1 02-Jan-96 03:20 387.15 2 02-Jan-96 03:45 387.10 3 02-Jan-96 04:20 387.15 – aur Sep 30 '14 at 17:05

2 Answers2

0

May be you want to create different dataframe objects based on the unique combination of date and time. I would recommend using split and then do the rest of the calculation/analysis in the list using lapply rather than creating individual data.frames in the global environment. 

lst <- split(df["Price"], list(df$Date, df$Time), drop=TRUE)

You can do most of the operations in the lst. For example:

sapply(lst, function(x) mean(x$Price, na.rm=TRUE))
#02-Jan-96.03:20 02-Jan-96.03:25 02-Jan-96.03:45 02-Jan-96.04:20 
 #  366.1500        337.1500        346.4333        353.4833

But, if you need to create individual data.frames

nm1 <- gsub("[[:punct:]]", "", paste("Var", names(lst),sep="_"))
nm1
#[1] "Var02Jan960320" "Var02Jan960325" "Var02Jan960345" "Var02Jan960420"

list2env(setNames(lst, nm1), envir=.GlobalEnv)

Var02Jan960320
  #   Price
 #1 387.15
 #4 345.15

data

 df <- structure(list(Date = c("02-Jan-96", "02-Jan-96", "02-Jan-96", 
 "02-Jan-96", "02-Jan-96", "02-Jan-96", "02-Jan-96", "02-Jan-96", 
 "02-Jan-96"), Time = c("03:20", "03:45", "04:20", "03:20", "03:45", 
 "04:20", "03:25", "03:45", "04:20"), Price = c(387.15, 387.1, 
387.15, 345.15, 325.1, 335.15, 337.15, 327.1, 338.15)), .Names = c("Date", 
"Time", "Price"), class = "data.frame", row.names = c("1", "2", 
"3", "4", "5", "6", "7", "8", "9"))
akrun
  • 874,273
  • 37
  • 540
  • 662
0

You can use the reshape2 package for this.

Taking the data code from akrun

your.data <- structure(list(Date = c("02-Jan-96", "02-Jan-96", "02-Jan-96", 
 "02-Jan-96", "02-Jan-96", "02-Jan-96", "02-Jan-96", "02-Jan-96", 
 "02-Jan-96"), Time = c("03:20", "03:45", "04:20", "03:20", "03:45", 
 "04:20", "03:25", "03:45", "04:20"), Price = c(387.15, 387.1, 
 387.15, 345.15, 325.1, 335.15, 337.15, 327.1, 338.15)), .Names = c("Date", 
 "Time", "Price"), class = "data.frame", row.names = c("1", "2", 
 "3", "4", "5", "6", "7", "8", "9"))

your.data
#       Date  Time  Price
#1 02-Jan-96 03:20 387.15
#2 02-Jan-96 03:45 387.10
#3 02-Jan-96 04:20 387.15
#4 02-Jan-96 03:20 345.15
#5 02-Jan-96 03:45 325.10
#6 02-Jan-96 04:20 335.15
#7 02-Jan-96 03:25 337.15
#8 02-Jan-96 03:45 327.10
#9 02-Jan-96 04:20 338.15

Using dcast()

dcast(your.data, Date ~ Time, mean)
#       Date  03:20  03:25    03:45    04:20
#1 02-Jan-96 366.15 337.15 346.4333 353.4833
zerovar
  • 23
  • 1
  • 4