arun
2013-Apr-18 19:09 UTC
[R] select and do some calculations/manipulations on certain rows based on conditions in R
Hi,
May be this helps (Assuming that there are only '0's and '1's in
the dataset)
dat1<-read.table(text="
??????? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
1?? 5184??? 0??? 0??? 0??? 0??? 0?? 0?? 0??? 0??? 0??? 1???? 0????? 0?????
0????? 0????? 0????? 0
2?? 6884??? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
3? 11329?? 0??? 0??? 0??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0????? 0?????
0????? 0????? 0
4? 11329?? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0????? 0?????
0????? 0????? 0
",sep="",header=TRUE)
dat1$y<-sapply(seq_len(nrow(dat1)),function(i)
{x1<-na.omit(dat1[i:(i+1),]); 1*any(colSums(x1[,-1])==2)})
?dat1
#???? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 y
#1? 5184? 0? 0? 0? 0? 0? 0? 0? 0? 0? 1?? 0?? 0?? 0?? 0?? 0?? 0 0
#2? 6884? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 1
#3 11329? 0? 0? 0? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 1
#4 11329? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 0
A.K.
________________________________
From: Thu Pham <thurachel at gmail.com>
To: smartpink111 at yahoo.com
Sent: Thursday, April 18, 2013 2:57 PM
Subject: Re: select and do some calculations/manipulations on certain rows based
on conditions in R
Hi,
Thank you for the response. You're absolutely right.
I'm trying to see if any of the X's variable will be repeated for the
next observation pertaining to the same subject(here would be having the same
ID). However, I am only interested if Xi=1 is repeated.
Therefore, Y=0 if each ID has only one observation, i.e. the very next row will
have a different ID# from the one directly above.
Y=1 if any of the Xs will take value=1 for two consecutive rows. However at the
very last observation of each same ID, there is no next row to compare the Xs to
so Y is automatically = 0 for that row.
So in my data I posted Y4=0.
I hope this clarify the issues a little more.
Thanks,
T
On Thu, Apr 18, 2013 at 2:42 PM, <smartpink111 at yahoo.com> wrote:
Hi,>Not sure I understand it correctly.
>For the 3rd row, you are comparing the 3row and 4th row. ?i.e if there is
anywhere 1 on 3rd row and 1 directly below that cell, then the y value will be
1. ?What is the use of the sum of products here? ?If one value is 0 and other is
1, the product should be 0. ?Also, by this method, you are comparing 1st row
against 2nd, 2nd against 3rd, 3rd against 4th. ?So, in the last row, even if
there are all 1's , y should be 0??
>
>It would be better if you provide the expected result column of 'y'.
>Thanks,
>A.K.
>
><quote author='thurachel'>
>I have a data set read into R that look like this:
>? ? ? ? ? ?ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
>1 ? 5184 ? ?0 ? ?0 ? ?0 ? ?0 ? ?0 ? 0 ? 0 ? ?0 ? ?0 ? ?1 ? ? 0 ? ? ?0 ? ? ?0
>0 ? ? ?0 ? ? ?0
>2 ? 6884 ? ?0 ? ?0 ? ?1 ? ?0 ? ?0 ? 1 ? 0 ? ?0 ? ?0 ? ?0 ? ? 0 ? ? ?0 ? ? ?0
>0 ? ? ?0 ? ? ?0
>3 ?11329 ? 0 ? ?0 ? ?0 ? ?0 ? ?0 ? 1 ? 0 ? ?0 ? ?0 ? ?0 ? ? 0 ? ? ?0 ? ? ?0
>0 ? ? ?0 ? ? ?0
>4 ?11329 ? 0 ? ?0 ? ?1 ? ?0 ? ?0 ? 1 ? 0 ? ?0 ? ?0 ? ?0 ? ? 0 ? ? ?0 ? ? ?0
>0 ? ? ?0 ? ? ?0
>>
>
>I want to create a Y column where Y = 1 if there is an observation for the
>same ID right below it and if the Sum of the product of the X's =1 for
the
>two consecutive observations of the same ID. Y=0 otherwise.
>
>So for example, ?Y3=1 because Sum(x0_3*x0_4+x1_3*x1_4+...+x15_3*x15_4)=1.
>
>Can I create program that can do the calculation for Y conditioning on the
>ID (i.e. if the ID changes then Y=0) and second conditioning on the sum of
>the product of the X's for two consecutive rows if the ID condition is
met,
>i.e. ID stays the same?
>
>I am fairly new to R and I don't know if there is a straight forward
>solution to this.
>
>Many thanks for any help/suggestions.
></quote>
>Quoted from:
>http://r.789695.n4.nabble.com/select-and-do-some-calculations-manipulations-on-certain-rows-based-on-conditions-in-R-tp4664633.html
>
>
>_____________________________________
>Sent from http://r.789695.n4.nabble.com
>
>
arun
2013-Apr-30 19:19 UTC
[R] select and do some calculations/manipulations on certain rows based on conditions in R
Hi,
Try this:
dat1<-read.table(text="
??????? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
1?? 5184??? 0??? 0??? 1??? 0??? 0?? 0?? 0??? 0??? 0??? 1???? 0????? 0?????
0????? 0????? 0????? 0
2?? 6884??? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
3?? 6884??? 0??? 0??? 1??? 0??? 0?? 0?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
4?? 6884??? 0??? 0??? 0??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
5? 11329?? 0??? 0??? 0??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0????? 0?????
0????? 0????? 0
6? 11329?? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0????? 0?????
0????? 0????? 0
7? 11340?? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0????? 0?????
0????? 0????? 0
",sep="",header=TRUE)
?dat1$y<-unsplit(lapply(split(dat1,dat1$ID),function(x)
sapply(seq_len(nrow(x)),function(i)
{x1<-na.omit(x[i:(i+1),]);1*any(colSums(x1[,-1])==2)})),dat1$ID)
?dat1
#???? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 y
#1? 5184? 0? 0? 1? 0? 0? 0? 0? 0? 0? 1?? 0?? 0?? 0?? 0?? 0?? 0 0
#2? 6884? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 1
#3? 6884? 0? 0? 1? 0? 0? 0? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 0
#4? 6884? 0? 0? 0? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 0
#5 11329? 0? 0? 0? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 1
#6 11329? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 0
#7 11340? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 0
A.K.
________________________________
From: Thu Pham <thurachel at gmail.com>
To: arun <smartpink111 at yahoo.com>
Sent: Tuesday, April 30, 2013 3:13 PM
Subject: Re: select and do some calculations/manipulations on certain rows based
on conditions in R
Hi,
The column for y would be
y
0
1
0
0
1
0
0
On Tue, Apr 30, 2013 at 3:10 PM, arun <smartpink111 at yahoo.com> wrote:
>
>
>HI,
>Just a doubt:
>Suppose the data is this:
>
>
>dat1<-read.table(text="
>??????? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
>1?? 5184??? 0??? 0??? 1??? 0??? 0?? 0?? 0??? 0??? 0??? 1???? 0????? 0?????
0????? 0????? 0????? 0
>2?? 6884??? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
>3?? 6884??? 0??? 0??? 1??? 0??? 0?? 0?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
>
>4?? 6884??? 0??? 0??? 0??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
>5? 11329?? 0??? 0??? 0??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
>6? 11329?? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
>7? 11340?? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0????? 0?????
0????? 0????? 0????? 0
>",sep="",header=TRUE)
>
>
>Then, could you confirm the y column values for this:
>
>
>
>
>________________________________
>From: Thu Pham <thurachel at gmail.com>
>To: arun <smartpink111 at yahoo.com>
>Sent: Tuesday, April 30, 2013 2:31 PM
>
>Subject: Re: select and do some calculations/manipulations on certain rows
based on conditions in R
>
>
>
>Hello,
>
>Thanks so much for your help. However, I would expect on the second line for
ID 6884, y should be 0 because the ID change.
>
>
>For ID 11329, it is shown as I expected: the first observation for Y=1
because one of x's remain 1 going from one observation to the next. Also y=0
for? the last observation of the same ID because no observation follows that.
>
>
>I'm very new to R and I have trouble interpret your code. Perhaps if I
understand what you did, I can fix it.
>
>P/S: there is only 0 or 1 value for the X's variables
>
>
>
>
>On Thu, Apr 18, 2013 at 3:09 PM, arun <smartpink111 at yahoo.com>
wrote:
>
>Hi,
>>May be this helps (Assuming that there are only '0's and
'1's in the dataset)
>>
>>dat1<-read.table(text="
>>
>>??????? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
>>1?? 5184??? 0??? 0??? 0??? 0??? 0?? 0?? 0??? 0??? 0??? 1???? 0?????
0????? 0????? 0????? 0????? 0
>>2?? 6884??? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0?????
0????? 0????? 0????? 0????? 0
>>3? 11329?? 0??? 0??? 0??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0?????
0????? 0????? 0????? 0????? 0
>>4? 11329?? 0??? 0??? 1??? 0??? 0?? 1?? 0??? 0??? 0??? 0???? 0?????
0????? 0????? 0????? 0????? 0
>>",sep="",header=TRUE)
>>dat1$y<-sapply(seq_len(nrow(dat1)),function(i)
{x1<-na.omit(dat1[i:(i+1),]); 1*any(colSums(x1[,-1])==2)})
>>?dat1
>>#???? ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15 y
>>#1? 5184? 0? 0? 0? 0? 0? 0? 0? 0? 0? 1?? 0?? 0?? 0?? 0?? 0?? 0 0
>>#2? 6884? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 1
>>#3 11329? 0? 0? 0? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 1
>>#4 11329? 0? 0? 1? 0? 0? 1? 0? 0? 0? 0?? 0?? 0?? 0?? 0?? 0?? 0 0
>>A.K.
>>
>>
>>
>>________________________________
>>?From: Thu Pham <thurachel at gmail.com>
>>To: smartpink111 at yahoo.com
>>Sent: Thursday, April 18, 2013 2:57 PM
>>Subject: Re: select and do some calculations/manipulations on certain
rows based on conditions in R
>>
>>
>>
>>
>>Hi,
>>
>>Thank you for the response. You're absolutely right.
>>
>>I'm trying to see if any of the X's variable will be repeated
for the next observation pertaining to the same subject(here would be having the
same ID). However, I am only interested if Xi=1 is repeated.
>>
>>
>>Therefore, Y=0 if each ID has only one observation, i.e. the very next
row will have a different ID# from the one directly above.
>>
>>
>>Y=1 if any of the Xs will take value=1 for two consecutive rows. However
at the very last observation of each same ID, there is no next row to compare
the Xs to so Y is automatically = 0 for that row.
>>
>>
>>So in my data I posted Y4=0.
>>
>>
>>
>>I hope this clarify the issues a little more.
>>
>>
>>Thanks,
>>T
>>
>>
>>
>>
>>
>>On Thu, Apr 18, 2013 at 2:42 PM, <smartpink111 at yahoo.com>
wrote:
>>
>>Hi,
>>>Not sure I understand it correctly.
>>>For the 3rd row, you are comparing the 3row and 4th row. ?i.e if
there is anywhere 1 on 3rd row and 1 directly below that cell, then the y value
will be 1. ?What is the use of the sum of products here? ?If one value is 0 and
other is 1, the product should be 0. ?Also, by this method, you are comparing
1st row against 2nd, 2nd against 3rd, 3rd against 4th. ?So, in the last row,
even if there are all 1's , y should be 0??
>>>
>>>It would be better if you provide the expected result column of
'y'.
>>>Thanks,
>>>A.K.
>>>
>>><quote author='thurachel'>
>>>I have a data set read into R that look like this:
>>>? ? ? ? ? ?ID X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 X15
>>>1 ? 5184 ? ?0 ? ?0 ? ?0 ? ?0 ? ?0 ? 0 ? 0 ? ?0 ? ?0 ? ?1 ? ? 0 ? ?
?0 ? ? ?0
>>>0 ? ? ?0 ? ? ?0
>>>2 ? 6884 ? ?0 ? ?0 ? ?1 ? ?0 ? ?0 ? 1 ? 0 ? ?0 ? ?0 ? ?0 ? ? 0 ? ?
?0 ? ? ?0
>>>0 ? ? ?0 ? ? ?0
>>>3 ?11329 ? 0 ? ?0 ? ?0 ? ?0 ? ?0 ? 1 ? 0 ? ?0 ? ?0 ? ?0 ? ? 0 ? ? ?0
? ? ?0
>>>0 ? ? ?0 ? ? ?0
>>>4 ?11329 ? 0 ? ?0 ? ?1 ? ?0 ? ?0 ? 1 ? 0 ? ?0 ? ?0 ? ?0 ? ? 0 ? ? ?0
? ? ?0
>>>0 ? ? ?0 ? ? ?0
>>>>
>>>
>>>I want to create a Y column where Y = 1 if there is an observation
for the
>>>same ID right below it and if the Sum of the product of the X's
=1 for the
>>>two consecutive observations of the same ID. Y=0 otherwise.
>>>
>>>So for example, ?Y3=1 because
Sum(x0_3*x0_4+x1_3*x1_4+...+x15_3*x15_4)=1.
>>>
>>>Can I create program that can do the calculation for Y conditioning
on the
>>>ID (i.e. if the ID changes then Y=0) and second conditioning on the
sum of
>>>the product of the X's for two consecutive rows if the ID
condition is met,
>>>i.e. ID stays the same?
>>>
>>>I am fairly new to R and I don't know if there is a straight
forward
>>>solution to this.
>>>
>>>Many thanks for any help/suggestions.
>>></quote>
>>>Quoted from:
>>>http://r.789695.n4.nabble.com/select-and-do-some-calculations-manipulations-on-certain-rows-based-on-conditions-in-R-tp4664633.html
>>>
>>>
>>>_____________________________________
>>>Sent from http://r.789695.n4.nabble.com
>>>
>>>
>>
>