CODEWITHSUNDEEP
r
CaitlinP
CaitlinP

Reputation: 55

R dataframe to table with 3 dimensions

This is incidence rate data. Deaths and time-at-risk stratified by farm and year combined I'm trying to put the data into the format needed for epi.2by2 with method = "cohort.time" (epiR package).

Example data: 

test <- rbind(c(12, 2,0), c(29,16,26), c(6941, 6083, 5051), c(4555, 5148, 3608))
colnames(test) <- c(3, 3.5, 4)
rownames(test) <- c("deaths-unexposed", "deaths-exposed", "timeatrisk-unexposed", "timeatrisk-exposed")

farm-year               3  3.5    4
deaths-unexposed       12    2    0
deaths-exposed         29   16   26
timeatrisk-unexposed 6941 6083 5051
timeatrisk-exposed   4555 5148 3608

Output needed: 

    , , farm_year = 3

          deaths timeatrisk 
exposed       
      1      29     6941
      0      12     4555

    , , farm_year = 3.5

          deaths timeatrisk 
exposed       
      1      16     6083
      0       2     6148

etc.

I tried using table() in several ways but it gives me extended tables with lots of 0's for each farm_year stratum. I'm sure it's a simple answer (reshape? xtabs?) I just can't find it!

Upvotes: 1

Views: 2773

Answers (2)

ahly
ahly

Reputation: 1131

Using data.table

test <- as.data.table(t(rbind(c(12, 2,0), c(29,16,26), c(6941, 6083, 5051), c(4555, 5148, 3608))))

colnames(test) <- c("deaths-unexposed", "deaths-exposed", "timeatrisk-unexposed", "timeatrisk-exposed")
test[, farm_year := c(3, 3.5, 4)]

result <- melt(test, measure = patterns("^deaths", "timeatrisk"), value.name = c("deaths", "timeatrisk"), variable.name = "Exposed", variable.factor=FALSE)
result[, Exposed := as.numeric(Exposed) - 1]

result[farm_year==3]
   farm_year Exposed deaths timeatrisk
1:         3       0     12       6941
2:         3       1     29       4555

result[farm_year==3.5]
   farm_year Exposed deaths timeatrisk
1:       3.5       0      2       6083
2:       3.5       1     16       5148

Upvotes: 1

alistaire
alistaire

Reputation: 43334

It's a bit of munging:

library(tidyverse)

test_tab <- test %>% 
    t() %>%    # transpose matrix
    as.data.frame() %>% 
    rownames_to_column('farm_year') %>% 
    gather(var, val, -farm_year) %>%    # reshape to long form
    separate(var, c('variable', 'exposed')) %>%    # separate variables
    xtabs(val ~ exposed + variable + farm_year, .)

test_tab
#> , , farm_year = 3
#> 
#>            variable
#> exposed     deaths timeatrisk
#>   exposed       29       4555
#>   unexposed     12       6941
#> 
#> , , farm_year = 3.5
#> 
#>            variable
#> exposed     deaths timeatrisk
#>   exposed       16       5148
#>   unexposed      2       6083
#> 
#> , , farm_year = 4
#> 
#>            variable
#> exposed     deaths timeatrisk
#>   exposed       26       3608
#>   unexposed      0       5051

Upvotes: 1

Related Questions