import csv
import pathlib
import re
import subprocess
import urllib.request

URL = 'https://raw.githubusercontent.com/mneedham/fifa/master/fboaward_menplayer2014_neutral.pdf'

PDF = pathlib.Path(URL.rpartition('/')[2])

TXT = PDF.with_suffix('.txt')

CSV = PDF.with_suffix('.csv')


def load_pages(filepath=TXT, encoding='utf-8', delimiter='\f'):
    text = filepath.read_text(encoding=encoding)
    return text.split(delimiter)


if not PDF.exists():
    urllib.request.urlretrieve(URL, PDF)

if not TXT.exists():  # requires one of popper-utils, miktex-poppler-bin, xpdf
    subprocess.run(['pdftotext', '-layout', PDF, TXT], check=True)

pages = load_pages()

len(pages)

def iterrows(pages):
    first = None
    for p in pages:
        if not p:
            continue
        title, _, rest = p.partition('\n\n\n')
        header, _, rest = rest.partition('\n')
        header = re.split(r' {2,}', header)
        if first is None:
            yield header
            first = title, header
        else:
            assert title, header == first
        table, _, footer = rest.partition('\n                              ')
        table, footer = (s.strip() for s in (table, footer))
        page, total = map(int, re.match(r'(\d+) / (\d+)$', footer).groups())
        for line in table.splitlines():
            if not line:
                continue
            if line.startswith(' '):
                assert line.endswith(' Grenadines')
                continue
            row = re.split(r' {2,}', line)
            assert len(row) == len(header)
            if row[1] == 'St. Vincent and the':
                row[1] += ' Grenadines'
            yield row


rows = list(iterrows(pages))

rows[:2]

rows[163]

def write_csv(rows, filepath=CSV, encoding='utf-8', dialect=csv.excel, has_header=True):
    with filepath.open('w',  encoding=encoding, newline='') as f:
        writer = csv.writer(f, dialect=dialect)
        for ndata, r in enumerate(rows, 0 if has_header else 1):
            writer.writerow(r)
    return ndata


write_csv(rows)

import pandas as pd


df = pd.read_csv(CSV, encoding='utf-8', dialect=csv.excel)

df.info()
assert df.set_index(['Vote', 'Country']).index.is_unique
df.head()

df['Vote'].value_counts().to_frame('Votes').sort_index()

pd.set_option('display.max_rows', 250)

df.groupby('Country')['Vote'].value_counts().unstack(fill_value=0)

df.head()

pf = (df.melt('Vote', df.columns[-3:].tolist(), var_name='Rank', value_name='_Name')
     .rename(columns={'_Name': 'Name'})
     .assign(Points=lambda x: x['Rank'].str.extract(r'(\d+)', expand=False).astype(int))
     .pivot_table('Points', ['Vote', 'Name'], 'Rank', aggfunc=[len, 'sum'], fill_value=0)
     .assign(Total=lambda x: x['sum'].sum(axis=1)))

pf.groupby(level='Name').sum().sort_values(by='Total', ascending=False)

pf.loc['Captain'].sort_values(by='Total', ascending=False).head()

pf.loc['Coach'].sort_values(by='Total', ascending=False).head()

pf.loc['Media'].sort_values(by='Total', ascending=False).head()