Hi all, I frequently get requests to do data analysis where the person references an excel column. e.g., "I want to analyze [insert complex variable name], located at column AAQ in Excel." I've been doing is gsub and inserting a part of the string for the complex variable name, then going from there. But, I was trying to make function that returns the following vector: excelVector = A, B, C, D,...AA, AB, AC...ZA, ZB, ZC,...AAA, AAB, AAC, etc. In other words, the argument would have one argument (n, or the number of columns), then it would return a list like that shown above. Then, all I would have to do is column.of.interest = which(excelVector=="AAQ") But I'm a bit stumped. The first part is easy: LETTERS[1:26] The next would probably use expand.grid, but all my potential solutions are pretty clunky. Any ideas?
HI,
May be you can try:
fun1 <- function(n){
?if(n <=26){
?res <- LETTERS[seq_len(n)]
?}
?else if(n>26 & n <=702){
res <-?
c(LETTERS,apply(expand.grid(vec1,vec1)[,2:1],1,paste,collapse=""))[1:n]
}
else if(n >702 & n <=18278){
res <-
c(LETTERS,apply(expand.grid(vec1,vec1)[,2:1],1,paste,collapse=""),apply(expand.grid(vec1,vec1,vec1)[,3:1],1,paste,collapse=""))[1:n]
}
else {
NA
}
res
}
fun1(0)
#character(0)
?fun1(2)
#[1] "A" "B"
fun1(28)
A.K.
On Monday, January 27, 2014 4:41 PM, Dustin Fife <fife.dustin at
gmail.com> wrote:
Hi all,
I frequently get requests to do data analysis where the person
references an excel column. e.g., "I want to analyze [insert complex
variable name], located at column AAQ in Excel."? I've been doing is
gsub and inserting a part of the string for the complex variable name,
then going from there. But, I was trying to make function that returns
the following vector:
excelVector = A, B, C, D,...AA, AB, AC...ZA, ZB, ZC,...AAA, AAB, AAC, etc.
In other words, the argument would have one argument (n, or the number
of columns), then it would return a list like that shown above. Then,
all I would have to do is
column.of.interest = which(excelVector=="AAQ")
But I'm a bit stumped. The first part is easy:
LETTERS[1:26]
The next would probably use expand.grid, but all my potential
solutions are pretty clunky.
Any ideas?
______________________________________________
R-help at r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
If you use XLConnect, to can reference the column symbolically to retrieve the data. Jim Holtman Data Munger Guru What is the problem that you are trying to solve? Tell me what you want to do, not how you want to do it. On Mon, Jan 27, 2014 at 4:30 PM, Dustin Fife <fife.dustin at gmail.com> wrote:> Hi all, > > I frequently get requests to do data analysis where the person > references an excel column. e.g., "I want to analyze [insert complex > variable name], located at column AAQ in Excel." I've been doing is > gsub and inserting a part of the string for the complex variable name, > then going from there. But, I was trying to make function that returns > the following vector: > > excelVector = A, B, C, D,...AA, AB, AC...ZA, ZB, ZC,...AAA, AAB, AAC, etc. > > In other words, the argument would have one argument (n, or the number > of columns), then it would return a list like that shown above. Then, > all I would have to do is > > column.of.interest = which(excelVector=="AAQ") > > But I'm a bit stumped. The first part is easy: > > LETTERS[1:26] > > The next would probably use expand.grid, but all my potential > solutions are pretty clunky. > > Any ideas? > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
On Jan 27, 2014, at 1:30 PM, Dustin Fife wrote:> Hi all, > > I frequently get requests to do data analysis where the person > references an excel column. e.g., "I want to analyze [insert complex > variable name], located at column AAQ in Excel." I've been doing is > gsub and inserting a part of the string for the complex variable name, > then going from there. But, I was trying to make function that returns > the following vector: > > excelVector = A, B, C, D,...AA, AB, AC...ZA, ZB, ZC,...AAA, AAB, AAC, etc. > > In other words, the argument would have one argument (n, or the number > of columns), then it would return a list like that shown above. Then, > all I would have to do is > > column.of.interest = which(excelVector=="AAQ") > > But I'm a bit stumped. The first part is easy: > > LETTERS[1:26] > > The next would probably use expand.grid, but all my potential > solutions are pretty clunky.Doesn't Excel still support R[n]C[m] references where n.m are integers? -- David Winsemius Alameda, CA, USA