Current and future prediction in a single data frame in r

Question

I have a dataset below for which I want to do linear regression and then get their fitted values and upper and lower limit in a single data frame.

The dataset has five weeks of data for each country and area combination. i.e. for US and G combination linear regression model will be built where amount will regress against week in five weeks and then this model will be used to predict 6 weeks. My final data frame would look like this

What I did is for one combination

data <- data.frame(country = c("US","US","US","US","US","US","US","US","US","US","UK","UK","UK","UK","UK"),
               Area = c("G","G","G","G","G","I","I","I","I","I","A","A","A","A","A"),
               week = c(1,2,3,4,5,1,2,3,4,5,1,2,3,4,5),amount = c(12,23,34,32,12,12,34,45,65,45,45,34,23,43,43))

data_1 <- data[(data$country=="US" & data$Area=="G"),]
# model building on initial five weeks
model <- lm(amount ~ week, data = data_1)


# creating one future week: week no 6
future <- data.frame(country="US",Area="G",week=6,amount = NA)
data2 <- rbind(data_1,future)

# prediction on all 6 weeks
pre <- predict(model,newdata = data2,interval = "prediction",level = 0.95)
pre

I took hint from here Looping linear regression output in a data frame in r

But not able to replicate this to include 6th weeks prediction for all other combination. Please help!

score 0 · Answer 1 · answered Jun 09 '21 at 11:18

I mostly copied your cope, in the end it's just a matter of combining the results

data_1 <- data[(data$country=="US" & data$Area=="G"),]
# model building on initial five weeks
model <- lm(amount ~ week, data = data_1)
pre1 <- predict(model,newdata = data_1, interval = "prediction",level = 0.95)

# creating one future week: week no 6
future <- data.frame(country="US",Area="G",week=6,amount = NA)
data_2 <- rbind(data_1,future)

# prediction on all 6 weeks
pre2 <- predict(model,newdata = data_2, interval = "prediction",level = 0.95)

rbind(
  cbind(data_1,pre1),
  cbind(data_2,pre2)
)

   country Area week amount  fit       lwr      upr
1       US    G    1     12 20.8 -27.67915 69.27915
2       US    G    2     23 21.7 -21.99851 65.39851
3       US    G    3     34 22.6 -19.38417 64.58417
4       US    G    4     32 23.5 -20.19851 67.19851
5       US    G    5     12 24.4 -24.07915 72.87915
11      US    G    1     12 20.8 -27.67915 69.27915
21      US    G    2     23 21.7 -21.99851 65.39851
31      US    G    3     34 22.6 -19.38417 64.58417
41      US    G    4     32 23.5 -20.19851 67.19851
51      US    G    5     12 24.4 -24.07915 72.87915
6       US    G    6     NA 25.3 -30.23984 80.83984

Current and future prediction in a single data frame in r

1 Answers1