Multivariate regression model with read.xlsx

,

Hi guys,
I have a little issue with my code and I hope someone of you can help me.
I am trying to run a multivariate regression model through a function. The problem is that, together with the values of the coefficients, I get in my console window also the entire time series of the corresponding independent variables.

I tried to slightly modified my code to cope with it but doing so I obtain a different errors message, which i think is related to the kind of variables my code get from using read.xlsx. I already tried to use escamotages like as.vector, as.matrix etc. but they did not work.

This is my code.

                ###Funzione modello regressione lineare con read.xlsx###

                          ##PLEASE CHECK##

#Il seguente codice riporta i valori dei coefficienti ma, insieme all'output di questi ultimi, riporta anche l'intero vettore della timeseries.

regressionelineare <- function (file, sheet, vardep, varindep1, varindep2) {
  vardep <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(1), header = TRUE)
  varindep1 <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(2), header = TRUE)
  varindep2 <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(3), header = TRUE)
  lmodel<-lm(paste(vardep, "~", varindep1, "+", varindep2))
  coef(lmodel)
}

regressionelineare("MLRAD.xlsx", "Sheet1",CPI,PPI,DIS) #EX COMPILAZIONE




#Il seguente codice riporta il messaggio di errore "invalid type(list) for variable 'vardep'

regressionelineare <- function (file, sheet, vardep, varindep1, varindep2) {
  
  vardep <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(1), header = TRUE)
  varindep1 <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(2), header = TRUE)
  varindep2 <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(3), header = TRUE)
  modelmulti <-lm(vardep~varindep1+varindep2) # modello multivariato
  summary(modelmulti)
  
}

regressionelineare("MLRAD.xlsx", "Sheet1",CPI,PPI,DIS) #EX COMPILAZIONE



### Procedo manualmente fuori dall'ambiente funzione: il problema รจ lo stesso, #insieme ai valori dei coefficienti
#vengono riportati anche le time series corrispondenti

CPI<-read.xlsx("MLRAD.xlsx", sheetName = "Sheet1",startRow = 1, colIndex = c(1), header = TRUE)
PPI<-read.xlsx("MLRAD.xlsx", sheetName = "Sheet1",startRow = 1, colIndex = c(2), header = TRUE)
DIS<-read.xlsx("MLRAD.xlsx", sheetName = "Sheet1",startRow = 1, colIndex = c(3), header = TRUE)
#modelmulti <-lm(CPI~PPI+DIS) # modello multivariato ### questo comando #NONFUNZIONA
lm(paste(CPI, "~", PPI, "+", DIS))

Someone can help me out?

Thank you guys

This isn't exactly what you want because I cannot write xlsx files on the computer I am using so I had to adapt to a file I happen to have. A key difference to your code is that I read all three data columns at once to make a single data frame and I used that data frame as the data argument of lm. I also passed the variable names as strings.
The file I used did not have useful headers, so I read the data without the header row and then manually set the column names in the data frame. I do not think you have to do that.

library(openxlsx)
regressionelineare <- function (file, sheet, vardep, varindep1, varindep2) {
  DF <- read.xlsx(file, sheet = sheet, startRow = 4, colNames = FALSE, 
                rowNames = FALSE, cols = c(7,8,10))
  colnames(DF) <- c(varindep1, varindep2, vardep)
  lmodel<-lm(paste(vardep, "~", varindep1, "+", varindep2), data = DF)
  coef(lmodel)
}

regressionelineare("Mydata.xlsx", "Sheet1", "MyY", "X1", "X2")
#>   (Intercept)            X1            X2 
#> -66074.360993     -2.724324      2.633628

Created on 2020-01-29 by the reprex package (v0.3.0)

Good evening FJCC, thank you for your support.
For anyone who might read this topic, in the following you will find my solution as well. I just renamed my coefficients. Now my output is clear.

regressionelineare <- function (file, sheet, vardep, varindep1, varindep2) {
  vardep <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(1), header = TRUE)
  varindep1 <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(2), header = TRUE)
  varindep2 <- read.xlsx(file = file, sheetIndex = sheet, startRow = 1, colIndex = c(3), header = TRUE)
  lmodel<-lm(paste(vardep, "~", varindep1, "+", varindep2))
  names(lmodel$coefficients) <- c('CPI','PPI','DIS')
  coef(lmodel)
}

Thank you again FJCC

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.