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

from plotly.tools import make_subplots

import advertools as adv

import pandas as pd

import plotly.graph_objs as go

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)

serp_clubs.drop_duplicates(['searchTerms']).groupby('searchTerms', as_index=False).agg({'totalResults': 'sum'}).sort_values('totalResults', ascending=False).reset_index(drop=True).head(15).style.format({'totalResults': '{:,}'})

hl_domain_appearances = serp_clubs.groupby(['hl', 'displayLink']).agg({'rank': 'count'}).reset_index().sort_values(['hl', 'rank'], ascending=False).rename(columns={'rank': 'search_appearances'})
hl_domain_appearances.groupby(['hl']).head(5)

fig = make_subplots(1, 7, print_grid=False, shared_yaxes=True)
for i, lang in enumerate(serp_clubs['hl'].unique()[:7]):
    df = serp_clubs[serp_clubs['hl']==lang]
    
    fig.append_trace(go.Bar(y=df['displayLink'].value_counts().values[:8], 
                            x=df['displayLink'].value_counts().index.str.replace('www.', '')[:8],
                            name=lang,
                            orientation='v'), row=1, col=i+1)

fig.layout.margin = {'b': 150, 'r': 30}
fig.layout.legend.orientation = 'h'
fig.layout.legend.y = -0.5
fig.layout.legend.x = 0.15
fig.layout.title = 'Top Domains by Language of Search'
fig.layout.yaxis.title = 'Number of Appearances on SERPs'
fig.layout.plot_bgcolor = '#eeeeee'
fig.layout.paper_bgcolor = '#eeeeee'
iplot(fig)

fig = make_subplots(1, 7, shared_yaxes=True, print_grid=False)
for i, country in enumerate(serp_clubs['country'].unique()[:7]):
    if country in top_countries['Country'][:7].values:
        df = serp_clubs[serp_clubs['country'] == country]
        fig.append_trace(go.Bar(y=df['displayLink'].value_counts().values[:8], x=df['displayLink'].value_counts().index.str.replace('www.', '')[:8], name=country, orientation='v'), row=1, col=i + 1)
fig.layout.margin = {'b': 150, 'r': 0}
fig.layout.legend.orientation = 'h'
fig.layout.legend.y = -0.5
fig.layout.legend.x = 0.15
fig.layout.title = 'Top Domains by Country of Club'
fig.layout.yaxis.title = 'Number of Appearances on SERPs'
fig.layout.plot_bgcolor = '#eeeeee'
fig.layout.paper_bgcolor = '#eeeeee'
iplot(fig)