Finding sum of data frame column in rows that contain certain value in R

Question

I'm working on a March Madness project. I have a data frame df.A with every team and season. For example:

Season   Team Name   Code
  2003   Creighton   2003-1166
  2003   Notre Dame  2003-1323
  2003   Arizona     2003-1112

And another data frame df.B with game results of of every game every season:

WTeamScore  LTeamScore  WTeamCode  LTeamCode
15          10          2003-1166  2003-1323
20          15          2003-1323  2003-1112
10          5           2003-1112  2003-1166

I'm trying to get a column in df.A that totals the number of points in both wins and losses. Basically:

Season   Team Name   Code        Points
  2003   Creighton   2003-1166   20
  2003   Notre Dame  2003-1323   30
  2003   Arizona     2003-1112   25

There are obviously thousands more rows in each data frame, but this is the general idea. What would be the best way of going about this?

Answer 1

Here is another option using tidyverse, where we can pivot df.B to long form, then get the sum for each team, then join back to df.A.

library(tidyverse)

df.B %>%
  pivot_longer(everything(),names_pattern = "(WTeam|LTeam)(.*)",
               names_to = c("rep", ".value")) %>% 
  group_by(Code) %>% 
  summarise(Points = sum(Score)) %>% 
  left_join(df.A, ., by = "Code")

Output

  Season  Team.Name      Code Points
1   2003  Creighton 2003-1166     20
2   2003 Notre Dame 2003-1323     30
3   2003    Arizona 2003-1112     25

Data

df.A <- structure(list(Season = c(2003L, 2003L, 2003L), Team.Name = c("Creighton", 
"Notre Dame", "Arizona"), Code = c("2003-1166", "2003-1323", 
"2003-1112")), class = "data.frame", row.names = c(NA, -3L))

df.B <- structure(list(WTeamScore = c(15L, 20L, 10L), LTeamScore = c(10L, 
15L, 5L), WTeamCode = c("2003-1166", "2003-1323", "2003-1112"
), LTeamCode = c("2003-1323", "2003-1112", "2003-1166")), class = "data.frame", row.names = c(NA, 
-3L))