Re: [R] Numeric Column Labels in Excel Function
On Tue, Jan 28, 2014 at 6:46 PM, David Winsemius wrote: > > 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? > > Yes, Excel refers to this as the "R1C1 reference style". You may need to enable lthe associated option to make use of it. -- Statistics & Software Consulting GKX Group, GKX Associates Inc. tel: 1-877-GKX-GROUP email: ggrothendieck at gmail.com [[alternative HTML version deleted]] __ R-help@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.
Re: [R] Numeric Column Labels in Excel Function
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 __ R-help@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.
Re: [R] Numeric Column Labels in Excel Function
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 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@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. __ R-help@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.
Re: [R] Numeric Column Labels in Excel Function
Sorry, this should work
fun1 <- function(n){
vec1 <- LETTERS
if(n <=26){
res <- vec1[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 {
res <- NA
}
res
}
fun1(0)
character(0)
> fun1(8)
[1] "A" "B" "C" "D" "E" "F" "G" "H"
> fun1(40)
[1] "A" "B" "C" "D" "E" "F" "G" "H" "I" "J" "K" "L" "M" "N" "O"
[16] "P" "Q" "R" "S" "T" "U" "V" "W" "X" "Y" "Z" "AA" "AB" "AC" "AD"
[31] "AE" "AF" "AG" "AH" "AI" "AJ" "AK" "AL" "AM" "AN"
> fun1(18279)
[1] NA
A.K.
On Monday, January 27, 2014 5:11 PM, Dustin Fife wrote:
There seems to be a problem with that function: object 'vec1' not found.
On Mon, Jan 27, 2014 at 4:05 PM, arun wrote:
>
>
> 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
> 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@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.
>
__
R-help@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.
Re: [R] Numeric Column Labels in Excel Function
There seems to be a problem with that function: object 'vec1' not found.
On Mon, Jan 27, 2014 at 4:05 PM, arun wrote:
>
>
> 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
> 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@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.
>
__
R-help@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.
Re: [R] Numeric Column Labels in Excel Function
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 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@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.
__
R-help@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.
[R] Numeric Column Labels in Excel Function
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@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.
