How do I get a contingency table?

Question

I am trying to create a contingency table from a particular type of data. This would be doable with loops etc... but because my final table would contain more than 10E5 cells, I am looking for a pre-existing function.

My initial data are as follow:

PLANT                  ANIMAL                          INTERACTIONS
---------------------- ------------------------------- ------------
Tragopogon_pratensis   Propylea_quatuordecimpunctata         1
Anthriscus_sylvestris  Rhagonycha_nigriventris               3
Anthriscus_sylvestris  Sarcophaga_carnaria                   2
Heracleum_sphondylium  Sarcophaga_carnaria                   1
Anthriscus_sylvestris  Sarcophaga_variegata                  4
Anthriscus_sylvestris  Sphaerophoria_interrupta_Gruppe       3
Cerastium_holosteoides Sphaerophoria_interrupta_Gruppe       1

I would like to create a table like this:

                       Propylea_quatuordecimpunctata Rhagonycha_nigriventris Sarcophaga_carnaria Sarcophaga_variegata Sphaerophoria_interrupta_Gruppe
---------------------- ----------------------------- ----------------------- ------------------- -------------------- -------------------------------
Tragopogon_pratensis   1                             0                       0                   0                    0
Anthriscus_sylvestris  0                             3                       2                   4                    3
Heracleum_sphondylium  0                             0                       1                   0                    0
Cerastium_holosteoides 0                             0                       0                   0                    1

That is, all plant species in row, all animal species in columns, and sometimes there are no interactions (while my initial data only list interactions that occur).

10E5 cells in a contingency table!!! What analysis are you doing? If you are checking interactions using chi-square, you need to have at least 5 observations in each cell. — Ramnath, Sep 16 '11 at 12:12

Andrie · Answer 1 · 2011-09-16T09:12:24.930

In base R, use table or xtabs:

with(warpbreaks, table(wool, tension))

    tension
wool L M H
   A 9 9 9
   B 9 9 9

xtabs(~wool+tension, data=warpbreaks)

    tension
wool L M H
   A 9 9 9
   B 9 9 9

The gmodels packages has a function CrossTable that gives output similar to what users of SPSS or SAS expects:

library(gmodels)
with(warpbreaks, CrossTable(wool, tension))


   Cell Contents
|-------------------------|
|                       N |
| Chi-square contribution |
|           N / Row Total |
|           N / Col Total |
|         N / Table Total |
|-------------------------|


Total Observations in Table:  54 


             | tension 
        wool |         L |         M |         H | Row Total | 
-------------|-----------|-----------|-----------|-----------|
           A |         9 |         9 |         9 |        27 | 
             |     0.000 |     0.000 |     0.000 |           | 
             |     0.333 |     0.333 |     0.333 |     0.500 | 
             |     0.500 |     0.500 |     0.500 |           | 
             |     0.167 |     0.167 |     0.167 |           | 
-------------|-----------|-----------|-----------|-----------|
           B |         9 |         9 |         9 |        27 | 
             |     0.000 |     0.000 |     0.000 |           | 
             |     0.333 |     0.333 |     0.333 |     0.500 | 
             |     0.500 |     0.500 |     0.500 |           | 
             |     0.167 |     0.167 |     0.167 |           | 
-------------|-----------|-----------|-----------|-----------|
Column Total |        18 |        18 |        18 |        54 | 
             |     0.333 |     0.333 |     0.333 |           | 
-------------|-----------|-----------|-----------|-----------|

Can you please explain what those numbers with the decimal places mean? I used gmodels to create a contingency tables and the only argument I set to TRUE was prop.c (i.e., everything else was set to FALSE). I still get an extra number that's displayed along with the column percentages and the actual n value for the cell... and I can't for the life of me figure out what it is (and yes, I've searched a lot on how to interpret the output! ). Thanks. — AHegde, Sep 28 '15 at 23:32
Your answer is in the output above. At the top of the output is a box called `Cell Contents`. It explains the meaning of each number, i.e. chi-square and various row and columns fractions. — Andrie, Sep 29 '15 at 11:58

score 10 · Answer 2 · answered Sep 16 '11 at 09:17

the reshape package should do the trick.

> library(reshape)

> df <- data.frame(PLANT = c("Tragopogon_pratensis","Anthriscus_sylvestris","Anthriscus_sylvestris","Heracleum_sphondylium","Anthriscus_sylvestris","Anthriscus_sylvestris","Cerastium_holosteoides"),
                   ANIMAL= c("Propylea_quatuordecimpunctata","Rhagonycha_nigriventris","Sarcophaga_carnaria","Sarcophaga_carnaria","Sarcophaga_variegata","Sphaerophoria_interrupta_Gruppe","Sphaerophoria_interrupta_Gruppe"),
                   INTERACTIONS = c(1,3,2,1,4,3,1),
                   stringsAsFactors=FALSE)

> df <- melt(df,id.vars=c("PLANT","ANIMAL"))    
> df <- cast(df,formula=PLANT~ANIMAL)
> df <- replace(df,is.na(df),0)

> df
                   PLANT Propylea_quatuordecimpunctata Rhagonycha_nigriventris
1  Anthriscus_sylvestris                             0                       3
2 Cerastium_holosteoides                             0                       0
3  Heracleum_sphondylium                             0                       0
4   Tragopogon_pratensis                             1                       0
  Sarcophaga_carnaria Sarcophaga_variegata Sphaerophoria_interrupta_Gruppe
1                   2                    4                               3
2                   0                    0                               1
3                   1                    0                               0
4                   0                    0                               0

I'm still figuring out how to fix the order issue, any suggestion?

You can replace the last three lines with just one command: cast(PLANT~ANIMAL, data = df, value = "INTERACTIONS", fill = 0) — Thierry, Sep 16 '11 at 12:44
If you want to order that result on the basis of the input dataframe ordering order, you can use `order(unique(df$PLANT))` on the rows and the obvious analog on the columns. Your example didn't need `unique` but a version that had multiple entries per pairing whose values were summed might need it. — IRTFM, Sep 16 '11 at 13:46

score 9 · Answer 3 · answered May 18 '15 at 08:17

I'd like to point out that we can get the same results Andrie posted without using the function with:

R Base Package

# 3 options
table(warpbreaks[, 2:3])
table(warpbreaks[, c("wool", "tension")])
table(warpbreaks$wool, warpbreaks$tension, dnn = c("wool", "tension"))

    tension
wool L M H
   A 9 9 9
   B 9 9 9

Package gmodels:

library(gmodels)
# 2 options    
CrossTable(warpbreaks$wool, warpbreaks$tension)
CrossTable(warpbreaks$wool, warpbreaks$tension, dnn = c("Wool", "Tension"))


   Cell Contents
|-------------------------|
|                       N |
| Chi-square contribution |
|           N / Row Total |
|           N / Col Total |
|         N / Table Total |
|-------------------------|


Total Observations in Table:  54 


                | warpbreaks$tension 
warpbreaks$wool |         L |         M |         H | Row Total | 
----------------|-----------|-----------|-----------|-----------|
              A |         9 |         9 |         9 |        27 | 
                |     0.000 |     0.000 |     0.000 |           | 
                |     0.333 |     0.333 |     0.333 |     0.500 | 
                |     0.500 |     0.500 |     0.500 |           | 
                |     0.167 |     0.167 |     0.167 |           | 
----------------|-----------|-----------|-----------|-----------|
              B |         9 |         9 |         9 |        27 | 
                |     0.000 |     0.000 |     0.000 |           | 
                |     0.333 |     0.333 |     0.333 |     0.500 | 
                |     0.500 |     0.500 |     0.500 |           | 
                |     0.167 |     0.167 |     0.167 |           | 
----------------|-----------|-----------|-----------|-----------|
   Column Total |        18 |        18 |        18 |        54 | 
                |     0.333 |     0.333 |     0.333 |           | 
----------------|-----------|-----------|-----------|-----------|

score 7 · Answer 4 · answered Dec 26 '13 at 05:26

xtabs in base R should work, for example:

dat <- data.frame(PLANT = c("p1", "p2", "p2", "p4", "p5", "p5", "p6"),
                  ANIMAL = c("a1", "a2", "a3", "a3", "a4", "a5", "a5"),
                  INTERACTIONS = c(1,3,2,1,4,3,1),
                  stringsAsFactors = FALSE)

(x2.table <- xtabs(dat$INTERACTIONS ~ dat$PLANT + dat$ANIMAL))

     dat$ANIMAL
dat$PLANT a1 a2 a3 a4 a5
       p1  1  0  0  0  0
       p2  0  3  2  0  0
       p4  0  0  1  0  0
       p5  0  0  0  4  3
       p6  0  0  0  0  1

chisq.test(x2.table, simulate.p.value = TRUE)

I think that should do what you're looking for fairly easily. I'm not sure how it scales up in terms of efficiency to a 10E5 contingency table, but that might be a separate issue statistically.

score 6 · Answer 5 · answered Feb 10 '17 at 17:21

With dplyr / tidyr:

df <- read.table(text='PLANT                  ANIMAL                          INTERACTIONS
                 Tragopogon_pratensis   Propylea_quatuordecimpunctata         1
                 Anthriscus_sylvestris  Rhagonycha_nigriventris               3
                 Anthriscus_sylvestris  Sarcophaga_carnaria                   2
                 Heracleum_sphondylium  Sarcophaga_carnaria                   1
                 Anthriscus_sylvestris  Sarcophaga_variegata                  4
                 Anthriscus_sylvestris  Sphaerophoria_interrupta_Gruppe       3
                 Cerastium_holosteoides Sphaerophoria_interrupta_Gruppe       1', header=TRUE)
library(dplyr)
library(tidyr)
df %>% spread(ANIMAL, INTERACTIONS, fill=0)

#                    PLANT Propylea_quatuordecimpunctata Rhagonycha_nigriventris Sarcophaga_carnaria Sarcophaga_variegata Sphaerophoria_interrupta_Gruppe
# 1  Anthriscus_sylvestris                             0                       3                   2                    4                               3
# 2 Cerastium_holosteoides                             0                       0                   0                    0                               1
# 3  Heracleum_sphondylium                             0                       0                   1                    0                               0
# 4   Tragopogon_pratensis                             1                       0                   0                    0                               0

score 3 · Answer 6 · answered Sep 30 '16 at 07:14

Simply use dcast() function of "reshape2" package:

ans = dcast( df, PLANT~ ANIMAL,value.var = "INTERACTIONS", fill = 0 )

Here "PLANT" will be on the left column, "ANIMALS" on the top row, filling of the table will happen using "INTERACTIONS" and "NULL" values will be filled using 0's.

How do I get a contingency table?

6 Answers6

Linked

Related