Los números del Mundial

%load_ext django_orm_magic

%%django_orm

from django.db import models

class Country(models.Model):
    name = models.CharField(max_length="100")
    
    def __unicode__(self):
        return self.name
    
class City(models.Model):
    country = models.ForeignKey('Country')
    name = models.CharField(max_length="100")

    def __unicode__(self):
        return "{0}, {1}".format(self.name, self.country) 
    
class Team(models.Model):
    country = models.ForeignKey('Country')
    group = models.CharField(max_length="100")
    
    def __unicode__(self):
        return unicode(self.country)

class Club(models.Model):
    name = models.CharField(max_length="100")
    country = models.ForeignKey('Country')

    def __unicode__(self):
        return self.name
    
class Player(models.Model):

    full_name = models.CharField(max_length=100)
    date_of_birth = models.DateField(null=True, blank=True)    
    team = models.ForeignKey('Team')
    url = models.URLField(max_length=200, null=True, blank=True)
    
    place_of_birth = models.ForeignKey('City', null=True, blank=True)
    height = models.FloatField(null=True, blank=True)
    position = models.CharField(max_length=2)
    current_club = models.ForeignKey('Club')
    last_season_apps = models.IntegerField(null=True, blank=True)
    last_season_goals = models.IntegerField(null=True, blank=True)
    national_team_apps = models.IntegerField(null=True, blank=True)
    national_team_goals = models.IntegerField(null=True, blank=True)
        
    def __unicode__(self):
        return self.full_name

from pyquery import PyQuery
from IPython.display import HTML, Image
pq = PyQuery('http://en.wikipedia.org/wiki/2014_FIFA_World_Cup_squads')
pq.make_links_absolute()

brazil = pq('table:first').html()
HTML(brazil)

def parse_squad(squad, group):
    country, _ = Country.objects.get_or_create(name=pq(squad).prev().prev().prev().text())
    print "Parsing", country
    team, _ = Team.objects.get_or_create(country=country, group=group)

    for row in pq('tr', squad)[2:]:
        
        position = pq('td:eq(1)', row).text()[-2:]
        full_name = pq('td:eq(2)', row).text()
        print full_name
        url = pq('td:eq(2) a', row).attr('href')
        
        club_country, _ = Country.objects.get_or_create(name=pq('td:eq(5) span.flagicon a', pq('tr', squad)[6]).attr('title'))
        club, _ = Club.objects.get_or_create(name=pq('td:eq(5)', pq('tr', squad)[6]).text(), country=club_country)
        print club
        Player.objects.create(full_name=full_name, url=url, position=position, team=team, current_club=club)
    
        

for i, squad in enumerate(pq('table:not(.sortable)')[:32]):
    parse_squad(squad, "ABCDEFGH"[i / 4])

Player.objects.all()

messi = PyQuery(Player.objects.get(full_name__contains='Messi').url)
messi.make_links_absolute()
HTML(messi('table.infobox').html())

messi('table.infobox').text()

import re
fecha = re.findall(r'\d{4}\-\d{2}\-\d{2}', messi('table.infobox').text())[0]
fecha

from datetime import datetime

def fill_player(player):
    # print 'Retriving data for %s (%d)' % (player, player.id)
    pq = PyQuery(player.url)
    pq.make_links_absolute()
    info = pq('table.infobox').text()
    player.date_of_birth = datetime.strptime(re.findall(r'\d{4}\-\d{2}\-\d{2}', info)[0], "%Y-%m-%d").date()
    try:
        player.height = re.findall(r'(\d{1}\.\d{2})', info)[0]
    except IndexError:
        player.height = float(re.findall(r'Height (\d{3})', info)[0])/100
    
    try:
        player.last_season_apps, player.last_season_goals = re.findall(r'(\d+) \((\d+)\) National team', info)[0]
    except:
        pass
    try: 
        player.national_team_apps, player.national_team_goals =  re.findall(r'(\d+) \((\d+)\)', info)[-1]
    except:
        pass
    player.save()

for player in Player.objects.exclude(url__contains='edit'):
    fill_player(player)

from datetime import date
viejo = Player.objects.filter(date_of_birth__isnull=False).order_by('date_of_birth')[0]
joven = Player.objects.filter(date_of_birth__isnull=False).order_by('-date_of_birth')[0]

today = date.today()
age = lambda player: (today - player.date_of_birth).days / 365.25 
print((viejo.full_name, viejo.team, viejo.date_of_birth, age(viejo)))
print((joven.full_name, joven.team, joven.date_of_birth, age(joven)))

%matplotlib inline

import numpy as np
import matplotlib.pyplot as plt
plt.xkcd()

def team_age(team):
    players = team.player_set.filter(date_of_birth__isnull=False)
    media = sum(map(age, players)) / players.count()
    younger = age(players.order_by('-date_of_birth')[0])
    older = age(players.order_by('date_of_birth')[0])
    return np.array([media, younger, older])


teams = map(unicode, Team.objects.all())
y_pos = np.arange(len(teams)) 
data = np.vstack(map(team_age, Team.objects.all())).T
plt.figure(figsize=(5,10))
plt.xlim([16, 45])
plt.barh(y_pos, data[0], xerr=[data[0] - data[1], data[2] - data[0]], align='center', alpha=0.3)
plt.yticks(y_pos, teams)
plt.show()

from django.db.models import Q

mundial = Q()
for i in xrange(12,31):
    mundial = mundial | Q(date_of_birth__day=i, date_of_birth__month=6)
for i in xrange(1,14):
    mundial = mundial | Q(date_of_birth__day=i, date_of_birth__month=7)
    
for p in Player.objects.filter(mundial):
    print "%s de %s cumple el %d/%d" % (p, p.team, p.date_of_birth.day, p.date_of_birth.month)

alto = Player.objects.filter(height__isnull=False).order_by('-height')[0]
petiso = Player.objects.filter(height__isnull=False).order_by('height')[0]
print "%s de %s es el jugador más alto con %.2f m" % (alto, alto.team, alto.height)
print "%s de %s es el jugador más  con %.2f m" % (petiso, petiso.team, petiso.height)

def team_height(team):
    players = team.player_set.filter(height__isnull=False)
    media = sum([p.height for p in players]) / players.count()
    petiso = players.order_by('height')[0].height
    alto = players.order_by('-height')[0].height
    return np.array([media, petiso, alto])

data = np.vstack(map(team_height, Team.objects.all())).T
plt.figure(figsize=(5,10))
plt.barh(y_pos, data[0], xerr=[data[0] - data[1], data[2] - data[0]], align='center', alpha=0.3)
plt.xlim([1.55, 2.1])
plt.title(u'Altura promedio, maximo y minimo de las selecciones')
plt.yticks(y_pos, teams)
plt.show()

def team_apps(team):
    players = team.player_set.filter(national_team_apps__isnull=False)
    media = sum([p.national_team_apps for p in players]) / players.count()
    nuevito = players.order_by('national_team_apps')[0].national_team_apps
    experimentado = players.order_by('-national_team_apps')[0].national_team_apps
    return np.array([media, nuevito, experimentado])

data = np.vstack(map(team_apps, Team.objects.all())).T
plt.figure(figsize=(5,10))
plt.barh(y_pos, data[0], xerr=[data[0] - data[1], data[2] - data[0]], align='center', alpha=0.3)
plt.title(u'Partidos jugados con la seleccion')
plt.yticks(y_pos, teams)
plt.show()

def team_ataque(team):
    players = team.player_set.filter(position='FW', national_team_goals__isnull=False)
    media = sum([p.national_team_goals for p in players]) / players.count()
    funes_mori = players.order_by('national_team_goals')[0]
    messi = players.order_by('-national_team_goals')[0]
    return (np.array([media, funes_mori.national_team_goals, messi.national_team_goals]), funes_mori, messi)

rows = []
for team in Team.objects.all():
    t, funes_mori, messi = team_ataque(team)
    rows.append(t)
    print "%s. Máximo goleador: %s (%d). Más perro: %s (%d)" % (team, messi, messi.national_team_goals, funes_mori, 
                                                                funes_mori.national_team_goals) 
    
data = np.vstack(rows).T
plt.figure(figsize=(5,10))
plt.barh(y_pos, data[0], xerr=[data[0] - data[1], data[2] - data[0]], align='center', alpha=0.3)
plt.title(u'Goles de delanteros en la selección')
plt.yticks(y_pos, teams)
plt.show()