Add a column to a cross tabulation table from a different data frame-CodePudding

I would like to create a cross tabulation table with table and addmargins and then add a column from another data frame to the table.

Example data:

df <- data.frame(country = c("B", "A", "C", "C", "A", "A", "B", "C", "C"), year = c(2005, 2006, 2005, 2007, 2005, 2005, 2007, 2006, 2006))

df2 <- data.frame(country = c("C", "B", "A", "C", "A", "B", "C", "A", "A", "A", "B", "C", "B", "A", "C"))

I would like to combine:

addmargins(table(df$country, df$year), FUN = list(Total = sum))

addmargins(table(df2$country), FUN = list(Total = sum))

such that I get

        2005 2006 2007 Total df2
A        2    1    0     3   6
B        1    0    1     2   4
C        1    2    1     4   5
Total    4    3    2     9   15

CodePudding user response：

We can use cbind:

cbind(
  addmargins(table(df[ c("country", "year") ]), FUN = list(Total = sum), quiet = TRUE),
  df2 = addmargins(table(df2[ "country"] ), FUN = list(Total = sum)))


#       2005 2006 2007 Total df2
# A        2    1    0     3   6
# B        1    0    1     2   4
# C        1    2    1     4   5
# Total    4    3    2     9  15

CodePudding user response：

tmp <- addmargins(table(df$country, df$year), FUN = list(Total = sum))
tmp2 <- addmargins(table(df2$country), FUN = list(Total = sum))

res <- cbind(as.matrix(tmp), as.matrix(tmp2))
colnames(res)[5] <- "df2"
res
#       2005 2006 2007 Total df2
# A        2    1    0     3   6
# B        1    0    1     2   4
# C        1    2    1     4   5
# Total    4    3    2     9  15

CodePudding user response：

Using tidyverse and janitor packages:

library(tidyverse)
library(janitor)
#> 
#> Attaching package: 'janitor'
#> The following objects are masked from 'package:stats':
#> 
#>     chisq.test, fisher.test

df <- data.frame(country = c("B", "A", "C", "C", "A", "A", "B", "C", "C"), year = c(2005, 2006, 2005, 2007, 2005, 2005, 2007, 2006, 2006))

df2 <- data.frame(country = c("C", "B", "A", "C", "A", "B", "C", "A", "A", "A", "B", "C", "B", "A", "C"))

df %>%
  tabyl(country, year) %>%
  adorn_totals(c("row", "col")) %>%
  inner_join(df2 %>%
               count(country, name = "df2") %>%
               adorn_totals("row"))
#> Joining, by = "country"
#>  country 2005 2006 2007 Total df2
#>        A    2    1    0     3   6
#>        B    1    0    1     2   4
#>        C    1    2    1     4   5
#>    Total    4    3    2     9  15

^{Created on 2022-05-31 by the reprex package (v2.0.1)}