Dataset: A Comprehensive Database On The FIFA World Cup

About this Dataset

A Comprehensive Database On The FIFA World Cup

The Fjelstul World Cup Database is a comprehensive database about the FIFA World Cup created by Joshua C. Fjelstul, Ph.D. that covers all 21 World Cup tournaments (1930-2018). The database includes 27 datasets (approximately 1.1 million data points) that cover all aspects of the World Cup. The data has been extensively cleaned and cross-validated.

Users can use data from the Fjelstul World Cup Database to calculate statistics about teams, players, managers, and referees. Users can also use the data to predict match results. With many units of analysis and opportunities for merging and reshaping data, the database is also an excellent resource for teaching data science skills, especially in R.

Overview of the database

The 27 datasets in the Fjelstul World Cup Database are organized into 5 groups:

A first group of datasets (containing 9 datasets) includes information about each of the 9 basic units of observation in the database: tournaments (tournaments), including the host country, the winner, the dates of the tournament, and information about the format of each tournament; the FIFA confederations (confederations); teams (teams); players (players); managers (managers), including their team and home country; referees (referees), including their home country and confederation; stadiums that have hosted World Cup matches (stadiums); matches (matches), including the stage of the tournament, the location of the match (country, city, stadium), the teams involved, and the result; and the individual awards that are handed out to players at each tournament (awards). Each of these units of observation has a unique ID number.
A second group of datasets (containing 4 datasets) maps teams, players, managers, and referees to tournaments. There is a dataset about which teams qualified (qualified teams), which indicates how each team performed in the tournament; a dataset about squads (squads), which indicates the name, position, and shirt number of each player; a dataset about manager appointments (manager_appointments), which indicates the team and home country of each manager; and a dataset about referee appointments (referee_appointments), which indicates the home country and confederation of each referee.
A third group of datasets (containing 4 datasets) maps teams, players, managers, and referees to individual matches. There are datasets about team appearances (team_appearances), player appearances (player_apperances), manager appearances (manager appearances), and referee appearances (referee appearances). Players who start a game on the bench but who are not substituted in appear in the squads dataset but not the player_appearances dataset.
A fourth group of datasets (containing 4 datasets) cover in-match events, including: all goals (goals); all attempted penalty kicks in penalty shootouts and their outcomes (penalty_kicks); all bookings (bookings), including yellow cards and red cards; and all substitutions (substitutions). Each dataset includes the minute of the event and the player(s) and team involved. Each of these 4 types of in-match events has a unique ID number.
A fifth group of datasets (containing 6 datasets) cover tournament-level attributes. There a dataset about host countries (host_countries), including the performance of each host country; a dataset about the stages in each tournament (tournament_stages), which records each stage of the tournament, the dates of the stage, and key features of the stage; a dataset about the groups in each group stage (groups), which indicates the name of each group and the number of teams in each group; a dataset about the final standings in each group (group_standings); a dataset about the final standings for each tournament (tournament_standings); and a dataset about all individual player awards handed out at each tournament (award_winners).

Accessing the data

The database is also available via an R package, which is available on GitHub. You can also download the database from GitHub in 4 formats: an .RData version of the database is available in the data/ folder, a .csv version is available in the data-csv/ folder, a .json version is available in the data-json/ folder, and a relational database version (SQLite) is available in the data-sqlite/ folder.

Accessing the codebook

The full codebook for the Fjelstul World Cup Database is available on GitHub. The codebook is available in .pdf format in the codebook/pdf/ folder. It is also available in .csv format in the codebook/csv/ folder. There are 2 files: datasets.csv, which describes the contents of each dataset, and variables.csv, which describes each variable.

The license

The copyright for the original structure and organization of the Fjelstul World Cup Database and for all of the documentation and replication code for the database is owned by Joshua C. Fjelstul, Ph.D.

The Fjelstul World Cup Database and the worldcup package are both published under a CC-BY-SA 4.0 license. This means that you can distribute, modify, and use all or part of the database for commercial or non-commercial purposes as long as (1) you provide proper attribution and (2) any new works you produce based on this database also carry the CC-BY-SA 4.0 license.

To provide proper attribution, according to the CC-BY-SA 4.0 license, you must provide the name of the author ("Joshua C. Fjelstul, Ph.D."), a notice that the database is copyrighted ("© 2022 Joshua C. Fjelstul, Ph.D."), a link to the CC-BY-SA 4.0 license (https://creativecommons.org/licenses/by-sa/4.0/legalcode), and a link to this repository (https://www.github.com/jfjelstul/worldcup). You must also indicate any modifications you have made to the database.

Consistent with the CC-BY-SA 4.0 license, I provide this database as-is and as-available, and make no representations or warranties of any kind concerning the database, whether express, implied, statutory, or other. This includes, without limitation, warranties of title, merchantability, fitness for a particular purpose, non-infringement, absence of latent or other defects, accuracy, or the presence or absence of errors, whether or not known or discoverable.

Tables

Awards

@kaggle.joshfjelstul_world_cup_database.awards

4.35 KB
8 rows
5 columns


CREATE TABLE awards (
  "key_id" BIGINT,
  "award_id" VARCHAR,
  "award_name" VARCHAR,
  "award_description" VARCHAR,
  "year_introduced" BIGINT
);

Award Winners

@kaggle.joshfjelstul_world_cup_database.award_winners

12.94 KB
132 rows
12 columns


CREATE TABLE award_winners (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "award_id" VARCHAR,
  "award_name" VARCHAR,
  "shared" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR
);

Bookings

@kaggle.joshfjelstul_world_cup_database.bookings

117.78 KB
2466 rows
26 columns


CREATE TABLE bookings (
  "key_id" BIGINT,
  "booking_id" VARCHAR,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "shirt_number" BIGINT,
  "minute_label" VARCHAR,
  "minute_regulation" BIGINT,
  "minute_stoppage" BIGINT,
  "match_period" VARCHAR,
  "yellow_card" BIGINT,
  "red_card" BIGINT,
  "second_yellow_card" BIGINT,
  "sending_off" BIGINT
);

Confederations

@kaggle.joshfjelstul_world_cup_database.confederations

4.93 KB
6 rows
5 columns


CREATE TABLE confederations (
  "key_id" BIGINT,
  "confederation_id" VARCHAR,
  "confederation_name" VARCHAR,
  "confederation_code" VARCHAR,
  "confederation_wikipedia_link" VARCHAR
);

Goals

@kaggle.joshfjelstul_world_cup_database.goals

124.64 KB
2548 rows
27 columns


CREATE TABLE goals (
  "key_id" BIGINT,
  "goal_id" VARCHAR,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "shirt_number" BIGINT,
  "player_team_id" VARCHAR,
  "player_team_name" VARCHAR,
  "player_team_code" VARCHAR,
  "minute_label" VARCHAR,
  "minute_regulation" BIGINT,
  "minute_stoppage" BIGINT,
  "match_period" VARCHAR,
  "own_goal" BIGINT,
  "penalty" BIGINT
);

Groups

@kaggle.joshfjelstul_world_cup_database.groups

6.57 KB
117 rows
7 columns


CREATE TABLE groups (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "stage_number" BIGINT,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "count_teams" BIGINT
);

Group Standings

@kaggle.joshfjelstul_world_cup_database.group_standings

20.23 KB
458 rows
19 columns


CREATE TABLE group_standings (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "stage_number" BIGINT,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "position" BIGINT,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "played" BIGINT,
  "wins" BIGINT,
  "draws" BIGINT,
  "losses" BIGINT,
  "goals_for" BIGINT,
  "goals_against" BIGINT,
  "goal_difference" BIGINT,
  "points" BIGINT,
  "advanced" BIGINT
);

Host Countries

@kaggle.joshfjelstul_world_cup_database.host_countries

6.05 KB
22 rows
7 columns


CREATE TABLE host_countries (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "performance" VARCHAR
);

Managers

@kaggle.joshfjelstul_world_cup_database.managers

22.13 KB
357 rows
6 columns


CREATE TABLE managers (
  "key_id" BIGINT,
  "manager_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "country_name" VARCHAR,
  "manager_wikipedia_link" VARCHAR
);

Manager Appearances

@kaggle.joshfjelstul_world_cup_database.manager_appearances

62.05 KB
1842 rows
17 columns


CREATE TABLE manager_appearances (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "manager_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "country_name" VARCHAR
);

Manager Appointments

@kaggle.joshfjelstul_world_cup_database.manager_appointments

22 KB
469 rows
10 columns


CREATE TABLE manager_appointments (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "manager_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "country_name" VARCHAR
);

Matches

@kaggle.joshfjelstul_world_cup_database.matches

65.84 KB
900 rows
37 columns


CREATE TABLE matches (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "group_stage" BIGINT,
  "knockout_stage" BIGINT,
  "replayed" BIGINT,
  "replay" BIGINT,
  "match_date" TIMESTAMP,
  "match_time" VARCHAR,
  "stadium_id" VARCHAR,
  "stadium_name" VARCHAR,
  "city_name" VARCHAR,
  "country_name" VARCHAR,
  "home_team_id" VARCHAR,
  "home_team_name" VARCHAR,
  "home_team_code" VARCHAR,
  "away_team_id" VARCHAR,
  "away_team_name" VARCHAR,
  "away_team_code" VARCHAR,
  "score" VARCHAR,
  "home_team_score" BIGINT,
  "away_team_score" BIGINT,
  "home_team_score_margin" BIGINT,
  "away_team_score_margin" BIGINT,
  "extra_time" BIGINT,
  "penalty_shootout" BIGINT,
  "score_penalties" VARCHAR,
  "home_team_score_penalties" BIGINT,
  "away_team_score_penalties" BIGINT,
  "result" VARCHAR,
  "home_team_win" BIGINT,
  "away_team_win" BIGINT,
  "draw" BIGINT
);

Penalty Kicks

@kaggle.joshfjelstul_world_cup_database.penalty_kicks

24.18 KB
279 rows
19 columns


CREATE TABLE penalty_kicks (
  "key_id" BIGINT,
  "penalty_kick_id" VARCHAR,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "shirt_number" BIGINT,
  "converted" BIGINT
);

Players

@kaggle.joshfjelstul_world_cup_database.players

386.83 KB
7907 rows
12 columns


CREATE TABLE players (
  "key_id" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "birth_date" VARCHAR,
  "goal_keeper" BIGINT,
  "defender" BIGINT,
  "midfielder" BIGINT,
  "forward" BIGINT,
  "count_tournaments" BIGINT,
  "list_tournaments" VARCHAR,
  "player_wikipedia_link" VARCHAR
);

Player Appearances

@kaggle.joshfjelstul_world_cup_database.player_appearances

334.48 KB
18623 rows
22 columns


CREATE TABLE player_appearances (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "shirt_number" BIGINT,
  "position_name" VARCHAR,
  "position_code" VARCHAR,
  "starter" BIGINT,
  "substitute" BIGINT,
  "captain" BIGINT
);

Qualified Teams

@kaggle.joshfjelstul_world_cup_database.qualified_teams

11.72 KB
457 rows
8 columns


CREATE TABLE qualified_teams (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "count_matches" BIGINT,
  "performance" VARCHAR
);

Referees

@kaggle.joshfjelstul_world_cup_database.referees

24.91 KB
380 rows
9 columns


CREATE TABLE referees (
  "key_id" BIGINT,
  "referee_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "country_name" VARCHAR,
  "confederation_id" VARCHAR,
  "confederation_name" VARCHAR,
  "confederation_code" VARCHAR,
  "referee_wikipedia_link" VARCHAR
);

Referee Appearances

@kaggle.joshfjelstul_world_cup_database.referee_appearances

44.41 KB
900 rows
15 columns


CREATE TABLE referee_appearances (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "referee_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "country_name" VARCHAR,
  "confederation_id" VARCHAR,
  "confederation_name" VARCHAR,
  "confederation_code" VARCHAR
);

Referee Appointments

@kaggle.joshfjelstul_world_cup_database.referee_appointments

21.58 KB
511 rows
10 columns


CREATE TABLE referee_appointments (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "referee_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "country_name" VARCHAR,
  "confederation_id" VARCHAR,
  "confederation_name" VARCHAR,
  "confederation_code" VARCHAR
);

Squads

@kaggle.joshfjelstul_world_cup_database.squads

234.31 KB
10142 rows
12 columns


CREATE TABLE squads (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "shirt_number" BIGINT,
  "position_name" VARCHAR,
  "position_code" VARCHAR
);

Stadiums

@kaggle.joshfjelstul_world_cup_database.stadiums

19.12 KB
185 rows
8 columns


CREATE TABLE stadiums (
  "key_id" BIGINT,
  "stadium_id" VARCHAR,
  "stadium_name" VARCHAR,
  "city_name" VARCHAR,
  "country_name" VARCHAR,
  "stadium_capacity" BIGINT,
  "stadium_wikipedia_link" VARCHAR,
  "city_wikipedia_link" VARCHAR
);

Substitutions

@kaggle.joshfjelstul_world_cup_database.substitutions

222.08 KB
6464 rows
24 columns


CREATE TABLE substitutions (
  "key_id" BIGINT,
  "substitution_id" VARCHAR,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "match_date" TIMESTAMP,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "player_id" VARCHAR,
  "family_name" VARCHAR,
  "given_name" VARCHAR,
  "shirt_number" BIGINT,
  "minute_label" VARCHAR,
  "minute_regulation" BIGINT,
  "minute_stoppage" BIGINT,
  "match_period" VARCHAR,
  "going_off" BIGINT,
  "coming_on" BIGINT
);

Teams

@kaggle.joshfjelstul_world_cup_database.teams

15.21 KB
84 rows
11 columns


CREATE TABLE teams (
  "key_id" BIGINT,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "federation_name" VARCHAR,
  "region_name" VARCHAR,
  "confederation_id" VARCHAR,
  "confederation_name" VARCHAR,
  "confederation_code" VARCHAR,
  "team_wikipedia_link" VARCHAR,
  "federation_wikipedia_link" VARCHAR
);

Team Appearances

@kaggle.joshfjelstul_world_cup_database.team_appearances

79.71 KB
1800 rows
36 columns


CREATE TABLE team_appearances (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "match_id" VARCHAR,
  "match_name" VARCHAR,
  "stage_name" VARCHAR,
  "group_name" VARCHAR,
  "group_stage" BIGINT,
  "knockout_stage" BIGINT,
  "replayed" BIGINT,
  "replay" BIGINT,
  "match_date" TIMESTAMP,
  "match_time" VARCHAR,
  "stadium_id" VARCHAR,
  "stadium_name" VARCHAR,
  "city_name" VARCHAR,
  "country_name" VARCHAR,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR,
  "opponent_id" VARCHAR,
  "opponent_name" VARCHAR,
  "opponent_code" VARCHAR,
  "home_team" BIGINT,
  "away_team" BIGINT,
  "goals_for" BIGINT,
  "goals_against" BIGINT,
  "goal_differential" BIGINT,
  "extra_time" BIGINT,
  "penalty_shootout" BIGINT,
  "penalties_for" BIGINT,
  "penalties_against" BIGINT,
  "result" VARCHAR,
  "win" BIGINT,
  "lose" BIGINT,
  "draw" BIGINT
);

Tournaments

@kaggle.joshfjelstul_world_cup_database.tournaments

13.9 KB
21 rows
18 columns


CREATE TABLE tournaments (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "year" BIGINT,
  "start_date" TIMESTAMP,
  "end_date" TIMESTAMP,
  "host_country" VARCHAR,
  "winner" VARCHAR,
  "host_won" BIGINT,
  "count_teams" BIGINT,
  "group_stage" BIGINT,
  "second_group_stage" BIGINT,
  "final_round" BIGINT,
  "round_of_16" BIGINT,
  "quarter_finals" BIGINT,
  "semi_finals" BIGINT,
  "third_place_match" BIGINT,
  "final" BIGINT
);

Tournament Stages

@kaggle.joshfjelstul_world_cup_database.tournament_stages

15.04 KB
107 rows
16 columns


CREATE TABLE tournament_stages (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "stage_number" BIGINT,
  "stage_name" VARCHAR,
  "group_stage" BIGINT,
  "knockout_stage" BIGINT,
  "unbalanced_groups" BIGINT,
  "start_date" TIMESTAMP,
  "end_date" TIMESTAMP,
  "count_matches" BIGINT,
  "count_teams" BIGINT,
  "count_scheduled" BIGINT,
  "count_replays" BIGINT,
  "count_playoffs" BIGINT,
  "count_walkovers" BIGINT
);

Tournament Standings

@kaggle.joshfjelstul_world_cup_database.tournament_standings

6.64 KB
84 rows
7 columns


CREATE TABLE tournament_standings (
  "key_id" BIGINT,
  "tournament_id" VARCHAR,
  "tournament_name" VARCHAR,
  "position" BIGINT,
  "team_id" VARCHAR,
  "team_name" VARCHAR,
  "team_code" VARCHAR
);