0

I have X number of spreadsheets with information spreading out over two tabs. I am looking to combine these into one data frame. The files have 3 distinct cells on tab 1 (D6, D9, D12) and tab 2 has a grid (D4:G6) that i want to pull out of each spreadsheet into a row.

So far i have made the data frame, and pulled a list of the files. I have managed to get a for-loop working that pulls out the data from sheet1 D6, i plan to copy this code for the rest of the cells I need. 

file.list <-
  list.files(
    path = "filepath",
    pattern = "*.xlsx",
    full.names = TRUE,
    recursive = FALSE
  )

colnames <- c( "A","B","C","etc",)

output <- matrix(NA,nrow = length(file.list), ncol = length(colnames), byrow = FALSE)
colnames(output) <- c(colnames)
rownames(output) <- c(file.list)




for (i in 1:length(file.list)) {

  filename=file.list[i]
  data = read.xlsx(file = filename, sheetIndex = 1, colIndex = 7, rowIndex = 6)
  assign(x = filename, value = data)

  }

The issue i have is that R then pulls out X number of single data points, and I am unable to bring this out as one list of multiple data points to insert in to the dataframe.

adjectivedave
  • 41
  • 5
  • 2
    I'd strongly advise using a `list` rather than `assign`. See, e.g., my answer at [How to make a list of data frames?](https://stackoverflow.com/a/24376207/903061). Beyond that, without your spreadsheet we can't run any of this code. Can you share a data frame for one of the files? `dput()` is nice for sharing a copy/pasteable version of a data frame. And, when you say "R then pulls out X number of single data points", what code do you have that does that? – Gregor Thomas Jun 07 '19 at 15:31
  • @Gregor - thanks. I'm going to look at the link and see if that helps. - for loop currently pulls out X number of single cells from each of the excel files in the appropriate folder. In this case D6 is a name. So I have X number of "names" in the data part of excel (when i click them a new tab pops up on Rstudio) - but i want one list of all names – adjectivedave Jun 07 '19 at 15:36

0 Answers0