import pandas as pd

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')