0

I have asked a similar question before, however the result was a list, and even when the data frames were split up they only contained one variable despite there being two columns and the function I'm trying to run will only run if there are two columns with specific titles). I specifically need the tables to be separate dataframes.

I have a table (called "book") with about 200 columns, time in the first column and then actual data from 2 onwards. It looks something like this:

Time 1 2 3 4 5 0 0 0 0 0 0 1 3 4 5 6 7 2 8 9 1 2 3 3 4 5 6 7 8

I need to split each column into separate files, so it looks like this:

Time 1 0 0 1 3 2 8 3 4

then

Time 2 0 0 1 4 2 9 3 5

and so forth.

I can do this using the dplyr package and datafile1 <- select(book, Time, 2). Then I can export this to excel using write_xlsx(datafile1, path = "1.xlsx", col_names = FALSE) I don't particularly want to do this 200 times (i.e. datafile2 <- select(book, Time, 3) then write_xlsx(datafile2, path = "2.xlsx", col_names = FALSE)) so I tried to create a for loop so I can automate the process.

n=200

for(i in 2:n){
    file_name = paste("datafile", toString(i-1), sep="")
    file_name <- select(book, T, i)
    write_xlsx(datafile, path = "(i-1).xlsx", col_names = FALSE)
}

The loop doesn't seem to run however, it just does it once for i= 200. Furthermore the name of the excel file created at the end is (i-1) not the actual variable - 1 (i.e. 200-1 which should have been 199). I figured, ok I messed up somewhere and the naming of a file can't be a variable. However, if I use path = "file_name.xlsx", col_name = FALSE, it still calls it 'file_name' instead of the datafile199 like i specified.

Any help would be much appreciated.

codingnewbie
  • 23
  • 5

1 Answers1

0

You could use lapply : 

library(writexl)
library(dplyr)
library(purrr)

lapply(2:ncol(book), function(x) 
       write_xlsx(book[c(1, x)], path = paste0(x-1, ".xlsx"), col_names = FALSE))

Or if you want to do it in tidyverse

map(2:ncol(book), ~write_xlsx(select(book, c(1, .x)), 
    path = paste0(x-1, ".xlsx"), col_names = FALSE))
Ronak Shah
  • 286,338
  • 16
  • 97
  • 143
  • Is there a way for r to be able to read the data as numbers? when it saves to excel they are saved as text files for some reason. Or would it be better if I saved them as csv files? – codingnewbie Feb 26 '20 at 01:48
  • @codingnewbie what is the class of them in R? Is it numeric? Check `str(book)`. You can do `book – Ronak Shah Feb 26 '20 at 01:58
  • It seems that col_names = FALSE turns them into characters. If i import it normally and turn col_names = TRUE then str returns them as num. If i make either of them false, it turns them into characters. However, my column titles from 2-200 need to be the same in the end product and right now R is adding numbers to them all if I don't turn it into false. – codingnewbie Feb 27 '20 at 01:18
  • @codingnewbie you can try any of the method mentioned here https://stackoverflow.com/questions/19414605/export-data-from-r-to-excel If that doesn't help and if you have a question specifically about writing into excel maybe ask a new question then. – Ronak Shah Feb 27 '20 at 01:31