Team scores in football tournament

# Table: Teams
# 
# +---------------+----------+
# | Column Name   | Type     |
# +---------------+----------+
# | team_id       | int      |
# | team_name     | varchar  |
# +---------------+----------+
# team_id is the column with unique values of this table.
# Each row of this table represents a single football team.
# 
#  
# 
# Table: Matches
# 
# +---------------+---------+
# | Column Name   | Type    |
# +---------------+---------+
# | match_id      | int     |
# | host_team     | int     |
# | guest_team    | int     |
# | host_goals    | int     |
# | guest_goals   | int     |
# +---------------+---------+
# match_id is the column of unique values of this table.
# Each row is a record of a finished match between two different teams.
# Teams host_team and guest_team are represented by their IDs in the Teams table (team_id), and they scored host_goals and guest_goals goals, respectively.
# 
#  
# 
# You would like to compute the scores of all teams after all matches. Points are awarded as follows:
# 
# A team receives three points if they win a match (i.e., Scored more goals than the opponent team).
# 
# A team receives one point if they draw a match (i.e., Scored the same number of goals as the opponent team).
# 
# A team receives no points if they lose a match (i.e., Scored fewer goals than the opponent team).
# 
# Write a solution that selects the team_id, team_name and num_points of each team in the tournament after all described matches.
# 
# Return the result table ordered by num_points in decreasing order. In case of a tie, order the records by team_id in increasing order.
# 
# The result format is in the following example.
#
# Example 1:
# Input:
# Teams table:
# +-----------+--------------+
# | team_id   | team_name    |
# +-----------+--------------+
# | 10        | Leetcode FC  |
# | 20        | NewYork FC   |
# | 30        | Atlanta FC   |
# | 40        | Chicago FC   |
# | 50        | Toronto FC   |
# +-----------+--------------+
# Matches table:
# +------------+--------------+---------------+-------------+--------------+
# | match_id   | host_team    | guest_team    | host_goals  | guest_goals  |
# +------------+--------------+---------------+-------------+--------------+
# | 1          | 10           | 20            | 3           | 0            |
# | 2          | 30           | 10            | 2           | 2            |
# | 3          | 10           | 50            | 5           | 1            |
# | 4          | 20           | 30            | 1           | 0            |
# | 5          | 50           | 30            | 1           | 0            |
# +------------+--------------+---------------+-------------+--------------+
# Output:
# +------------+--------------+---------------+
# | team_id    | team_name    | num_points    |
# +------------+--------------+---------------+
# | 10         | Leetcode FC  | 7             |
# | 20         | NewYork FC   | 3             |
# | 50         | Toronto FC   | 3             |
# | 30         | Atlanta FC   | 1             |
# | 40         | Chicago FC   | 0             |
# +------------+--------------+---------------+

import pandas as pd

def team_scores(teams: pd.DataFrame, matches: pd.DataFrame) -> pd.DataFrame:
  if matches.empty:
    teams['num_points'] = 0
    return teams[['team_id', 'team_name', 'num_points']].sort_values(['num_points', 'team_id'], ascending=[False, True])

  # Host points
  matches['host_points'] = matches.apply(
    lambda r: 3 if r['host_goals'] > r['guest_goals'] else (1 if r['host_goals'] == r['guest_goals'] else 0), axis=1
  )
  matches['guest_points'] = matches.apply(
    lambda r: 3 if r['guest_goals'] > r['host_goals'] else (1 if r['guest_goals'] == r['host_goals'] else 0), axis=1
  )

  host = matches.groupby('host_team')['host_points'].sum().reset_index()
  host.columns = ['team_id', 'num_points']
  guest = matches.groupby('guest_team')['guest_points'].sum().reset_index()
  guest.columns = ['team_id', 'num_points']

  points = pd.concat([host, guest]).groupby('team_id')['num_points'].sum().reset_index()
  result = teams.merge(points, on='team_id', how='left').fillna(0)
  result['num_points'] = result['num_points'].astype(int)
  return result[['team_id', 'team_name', 'num_points']].sort_values(['num_points', 'team_id'], ascending=[False, True]).reset_index(drop=True)