application_vnd.jupyter.stderr_output_1.png
from plotly.offline import iplot, init_notebook_mode

import advertools as adv

import pandas as pd

import advertools as adv
import pandas as pd
pd.options.display.max_columns = None
from plotly.tools import make_subplots
import plotly.graph_objs as go
from plotly.offline import iplot, init_notebook_mode
init_notebook_mode()
adv.__version__

column_key = pd.read_csv('../input/column_key.csv')
column_key

clubs = pd.read_csv('../input/clubs.csv')

top_countries = clubs.groupby('Country').agg({'Total': 'sum'}).sort_values('Total', ascending=False).reset_index().head(10)
top_countries

clubs.groupby(['Country']).agg({'Club': 'count', 'Total': 'sum'}).sort_values('Club', ascending=False).reset_index().head(9).set_axis(['country', 'num_clubs', 'total_wins'], axis=1, inplace=False).assign(wins_per_club=lambda df: df['total_wins'].div(df['num_clubs'])).style.background_gradient(high=0.2)

lang_football = {'en': 'football', 'fr': 'football', 'de': 'fußball', 'es': 'fútbol', 'it': 'calcio', 'pt-BR': 'futebol', 'nl': 'voetbal'}
lang_football

serp_clubs = pd.read_csv('../input/serp_clubs.csv')
serp_clubs['totalResults'] = serp_clubs['totalResults'].astype('int')
serp_clubs['queryTime'] = pd.to_datetime(serp_clubs['queryTime'])

club_country = {club.lower(): country.lower() for club, country in zip(clubs['Club'], clubs['Country'])}
football_multi = '|'.join([' ' + football for football in lang_football.values()])
serp_clubs['country'] = [club_country[club].title() for club in serp_clubs['searchTerms'].str.replace(football_multi, '')]
serp_clubs[['searchTerms', 'country']].sample(10)