replicate multiple regression plot from excel in R

Question

I am struggling with reproducing a multiple linear regression plot in R which is rather easily obtainable in Excel. I make an example. Say I have the following data frame (called test) in R:

y   x1  x2  x3
2   5   5   9
6   4   2   9
4   2   6   15
7   5   10  6
7   5   10  6
5   4   3   12

To generate a linear regression, I simply write:

reg=lm(y ~ x1 + x2 + x3, data = test)

Now I would like to create a plot of the actual value of the y variable, the predicted y and on a secondary axis the standardised residuals. I add a screenshot from Excel so you see what I mean.

To access the Excel plot I would like to obtain: the plot is in italian, "y" means observed y values, "Y prevista" means predicted Y values and "Residui standard" means standardized residuals. The standard residuals are plotted on a secondary axis

If anyone could show me who I can achieve the above in R, it would be very appreciated.

Answer 1

Use something like

matplot(seq(nrow(test)), cbind(test$y, predict(reg), rstudent(reg)), type="l")

but you'd have to set the axes to make sure everything is okay

Answer 2

You could try something like this. Easier to debug your code.

test <- data.frame(y=c(2,6,4,7,7,5), x1=c(5,4,2,5,5,4), x2=c(5,2,6,10,10,3), 
                  x3=c(9,9,15,6,6,12))

reg=lm(y ~ x1 + x2 + x3, data = test)

# Add new columns to dataframe from regression
test$yhat <- reg$fitted.values
test$resid <- reg$residuals
# Create your x-variable column
test$X <-seq(nrow(test))

library(ggplot2)
library(reshape2)
# Columns to keep
keep = c("y", "yhat", "resid", "X")

# Drop columns not needed
test <-test[ , keep, drop=FALSE]

# Reshape for easy plotting
test <- melt(test, id.vars="X")

# Everything on the same plot
ggplot(test, aes(X,value, col=variable)) + 
  geom_point() + 
  geom_line()

For different look, you could also replace geom_line with geom_smooth()