[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