Adding 2 DF's of Different Sizes Together

user_11453277 0
Viewed 55

I have two DF's:

passesComb <- structure(list(P1_Good = c(0, 1, 0, 0, 0, 0, 1), P2_Good = c(2, 
0, 0, 0, 0, 0, 2), P3_Good = c(0, 1, 0, 0, 0, 0, 1), P4_Good = c(0, 
0, 1, 0, 0, 0, 1), P5_Good = c(0, 0, 0, 1, 0, 0, 1), P1_Bad = c(0, 
0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 0, 0, 0, 0, 0), P3_Bad = c(0, 
0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 1, 0, 0, 0, 1), P5_Bad = c(0, 
0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 1, 0, 0, 1, 1), `Good Pass` = c(2, 
2, 1, 1, 0, 3, 6), `Intercepted Pass` = c(0, 0, 0, 0, 0, 1, 0
), Turnover = c(0, 0, 0, 0, 0, 1, 0), totalEvents = c(2, 2, 2, 
1, 0, 6, 7)), row.names = c("P1", "P2", "P3", "P4", "P5", "Opponent", 
"VT"), class = "data.frame")

of size 7x15, and

copyComb <- structure(list(P1_Good = c(0, 1, 0, 0, 0, 1), P2_Good = c(2, 
0, 0, 0, 0, 2), P4_Good = c(0, 0, 0, 0, 0, 0), P5_Good = c(0, 
0, 1, 0, 0, 1), P1_Bad = c(0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 
0, 0, 0, 0), P3_Bad = c(0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 0, 
0, 0, 0), P5_Bad = c(0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 
0, 0, 1, 0), `Good Pass` = c(2, 1, 1, 0, 3, 4), `Intercepted Pass` = c(0, 
0, 0, 0, 1, 0), Turnover = c(0, 0, 0, 0, 1, 0), totalEvents = c(2, 
1, 1, 0, 6, 4)), row.names = c("P1", "P2", "P4", "P5", "Opponent", 
"VT"), class = "data.frame")

or simply,

copyComb <- passesComb
copyComb <- copyComb[-3,-3]
#Updating specific cells since [3,3] is removed
copyComb[2,11] <- 1
copyComb[2,14] <- 1
copyComb[6,8] <- 0
copyComb[6,3] <- 0
copyComb[6,10] <- 0
copyComb[6,11] <- 4
copyComb[6,14] <- 4
#This now equals the copyComb from dput() above

of size 6x14.

I am trying to combine/add these two df's together based on matching row/column names. I tried to achieve this using the code from the answer to this post

gamesComb <- data.frame(matrix(NA, nrow = ifelse(nrow(passesComb) >= nrow(copyComb), nrow(passesComb),nrow(copyComb)),
                               ncol = ifelse(ncol(passesComb) >= ncol(copyComb), ncol(passesComb),ncol(copyComb))))
                        
gamesComb[row.names(ifelse(nrow(passesComb) >= nrow(copyComb), passesComb, copyComb)),
                           colnames(ifelse(ncol(passesComb) >= ncol(copyComb), passesComb, copyComb))] <- passesComb

Here, I create a df, gamesComb and set the dimensions of whichever passesComb or copyComb is bigger. It does create a 7x15 df, but doesn't add the row/col names.

I also am trying to then add the 2 df's together based on the cell value if they have the same row/col name (same as in the post link above), i.e. passesComb["P2","P1_Good"] = 1 and copyComb["P2","P1_Good"] = 1, so gamesComb["P2","P1_Good"] should = 2, and same for all similar row/col names.

So the final result look like:

expectedOutput <- structure(list(P1_Good = c(0, 2, 0, 0, 0, 0, 2), P2_Good = c(4, 
0, 0, 0, 0, 0, 4), P3_Good = c(0, 1, 0, 0, 0, 0, 1), P4_Good = c(0, 
0, 1, 0, 0, 0, 1), P5_Good = c(0, 0, 0, 2, 0, 0, 2), P1_Bad = c(0, 
0, 0, 0, 0, 0, 0), P2_Bad = c(0, 0, 0, 0, 0, 0, 0), P3_Bad = c(0, 
0, 0, 0, 0, 0, 0), P4_Bad = c(0, 0, 1, 0, 0, 0, 1), P5_Bad = c(0, 
0, 0, 0, 0, 0, 0), `Bad Pass` = c(0, 0, 1, 0, 0, 2, 1), `Good Pass` = c(4, 
3, 1, 2, 0, 6, 10), `Intercepted Pass` = c(0, 0, 0, 0, 0, 2, 
0), Turnover = c(0, 0, 0, 0, 0, 2, 0), totalEvents = c(4, 3, 
2, 2, 0, 12, 11)), row.names = c("P1", "P2", "P3", "P4", "P5", 
"Opponent", "VT"), class = "data.frame")
dplyr r
2 Answers

Here's a dplyr/tidyr approach where I reshape each table into a long format, then join them, sum, and pivot wider again.

library(dplyr); library(tidyr)
lengthen <- function(df) { df %>% rownames_to_column(var = "row") %>% pivot_longer(-row)}

full_join(lengthen(passesComb), lengthen(copyComb), by = c("row", "name")) %>%
  mutate(new_val = coalesce(value.x, 0) + coalesce(value.y, 0)) %>%
  select(-starts_with("value")) %>%
  pivot_wider(names_from = name,values_from = new_val)
user_68518250

Another option is to stack them and then sum by rowname groups.

library(dplyr, warn.conflicts = FALSE)
library(tibble)

out <- 
  rownames_to_column(passesComb) %>% 
    bind_rows(rownames_to_column(copyComb)) %>% 
    # bind_rows(rownames_to_column(third_table)) %>% if you want to add another
    select(rowname, names(passesComb)) %>% 
    group_by(rowname) %>% 
    summarise(across(everything(), sum, na.rm = T)) %>% 
    slice(match(rownames(passesComb), rowname)) %>% 
    column_to_rownames('rowname')
  
all.equal(out, expectedOutput)
#> [1] TRUE

Created on 2021-10-09 by the reprex package (v2.0.1)

user_67627550
Related