CODEWITHSUNDEEP
rdata.table
RaiderNAYSHUN
RaiderNAYSHUN

Reputation: 61

How to combine contents of two frequency tables into one frequency table in R?

I had a question about how to combine two frequency tables into one frequency table.

So if I have 2 tables:

table1:

    Col1
    18
    19
    17
    19
    13
    19

table2:

    Col1
    18
    19
    12
    15
    18

I'd like to make a 3rd table, table3 such that table3$"Col2" counts the number of times a number in table3$"Col1" appears in table1$"Col1" and such that table3$"Col3" counts the number of times a number in table3$"Col1" appears in table2$"Col1"

table3$"Col1" is a list of all elements in table1$"Col1" and in table2$"Col2"

table3:

    Col1   Col2   Col3
    12     0      1
    13     1      0
    15     0      1
    17     1      0
    18     1      2
    19     3      1

I originally tried doing this: table3$"Col1"<-table(table1$"Col1",table2$"Col1") but it doesn't work because table1$"Col1" and table2$"Col1" have different lengths:

Error in table(table1$"Col1", table2$"Col1") : all arguments must have the same length

Upvotes: 1

Views: 1301

Answers (3)

Brian Davis
Brian Davis

Reputation: 992

Here's another option:

f <-function(x,y) sum(x %in% y)
V1 <- sort(unique(c(table1$'Col1', table2$'Col1')))
V2 <- sapply(V1,f,x = Col1)
V3 <- sapply(V1,f,x = Col2)
> data.frame(V1,V2,V3)
  V1 V2 V3
1 12  0  1
2 13  1  0
3 15  0  1
4 17  1  0
5 18  1  2
6 19  3  1

Upvotes: 2

Dan
Dan

Reputation: 12084

Here's another dplyr solution.

First, I load the libraries.

library(dplyr)
library(magrittr)

Next, I count each element in both tables using table, then perform a full join. Missing elements in each table will appear as NA.

df <- full_join(data.frame(table(table1)), 
                data.frame(table(table2)), 
                by = c("table1" = "table2"))

Finally, I replace NAs with zeroes, rename the columns, and sort according to the first column. 

df %<>% 
  replace(is.na(.), 0) %>% 
  rename_all(funs(paste("Col", 1:3, sep = ""))) %>% 
  arrange(Col1)

#   Col1 Col2 Col3
# 1   12    0    1
# 2   13    1    0
# 3   15    0    1
# 4   17    1    0
# 5   18    1    2
# 6   19    3    1

Upvotes: 1

Nick Criswell
Nick Criswell

Reputation: 1743

I am going to use a tidyverse solution. There is perhaps a base R approach that could work as well.

library(tidyverse)

table1 <- read.table(text = "    Col1
    18
                     19
                     17
                     19
                     13
                     19", header = TRUE)

table2 <- read.table(text = "    Col1
    18
                     19
                     12
                     15
                     18", header = TRUE)

First, we want to get a list of all possible options for the Col1 column of table3. 

table3 <- data.frame(Col1 = (unique(c(table1$Col1, table2$Col1))))

Then we use the count function from dplyr to get the number of instances of each observation in both table1 and table2. Note, that count returns a column called n to represent the tally of each observation. I rename that to match the column names in your final table3.

df1 <- table1 %>% 
    count(Col1) %>% 
    rename(Col2 = n)
df2 <- table2 %>% 
    count(Col1) %>% 
    rename(Col3 = n)

Finally, we join all of these together with a left_join and then replace missing values with 0. 

table3 <- table3 %>% 
    left_join(df1, by = "Col1") %>% 
    left_join(df2, by = "Col1") %>% 
    mutate(Col2 = ifelse(is.na(Col2), 0, Col2), 
           Col3 = ifelse(is.na(Col3), 0, Col3)) %>% 
    arrange(Col1)

> table3
  Col1 Col2 Col3
1   12    0    1
2   13    1    0
3   15    0    1
4   17    1    0
5   18    1    2
6   19    3    1

Upvotes: 0

Related Questions