<jupyter_start><jupyter_text>NBA Database (SQL) IntroductionThe National Basketball Association (NBA) is a men's professional basketball league in North America; composed of 30 teams (29 in the United States and 1 in Canada). It is widely considered to be the premier men's professional basketball league in the world. The NBA is an active member of USA Basketball (USAB), which is recognized by FIBA (also known as the International Basketball Federation) as the national governing body for basketball in the United States. The NBA is one of the four major professional sports leagues in the United States and Canada. NBA players are the world's best paid athletes by average annual salary per player. Here, I am about to design a database for the NBA basketball conference. Here is the mini-world description: Each team has 15 players. Players have names, heights, weights, positions they play. We assume that no two players will have exactly the same name. Each team also has a coach. The coaches’ names, years in practice, and salary will be stored in the database. Again, we assume that no two coaches will have exactly the same name. Each team will play against other teams. For each game, the date of the game, location of the game as well as the score of two teams (home and visitor) will be recorded.Choose three entities:player coach and teamser diagram:![_auto_0](attachment:_auto_0) SQL database![_auto_0](attachment:_auto_0)![_auto_1](attachment:_auto_1) Set the keys and tokens for connecting to TweepyGo to http://dev.twitter.com/apps/new to create an app and get values<jupyter_code>import pymysql
import tweepy
import json
from dateutil import parser
import pandas as pd
import re
import pymysql
import sqlalchemy
import numpy as np  
import twitter 
from pandas.io import sql
import csv
import json
import pandas as pd
import sys, getopt, pprint
consumerKey = 'g98ma1alivKBO1F8mgxqsmhwW'
consumerSecret = '<KEY>'
ACCESS_TOKEN = '<KEY>'
ACCESS_SECRET = '<KEY>'
#Use tweepy.OAuthHandler to create an authentication using the given 

auth = tweepy.OAuthHandler(consumer_key=consumerKey, 
    consumer_secret=consumerSecret)

#Connect to the Twitter API using the authentication
api = tweepy.API(auth)
results = []

try:


#Get the first 5000 items based on the search query
 for tweet in tweepy.Cursor(api.search, q='%#Lebronjames', since='2018-04-21',until='2018-04-23').items(1000):
     results.append(tweet)

except tweepy.error.TweepError:
  raise

# Verify the number of items returned print 
len (results)
def toDataFrame(tweets):

    DataSet = pd.DataFrame()

    DataSet['tweetID'] = [tweet.id for tweet in tweets]
    DataSet['tweetText'] = [tweet.text.encode('utf-8') for tweet in tweets]
    DataSet['tweetRetweetCt'] = [tweet.retweet_count for tweet 
    in tweets]
    DataSet['tweetFavoriteCt'] = [tweet.favorite_count for tweet 
    in tweets]
    DataSet['tweetSource'] = [tweet.source for tweet in tweets]
    DataSet['tweetCreated'] = [tweet.created_at for tweet in tweets]




    return DataSet

#Pass the tweets list to the above function to create a DataFrame
DataSet = toDataFrame(results)
DataSet.head(100)
DataSet.to_csv('Lebronjames23.csv' )<jupyter_output><empty_output><jupyter_text>Show the csv data:![_auto_0](attachment:_auto_0) Then what I should do it insert this data into database as my tags"lebronjames"'s table.what I also do to query the tags about lebron james , then get the most popular one as my tags.Next, do the same things to get data from twitter. Social media questions first to use pymysql connect mysql use python.<jupyter_code>import pymysql
import pymysql.cursors
import numpy as np
import pandas as pd
import pprint as pp



db=pymysql.connect(host='localhost',
                          user='root',
                          password='<PASSWORD>',
                          db='finalprojects',
                          port=3306,
                           charset='utf8'
                          )
cursor=db.cursor()
def sql_to_df(sql_query):
    df=pd.read_sql(sql_query,db)
    return df<jupyter_output><empty_output><jupyter_text>i.What are people saying about me (somebody)?  Thinking : I think this question needs to choose tag first.We can choose a tag to represent a people ,and then search all the comments that have this tag.Then We can get that what people are saying about me.So I choose stephencurry As my tag, then search from nba table,get the text ,then collect them.<jupyter_code>sql="Select text from nba where text like'%#StephenCurry%'"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>ii.How viral are my posts?  thinking: the viral of the posts is reflecting on the favs and retweets.Here,I define :viral=0.7*retweets+0.3*favs,then caculate the viral of its viral of posts, then average the viral,get the final viral of its posts. So the first step is that I have to grasp twitters from one person(here, I use celtics homepage) and then use my varl equal, get the average of viral.<jupyter_code>sql="SELECT ID,Created_at,text,Fav,retweets,(0.3*Fav +0.7*retweets) As viralofmypost from tweets order BY viralofmypost desc;"
sql_to_df(sql)
sql="Create table theviralofpost(Select (0.3*Fav +0.7*retweets) As viralofmypost from tweets)"

sql="Select Avg(viralofmypost)from theviralofpost"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>Finally, I get the avg of viralofcelticshomepage post.This result just show how viral of posts.If you want compare with others' page , this can be used. iii.How much influence to my posts have?  Thinking:first you should choose screen name,get what he has posted in a peroid time.And then I define: influence=0.3*favos +0.1*reweet+0.6*userfollower.Then get it's average infulence.<jupyter_code>sql="select screen_name, (0.3*favos +0.1*reweet+0.6*userfollower) As influenceoftweets from nba where screen_name like '%Motiversity_%'"
sql_to_df(sql)
sql="create table influenceof (select screen_name, (0.3*favos +0.1*reweet+0.6*userfollower) As influenceoftweets from nba where screen_name like '%Motiversity_%')"
sql="select Avg(influenceoftweets)from influenceof "
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>Finally I get this person's influence of post iv.What posts are like mine? First, I choose the screen_name do get his posts.Then use get what hashtags he uses,then use the hashtags to search what post does have the similar hashtags, then we get post that is like him.<jupyter_code>sql=sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT Num , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM lbj123, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>So we can get tags like "Lebronjames,Cavs","NBAplayoff","James"<jupyter_code>sql="select * from rockets where text like '% #nbaplayoff%'"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>finally , we get the post like mine v.What users post like me?thinking: I firstly go to Celtics_homepage find some tags and search tags in NBA_tweets to find other users,this is to search users post like me,we should get screen name.<jupyter_code>sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT id  , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM tweets, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>we can easily get some heshtags("Playoff","NBA")<jupyter_code>sql="select * from nba where text like '%#playoff%'"
sql_to_df(sql)


sql="select *from nba where text like'%#NBA%'"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>vi.Who should I be following?  thinking : I choose  Celtics_homepage to find some tags and search tags in NBA tweets to find other usersAlso I define popular person=  0.2 Fav + 0.2 Retweet+0.6followersthen get the most popular person to follow.<jupyter_code>sql="select screen_name, (0.2*favos +0.2*reweet+0.6*userfollower) As popularity from nba where text like '%#playoff%' order by popularity desc"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>So we can easily to get who we should follow, using popularity. vii.What topics are trending in my domain? divide all the posts into several parts according to time perio Use the function count (high frequency) in mysql to get the high frequency tags multiple times<jupyter_code>sql="SELECT * FROM finalprojects.tweets where Created_at between '2018-02-25' and '2018-02-27'"
sql_to_df(sql)
sql="create table time1 (SELECT * FROM finalprojects.tweets where Created_at between '2018-02-25' and '2018-02-27')"
sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT id  , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM time1, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)
sql="SELECT * FROM finalprojects.tweets where Created_at between '2018-02-23' and '2018-02-25'"
sql_to_df(sql)
sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT id  , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM time2, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)
sql="SELECT * FROM finalprojects.tweets where Created_at between '2018-02-21' and '2018-02-23'"
sql_to_df(sql)
sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT id  , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM time3, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>So we need to compare three time slots :time1 time2 time3 to get what is the trends of our domian. viii.What keywords/ hashtags should I add to my post? thinking : so this is the most choose what hashtags should add to my post , I think there are two details, first you have to specify what topic you post , post one news for a person or a team even events.So you should firstly search all of tweets about this person "stephencurry", then get his most popular tags, this easily to get count of tags from text.<jupyter_code>sql=sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT id , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM nba, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>My nba table is the post all about stephencurry in a time period , then we should get high popularity tags about curry to add on my post about curry. ix.Should I follow somebody back?  Thinking : first you should choose the person who is you follower and you are not following him.Then you should see it's favorite and retweet of his post  also the follower so I also define followback=0.2 Fav + 0.2 Retweet+0.6followers to get the person that I shoule follow back. Then I used the table of the person who post in a period time for your post  and you have similarity of their post.<jupyter_code>sql=sql="SELECT word , COUNT(*) total FROM ( SELECT DISTINCT id , SUBSTRING_INDEX(SUBSTRING_INDEX(text,' ',i+1),' ',-1) word  FROM nba, ints ) x GROUP BY word HAVING COUNT(*) > 1 ORDER BY total DESC, word;"
sql_to_df(sql)

sql="select distinct screen_name, (0.2*favos +0.2*reweet+0.6*userfollower) As popularity from nba where text like '%#StephenCurry%' order by popularity desc"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>So we can easily to get who we should follow back. x.What is the best time to post?(facebook) thinking: this question is to ask something about what time to post you can get more retweet and favs, so what I should do is filter the similar post which have same hashtags.Then sorting them by favs and retweet.this I should define the best time to post is 0.5*favs+0.5retweets<jupyter_code>sql="select Created_at ,(0.5*Fav+0.5*retweets) as timepost from tweets where text like '%#NBAvote%' order by timepost desc"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>So see the created_at we can easily get what time we should post.(remember choose a topic first) xi.Should I add and picture or url to my post?  Here ,what we want to see is that we should add picture or url to my post.Easily get the answer when compare with picture or video with no picture and video.I use facebookdata about NBA.SO I like to define the popularity:0.5*coments + 0.25*likes+0.25*shares.<jupyter_code>sql="select link_type,(0.5*comments + 0.25*likes+0.25*shares)as popularity from facebooksnba where link_type='null' order by popularity desc"
sql_to_df(sql)
sql="select link_type,(0.5*comments + 0.25*likes+0.25*shares)as popularity from facebooksnba where link_type='video' order by popularity desc"
sql_to_df(sql)
sql="select link_type,(0.5*comments + 0.25*likes+0.25*shares)as popularity from facebooksnba where link_type='photo' order by popularity desc"
sql_to_df(sql)<jupyter_output><empty_output><jupyter_text>Compared with three tables we can easily get the answer of this question" should I add url or photo "ON our post. xii.What’s my reach? what i am thinking about is that we should get information from the userfollower.Becuase As we all know that one peoson is be followed by others ,if he firstime to post it;s news , his follower are the first to get because they have notices.So their followers' location define it;s reach'So we should get our follower location like that .<jupyter_code>sql="select * from userfollowers"
sql_to_df(sql)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08411423471664435, 'fraction_numerical': 0.01814666071694184, 'mean_word_length': 5.140182648401827, 'pattern_counts': {'":': 0, '<': 69, '<?xml version=': 0, '>': 76, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '16', 'dir': 'jupyter-structured-clean-dedup', 'id': '482736', 'license': 'MIT', 'n_tokens_mistral': 4409, 'n_tokens_neox': 4116, 'n_words': 1669, 'path': 'Examples/DB_NBA.ipynb', 'repo_name': 'nikbearbrown/INFO_6210', 'size': '13575'}