-1

I have a data set like below:

money   date1    date2
"300"  "10/30 " " 11/1" 
"400"  "10/28 " " 10/31"
"360"  "10/28 " " 10/30"
"440"  "10/25 " " 10/28"
"620"  "10/21 " " 10/28"

I want to extract the days between two dates such as 10/30,10/31,and 11/1 for the first line. In addition, my code should assign a number to each extracted day. This number should be money/(# of days). As an example I would like to obtain 10/30,10/31,and 11/1 and 300/3 (i.e.=100),300/3,300/3 for each one. Does anyone have any idea about this?

mnel
  • 113,303
  • 27
  • 265
  • 254
John Smith
  • 110
  • 2
  • 11

1 Answers1

1

This will give the total for everyday during the time period

data$date1<-as.Date(paste(data$date1,"/2012"),  "%m/%d/%Y")
data$date2<-as.Date(paste(data$date2,"/2012"),  "%m/%d/%Y")
data$perday<-with(data, money/(date2-date1))
period <- as.Date(min(data$date1):max(data$date2), origin = "1970-01-01")
sum <- sapply(period, function(x) sum(data[x >= data$date1 & x <= data$date2, 'perday']))
sumperday<-as.data.frame(period, sum)
cconnell
  • 843
  • 1
  • 10
  • 14
  • Thank you for your code but I don't think that's the solution. I can calculate the number of days between two dates. What I want to do is to extract each days between two dates and assign equal amount of money to each day. The thing is each line does not have equal number of days. Some lines have 3 days while some have 4 days. If I can put the days into a column and amount of money in another column this would solve my problem. I hope this is more clear. This a small part of my hw. Any help will be greatly appreciated. – John Smith Nov 15 '12 at 02:27
  • 2
    @JohnSmith I assume you're aware that without specifying years, you'll never be able to correctly determine the number of days between each day if they happen to occur near the end of February. If that's not a concern, you probably just want to investigate `seq.Date`. – joran Nov 15 '12 at 02:31
  • Good point! I will just use 2012 data. I will investigate my data.. thanks for telling this... – John Smith Nov 15 '12 at 02:39