Skip to content

Instantly share code, notes, and snippets.

@TeijiW

TeijiW/pl_matches_dataset.livemd

Last active September 8, 2024 16:03
Show Gist options
  • Save TeijiW/d2ca73bc3dc68a79d73a2dbfb0be6688 to your computer and use it in GitHub Desktop.
Save TeijiW/d2ca73bc3dc68a79d73a2dbfb0be6688 to your computer and use it in GitHub Desktop.
Download ZIP
dados
Raw
pl_matches_dataset.livemd

Testes com dados

Mix.install([
  {:kino_db, "~> 0.2.10"},
  {:explorer, "~> 0.9.0"},
  {:req, "~> 0.4.8"},
  {:kino_explorer, "~> 0.1.18"},
  {:nx, "~> 0.5"},
  {:csv, "~> 3.2"},
  {:kino_vega_lite, "~> 0.1.11"}
])

Requires

require Explorer.DataFrame, as: DF
require Explorer.Series, as: Series

Input de dados

response = Req.get!("https://raw.githubusercontent.com/joaorlima/data-sets/master/premier-league-data.txt")

Equipes com mais gols no período do dataset

data = DF.load_csv!(response.body)

data = data
|> DF.filter(season != "?")
|> DF.mutate(away_goals: if(away_goals < 0, do: 0, else: away_goals))
|> DF.mutate(home_goals: if(home_goals < 0, do: 0, else: home_goals))

home = data
|> DF.discard(["away_goals", "away_team", "season", "result"])
|> DF.group_by("home_team")
|> DF.summarise(goals: sum(home_goals))

away = data
|> DF.discard(["home_goals", "home_team", "season", "result"])
|> DF.group_by("away_team")
|> DF.summarise(goals: sum(away_goals))


merged_data = DF.join(home, away, how: :inner, on: [home_team: :away_team])

result = merged_data 
|> DF.mutate(goals_total: goals + goals_right)
|> DF.mutate(team: home_team)
|> DF.discard(["goals_right", "goals", "home_team"])
|> DF.sort_by(desc: goals_total)
VegaLite.new(width: 700, height: 600, title: "Total de gols")
|> VegaLite.data_from_values(result, only: ["goals_total", "team"])
|> VegaLite.mark(:bar)
|> VegaLite.encode_field(:x, "goals_total", type: :quantitative)
|> VegaLite.encode_field(:y, "team", type: :nominal)
Raw
pl_matches_dataset.sql
WITH
HOME AS (
SELECT
HOME_TEAM TEAM,
SUM(
CASE
WHEN HOME_GOALS < 0 THEN 0
ELSE HOME_GOALS
END
) GOALS
FROM
PL_MATCHES
GROUP BY
HOME_TEAM
),
AWAY AS (
SELECT
AWAY_TEAM TEAM,
SUM(
CASE
WHEN AWAY_GOALS < 0 THEN 0
ELSE AWAY_GOALS
END
) GOALS
FROM
PL_MATCHES
GROUP BY
TEAM
)
SELECT
COALESCE(HOME.TEAM, AWAY.TEAM) AS TEAM,
COALESCE(HOME.GOALS, 0) + COALESCE(AWAY.GOALS, 0) AS GOALS
FROM
HOME
FULL OUTER JOIN AWAY ON HOME.TEAM = AWAY.TEAM
ORDER BY
GOALS DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment