by Felipe Hoffa
World Cup visualized: The most valuable players
Check out the players who touched the ball most often with this interactive dashboard. Data from Opta Sports, analyzed with BigQuery, visualized with Data Studio.
With the Opta Sports fútbol dataset we can find out each time a player touched the ball — and where in the field they where at that time.
With a quick query we can find out who are the players that touched most the ball during these last few days:
We can also jump back to 2014:
Or just looking at the top attackers:
And the ones that build the game on the middle field:
Or you can just check out your favorite team:
How I Built this Visualization
- Count the number of times each player touched the ball.
- Check their position (0–100) and assign it one of the 3 thirds of the field.
#standardSQLWITH team_names AS ( SELECT team_id, REGEXP_REPLACE(MIN(name), r'C..te', 'Cote') name FROM ( SELECT away_team_id team_id, away_team_name name FROM `cloude-sandbox.galacticos.games` WHERE competition_id = 4 UNION ALL SELECT home_team_id team_id, home_team_name name FROM `cloude-sandbox.galacticos.games` WHERE competition_id = 4 ) GROUP BY 1), player_touches AS ( SELECT COUNT(*) touches , (SELECT name FROM team_names WHERE team_id=a.team_id) team , LEAST(FLOOR(x/33.3333),2.0) x_group , (SELECT CONCAT(MAX(name)) FROM `cloude-sandbox.galacticos.sqauds` WHERE player_id = CONCAT('p', CAST(a.player_id AS STRING))) player , EXTRACT(YEAR FROM event_timestamp) year FROM `cloude-sandbox.galacticos.events` a WHERE competition_id = 4 AND x>0 AND y>0 AND EXTRACT( YEAR FROM event_timestamp) IN (2014,2018) GROUP BY team, x_group, player, year)
SELECT SUM(touches) touches, team, player , SUM(IF(x_group=1, touches,0)) middlefield , SUM(IF(x_group=0, touches,0)) defense , SUM(IF(x_group=2, touches,0)) attack , yearFROM player_touchesWHERE NOT player IS nullGROUP BY team, player, yearORDER BY 1 DESC
The 2018 World Cup Visualized: All the Goals So Far
Check out all the goals so far with this Data Studio interactive visualization. Data extracted using BigQuery. keep…medium.freecodecamp.orgMaking World Cup Sausage with Cloud Dataflow and BigQuery
The 2018 World Cup is finally here. In the opening match, Saudi Arabia will take the pitch against the host country…medium.comThe 2018 World Cup Visualized: All the Goals So Far
Check out all the goals so far with this Data Studio interactive visualization. Data extracted using BigQuery. keep…medium.freecodecamp.org