from scipy.sparse import spdiags, coo_matrix

import networkx as nx

import numpy as np

import numpy as np

import pandas as pd

import plotly

import sqlite3

conn = sqlite3.connect('../input/database.sqlite')
teams = pd.read_sql_query('select * from Teams', conn)
users = pd.read_sql_query('select * from Users', conn)
teammembers = pd.read_sql_query('select * from TeamMemberships', conn)

teams_q = teammembers.groupby('TeamId').UserId.count()
teams_q = teams_q[teams_q > 1].reset_index()
teammembers_cut = teammembers.merge(teams_q, on='TeamId')
users_q = teammembers_cut.groupby('UserId_x').TeamId.count().reset_index()
teammembers_cut = teammembers_cut.merge(users_q, left_on='UserId_x', right_on='UserId_x')
teammembers_cut = teammembers_cut.merge(teams, left_on='TeamId_x', right_on='Id')
teammembers_cut = teammembers_cut.merge(users, left_on='UserId_x', right_on='Id')
tm4graph = teammembers_cut[['TeamId_x', 'UserId_x']]
tm4graph['TeamId_x'] = 'Team_' + tm4graph['TeamId_x'].astype('str')
tm4graph['UserId_x'] = 'User_' + tm4graph['UserId_x'].astype('str')

from scipy.sparse import spdiags, coo_matrix
import scipy as sp
import numpy as np
import matplotlib.pyplot as plt
def forceatlas2_layout(G, iterations=10, linlog=False, pos=None, nohubs=False, kr=0.001, k=None, dim=2):
    """
    Options values are
    g                The graph to layout
    iterations       Number of iterations to do
    linlog           Whether to use linear or log repulsion
    random_init      Start with a random position
                     If false, start with FR
    avoidoverlap     Whether to avoid overlap of points
    degreebased      Degree based repulsion
    """
    for n in G:
        G.node[n]['prevcs'] = 0
        G.node[n]['currcs'] = 0
    A = nx.to_scipy_sparse_matrix(G, dtype='f')
    (nnodes, _) = A.shape
    try:
        A = A.tolil()
    except Exception as e:
        A = coo_matrix(A).tolil()
    if pos is None:
        pos = np.asarray(np.random.random((nnodes, dim)), dtype=A.dtype)
    else:
        pos = pos.astype(A.dtype)
    if k is None:
        k = np.sqrt(1.0 / nnodes)
    t = 0.1
    dt = t / float(iterations + 1)
    displacement = np.zeros((dim, nnodes))
    for iteration in range(iterations):
        displacement *= 0
        for i in range(A.shape[0]):
            delta = (pos[i] - pos).T
            distance = np.sqrt((delta ** 2).sum(axis=0))
            distance = np.where(distance < 0.01, 0.01, distance)
            Ai = np.asarray(A.getrowview(i).toarray())
            Dist = k * k / distance ** 2
            if nohubs:
                Dist = Dist / float(Ai.sum(axis=1) + 1)
            if linlog:
                Dist = np.log(Dist + 1)
            displacement[:, i] += (delta * (Dist - Ai * distance / k)).sum(axis=1)
        length = np.sqrt((displacement ** 2).sum(axis=0))
        length = np.where(length < 0.01, 0.01, length)
        pos += (displacement * t / length).T
        t -= dt
    return dict(zip(G, pos))

axis = dict(showline=False, zeroline=False, showgrid=False, showticklabels=False, title='')
layout = Layout(title='Kaggle teams/users universe', font=Font(size=12), showlegend=True, autosize=False, width=800, height=800, xaxis=XAxis(axis), yaxis=YAxis(axis), margin=Margin(l=40, r=40, b=85, t=100), hovermode='closest', annotations=Annotations([Annotation(showarrow=False, text='', xref='paper', yref='paper', x=0, y=-0.1, xanchor='left', yanchor='bottom', font=Font(size=14))]))

edges_to_use = 22000
G = nx.Graph()
G.add_edges_from(tm4graph.values[0:edges_to_use])
pos = forceatlas2_layout(G, iterations=300, nohubs=True)
N = G.number_of_nodes()
E = G.edges()
labels = G.nodes()
Xv_teams = [pos[k][0] for k in labels if 'Team' in k]
Yv_teams = [pos[k][1] for k in labels if 'Team' in k]
Xv_users = [pos[k][0] for k in labels if 'User' in k]
Yv_users = [pos[k][1] for k in labels if 'User' in k]
labels_team = [teammembers_cut.iloc[0:edges_to_use, :].loc[teammembers_cut.TeamId_x == int(k.replace('Team_', '')), 'TeamName'].values[0] for k in labels if 'Team' in k]
labels_users = [teammembers_cut.iloc[0:edges_to_use, :].loc[teammembers_cut.UserId_x == int(k.replace('User_', '')), 'DisplayName'].values[0] for k in labels if 'User' in k]
Xed = []
Yed = []
for edge in E:
    Xed += [pos[edge[0]][0], pos[edge[1]][0], None]
    Yed += [pos[edge[0]][1], pos[edge[1]][1], None]
trace3 = Scatter(x=Xed, y=Yed, mode='lines', line=Line(color='rgb(200,200,200)', width=2), name='Links', hoverinfo='none')
trace4 = Scatter(x=Xv_teams, y=Yv_teams, mode='markers', name='Teams', marker=Marker(symbol='dot', size=[teammembers_cut.iloc[0:edges_to_use, :].loc[teammembers_cut.TeamId_x == int(k.replace('Team_', '')), 'UserId_y'].values[0] for k in labels if 'Team' in k], color='rgb(146,209,81)', line=Line(color='rgb(50,50,50)', width=0.5)), text=map(lambda x: ['Team: ' + u''.join(x[0]).encode('utf8').strip() + '<br>Users: ' + str(','.join(x[1]).encode('utf8')) + '<br>'], zip(labels_team, [teammembers_cut.iloc[0:edges_to_use, :].loc[teammembers_cut.TeamId_x == int(k.replace('Team_', '')), 'DisplayName'].values.tolist() for k in labels if 'Team' in k])), hoverinfo='text')
trace5 = Scatter(x=Xv_users, y=Yv_users, mode='markers', name='Users', marker=Marker(symbol='dot', size=[teammembers_cut.iloc[0:edges_to_use, :].loc[teammembers_cut.UserId_x == int(k.replace('User_', '')), 'TeamId_y'].values[0] * 0.5 for k in labels if 'User' in k], color='#000000', line=Line(color='rgb(50,50,50)', width=0.5)), text=map(lambda x: ['User: ' + u''.join(x[0]).encode('utf8').strip() + '<br>Teams: ' + str(','.join(x[1]).encode('utf8')) + '<br>'], zip(labels_users, [teammembers_cut.iloc[0:edges_to_use, :].loc[teammembers_cut.UserId_x == int(k.replace('User_', '')), 'TeamName'].values.tolist() for k in labels if 'User' in k])), hoverinfo='text')
data1 = Data([trace3, trace4, trace5])
fig1 = Figure(data=data1, layout=layout)
plotly.offline.iplot(fig1)