How do I merge and aggregate two tables with row and column variables in R?

user_18763627 0
Viewed 28

I'm learning to use R using my classroom data. I scrape data from the local school website onto excel and then read it into R. After each quiz, I want to aggregate data into one table. For example, I have two tables

    Subject Correct Incorrect
1      Math       9         1
2 Chemistry       7         6


    Subject Correct Incorrect
1    Math       4         3
2 Biology       3         6

I want to end up with

    Subject Correct Incorrect
1      Math      13         4
2 Chemistry       7         6
3   Biology       3         6

I'm sorry if this is too basic or something that has already been answered.

r
2 Answers

Here is a base R way with rbind and aggregate.

x<-'Subject Correct Incorrect
1      Math       9         1
2 Chemistry       7         6
'
df1 <- read.table(textConnection(x), header = TRUE)

x<-'Subject Correct Incorrect
1    Math       4         3
2 Biology       3         6'
df2 <- read.table(textConnection(x), header = TRUE)


aggregate(cbind(Correct, Incorrect) ~ Subject, rbind(df1, df2), sum)
#>     Subject Correct Incorrect
#> 1   Biology       3         6
#> 2 Chemistry       7         6
#> 3      Math      13         4

Created on 2022-07-14 by the reprex package (v2.0.1)

user_82454060

Here is a dplyr solution using group_by and summarise

library(tidyverse)

"Subject Correct Incorrect
Math       9         1
Chemistry       7         6" %>% 
  read_table() -> df1

"    Subject Correct Incorrect
Math       4         3
Biology       3         6" %>% 
  read_table() -> df2

df1 %>% 
  bind_rows(df2) %>% 
  group_by(Subject) %>% 
  summarise(Correct = Correct %>% sum(),
            Incorrect = Incorrect %>% sum())
#> # A tibble: 3 × 3
#>   Subject   Correct Incorrect
#>   <chr>       <dbl>     <dbl>
#> 1 Biology         3         6
#> 2 Chemistry       7         6
#> 3 Math           13         4
user_122967120
Related