import matplotlib.pyplot as plt
import numpy as np
from goodies import as_table
%matplotlib inline
import sqlite3
sql_query = """
SELECT country1, teams.title as country2, winner
FROM ( SELECT teams.title as country1, team2_id, winner
FROM (SELECT team1_id, team2_id, winner
FROM games
INNER JOIN rounds on games.round_id=rounds.id
WHERE rounds.title='Final') t1
INNER JOIN teams on teams.id=t1.team1_id) t2
INNER JOIN teams on teams.id=t2.team2_id;
"""
db = sqlite3.connect('../data/worldcup.db')
cur = db.execute(sql_query)
arr = np.fromiter(cur, dtype=[('country1', 'S30'),
('country2', 'S30'),
('winner', 'i2')])
arr['country1'] = np.char.replace(arr['country1'], 'West Germany (-1989)', 'Germany')
arr['country2'] = np.char.replace(arr['country2'], 'West Germany (-1989)', 'Germany')
as_table(arr, maxrows=100)
country1 | country2 | winner |
---|---|---|
Uruguay | Argentina | 1 |
Italy | Czechoslovakia (-1992) | 1 |
Hungary | Italy | 2 |
Hungary | Germany | 2 |
Sweden | Brazil | 2 |
Brazil | Czechoslovakia (-1992) | 1 |
England | Germany | 1 |
Brazil | Italy | 1 |
Netherlands | Germany | 2 |
Netherlands | Argentina | 2 |
Italy | Germany | 1 |
Argentina | Germany | 1 |
Germany | Argentina | 1 |
Brazil | Italy | 1 |
Brazil | France | 2 |
Germany | Brazil | 2 |
Italy | France | 1 |
Netherlands | Spain | 2 |
Germany | Argentina | 1 |
countries = np.unique(np.concatenate([arr['country1'], arr['country2']]))
winners = np.where(arr['winner']==1, arr['country1'], arr['country2'])
loosers = np.where(arr['winner']==2, arr['country1'], arr['country2'])
wins = [np.sum(winners==c) for c in countries]
losses = [np.sum(loosers==c) for c in countries]
stats = np.rec.fromarrays((countries, wins, losses),
names='country,wins,losses')
i = np.argsort(stats, order=('wins', 'losses', 'country'))[::-1]
stats = stats[i]
x = np.arange(len(stats))
plt.bar(x, stats['wins'])
plt.xticks(x+0.5, stats['country'], rotation=90)
plt.ylabel('wins in\nworld cup', rotation=0, labelpad=30)
<matplotlib.text.Text at 0x10d07cf90>
plt.bar(x, stats['wins']+stats['losses'], fc='w', ec='k')
plt.bar(x, stats['wins'], ec='none', fc='k')
plt.xticks(x+0.5, stats['country'], rotation=90)
ax = plt.gca()
[ax.spines[s].set_visible(False) for s in ['top', 'left', 'right']]
#ax.spines['left'].set_position(('outward',20))
ax.xaxis.set_ticks_position('none')
ax.yaxis.set_ticks_position('left')
plt.ylabel('wins vs\nfinals', rotation=0, labelpad=30)
ax.set_axis_bgcolor('0.9')
ax.set_axisbelow(True)
plt.grid(axis='y', ls='-', color='w',zorder=200)
plt.title('FIFA World Cup (1930-2014)')
plt.figtext(0.15,-0.35, 'Data source: openfootball.github.io', size=9)
<matplotlib.text.Text at 0x10d1d3b10>