Re: [R] Multiple count if style "queries"

Fri, 20 May 2011 14:58:39 -0700 


On May 20, 2011, at 5:50 PM, Chris Mcowen wrote:


Sorry for not including the data, i did intend to.
You included the data, just not in a format that anyone could reasonably be expected to edit so it can be used. Please read the Posting Guide. 

--
David.



I tried your code but got this error:


answer <- sqldf("

+     select ECO_NAME
+               , count(distinct Order) as Order
+               , count(distinct Family) as Family
+               , count(distinct Genus) as Genus
+        from data2
+        group by ECO_NAME
+ ")
Loading required package: tcltk
Loading Tcl/Tk interface ... done
Error in sqliteExecStatement(con, statement, bind.data) :
 RS-DBI driver: (error in statement: near "Order": syntax error)


For the second question i would want:
The number of times each Order/Family and Genus was represented in each eco_region. 

I.E

Biak-Numfoor rain forests       
Order: Alismatales-1, Araceae-1
Family: Homalomena -1, Asparagales-1    
Genus: Orchidaceae-1, Bromheadia-1

So the number after each name is the count.

Thanks for the help.

Chris


data2 <- structure(list(ECO_NAME = structure(c(1L, 1L, 2L, 2L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L,
3L, 3L, 3L, 3L, 3L), .Label = c("Biak-Numfoor rain forests",
"Central Range montane rain forests", "Huon Peninsula montane rain forests" 
), class = "factor"), Order = structure(c(1L, 2L, 1L, 2L, 2L,
2L, 2L, 2L, 2L, 2L, 2L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L,
2L, 2L, 2L, 2L, 4L, 4L), .Label = c("Alismatales", "Asparagales",
"Commelinales", "Poales", "Zingiberales"), class = "factor"),
   Family = structure(c(1L, 7L, 1L, 6L, 7L, 7L, 7L, 7L, 7L,
   7L, 7L, 3L, 2L, 4L, 4L, 5L, 8L, 8L, 9L, 9L, 9L, 7L, 7L, 7L,
7L, 2L, 8L), .Label = c("Araceae", "Centrolepidaceae", "Commelinaceae", 
   "Cyperaceae", "Eriocaulaceae", "Lomandraceae", "Orchidaceae",
"Poaceae", "Zingiberaceae"), class = "factor"), Genus = structure(c(13L, 
   3L, 13L, 6L, 19L, 9L, 20L, 4L, 4L, 9L, 9L, 15L, 5L, 14L,
   10L, 11L, 17L, 16L, 1L, 8L, 2L, 18L, 7L, 19L, 12L, 5L, 16L
   ), .Label = c("Alpinia", "Amomum", "Bromheadia", "Bulbophyllum",
   "Centrolepis", "Cordyline", "Corybas", "Curcuma", "Dendrobium",
   "Eleocharis", "Eriocaulon", "Glomera", "Homalomena", "Machaerina",
"Murdannia", "Poa", "Schizostachyum", "Taeniophyllum", "Thelymitra", 
   "Vanda"), class = "factor")), .Names = c("ECO_NAME", "Order",
"Family", "Genus"), class = "data.frame", row.names = c(NA, -27L
))


On 20 May 2011, at 22:33, jim holtman wrote:

use the 'sqldf' package.  Also use 'dput' to include sample data since
it was impossible to use the data in the format you provided, so my
guess at a solution would be:

answer <- sqldf("
   select ECO_NAME
             , count(distinct Order) as Order
             , count(distinct Family) as Family
             , count(distinct Genus) as Genus
      from yourData
      group by ECO_NAME
")

Not sure exactly what you wanted in the second question; it would be
nice to include what some sample output would be so we have an idea of
what you are after.
On Fri, May 20, 2011 at 5:18 PM, Chris Mcowen <chrismco...@gmail.com> wrote: 
Dear List,
I am looking to calculate two things from my data frame and was after some advice. For the example below i want to know. 

1. How many unique Orders/Families and Genera there are per eco-name
2. How many incidences are there for each Order/Family and Genus there are per eco-region 

I have 650 econame.
I.e for Biak-Numfoor rain forests there are 2 orders, 2 families and two genera. Also, Alismatales are represented once, Asparagales once etc etc. 

Thanks for any advice.

Chris
ECO_NAME Order Family Genus Biak-Numfoor rain forests Alismatales Araceae Homalomena Biak-Numfoor rain forests Asparagales Orchidaceae Bromheadia Central Range montane rain forests Alismatales Araceae Homalomena Central Range montane rain forests Asparagales Lomandraceae Cordyline Central Range montane rain forests Asparagales Orchidaceae Thelymitra Central Range montane rain forests Asparagales Orchidaceae Dendrobium Central Range montane rain forests Asparagales Orchidaceae Vanda Central Range montane rain forests Asparagales Orchidaceae Bulbophyllum Central Range montane rain forests Asparagales Orchidaceae Bulbophyllum Central Range montane rain forests Asparagales Orchidaceae Dendrobium Central Range montane rain forests Asparagales Orchidaceae Dendrobium Central Range montane rain forests Commelinales Commelinaceae Murdannia Central Range montane rain forests Poales Centrolepidaceae Centrolepis Central Range montane rain forests Poales Cyperaceae Machaerina Central Range montane rain forests Poales Cyperaceae Eleocharis Central Range montane rain forests Poales Eriocaulaceae Eriocaulon Central Range montane rain forests Poales Poaceae Schizostachyum Central Range montane rain forests Poales Poaceae Poa Central Range montane rain forests Zingiberales Zingiberaceae Alpinia Central Range montane rain forests Zingiberales Zingiberaceae Curcuma Central Range montane rain forests Zingiberales Zingiberaceae Amomum Huon Peninsula montane rain forests Asparagales Orchidaceae Taeniophyllum Huon Peninsula montane rain forests Asparagales Orchidaceae Corybas Huon Peninsula montane rain forests Asparagales Orchidaceae Thelymitra Huon Peninsula montane rain forests Asparagales Orchidaceae Glomera Huon Peninsula montane rain forests Poales Centrolepidaceae Centrolepis Huon Peninsula montane rain forests Poales Poaceae Poa 
______________________________________________
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.





--
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?

______________________________________________
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.


David Winsemius, MD
West Hartford, CT

______________________________________________
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.

Reply via email to