Hi all, I have a data set with time interval and depending on the interval I want to create 5 more variables . Sample data below obs, Start, End 1,2/1/2015, 1/1/2017 2,4/11/2010, 1/1/2011 3,1/4/2006, 5/3/2007 4,10/1/2007, 1/1/2008 5,6/1/2011, 1/1/2012 6,10/15/2004,12/1/2004 First, I want get interval between the start date and end dates (End-start). obs, Start , end, datediff 1,2/1/2015, 1/1/2017, 700 2,4/11/2010, 1/1/2011, 265 3,1/4/2006, 5/3/2007, 484 4,10/1/2007, 1/1/2008, 92 5,6/1/2011, 1/1/2012, 214 6,10/15/2004,12/1/2004,47 Second. I want create 5 more variables t1, t2, t3, t4 and t5 The value of each variable is defined as follows if datediff < 100 then t1=1, t2=t3=t4=t5=-1. if datediff >= 100 and < 200 then t1=0, t2=1,t3=t4=t5=-1, if datediff >= 200 and < 300 then t1=0, t2=0,t3=1,t4=t5=-1, if datediff >= 300 and < 400 then t1=0, t2=0,t3=0,t4=1,t5=-1, if datediff >= 400 and < 500 then t1=0, t2=0,t3=0,t4=0,t5=1, if datediff >= 500 then t1=0, t2=0,t3=0,t4=0,t5=0 The complete out put looks like as follow. obs, start, end, datediff, t1, t2, t3, t4, t5 1, 2/1/2015, 1/1/2017, 700, 0, 0, 0, 0, 0 2, 4/11/2010, 1/1/2011, 265, 0, 0, 1, -1, -1 3, 1/4/2006, 5/3/2007, 484, 0, 0, 0, 0, 1 4, 10/1/2007, 1/1/2008, 92, 1, -1, -1,-1, -1 5 , 6/1/2011, 1/1/2012, 214, 0, 0, 1,-1, -1 6, 10/15/2004, 12/1/2004, 47, 1, -1, -1, -1, -1 Thank you. [[alternative HTML version deleted]]
You do understand that this is not the "do my work for me" mailing list, don't you? You should be asking questions like "why doesn't my code do one of these if conditions?" so that you know HOW to write the rest of them yourself. In addition you have been posting here long enough to know that your use of HTML format means we get a scrambled version of what you think you posted. Go do your homework this time (follow the Posting Guide) and come back with a question that helps us help you instead of asking for us to provide you with free work. -- Sent from my phone. Please excuse my brevity. On June 2, 2017 8:49:11 PM PDT, Val <valkremk at gmail.com> wrote:>Hi all, > >I have a data set with time interval and depending on the interval I >want >to create 5 more variables . Sample data below > >obs, Start, End >1,2/1/2015, 1/1/2017 >2,4/11/2010, 1/1/2011 >3,1/4/2006, 5/3/2007 >4,10/1/2007, 1/1/2008 >5,6/1/2011, 1/1/2012 >6,10/15/2004,12/1/2004 > >First, I want get interval between the start date and end dates >(End-start). > > obs, Start , end, datediff >1,2/1/2015, 1/1/2017, 700 >2,4/11/2010, 1/1/2011, 265 >3,1/4/2006, 5/3/2007, 484 >4,10/1/2007, 1/1/2008, 92 >5,6/1/2011, 1/1/2012, 214 >6,10/15/2004,12/1/2004,47 > >Second. I want create 5 more variables t1, t2, t3, t4 and t5 >The value of each variable is defined as follows >if datediff < 100 then t1=1, t2=t3=t4=t5=-1. >if datediff >= 100 and < 200 then t1=0, t2=1,t3=t4=t5=-1, >if datediff >= 200 and < 300 then t1=0, t2=0,t3=1,t4=t5=-1, >if datediff >= 300 and < 400 then t1=0, t2=0,t3=0,t4=1,t5=-1, >if datediff >= 400 and < 500 then t1=0, t2=0,t3=0,t4=0,t5=1, >if datediff >= 500 then t1=0, t2=0,t3=0,t4=0,t5=0 > >The complete out put looks like as follow. >obs, start, end, datediff, t1, t2, t3, t4, t5 >1, 2/1/2015, 1/1/2017, 700, 0, 0, 0, 0, 0 >2, 4/11/2010, 1/1/2011, 265, 0, 0, 1, -1, -1 >3, 1/4/2006, 5/3/2007, 484, 0, 0, 0, 0, 1 >4, 10/1/2007, 1/1/2008, 92, 1, -1, -1,-1, -1 >5 , 6/1/2011, 1/1/2012, 214, 0, 0, 1,-1, -1 >6, 10/15/2004, 12/1/2004, 47, 1, -1, -1, -1, -1 > >Thank you. > > [[alternative HTML version deleted]] > >______________________________________________ >R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >https://stat.ethz.ch/mailman/listinfo/r-help >PLEASE do read the posting guide >http://www.R-project.org/posting-guide.html >and provide commented, minimal, self-contained, reproducible code.
Ii is difficult to provide useful help, because you have failed to read and follow the posting guide. In particular: 1. Plain text, not HTML. 2. Use dput() or provide code to create your example. Text printouts such as that which you gave require some work to wrangle into into an example that we can test. Specifically: 3. Have you gone through any R tutorials?-- it sure doesn't look like it. We do expect some effort to learn R before posting. 4. What is the format of your date columns? character, factors, POSIX,...? See ?date-time for details. Note particularly the "difftime" link to obtain intervals. 5. ?ifelse for vectorized conditionals. Also, you might want to explain the context of what you are trying to do. I strongly suspect you shouldn't be doing it at all, but that is just a guess. Be sure to cc your reply to the list, not just to me. Cheers, Bert Bert Gunter "The trouble with having an open mind is that people keep coming along and sticking things into it." -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) On Fri, Jun 2, 2017 at 8:49 PM, Val <valkremk at gmail.com> wrote:> Hi all, > > I have a data set with time interval and depending on the interval I want > to create 5 more variables . Sample data below > > obs, Start, End > 1,2/1/2015, 1/1/2017 > 2,4/11/2010, 1/1/2011 > 3,1/4/2006, 5/3/2007 > 4,10/1/2007, 1/1/2008 > 5,6/1/2011, 1/1/2012 > 6,10/15/2004,12/1/2004 > > First, I want get interval between the start date and end dates > (End-start). > > obs, Start , end, datediff > 1,2/1/2015, 1/1/2017, 700 > 2,4/11/2010, 1/1/2011, 265 > 3,1/4/2006, 5/3/2007, 484 > 4,10/1/2007, 1/1/2008, 92 > 5,6/1/2011, 1/1/2012, 214 > 6,10/15/2004,12/1/2004,47 > > Second. I want create 5 more variables t1, t2, t3, t4 and t5 > The value of each variable is defined as follows > if datediff < 100 then t1=1, t2=t3=t4=t5=-1. > if datediff >= 100 and < 200 then t1=0, t2=1,t3=t4=t5=-1, > if datediff >= 200 and < 300 then t1=0, t2=0,t3=1,t4=t5=-1, > if datediff >= 300 and < 400 then t1=0, t2=0,t3=0,t4=1,t5=-1, > if datediff >= 400 and < 500 then t1=0, t2=0,t3=0,t4=0,t5=1, > if datediff >= 500 then t1=0, t2=0,t3=0,t4=0,t5=0 > > The complete out put looks like as follow. > obs, start, end, datediff, t1, t2, t3, t4, t5 > 1, 2/1/2015, 1/1/2017, 700, 0, 0, 0, 0, 0 > 2, 4/11/2010, 1/1/2011, 265, 0, 0, 1, -1, -1 > 3, 1/4/2006, 5/3/2007, 484, 0, 0, 0, 0, 1 > 4, 10/1/2007, 1/1/2008, 92, 1, -1, -1,-1, -1 > 5 , 6/1/2011, 1/1/2012, 214, 0, 0, 1,-1, -1 > 6, 10/15/2004, 12/1/2004, 47, 1, -1, -1, -1, -1 > > Thank you. > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
As Bert says/implies, "some assembly is required". as.Date() is your friend and you can basically just subtract those to get differences. Also, it seems to me that you are really trying to convert differences to a factor, and then represent the factor using particular dummy variables for successive differences, for use in a linear model of sorts. A combination of cut() and a contrast matrix (see contrasts<-) might be a better structured approach. -pd> On 3 Jun 2017, at 07:13 , Bert Gunter <bgunter.4567 at gmail.com> wrote: > > Ii is difficult to provide useful help, because you have failed to > read and follow the posting guide. In particular: > > 1. Plain text, not HTML. > 2. Use dput() or provide code to create your example. Text printouts > such as that which you gave require some work to wrangle into into an > example that we can test. > > Specifically: > > 3. Have you gone through any R tutorials?-- it sure doesn't look like > it. We do expect some effort to learn R before posting. > > 4. What is the format of your date columns? character, factors, > POSIX,...? See ?date-time for details. Note particularly the > "difftime" link to obtain intervals. > > 5. ?ifelse for vectorized conditionals. > > Also, you might want to explain the context of what you are trying to > do. I strongly suspect you shouldn't be doing it at all, but that is > just a guess. > > Be sure to cc your reply to the list, not just to me. > > Cheers, > Bert > > > Bert Gunter > > "The trouble with having an open mind is that people keep coming along > and sticking things into it." > -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip ) > > > On Fri, Jun 2, 2017 at 8:49 PM, Val <valkremk at gmail.com> wrote: >> Hi all, >> >> I have a data set with time interval and depending on the interval I want >> to create 5 more variables . Sample data below >> >> obs, Start, End >> 1,2/1/2015, 1/1/2017 >> 2,4/11/2010, 1/1/2011 >> 3,1/4/2006, 5/3/2007 >> 4,10/1/2007, 1/1/2008 >> 5,6/1/2011, 1/1/2012 >> 6,10/15/2004,12/1/2004 >> >> First, I want get interval between the start date and end dates >> (End-start). >> >> obs, Start , end, datediff >> 1,2/1/2015, 1/1/2017, 700 >> 2,4/11/2010, 1/1/2011, 265 >> 3,1/4/2006, 5/3/2007, 484 >> 4,10/1/2007, 1/1/2008, 92 >> 5,6/1/2011, 1/1/2012, 214 >> 6,10/15/2004,12/1/2004,47 >> >> Second. I want create 5 more variables t1, t2, t3, t4 and t5 >> The value of each variable is defined as follows >> if datediff < 100 then t1=1, t2=t3=t4=t5=-1. >> if datediff >= 100 and < 200 then t1=0, t2=1,t3=t4=t5=-1, >> if datediff >= 200 and < 300 then t1=0, t2=0,t3=1,t4=t5=-1, >> if datediff >= 300 and < 400 then t1=0, t2=0,t3=0,t4=1,t5=-1, >> if datediff >= 400 and < 500 then t1=0, t2=0,t3=0,t4=0,t5=1, >> if datediff >= 500 then t1=0, t2=0,t3=0,t4=0,t5=0 >> >> The complete out put looks like as follow. >> obs, start, end, datediff, t1, t2, t3, t4, t5 >> 1, 2/1/2015, 1/1/2017, 700, 0, 0, 0, 0, 0 >> 2, 4/11/2010, 1/1/2011, 265, 0, 0, 1, -1, -1 >> 3, 1/4/2006, 5/3/2007, 484, 0, 0, 0, 0, 1 >> 4, 10/1/2007, 1/1/2008, 92, 1, -1, -1,-1, -1 >> 5 , 6/1/2011, 1/1/2012, 214, 0, 0, 1,-1, -1 >> 6, 10/15/2004, 12/1/2004, 47, 1, -1, -1, -1, -1 >> >> Thank you. >> >> [[alternative HTML version deleted]] >> >> ______________________________________________ >> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see >> https://stat.ethz.ch/mailman/listinfo/r-help >> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html >> and provide commented, minimal, self-contained, reproducible code. > > ______________________________________________ > R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.-- Peter Dalgaard, Professor, Center for Statistics, Copenhagen Business School Solbjerg Plads 3, 2000 Frederiksberg, Denmark Phone: (+45)38153501 Office: A 4.23 Email: pd.mes at cbs.dk Priv: PDalgd at gmail.com
Thank you all for the useful suggestion. I did some of my homework.
library(data.table)
DFM <- read.table(header=TRUE, text='obs start end
1 2/1/2015 1/1/2017
2 4/11/2010 1/1/2011
3 1/4/2006 5/3/2007
4 10/1/2007 1/1/2008
5 6/1/2011 1/1/2012
6 10/5/2004 12/1/2004',stringsAsFactors = FALSE)
DFM
DFM$D =as.numeric(difftime(as.Date(DFM$end,format="%m/%d/%Y"),
as.Date(DFM$start,format="%m/%d/%Y"), units = "days"))
DFM
output.
obs start end D
1 1 2/1/2015 1/1/2017 700
2 2 4/11/2010 1/1/2011 265
3 3 1/4/2006 5/3/2007 484
4 4 10/1/2007 1/1/2008 92
5 5 6/1/2011 1/1/2012 214
6 6 10/5/2004 12/1/2004 57
My problem is how do I get the other new variables
obs start end D t1,t2,t3,t4, t5
1, 2/1/2015, 1/1/2017, 700,0,0,0,0,0
2, 4/11/2010, 1/1/2011, 265,0,0,1,-1,-1
3, 1/4/2006, 5/3/2007, 484,0,0,0,0,1
4, 10/1/2007, 1/1/2008, 92,1,-1,-1,-1,-1
5, 6/1/2011, 1/1/2012, 214,0,0,1,-1,-1
6, 10/15/2004,12/1/2004,47,1,-1,-1,-1,-1
Thank you again.
On Sat, Jun 3, 2017 at 12:13 AM, Bert Gunter <bgunter.4567 at gmail.com>
wrote:> Ii is difficult to provide useful help, because you have failed to
> read and follow the posting guide. In particular:
>
> 1. Plain text, not HTML.
> 2. Use dput() or provide code to create your example. Text printouts
> such as that which you gave require some work to wrangle into into an
> example that we can test.
>
> Specifically:
>
> 3. Have you gone through any R tutorials?-- it sure doesn't look like
> it. We do expect some effort to learn R before posting.
>
> 4. What is the format of your date columns? character, factors,
> POSIX,...? See ?date-time for details. Note particularly the
> "difftime" link to obtain intervals.
>
> 5. ?ifelse for vectorized conditionals.
>
> Also, you might want to explain the context of what you are trying to
> do. I strongly suspect you shouldn't be doing it at all, but that is
> just a guess.
>
> Be sure to cc your reply to the list, not just to me.
>
> Cheers,
> Bert
>
>
> Bert Gunter
>
> "The trouble with having an open mind is that people keep coming along
> and sticking things into it."
> -- Opus (aka Berkeley Breathed in his "Bloom County" comic strip
)
>
>
> On Fri, Jun 2, 2017 at 8:49 PM, Val <valkremk at gmail.com> wrote:
>> Hi all,
>>
>> I have a data set with time interval and depending on the interval I
want
>> to create 5 more variables . Sample data below
>>
>> obs, Start, End
>> 1,2/1/2015, 1/1/2017
>> 2,4/11/2010, 1/1/2011
>> 3,1/4/2006, 5/3/2007
>> 4,10/1/2007, 1/1/2008
>> 5,6/1/2011, 1/1/2012
>> 6,10/15/2004,12/1/2004
>>
>> First, I want get interval between the start date and end dates
>> (End-start).
>>
>> obs, Start , end, datediff
>> 1,2/1/2015, 1/1/2017, 700
>> 2,4/11/2010, 1/1/2011, 265
>> 3,1/4/2006, 5/3/2007, 484
>> 4,10/1/2007, 1/1/2008, 92
>> 5,6/1/2011, 1/1/2012, 214
>> 6,10/15/2004,12/1/2004,47
>>
>> Second. I want create 5 more variables t1, t2, t3, t4 and t5
>> The value of each variable is defined as follows
>> if datediff < 100 then t1=1, t2=t3=t4=t5=-1.
>> if datediff >= 100 and < 200 then t1=0, t2=1,t3=t4=t5=-1,
>> if datediff >= 200 and < 300 then t1=0, t2=0,t3=1,t4=t5=-1,
>> if datediff >= 300 and < 400 then t1=0, t2=0,t3=0,t4=1,t5=-1,
>> if datediff >= 400 and < 500 then t1=0, t2=0,t3=0,t4=0,t5=1,
>> if datediff >= 500 then t1=0, t2=0,t3=0,t4=0,t5=0
>>
>> The complete out put looks like as follow.
>> obs, start, end, datediff, t1, t2, t3, t4, t5
>> 1, 2/1/2015, 1/1/2017, 700, 0, 0, 0, 0, 0
>> 2, 4/11/2010, 1/1/2011, 265, 0, 0, 1, -1, -1
>> 3, 1/4/2006, 5/3/2007, 484, 0, 0, 0, 0, 1
>> 4, 10/1/2007, 1/1/2008, 92, 1, -1, -1,-1, -1
>> 5 , 6/1/2011, 1/1/2012, 214, 0, 0, 1,-1, -1
>> 6, 10/15/2004, 12/1/2004, 47, 1, -1, -1, -1, -1
>>
>> Thank you.
>>
>> [[alternative HTML version deleted]]
>>
>> ______________________________________________
>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>> https://stat.ethz.ch/mailman/listinfo/r-help
>> PLEASE do read the posting guide
http://www.R-project.org/posting-guide.html
>> and provide commented, minimal, self-contained, reproducible code.