[R] Obtaining data from a different row of data frame

arun smartpink111 at yahoo.com
Sun Sep 22 06:07:47 CEST 2013



Hi,
May be you can try this:
Change the 

df1<- structure(list(Dates = structure(c(13151, 13152, 13153, 13154,
 13157, 13158, 13159, 13160, 13161, 13164), class = "Date"), P1 = c(10,
 13, 16, 19, 22, 25, 28, 31, 34, 37), P2 = c(100, 102, 104, 106,
 108, 110, 112, 114, 116, 118), P3 = c(90, 94, 98, 102, 106, 110,
 114, 118, 122, 126), P4 = c(70, 75, 80, 85, 90, 95, 100, 105,
 110, 115), OF1 = c(3, 3, 4, 5, 2, 2, 2, 1, 1, 5), OF2 = c(5,
 3, 4, 2, 1, 2, 2, 1, 1, 0), OF3 = c(4, 3, 4, 1, 3, 2, 2, 1, 1,
 0), OF4 = c(3, 5, 4, 2, 3, 1, 2, 1, 1, 0)), .Names = c("Dates",
 "P1", "P2", "P3", "P4", "OF1", "OF2", "OF3", "OF4"), row.names = c(NA,
 -10L), class = "data.frame")
df1$OF2[9]<-4

df2<- df1
 df2[,10:13]<- NA
colnames(df2)[10:13]<- paste0("newPrice",1:4)

##your code

for(j in 2:5) {
 df2[j+8] = df2[df2[,j+4] + row(df2)[,j], j]
 }
 vec1<- 5:1 ##change values according to the range of actual values in your rows
 vec2<- 6:10 ##change accordingly
df1[vec2,grep("OF",colnames(df1))]<- t(sapply(seq_along(vec1),function(i) {x1<-as.matrix(df1[vec2[i],grep("OF",colnames(df1))]); x1[x1>vec1[i]]<-NA; x1}))

indx1<- unlist(df1[,grep("OF",colnames(df1))],use.names=FALSE)
val1<- unlist(df1[,grep("P",colnames(df1))],use.names=FALSE)
 df1[,10:13]<- val1[indx1+seq_along(indx1)]
 colnames(df1)[10:13]<- colnames(df2)[10:13]
identical(df1[,10:13],df2[,10:13])
#[1] TRUE
A.K.

________________________________
From: Ira Sharenow <irasharenow100 at yahoo.com>
To: arun <smartpink111 at yahoo.com> 
Sent: Saturday, September 21, 2013 11:36 PM
Subject: Re: [R] Obtaining data from a different row of data frame



Arun,

Yes, I definitely want NA, and I certainly do not want to pick off values from a different stock.

I set up the example the way I did because I was more concerned about finding the values than dealing with these bad cases. I figured I could work them out later and with my strategy I get the NA values.

I had not considered your approach.

Ira 
On 9/21/2013 8:30 PM, arun wrote:

Hi Ira, This info was not provided before.  In fact, I wanted to ask about this in cases where the last row entries are not 0.  So, you wanted the newPrices to be NA's for the corresponding rows, right?? ________________________________
From: Ira Sharenow <irasharenow100 at yahoo.com> To: arun <smartpink111 at yahoo.com> Sent: Saturday, September 21, 2013 11:24 PM
Subject: Re: [R] Obtaining data from a different row of data frame Arun, Thanks. I appreciate the details. I am learning a lot. But there may be a bug in your code. This information is gathered on an ongoing basis. So on September 1, 2013 a prediction for A1 stock may have been made and the prediction date is December 15, 2013, which has not yet occurred. My method produces NA. For example go to  OF4 and change the last entry from 0 to 6. Ira 
># change OF1[10] = 5 <------
df1<- structure(list(Dates = structure(c(13151, 13152, 
>    13153, 13154, 
+ 13157, 13158, 13159, 13160, 13161, 13164), class = "Date"), P1 =
    c(10, 
+ 13, 16, 19, 22, 25, 28, 31, 34, 37), P2 = c(100, 102, 104, 106, 
+ 108, 110, 112, 114, 116, 118), P3 = c(90, 94, 98, 102, 106, 110, 
+ 114, 118, 122, 126), P4 = c(70, 75, 80, 85, 90, 95, 100, 105, 
+ 110, 115), OF1 = c(3, 3, 4, 5, 2, 2, 2, 1, 1, 5), OF2 = c(5, 
+ 3, 4, 2, 1, 2, 2, 1, 1, 0), OF3 = c(4, 3, 4, 1, 3, 2, 2, 1, 1, 
+ 0), OF4 = c(3, 5, 4, 2, 3, 1, 2, 1, 1, 0)), .Names = c("Dates", 
+ "P1", "P2", "P3", "P4", "OF1", "OF2", "OF3", "OF4"), row.names =
    c(NA, 
+ -10L), class = "data.frame") 
>#Splitting the code grep("OF",colnames(df1)) #gives the column numbers  having "OF" 
>    as column names
[1] 6 7 8 9 
>[1] 6 7 8 9 
>Error: unexpected '[' in "[" 
>#Subset those columns   df1[,grep("OF",colnames(df1))] 
>   OF1 OF2 OF3 OF4
1    3   5   4   3
2    3   3   3   5
3    4   4   4   4
4    5   2   1   2
5    2   1   3   3
6    2   2   2   1
7    2   2   2   2
8    1   1   1   1
9    1   1   1   1
10  5   0   0   0 
>#unlist those columns to create a vector
  indx1<- 
>    unlist(df1[,grep("OF",colnames(df1))],use.names=FALSE) 
>indx1 
> [1] 3 3 4 5 2 2 2 1 1 5 5 3 4 2 1 2 2 1 1 0 4 3 4 1 3 2 2 1 1 0 3 5
    4 2 3 1 2 1 1 0 
>#Same steps done with columns having names "P"
val1<- unlist(df1[,grep("P",colnames(df1))],use.names=FALSE)
val1 
> [1]  10  13  16  19  22  25  28  31  34  37 100 102 104 106 108 110
    112 114 116 118  90  94  98 102 106
[26] 110 114 118 122 126  70  75  80  85  90  95 100 105 110 115 
>  val1[indx1+seq_along(indx1)] 
> [1]  19  22  28  34  28  31  34  34  37 108 110 108 112 110 110 114
    116 116 118 118 106 106 114 106 118
[26] 118 122 122 126 126  85 100 100  95 105 100 110 110 115 115 
>df1[,10:13]<- val1[indx1+seq_along(indx1)]
  df1 
>        Dates P1  P2  P3  P4 OF1 OF2 OF3 OF4 V10 V11 V12 V13
1  2006-01-03 10 100  90  70   3   5   4   3  19 110 106  85
2  2006-01-04 13 102  94  75   3   3   3   5  22 108 106 100
3  2006-01-05 16 104  98  80   4   4   4   4  28 112 114 100
4  2006-01-06 19 106 102  85   5   2   1   2  34 110 106  95
5  2006-01-09 22 108 106  90   2   1   3   3  28 110 118 105
6  2006-01-10 25 110 110  95   2   2   2   1  31 114 118 100
7  2006-01-11 28 112 114 100   2   2   2   2  34 116 122 110
8  2006-01-12 31 114 118 105   1   1   1   1  34 116 122 110
9  2006-01-13 34 116 122 110   1   1   1   1  37 118 126 115
10 2006-01-16 37 118 126 115   5   0   0   0 108 118 126 115 On 9/21/2013 7:47 PM, arun wrote: Hi Ira, The code I used was based on how you calculated the values from your previous explanation. Let us, take ##dput  df1<- structure(list(Dates = structure(c(13151, 13152, 13153, 13154, 
13157, 13158, 13159, 13160, 13161, 13164), class = "Date"), P1 = c(10, 
13, 16, 19, 22, 25, 28, 31, 34, 37), P2 = c(100, 102, 104, 106, 
108, 110, 112, 114, 116, 118), P3 = c(90, 94, 98, 102, 106, 110, 
114, 118, 122, 126), P4 = c(70, 75, 80, 85, 90, 95, 100, 105, 
110, 115), OF1 = c(3, 3, 4, 5, 2, 2, 2, 1, 1, 0), OF2 = c(5, 
3, 4, 2, 1, 2, 2, 1, 1, 0), OF3 = c(4, 3, 4, 1, 3, 2, 2, 1, 1, 
0), OF4 = c(3, 5, 4, 2, 3, 1, 2, 1, 1, 0)), .Names = c("Dates", 
"P1", "P2", "P3", "P4", "OF1", "OF2", "OF3", "OF4"), row.names = c(NA, 
-10L), class = "data.frame") #Splitting the code grep("OF",colnames(df1)) #gives the column numbers  having "OF" as column names
[1] 6 7 8 9 #Subset those columns  df1[,grep("OF",colnames(df1))]
   OF1 OF2 OF3 OF4
1    3   5   4   3
2    3   3   3   5
3    4   4   4   4
4    5   2   1   2
5    2   1   3   3
6    2   2   2   1
7    2   2   2   2
8    1   1   1   1
9    1   1   1   1
10   0   0   0   0 #unlist those columns to create a vector
 indx1<- unlist(df1[,grep("OF",colnames(df1))],use.names=FALSE)
indx1
# [1] 3 3 4 5 2 2 2 1 1 0 5 3 4 2 1 2 2 1 1 0 4 3 4 1 3 2 2 1 1 0 3 5 4 2 3 1 2 1
#[39] 1 0 #Same steps done with columns having names "P"
val1<- unlist(df1[,grep("P",colnames(df1))],use.names=FALSE)
val1
# [1]  10  13  16  19  22  25  28  31  34  37 100 102 104 106 108 110 112 114 116
#[20] 118  90  94  98 102 106 110 114 118 122 126  70  75  80  85  90  95 100 105
#[39] 110 115
indx1+seq_along(indx1)  #get the index 
# [1]  4  5  7  9  7  8  9  9 10 10 16 15 17 16 16 18 19 19 20 20 25 25 27 25 28
#[26] 28 29 29 30 30 34 37 37 36 38 37 39 39 40 40
 val1[indx1+seq_along(indx1)]
# [1]  19  22  28  34  28  31  34  34  37  37 110 108 112 110 110 114 116 116 118
#[20] 118 106 106 114 106 118 118 122 122 126 126  85 100 100  95 105 100 110 110
#[39] 115 115 df1[,10:13]<- val1[indx1+seq_along(indx1)]
 df1
        Dates P1  P2  P3  P4 OF1 OF2 OF3 OF4 V10 V11 V12 V13
1  2006-01-03 10 100  90  70   3   5   4   3  19 110 106  85
2  2006-01-04 13 102  94  75   3   3   3   5  22 108 106 100
3  2006-01-05 16 104  98  80   4   4   4   4  28 112 114 100
4  2006-01-06 19 106 102  85   5   2   1   2  34 110 106  95
5  2006-01-09 22 108 106  90   2   1   3   3  28 110 118 105
6  2006-01-10 25 110 110  95   2   2   2   1  31 114 118 100
7  2006-01-11 28 112 114 100   2   2   2   2  34 116 122 110
8  2006-01-12 31 114 118 105   1   1   1   1  34 116 122 110
9  2006-01-13 34 116 122 110   1   1   1   1  37 118 126 115
10 2006-01-16 37 118 126 115   0   0   0   0  37 118 126 115 Hope it helps. A.K.          



More information about the R-help mailing list