How to get standardized column for specific rows only?

Question

Possible Duplicate:
Idiomatic R code for partitioning a vector by an index and performing an operation on that partition

Related to How to get column mean for specific rows only?

I am trying to create a new column in my dataframe that scales the "Score" column into sections based off the "Round" column.

Score     Quarter        
98.7      QTR 1 2011
88.6      QTR 1 2011
76.5      QTR 1 2011
93.5      QTR 2 2011
97.7      QTR 2 2011
89.1      QTR 1 2012 
79.4      QTR 1 2012
80.3      QTR 1 2012

Would look like this

Unit Score     Quarter         Scale        
 6    98.7      QTR 1 2011     1.01
 1    88.6      QTR 1 2011     .98 
 3    76.5      QTR 1 2011     .01
 5    93.5      QTR 2 2011     2.0
 6    88.6      QTR 2 2011     2.5
 9    89.1      QTR 1 2012     2.2
 1    79.4      QTR 1 2012     -.09
 3    80.3      QTR 1 2012     -.01
 3    98.7      QTR 1 2011     -2.2

I do not want to standardize the entire column because I want to trend the data and truly see how units did relative to each other quarter to quarter rather than scale(data$Score) which would compare all points to each other regardless of round.

I've tried variants of something like this:

data$Score_Scale <-  with (data, scale(Score), findInterval(QTR, c(-Inf,"2011-01-01","2011-06-30", Inf)), FUN= scale)

Are `Round` and `QTR` used interechangeably? Where is `Date` in your example data? — mnel, Jan 10 '13 at 00:54
Why would I get a downvote? I understand there was a typo in the question but it's fixed now. My question was as valid as the previous one. — KLDavenport, Jan 10 '13 at 01:28

thelatemail · Accepted Answer · 2013-01-10T01:29:18.800

Using ave might be a good option here:

Get your data:

test <- read.csv(textConnection("Score,Quarter
98.7,Round 1 2011
88.6,Round 1 2011
76.5,Round 1 2011
93.5,Round 2 2011
97.7,Round 2 2011
89.1,Round 1 2012
79.4,Round 1 2012
80.3,Round 1 2012"),header=TRUE)

scale the data within each Quarter group:

test$score_scale <- ave(test$Score,test$Quarter,FUN=scale)
test

  Score      Quarter score_scale
1  98.7 Round 1 2011  0.96866054
2  88.6 Round 1 2011  0.05997898
3  76.5 Round 1 2011 -1.02863953
4  93.5 Round 2 2011 -0.70710678
5  97.7 Round 2 2011  0.70710678
6  89.1 Round 1 2012  1.15062301
7  79.4 Round 1 2012 -0.65927589
8  80.3 Round 1 2012 -0.49134712

Just to make it obvious that this works, here are the individual results for each Quarter group:

> as.vector(scale(test$Score[test$Quarter=="Round 1 2011"]))
[1]  0.96866054  0.05997898 -1.02863953
> as.vector(scale(test$Score[test$Quarter=="Round 2 2011"]))
[1] -0.7071068  0.7071068
> as.vector(scale(test$Score[test$Quarter=="Round 1 2012"]))
[1]  1.1506230 -0.6592759 -0.4913471

How to get standardized column for specific rows only?

1 Answers1