Loop function on an Excel file with Multiple Worksheets

[afmannew]

Hello. I'm trying to make a dataframe in R with an Excel file that has 216 worksheets, representing some student data.
I made the dataframe just the way i wanted it for student 1, but i can't find a way an easy way to make it to the other 215 students, even though every worksheet has the EXACT SAME FORMAT. I wanna build a dataframe just like the ive built for student 1, but with all the 216 worksheets.

Here's what i've done so far

for (i in 1:215)
  {
exceldata = read_excel("C:/Users/Windows 10/Desktop/Projeto R/alunos2019.xlsx",sheet = i, col_names = FALSE)
nome <- exceldata$...1[1]
ID <- exceldata$...1[2]
tempo <- c(1,2,3)
R1<-c(exceldata$...2[4],exceldata$...3[4], exceldata$...4[4])
R2<-c(exceldata$...2[5],exceldata$...3[5], exceldata$...4[5])
R3<-c(exceldata$...2[6],exceldata$...3[6], exceldata$...4[6])
R4<-c(exceldata$...2[7],exceldata$...3[7], exceldata$...4[7])
R5<-c(exceldata$...2[8],exceldata$...3[8], exceldata$...4[8])
R6<-c(exceldata$...2[10],exceldata$...3[10], exceldata$...4[10])
R7<-c(exceldata$...2[11],exceldata$...3[11], exceldata$...4[11])
R8<-c(exceldata$...2[12],exceldata$...3[12], exceldata$...4[12])
R9<-c(exceldata$...2[13],exceldata$...3[13], exceldata$...4[13])
R10<-c(exceldata$...2[14],exceldata$...3[14], exceldata$...4[14])
R11<-c(exceldata$...2[16],exceldata$...3[16], exceldata$...4[16])
R12<-c(exceldata$...2[17],exceldata$...3[17], exceldata$...4[17])
R13<-c(exceldata$...2[18],exceldata$...3[18], exceldata$...4[18])
R14<-c(exceldata$...2[19],exceldata$...3[19], exceldata$...4[19])
R15<-c(exceldata$...2[20],exceldata$...3[20], exceldata$...4[20])
R16<-c(exceldata$...2[22],exceldata$...3[22], exceldata$...4[22])
R17<-c(exceldata$...2[23],exceldata$...3[23], exceldata$...4[23])
R18<-c(exceldata$...2[24],exceldata$...3[24], exceldata$...4[24])
R19<-c(exceldata$...2[25],exceldata$...3[25], exceldata$...4[25])
R20<-c(exceldata$...2[26],exceldata$...3[26], exceldata$...4[26])
R21<-c(exceldata$...2[28],exceldata$...3[28], exceldata$...4[28])
R22<-c(exceldata$...2[29],exceldata$...3[29], exceldata$...4[29])
R23<-c(exceldata$...2[30],exceldata$...3[30], exceldata$...4[30])
R24<-c(exceldata$...2[31],exceldata$...3[31], exceldata$...4[31])
R25<-c(exceldata$...2[32],exceldata$...3[32], exceldata$...4[32])
modelo = data.frame(nome,ID,tempo,R1,R2,R3,R4,R5,R6,R7,R8,R9,R10,R11,R12,R13,R14,R15,R16,R17,R18,R19,R20,R21,R22,R23,R24,R25)
  }

When i do that, R just makes the dataframe with the last person's data (the 216th student)
Can anyone help?

[GreyMerchant]

Hi,

It is hard to help you without a reprex (I can't run your data as it is). It is only returning the last person's data because you don't have a list. Try something like the below. modelo will finally consist of all 215 student's dataframes.

Let me know if this works.

modelo <- list()

for (i in 1:215)
{
  exceldata = read_excel("C:/Users/Windows 10/Desktop/Projeto R/alunos2019.xlsx",sheet = i, col_names = FALSE)
  nome <- exceldata$...1[1]
  ID <- exceldata$...1[2]
  tempo <- c(1,2,3)
  R1<-c(exceldata$...2[4],exceldata$...3[4], exceldata$...4[4])
  R2<-c(exceldata$...2[5],exceldata$...3[5], exceldata$...4[5])
  R3<-c(exceldata$...2[6],exceldata$...3[6], exceldata$...4[6])
  R4<-c(exceldata$...2[7],exceldata$...3[7], exceldata$...4[7])
  R5<-c(exceldata$...2[8],exceldata$...3[8], exceldata$...4[8])
  R6<-c(exceldata$...2[10],exceldata$...3[10], exceldata$...4[10])
  R7<-c(exceldata$...2[11],exceldata$...3[11], exceldata$...4[11])
  R8<-c(exceldata$...2[12],exceldata$...3[12], exceldata$...4[12])
  R9<-c(exceldata$...2[13],exceldata$...3[13], exceldata$...4[13])
  R10<-c(exceldata$...2[14],exceldata$...3[14], exceldata$...4[14])
  R11<-c(exceldata$...2[16],exceldata$...3[16], exceldata$...4[16])
  R12<-c(exceldata$...2[17],exceldata$...3[17], exceldata$...4[17])
  R13<-c(exceldata$...2[18],exceldata$...3[18], exceldata$...4[18])
  R14<-c(exceldata$...2[19],exceldata$...3[19], exceldata$...4[19])
  R15<-c(exceldata$...2[20],exceldata$...3[20], exceldata$...4[20])
  R16<-c(exceldata$...2[22],exceldata$...3[22], exceldata$...4[22])
  R17<-c(exceldata$...2[23],exceldata$...3[23], exceldata$...4[23])
  R18<-c(exceldata$...2[24],exceldata$...3[24], exceldata$...4[24])
  R19<-c(exceldata$...2[25],exceldata$...3[25], exceldata$...4[25])
  R20<-c(exceldata$...2[26],exceldata$...3[26], exceldata$...4[26])
  R21<-c(exceldata$...2[28],exceldata$...3[28], exceldata$...4[28])
  R22<-c(exceldata$...2[29],exceldata$...3[29], exceldata$...4[29])
  R23<-c(exceldata$...2[30],exceldata$...3[30], exceldata$...4[30])
  R24<-c(exceldata$...2[31],exceldata$...3[31], exceldata$...4[31])
  R25<-c(exceldata$...2[32],exceldata$...3[32], exceldata$...4[32])
  modelo[[i]]  <- data.frame(nome,ID,tempo,R1,R2,R3,R4,R5,R6,R7,R8,R9,R10,R11,R12,R13,R14,R15,R16,R17,R18,R19,R20,R21,R22,R23,R24,R25)
}

[afmannew]

It does work. However, here is how it looks

[afmannew]

And here is what i actually wanted (with all the students, not only 1)

Can i merge all the small dataframes on the list on a real big dataframe ?