Football Unites the World
Since 1930, the FIFA World Cup matches have delivered record-breaking television audiences across the globe, proving football's unique ability to unite millions of fans. But of course, with such an audience, many questions and debates spark up as well. The answer to every debate you’ve ever had with a friend about the history of football is hidden somewhere in the data. Hence, you want to use historic FIFA World Cup data to build an interactive composite view, that allows you to explore the history for yourself and find the answers.
For this challenge, you will be using the Fjelstul World Cup Database which is a comprehensive database containing 27 .csv files about the FIFA World Cup that covers all men's and women's tournaments. The goal of this challenge is to build an interactive composite view using KNIME's View and Widget nodes. A great place to begin your exploration is with the tournament-standings.csv and award-winners.csv files. But don't feel restricted. You are free to use any of the datasets available in the GitHub repository. Whether you choose to combine datasets to tell a story of football history or focus on a very specific statistical question, the choice is yours. Here are some questions to get you thinking:
Which nation holds the record for the most titles?
Has any nation ever won back-to-back titles? Who and when?
Who are considered the tournament's top players and in which years?
Who won the Golden Boot in the 2022 Men's World Cup?
How has the total number of goals per match changed over the decades?
Let your creativity flow!
Dataset: The Fjelstul World Cup Database on GitHub. The data is available as .csv files in the data-csv folder. A detailed description of each dataset can be found in the README.
---
Solution:
We start by reading in all four datasets using a CSV Reader node each: tournament-standings, host-countries, award-winners, and matches.
Preprocessing of the data:
tournament-standings.csv: A Row Filter node keeps only Men's World Cups, filtering out Women's tournaments. An Expression node then isolates the year from the tournament_id which is needed later for the Value Selection Widget node. Finally, a String Replacer node merges results for "West Germany" and "Germany", so the results are counted towards one nation.
host-countries.csv: Same as for tournament-standings, a Row Filter keeps only Men's World Cups and an Expression node isolates the year, so this data lines up with the year values used elsewhere.
award-winners.cv: A Row Filter keeps Golden Ball, Golden Boot, Golden Glove, and Best Young Player categories, dropping any other award types we don't want to on the dashboard.
matches.csv: No preprocessing required.
All four branches go into one component that builds the dashboard.
Building the interactive composite view:
The composite view shows two statistics: The top part shows some all-time statistics, whereas the bottom part allows for selecting a year/tournament and shows tournament-specific statistics.
All-time statistics - nations by number of wins: A Pivot node aggregates the standings by country and counts the number of wins per nation. Next, we use a Row Filter node to keep only those countries that have won at least once and add a Sorter node to sort the filtered table by number of wins in descending order. Lastly, we send the sorted table into a Bar Chart node, which visualizes nations by number of wins.
All-time statistics - summary table: In a second branch, another Pivot node reshapes the standings data, so each tournament year has its own row, and a Column Renamer node cleans up the resulting column names for clarity. The output goes to a Table View node, which gives us a full year-by-year summary of champion, runner-up, third place, and fourth place.
Tournament-specific statistics - year selection: A Value Selection Widget node lets the user pick a tournament year. The node has “re-execution” enabled, which ensures that the views are updated as soon as a new value is selected.
Tournament-specific statistics - standings: The selected year variable is used in the Row Filter to filter the data to only the chosen year/tournament. A Column Filter node keeps only the relevant columns. In a parallel branch, a Reference Row Filter node uses the tournament_id to isolate all matches from the chosen tournament. An Expression node calculates the total_score per match and a GroupBy node aggregates this into total goals scored and number of matches played for the selected tournament. Both tables are combined into a single-row table using the Column Appender node. A Value Lookup and Table Manipulator node combine and align the host-country and standings data for the selected year, and a Table Transposer reshapes the row into a compact key-value layout. The result is displayed in a Tile View node, showing host country, champion, runner-up, total goals, and number of matches for the chosen tournament.
Tournament-specific statistics - award winners: A second Reference Row Filter node filters award-winners to the chosen year/tournament. An Expression node combines the winner's first and last name, a Column Renamer relabels columns for clarity, and a Column Resorter reorders them for display. A Color Palette Designer assigns a distinct color to each award category, and the result is visualized in a Tile View node showing each award, the winner's name and their team.
Headers: Lastly, two Text View nodes display the top and bottom header of the composite view with the second header being parameterized using a flow variable so that it updates according to the selected tournament.