How to convert contingency table obtained using table command to dataframe

Question

I am trying to use table function in r to create a contingency table. But instead of a contingency table, I got values (instead of table), list of two.

My data has 15000 observations and 2 variables. The data looks like this:

      **Field.Label**                     |**Form.Name**
      ------------------------------------|---------------
      Applicant Name                      |F1
      ------------------------------------|---------------
      Phone                               |F1
      ------------------------------------|---------------
      Address                             |F1
      ------------------------------------|---------------
      Phone                               |F2
      ------------------------------------|---------------
      Address                             |F2
      ------------------------------------|---------------
      Title                               |F2
      ------------------------------------|---------------
      Phone                               |F3
      ------------------------------------|---------------
      ...                                 |...

I used following code:

      CTable<-table(mydata$Field.Label, mydata$Form.Name)
      CTable<-as.data.frame(CTable)
      View(CTable)

And got result like this with 1,665,495 entries (Field.Label has 8,541 levels, and Form.Name has 195 levels. 8,541 * 195 = 1,665,495):

      ** Field.Label**                    |** Form.Name** |**Freq**
      ------------------------------------|---------------|-----------
      Applicant Name                      |F1             |1
      ------------------------------------|---------------|-----------
      Phone                               |F1             |1
      ------------------------------------|---------------|-----------
      Address                             |F1             |1
      ------------------------------------|---------------|-----------
      Title                               |F1             |0
      ------------------------------------|---------------|-----------
      Applicant Name                      |F2             |0
      ------------------------------------|---------------|-----------
      Phone                               |F2             |1
      ------------------------------------|---------------|-----------
      Address                             |F2             |1
      ------------------------------------|---------------|-----------
      Title                               |F2             |1
      ------------------------------------|---------------|-----------
      Applicant Name                      |F3             |0
      ------------------------------------|---------------|-----------
      Phone                               |F3             |1
      ------------------------------------|---------------|-----------
      Address                             |F3             |0
      ------------------------------------|---------------|-----------
      Title                               |F3             |0
      ------------------------------------|---------------|-----------
      ...                                 |...

How can I get the result of contingency table like the following?

                                      |**F1** |**F2** |**F3** |
  ------------------------------------|-------|-------|-------|
  Applicant Name                      | 1     |0      |0      |
  ------------------------------------|-------|-------|-------|
  Phone                               | 1     |1      |1      |
  ------------------------------------|-------|-------|-------|
  Address                             | 1     |1      |0      |
  ------------------------------------|-------|-------|-------|
  Title                               | 0     |1      |0      |
  ------------------------------------|-------|-------|-------|

Thanks!

Search for "reshape wide" or "dcast" and you'll find what you are looking for. — lmo, Aug 05 '16 at 19:11
If @lmo's comment doesn't provide enough leads for you, I suggest you reformat your examples to be something that we can use. These may look fine, but I'm not about to try to manually type all of these in manually to help you (sorry). I'd suggest reading [reproducible examples](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). — r2evans, Aug 05 '16 at 19:20
Just don't use the `as.dataframe` part and the value will remain a rather wide table. as.data.frame.table converts a wide format object (that inherits from `matrix`) to a normalized list version. — IRTFM, Aug 05 '16 at 19:25
@lmo I have tried that but didn't work for me. Thanks for the inputs. — kwc, Aug 08 '16 at 16:49
@r2evans Thanks for pointing me to the reproducible examples. This is helpful! — kwc, Aug 08 '16 at 16:51

Sathish · Accepted Answer · 2016-08-05T20:12:25.623

Data.Table Solution:

library("data.table")
mydata <- fread('Field.Label,Form.Name
                 Applicant,F1
                 Phone,F1
                 Address,F1
                 Phone,F2
                 Address,F2
                 Title,F2
                 Phone,F3')

CTable <- dcast(setDT(mydata), Field.Label ~ Form.Name, fun = length)
CTable
#    Field.Label F1 F2 F3
# 1:     Address  1  1  0
# 2:   Applicant  1  0  0
# 3:       Phone  1  1  1
# 4:       Title  0  1  0
class(CTable)
# [1] "data.table" "data.frame"

Base R Solution:

CTable <- table(mydata$Field.Label, mydata$Form.Name)
CTable <- data.frame(matrix(CTable, nrow = dim(CTable)[1], dimnames = dimnames(CTable)))
CTable
#           F1 F2 F3
# Address    1  1  0
# Applicant  1  0  0
# Phone      1  1  1
# Title      0  1  0
class(CTable)
# [1] "data.frame"

Base R Solution works beautifully. With this method, I don't need to type out all the variable names. Thanks, @Sathish! — kwc, Aug 08 '16 at 16:20
Yes, I'll make sure to do that from now on. Thanks for the tips on how to do so! — kwc, Aug 08 '16 at 16:48

How to convert contingency table obtained using table command to dataframe

1 Answers1