import pandas as pd

import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

import sqlite3

con = sqlite3.connect('../input/database.sqlite')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

def load(what='NationalNames'):
    assert what in ('NationalNames', 'StateNames')
    cols = ['Name', 'Year', 'Gender', 'Count']
    if what == 'StateNames':
        cols.append('State')
    df = pd.read_sql_query('SELECT {} from {}'.format(','.join(cols), what), con)
    return df

df2 = load(what='StateNames')

tmp = df2.groupby(['Year', 'State']).agg({'Count': 'sum'}).reset_index()
largest_states = tmp.groupby('State').agg({'Count': 'sum'}).sort_values('Count', ascending=False).index[:5].tolist()
tmp.pivot(index='Year', columns='State', values='Count')[largest_states].plot()