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

user_17399178 0
Viewed 64

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?

dataframe r sum
2 Answers

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))
user_152931910

We may use match (from base R) between 'Code' on 'df.A' to 'WTeamCode', 'LTeamCode' in df.B to get the matching index, to extract the corresponding 'Score' columns and get the sum (+)

df.A$Points <- with(df.A, df.B$WTeamScore[match(Code, 
       df.B$WTeamCode)] + 
       df.B$LTeamScore[match(Code, df.B$LTeamCode)])

-output

> df.A
  Season   TeamName      Code Points
1   2003  Creighton 2003-1166     20
2   2003 Notre Dame 2003-1323     30
3   2003    Arizona 2003-1112     25

If there are nonmatches resulting in missing values (NA) from match, cbind the vectors to create a matrix and use rowSums with na.rm = TRUE

df.A$Points <- with(df.A, rowSums(cbind(df.B$WTeamScore[match(Code, 
   df.B$WTeamCode)],  
     df.B$LTeamScore[match(Code, df.B$LTeamCode)]), na.rm = TRUE))

data

df.A <- structure(list(Season = c(2003L, 2003L, 2003L), TeamName = 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))
user_37322710
Related