Convert a table with missing values to a data frame of the same structure as the original table?

Question

Convert a table with missing values to a data frame of the same structure as the original table?

Neither of the following methods work, as they either change the structure or do not work with missing values:

t1  <- with( mtcars, table( gear,cyl, exclude = NULL  ) ) # the table


     data.frame(t1)
     as.data.frame(t1)
     as.data.frame.table(t1)
     as.data.frame.matrix(t1)

The following code works but I was hoping for a solution involving less writing:

library(reshape2)
dcast( data.frame(t1), value.var = "Freq", formula = gear ~ cyl )

The solutions to this SO question does not work with missing values: How to convert a table to a data frame

maybe I'm just too lazy. :/

score 1 · Accepted Answer · edited Oct 22 '14 at 09:32

1

library(data.table); library(tidyr)
t1  <- with( mtcars, table( gear,cyl, exclude = NULL  ) )
as.data.table(t1) %>% spread(cyl, N)
#   gear 4 6  8 NA
# 1    3 1 2 12  0
# 2    4 8 4  0  0
# 3    5 2 1  2  0
# 4 <NA> 0 0  0  0

edited Oct 22 '14 at 09:32

talat

68,970
21
126
157

answered Oct 22 '14 at 09:29

KFB

3,501
3
15
18

score 1 · Answer 2 · answered Oct 23 '14 at 07:48

The problem is that the NA that you see in the result of "t1" is actually an NA value in the dimnames, so you can't directly use as.data.frame.matrix.

I guess if you do this a lot and want to save typing, the best recourse might be to write a function like the following:

dimFixDF <- function(intable) {
  as.data.frame.matrix(`dimnames<-`(intable, lapply(dimnames(intable), 
    function(x) {
      ifelse(is.na(x), "<NA>", x)
    })))
}

When you use it, it replaces the NA values in the dimnames with the character representation, and then it converts it to a data.frame with as.data.frame.matrix.

dimFixDF(t1)
#      4 6  8 <NA>
# 3    1 2 12    0
# 4    8 4  0    0
# 5    2 1  2    0
# <NA> 0 0  0    0

score 0 · Answer 3 · answered Oct 22 '14 at 09:32

If you're happy with a matrix, you can do this in base R by setting the object's class to 'matrix':

t1  <- with(mtcars, table(gear, cyl, exclude = NULL))
class(t1) <- 'matrix'

t1
#       cyl
# gear   4 6  8 <NA>
#   3    1 2 12    0
#   4    8 4  0    0
#   5    2 1  2    0
#   <NA> 0 0  0    0  

str(t1)
#  int [1:4, 1:4] 1 8 2 0 2 4 1 0 12 0 ...
#  - attr(*, "dimnames")=List of 2
#   ..$ gear: chr [1:4] "3" "4" "5" NA
#   ..$ cyl : chr [1:4] "4" "6" "8" NA

Convert a table with missing values to a data frame of the same structure as the original table?

3 Answers3