Create forecast for subsets of dataframe and append to original file

Question

I am using R 3.3.2.

I would like to predict scores of institutions for various subrankings based on their scores in previous years. Then I need to add these predicted scores as new rows to the original dataframe. My input is a csv file

I want to use the least squares linear model and found that "lm" and "predict" does exactly what I need.

I know this a pretty beginner question, but hope someone can help me. Please see below the data and code with two solutions I've started.

score<-c(63.6,  60.3,   60.4,   53.4,   46.5,   65.8,   45.8,   65.9,   
44.9,   60, 83.5,   81.7,   81.2,   78.8,   83.3,   79.4,   83.2,   77.3,   
79.4)

year<-c(2013,   2014,   2015,   2016,   2014,   2014,   2015,   2015,   
2016,   2016,   2011,   2012,   2013,   2014,   2014,   2015,   2015,   
2016,   2016)

institution<-c(1422,    1422,   1422,   1422,   1384,   1422,   1384,   
1422,   1384,   1422,   1384,   1384,   1384,   1422,   1384,   1422,   
1384,   1422,   1384)

subranking<-c('CMP',    'CMP',  'CMP',  'CMP',  'SSC',  'SSC',  'SSC',  
'SSC',  'SSC',  'SSC',  'ETC',  'ETC',  'ETC',  'ETC',  'ETC',  'ETC',  
'ETC',  'ETC',  'ETC')

d <- data.frame(score, year, institution,subranking)


#-----------SOLUTION 1 -------------------

p<- unique(d$institution)
for (i in (1:length(p))){
  x<- d$score[d$institution==p[i]]
  y<- d$year[d$institution==p[i]]
  model<- lm(x~y)
  result<-predict(model, data.frame(y = c(2017,2018,2019,2020)))
  z<- cbind(result,data.frame(y = c(2017,2018,2019,2020)))
  print(z)
}

##----------SOLUTION 2 -------------------

calculate_predicted_scores <- function(scores, years) {predicted_scores <-0
mod = lm(scores ~ years)
predicted_scores<-predict(mod, data.frame(years = c(2017,2018,2019,2020)))
return(predicted_scores)
}

To illustrate, this is what I want to get at the end - the yellow rows are the predictions:

Patrik_P · Accepted Answer · 2017-08-03T12:04:15.053

You can try dplyr with broom as described in this very helpful answer

library(dplyr)
library(broom)
pred_per_group = d %>% group_by(subranking, institution) %>%
  do(predicted_scores=predict(lm(score ~ year, data=.), data.frame(year = c(2017,2018,2019, 2020))))
pred_df = tidy(pred_per_group, predicted_scores)

Then, add the resulting data frame with predicitons to yours with rbind.

pred_df <- data.frame(score=pred_df$x, year=rep(c(2017,2018,2019,2020), 5), institution=pred_df$institution, subranking=pred_df$subranking)
result <- rbind(d, pred_df)

EDIT on 3 Aug : as you wanted to conclude your own pursuit of coding I would go about it as follows:

p<- unique(d$institution)
r <- unique(d$subranking)
for (i in (1:length(p))){
  for(j in seq_along(r)){
  score<- d$score[d$institution==p[i] & d$subranking==r[j]]
  year<- d$year[d$institution==p[i] & d$subranking==r[j]]
  if(length(score)== 0){
    print(sprintf("No level for the following combination: Institution: %s and Subrank: %s", p[i], r[j]))
  } else{
  model<- lm(score~year)
  result<-predict(model, data.frame(year = c(2017,2018,2019,2020)))
  z<- cbind(result,data.frame(year = c(2017,2018,2019,2020)))
  print(sprintf("For Institution: %s and Subrank: %s the Score is:",p[i], r[j]))
  print(z)
  }
  }
}

giving

[1] "For Institution: 1422 and Subrank: CMP the Score is:"
  result year
1  51.80 2017
2  48.75 2018
3  45.70 2019
4  42.65 2020
[1] "For Institution: 1422 and Subrank: SSC the Score is:"
  result year
1   58.1 2017
2   55.2 2018
3   52.3 2019
4   49.4 2020
[1] "For Institution: 1422 and Subrank: ETC the Score is:"
  result year
1  77.00 2017
2  76.25 2018
3  75.50 2019
4  74.75 2020
[1] "No level for the following combination: Institution: 1384 and Subrank: CMP"
[1] "For Institution: 1384 and Subrank: SSC the Score is:"
    result year
1 44.13333 2017
2 43.33333 2018
3 42.53333 2019
4 41.73333 2020
[1] "For Institution: 1384 and Subrank: ETC the Score is:"
     result year
1 80.66000 2017
2 80.26286 2018
3 79.86571 2019
4 79.46857 2020

@Ago https://stackoverflow.com/help/someone-answers: Accepting an answer is important as it both rewards posters for solving your problem and informs others that your issue is resolved — Mal_a, Jul 28 '17 at 09:09
Would you be able to help me with how I could get the same result by using the two solution alternatives I've started in my original script? — Ago, Aug 01 '17 at 00:40
You say 'I would like to predict scores of institutions for various subrankings based on their scores in previous years' however In both solutions (1 & 2) you let out the subranking variable. A double loop or a adjusted function would do what is done with dplyr and broom in my answer but is that what you really want? I mean methodologically not programmatically. Don´t you want to take the institution/subrank variables in the regression equation as factor? If you say yes or I´m not sure, I advise you to visit and ask in CrossValidated as methodological question are out of scope of StakOverflow. — Patrik_P, Aug 01 '17 at 08:45
I want to do the same as you have done with dplyr and broom in your answer. I was just not able to figure out how to create the double loop/adjusted function and then how to pull the results out from the loop/function and add them to the dataframe. I think I mainly got stuck because of my limited knowledge of data types. — Ago, Aug 03 '17 at 00:18
Ok, when methodologically you are in a clear, then check the update for another way of how to program it with your solution Nr. 1 — Patrik_P, Aug 03 '17 at 11:49

Create forecast for subsets of dataframe and append to original file

1 Answers1