from collections import Counter

import numpy as np

import sqlite3

conn = sqlite3.connect('../input/database.sqlite')
c = conn.cursor()

ids = [i[0] for i in c.execute('SELECT id FROM League').fetchall()]
names = [i[0] for i in c.execute('SELECT name FROM League').fetchall()]
id_league = {i: n for (i, n) in zip(ids, names)}
id_league

ids = [i[0] for i in c.execute('SELECT id FROM Country').fetchall()]
names = [i[0] for i in c.execute('SELECT name FROM Country').fetchall()]
id_country = {i: n for (i, n) in zip(ids, names)}

c.execute('PRAGMA TABLE_INFO(Player_Stats)').fetchall()

cols = ', '.join(['home_player_Y' + str(i) for i in range(1, 12)])
c.execute('SELECT {0:s} FROM Match'.format(cols))
Y_array = c.fetchall()
Y = np.array([a for row in Y_array for a in row])
from collections import Counter
print('Player Y value: # of instances in database (home players)')
Counter(Y)