[R] Cross-tabulation of data from database

[Jens Nieschulze]

On Tue, 26 Feb 2002, Morten Sickel wrote:

%I am quite new to R, so please bear over with me if I have problems with the 
%R terminology.
%
%I want to (try to) use R for some analyses within vegetation ecology, using 
%the vegan package. I have my data in a postgresql database, and I manage to 
%get them into R as a dataframe with columns for respectively: Name of the 
%analysed m2, Name of the species, coverage of species in the square in %. I 
%want to have the dataset transformed into a table similiar to the example 
%tables in vegan, i.e, one column for each species, one row for each square. 
%
%What I have done so far is to establish a connection to postgres using Rdbi, 
%fetching the data :
%> dca.raw<-dbGetResult(dcaq)
%> dca.raw
%                                                nornavn dekningsgrad  ruteid
%1    Fjellmarikåpe                                                 1 LIASB01
%2    Gulaks                                                        3 LIASB01
%3    Harerug                                                       1 LIASB01
%4    Brearve                                                       1 LIASB01
%5    Sveve sp.                                                     5 LIASB01
%6    Finnskjegg                                                   50 LIASB01
%7    Setergråurt                                                   1 LIASB01
%8    Engrapp-gruppa                                                5 LIASB01
%9    Legeveronika                                                  1 LIASB01
%etc.
%(Norwegian species names, nornavn = species, dekningsgrad= coverage 
%ruteid=name of square)
%then I converted it to a data frame:
%> dca.frame <- data.frame(dca.raw)
%and 
%> dca.frame 
%gives an output that seems reasonable.
%so, I thought I could use xtabs and tried:
%> xtabs(dekningsgrad ~ ruteid+nornavn, data=dca.frame)
%which, as far as I can see, is quite analogous to the inner part of the 
%example given: 
%> ftable(xtabs(breaks ~ wool + tension + replicate, data = warpbreaks))
%
%But I end up with:
%Error in Summary.factor(..., na.rm = na.rm) :
%        "sum" not meaningful for factors

I guess dekningsgrad (didn't know Norwegian can be this close to
German:Deckungsgrad) is read in as a factor. You can check the structure
of an object with str(), as in the example below
If this is the case you should convert it to numeric, something like
dca.raw$dekningsgrad<-as.numeric(dca.raw$dekningsgrad)
and go ahead

	JN

data(UCBAdmissions)

DF <- as.data.frame(UCBAdmissions)
> xtabs(Freq ~ Gender + Admit, DF)
        Admit
Gender   Admitted Rejected
  Male       1198     1493
  Female      557     1278
> str(DF)
`data.frame':   24 obs. of  4 variables:
 $ Admit : Factor w/ 2 levels "Admitted","Reje..": 1 2 1 2 1 2 1 2 1 2 ...
 $ Gender: Factor w/ 2 levels "Male","Female": 1 1 2 2 1 1 2 2 1 1 ...
 $ Dept  : Factor w/ 6 levels "A","B","C","D",..: 1 1 1 1 2 2 2 2 3 3 ...
 $ Freq  : num  512 313 89 19 353 207 17 8 120 205 ...


%
%And then,  as a still quite clueless R-user, I am stranded. I have been 
%searching in the docs and mailinglist, but have not came across much 
%information on xtabs and its prerequisites, I have probably stumbled into 
%some quite basic R things, so any help would be greatly appreciated.
%
%Best regards,
%
%-- 
%Morten Sickel
%GIS Consultant
%Drøbak, Norway
%-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
%r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
%Send "info", "help", or "[un]subscribe"
%(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
%_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._
%

***********************************************************************
Jens Nieschulze

Institute for Forest Biometrics &	Phone: ++49-551-39-12107
Applied Computer Science		Fax  : ++49-551-39-3465
Buesgenweg 4
37077 Goettingen		E-mail: jniesch at uni-forst.gwdg.de
GERMANY				http://www.uni-forst.gwdg.de/~jniesch

-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._