Can I have a more simple code? (r , dplyr)

Question

I need help regarding this database https://www.kaggle.com/datasets/hugomathien/soccer. I need a table of the date , the hometeam name , the away team name , the goals the hometeam scored and the goals the away team scored, for a random game ( I chose match_api_id = 492476). I use this code in r :

con <- DBI::dbConnect(RSQLite::SQLite(), "data/database.sqlite")
library(tidyverse)
library(DBI)
match<-tbl(con,"Match")
team<-tbl(con,"Team")

T1<-match %>%
  left_join(team, by = c(away_team_api_id="team_api_id"))
T1<-T1 %>% rename(away_team_name = team_long_name)

T2<-match %>%
  left_join(team, by = c(home_team_api_id="team_api_id"))
T2<-T2 %>% rename(home_team_name = team_long_name)

TT<-T1 %>%
  left_join(T2,by = c("match_api_id","date","home_team_goal","away_team_goal"))        

finalT<-TT %>%
  left_join(match,by = c("match_api_id","date","home_team_goal","away_team_goal")) %>%
  filter(match_api_id==492476) %>%
  select(home_team_name,away_team_name,date,home_team_goal,away_team_goal)

glimpse(finalT)

and it works, but can i take take the same table with a more simple and straighforward code , without defining all theese tables?

Answer 1

Try this to reduce the objects created:

library(tidyverse)

con <- DBI::dbConnect(RSQLite::SQLite(), "database.sqlite")

team <- tbl(con, "Team") %>% 
  select(team_api_id, team_long_name)

tbl(con, "Match") %>% 
  select(date, ends_with("api_id"), ends_with("_goal")) %>%
  left_join(team, by = c("home_team_api_id" = "team_api_id")) %>% 
  rename(home_team = team_long_name) %>% 
  left_join(team, by = c("away_team_api_id" = "team_api_id")) %>% 
  rename(away_team = team_long_name) %>%
  filter(match_api_id == 492476) %>%
  select(-ends_with("_id"))