Pasting in a for loop

Question

I have a number of data.frames that I want to export to Excel using the package XLConnect. I'm doing this using a for loop, within the loop there is a paste function that gives the sheet name but I don't know how to construct the data name.

Here's what I mean.

My data.frames are named as follows,

port.1a, port.1b, port.2a, port.2b ... port.10a, port.10b

So far my code looks like this,

library(XLConnect)
for (i in 1:10){
    a[i]  <- paste("port.",i,"a",sep="")
    b[i]  <- paste("port.",i,"a",sep="")
    writeWorksheetToFile("wfe_bx3.xlsx", data=b[i], sheet=a[i])
}

I haven't bother with the port.1b data.frames as yet because I'm having difficulty with this part. Obviously, paste returns a character rather than a data.frame for data=b but cat doesn't work either.

Any help would be appreciated, if only to tell me that this is a repeat question. (There are lots of similar questions but they haven't helped).

You're also pasting a character string into an argument that likely takes a name (`data`). You may need e.g. `data = as.name(b[i])`. Not sure about `sheet` — Rich Scriven, Sep 18 '14 at 16:25
Generally, you'd be better off if your data frames where in a list, or in this case maybe two lists `port.a` and `port.b`. Next time... — Gregor Thomas, Sep 18 '14 at 17:04
@RichardScriven not in the example but I will do. Thanks for reminding me. The as.name is most probably what I need. (I tried as.data.frame(b[i]), which, of course, gave me a data.frame (d'oh). — DarrenRhodes, Sep 18 '14 at 17:13
as.name didn't work. The sheets are in the excel spreadsheet but the data= part of the function is not picking up the data.frames. Maybe this is an environment thing where the data.frames aren't in the loop's environment, just the names generated by the loop. (Inspection of the spreadsheet shows that the first column is populated with port.1a going down to port.10a). Why was @Gregor talking about lists? — DarrenRhodes, Sep 18 '14 at 17:28
If `as.name` doesn't work, you could try `quote()` or `get()` instead — Gregor Thomas, Sep 18 '14 at 17:59
quote() and get() aren't working; they just don't pick up the values of the data.frame and put them into the appropriate sheet. Thanks for your suggestions, though @Gregor — DarrenRhodes, Sep 18 '14 at 19:36

Gregor Thomas · Answer 1 · 2014-09-18T19:37:16.237

This ought to work. There's no real reason to make a and b length-10 vectors unless you want to use them afterwards.

for (i in 1:10){
    a  <- paste0("port.", i, "a")
    b  <- paste0("port.", i, "a")
    writeWorksheetToFile("wfe_bx3.xlsx", data = as.name(b), sheet = a)
}

I copy/pasted the code from your question, which doesn't seem to use the "b" files at all (you have "a" in both your pastes). Thus, a and b are identical in your loop and you really only need one.

Alternatively you could build the names first

a <- paste0("port.", 1:10, "a")
b <- paste0("port.", 1:10, "b") # assuming you wanted a "b" in here
for (i in 1:10){
    writeWorksheetToFile("wfe_bx3.xlsx", data = as.name(b[i]), sheet = a[i])
}

This is probably very slightly more efficient, definitely negligible in this use case.

One of the advantages of doing things in a list (as per my comment above) is that you don't have to worry about getting passing a string to a function expecting a data.frame. Let's say your data.frames are in two 10-element lists, porta and portb. Then something like this would be the easiest solution of all:

 for (i in 1:10){
        writeWorksheetToFile("wfe_bx3.xlsx", data = portb[[i]], sheet = names(portb)[i])
    }

neither piece of code worked. The function didn't pick up the data. I think that it's an environment issue. — DarrenRhodes, Sep 18 '14 at 18:52
Functions have their own environments, for loops do not. Also see my edits at the bottom for a suggestion of how to use lists. — Gregor Thomas, Sep 18 '14 at 19:34
the use of lists worked. Thanks! I also used http://stackoverflow.com/questions/17499013/how-do-i-make-a-list-of-data-frames-in-r to prepare port_a in the first place. (I still need to be able to prepare port_b etc but the questions suffered from 'I wouldn't have started from here', syndrome) — DarrenRhodes, Sep 19 '14 at 17:20

score 1 · Accepted Answer · answered Sep 19 '14 at 16:50

1

As already suggested by @Gregor, ideally your objects would already be in a list. But here is a possible solution to your problem:

## Reproducing use case ##

# Names of data.frames
obj.names = do.call("paste0", expand.grid("port.", 1:10, c("a", "b")))
# Create dummy objects
sapply(obj.names, assign, mtcars, envir = globalenv())


## Solution ##

# Gather objects in a list
objs = lapply(obj.names, get)

require(XLConnect)
writeWorksheetToFile("wfe_bx3.xlsx", data = objs, sheet = obj.names)
# NOTE: Many functions in XLConnect are vectorized!

answered Sep 19 '14 at 16:50

Martin Studer

2,213
1
18
23

I used a mixture of Marting Studer's answer and Gregor's answer. I used Mark Miller's list.function from here http://stackoverflow.com/questions/17499013/how-do-i-make-a-list-of-data-frames-in-r/24376207#24376207 to set-up my list of data.frames. I used Martin Studer's do.call to modify the list.function and I used @Gregor's for loop to put everything into excel. It worked. My only problem ... I wanted df.1a, df.1b, df.2a, df.2b but I got, df.1a,df.2a,df1b,df.2b etc... – DarrenRhodes Sep 19 '14 at 21:00

Pasting in a for loop

2 Answers2