Merge two tables with uneven columns and keep both values

user_11351694 0
Viewed 76

Example data:

set0 <- data.frame(A = c("A", "B", "C", "D", "A", "B", "C", "D", "A", "B"),
                         B = c("E", "F", "G", "H", "I", "E", "F", "G", "H", "I"))

Some of the columns need to be merged:

set0 <- table(transform(set0, 
                B = ifelse(B %in% c('F', 'G'), 'V', 
                           ifelse(B %in% c('H', 'I'), 'R', B))))
> set0
   B
A   E R V
  A 1 2 0
  B 1 1 1
  C 0 0 2
  D 0 1 1

Then I use the chisq.test$expected to get the results I need:

cset0 <- chisq.test(set0)$expected %>% round(digits = 2)
> cset0
   B
A     E   R   V
  A 0.6 1.2 1.2
  B 0.6 1.2 1.2
  C 0.4 0.8 0.8
  D 0.4 0.8 0.8

I left addmargins(set0) out of the previous one otherwise it changes the outcome of the chisq.test results. Therefore create a new table:

mset0 <- addmargins(set0)
> mset0
     B
A      E  R  V Sum
  A    1  2  0   3
  B    1  1  1   3
  C    0  0  2   2
  D    0  1  1   2
  Sum  2  4  4  10

I am trying to merge mset0 and cset0 into one which should look a bit like:

#      B
#A   |     E  |     R  |     V  | Sum
#  A | 1 [0.6]| 2 [1.2]| 0 [1.2]|   3
#  B | 1 [0.6]| 1 [1.2]| 1 [1.2]|   3
#  C | 0 [0.4]| 0 [0.8]| 2 [0.8]|   2
#  D | 0 [0.4]| 1 [0.8]| 1 [0.8]|   2
# sum|    2   |    4   |    4   |  10
r merge
1 Answers

You can remove last row and last column from mset0 and replace.

mset0[-nrow(mset0), -ncol(mset0)] <- sprintf('%s [%s]', mset0[-nrow(mset0), -ncol(mset0)], cset0)

mset0
#   B
#A     E       R       V       Sum
#  A   1 [0.6] 2 [1.2] 0 [1.2] 3  
#  B   1 [0.6] 1 [1.2] 1 [1.2] 3  
#  C   0 [0.4] 0 [0.8] 2 [0.8] 2  
#  D   0 [0.4] 1 [0.8] 1 [0.8] 2  
#  Sum 2       4       4       10
user_39629140
Related