<jupyter_start><jupyter_text>Tweets on 2016 US Presidential Election: Exploring Relational Data and Visualization Author: <NAME> Click [here](http://www.hexingren.com/practical-data-science) to go back.<jupyter_code>import csv
import sqlite3
import pandas as pd
import math
# Use svg backend for better quality
import matplotlib
# AUTOLAB_IGNORE_START
matplotlib.use("svg")
# AUTOLAB_IGNORE_STOP
import matplotlib.pyplot as plt
# AUTOLAB_IGNORE_START
%matplotlib inline
plt.style.use('ggplot')
matplotlib.rcParams['figure.figsize'] = (10.0, 5.0) # adjust this to fit your screen
# AUTOLAB_IGNORE_STOP<jupyter_output><empty_output><jupyter_text>In this problem, we will be analyzing the Twitter data we extracted using [this](https://dev.twitter.com/overview/api) api. The data consists of Twitter users (with unique handles) and their attributes (e.g., number of followers), some recent tweets posted by them with attributes (e.g., time stamp, number of retweets), and the follow relationship between the users. These are available in the three CSV files:- users.csv - users, user attributes- edges.csv - follow edges (directed, an edge from A to B means A follows B or B is a friend of A)- tweets.csv - tweets posted by the users along with its attributes  Q1. Relational DataThis question will guide us through loading Twitter data into an in-memory SQLite database and running some basic queries on it.  Q1. Task A: Load Twitter data into SQLite databaseThe task is to use the csv and sqlite3 python packages to load the three csv files as relations (or tables) into an SQLite in-memory database.Loading the data from csv file into the database involves the following steps:1. Identify the schema of the table (for this problem, TEXT and INTEGER attribute types)2. Create a table with the identified schema3. Load the contents of csv in memory4. Insert every row of csv file as a record in the tableWe can refer to [sqlite3 documentation](https://docs.python.org/2/library/sqlite3.html) for steps 2 and 4. For step 3, refer to the [csv documentation](https://docs.python.org/2/library/csv.html). Be sure to name the tables `users`, `edges`, and `tweets`. Make sure to commit (the equivalent of Ctrl+S for databases) any changes made to the database. [This](https://www.techopedia.com/definition/16/commit) page should give us an idea about why commit is essential.<jupyter_code>def load_twitter_data_sqlite3(conn, users_filepath, edges_filepath, tweets_filepath) :
    """ Load twitter data in the three files as tables into an in-memory SQLite database
    Input:
        conn (sqlite3.Connection) : Connection object corresponding to the database; used to perform SQL commands.
        users_filepath (str) : absolute/relative path to users.csv file
        edges_filepath (str) : absolute/relative path to edges.csv file
        tweets_filepath (str) : absolute/relative path to tweets.csv file
    Output:
        None
    """
    c = conn.cursor()
    c.execute('''CREATE TABLE users
                 (name text, screen_name text, location text, created_at text, friends_count integer, 
                 followers_count integer, statuses_count integer, favourites_count integer)''')
    c.execute('''CREATE TABLE edges
                 (screen_name text, friend text)''')
    c.execute('''CREATE TABLE tweets
                 (screen_name text, created_at text, retweet_count integer, favorite_count integer, text text)''')
    list_users = [ ]
    list_edges = [ ]
    list_tweets = [ ]
    with open(users_filepath, 'rb') as users:
        reader_users = csv.reader(users)
        for row_users in reader_users:
            list_users.append(tuple(row_users))
        list_users.pop(0)
        #print len(list_users)
        c.executemany("INSERT INTO users VALUES (?,?,?,?,?,?,?,?)", list_users)
    with open(edges_filepath, 'rb') as edges:
        reader_edges = csv.reader(edges)
        for row_edges in reader_edges:
            list_edges.append(tuple(row_edges))
        list_edges.pop(0)
        #print len(list_edges)
        c.executemany("INSERT INTO edges VALUES (?,?)", list_edges)
    with open(tweets_filepath, 'rb') as tweets:
        reader_tweets = csv.reader(tweets)
        for row_tweets in reader_tweets:
            list_tweets.append(tuple(row_tweets))
        list_tweets.pop(0)
        #print len(list_tweets)
        c.executemany("INSERT INTO tweets VALUES (?,?,?,?,?)", list_tweets)
    conn.commit()<jupyter_output><empty_output><jupyter_text>The function will be called as in the cell below. The cell also contains some test code to display all tables in the database. Tests for the individual tables are added to verify that the data has been loaded properly. (e.g., number of tuples in each table)<jupyter_code># AUTOLAB_IGNORE_START
# connect to an in memory database
conn = sqlite3.connect(":memory:")
conn.text_factory = str
# call to your function
load_twitter_data_sqlite3(conn, 'users.csv', 'edges.csv', 'tweets.csv')
# make sure to change the path to csv files appropriately
cursor = conn.cursor()
# prints all tables in the database
for row in cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table';"):
    print row
# AUTOLAB_IGNORE_STOP<jupyter_output>('users',)
('edges',)
('tweets',)<jupyter_text>Q1. Task B: Trending tweets in a topicTwitter is regarded as an invaluable source of valuable information. Hence, one of the favorite tasks of data miners is the analyse the trending tweets in a given topic.This task requires us to retrieve the top N most trending tweets (in descending order of trending_score) about a given topic (which is a list of keywords). The following information may be useful:- A tweet is said to be about a given topic if it contains any of the given topical phrases/keywords.- We will use the following simple trending_score: retweet_count + favorite_count. Tweets with higher trending_score must be ranked before the ones with lower trending_score.- Your result must contain unique tweets. If a tweet text occurs multiple times, display it only once with its highest trending_score.- Break ties by sorting the tweets in alphabetical order.The output schema should be as follows:|tweet (TEXT)| trending_score (INTEGER) || :--- |:--- || | |<jupyter_code>def trending_tweets(cursor, topical_phrases=['Hillary', 'Clinton'], N=5):
    """ Retrieves the top N trending tweets containing one or more of the given topical phrases.
    Input:
        cursor (sqlite3.Cursor): Cursor object to query the database.
        topical_phrases (list of strings): A list of keywords identifying a topic.
        N: Number of trending tweets to retrieve
    Output:
        results (sqlite3.Cursor): Cursor object which can be used to iterate over the retrieved records/tuples.
    """
    query = '''SELECT text, max(retweet_count + favorite_count) AS trending_score FROM tweets
             WHERE text LIKE ? '''
    for i in range(len(topical_phrases)-1):
        query = query + "OR ? "
    query = query + '''GROUP BY text
             ORDER BY (retweet_count + favorite_count) DESC, text ASC
             LIMIT ?'''
    for i in range(len(topical_phrases)):
        topical_phrases[i] = '%' + str(topical_phrases[i]) + '%'
    topical_phrases.append(N)
    results = cursor.execute(query, topical_phrases)
    return results<jupyter_output><empty_output><jupyter_text>The default inputs to the function will retrieve 5 trending tweets about topic Hillary Clinton. We can view the output of the query using the following code.<jupyter_code># AUTOLAB_IGNORE_START
results = trending_tweets(conn.cursor())
#results = trending_tweets(conn.cursor(), ['Donald', 'Trump'], 7)
for row in results:
    print row
# AUTOLAB_IGNORE_STOP<jupyter_output>("Hillary just gave a disastrous news conference on the tarmac to make up for poor performance last night. She's being decimated by the media!", 37903)
('"A rough night for <NAME>"  ABC News.', 34418)
('<NAME> answered email questions differently last night than she has in the past. She is totally confused. Unfit to serve as #POTUS.', 32843)
("'<NAME> leads <NAME> by 19 points among military, veteran voters: poll' #AmericaFirst #MAGA\nhttps://t.co/5FmxGtLkwt", 29538)
("It wasn't <NAME> that hurt Hillary last night. It was her very dumb answer about emails &amp; the veteran who said she should be in jail.", 24414)<jupyter_text>Q1. Task C: Tweet recommendationHow does Twitter go about populating the feed for a user? While Twitter may use a comple models to do this, in this task, we will use a Simple Tweet Recommender (STR), which recommends a user's tweets to all users who follow him/her (without checking for possible duplicates; i.e., STR may recommend the same tweet twice if two of a user's friends have posted it).In this task, we will write a query to determine the number of tweets recommended to each user. Use only the snapshot of edges and tweets provided to do the recommendation. Report the results on the users present in the users table. (Hint: The number of records in the output should match that in the "users" table.) The order of results does not matter.The output schema should be:|screen_name (TEXT)| num_tweets (INTEGER) || :--- |:--- || | | |<jupyter_code>def num_tweets_in_feed(cursor):
    """ Retrieves the number of tweets STR recommends to each Twitter user.
    Input:
        cursor (sqlite3.Cursor): Cursor object to query the database.
    Output:
        results (sqlite3.Cursor): Cursor object which can be used to iterate over the retrieved records/tuples.
    """
    query = ''' WITH tweetsNum (screen_name, tweet_num) AS (
                    SELECT screen_name, COUNT(*)
                    FROM tweets
                    GROUP BY screen_name),
                     partialUserTweets (screen_name, rmd_cnt) AS (
                    SELECT edges.screen_name, SUM(tweetsNum.tweet_num)
                    FROM edges INNER JOIN tweetsNum
                    ON edges.friend = tweetsNum.screen_name
                    GROUP BY edges.screen_name)
                SELECT users.screen_name, IFNULL(partialUserTweets.rmd_cnt, 0) AS num_tweets
                FROM users LEFT OUTER JOIN partialUserTweets
                ON users.screen_name = partialUserTweets.screen_name
                ORDER BY num_tweets DESC; ''' # your query here
    return cursor.execute(query)

# AUTOLAB_IGNORE_START
results = num_tweets_in_feed(conn.cursor())
# for row in results:
#     print row
# AUTOLAB_IGNORE_STOP<jupyter_output><empty_output><jupyter_text>Q2. VisualizationIn this question, we will load all data into pandas dataframes and analyse (and visualize!) some interesting trends using [matplotlib](http://matplotlib.org) python package.  Q2. Task A: Load Twitter data using pandasFill in the following method stub and return the data frames for users, edges and tweets.Pandas will treat missing values as NaNs by default. However, for this task, we treat missing values (i.e., empty strings in the csv files) as empty strings.<jupyter_code>import numpy as np
def load_twitter_data_pandas(users_filepath, edges_filepath, tweets_filepath):
    """ Loads the Twitter data from the csv files into Pandas dataframes
    Input:
        users_filepath (str) : absolute/relative path to users.csv file
        edges_filepath (str) : absolute/relative path to edges.csv file
        tweets_filepath (str) : absolute/relative path to tweets.csv file
    Output:
        (pd.DataFrame, pd.DataFrame, pd.DataFrame) : A tuple of three dataframes, the first one for users,
                                                    the second for edges and the third for tweets.
    """
    df_users = pd.read_csv(users_filepath)
    dfu = df_users.replace(np.nan, '', regex=True)
    df_edges = pd.read_csv(edges_filepath)
    dfe = df_edges.replace(np.nan, '', regex=True)
    df_tweets = pd.read_csv(tweets_filepath)
    dft = df_tweets.replace(np.nan, '', regex=True)
    return (dfu, dfe, dft)<jupyter_output><empty_output><jupyter_text>We can test the function using the following code.<jupyter_code># AUTOLAB_IGNORE_START
(users_df, edges_df, tweets_df) = load_twitter_data_pandas('users.csv', 'edges.csv', 'tweets.csv')
# make sure to change the path to csv files appropriately
print users_df.head()
print edges_df.head()
print tweets_df.head()
# AUTOLAB_IGNORE_STOP<jupyter_output>name      screen_name      location  \
0     <NAME>  realDonaldTrump  New York, NY   
1  Trump Organization            Trump  New York, NY   
2          <NAME>        TrumpGolf                 
3       Tiffany Trump    TiffanyATrump                 
4      <NAME>    IngrahamAngle            DC   

                       created_at  friends_count  followers_count  \
0  Wed Mar 18 13:46:38 +0000 2009             42         11397769   
1  Wed Apr 13 16:51:54 +0000 2016             35             9954   
2  Mon Feb 03 13:46:03 +0000 2014            200             8797   
3  Tue Feb 01 20:59:30 +0000 2011             79            63138   
4  Thu Jun 25 21:03:25 +0000 2009            289           851876   

   statuses_count  favourites_count  
0           33136                38  
1              43               125  
2             758               251  
3             573                28  
4           26523                71  
       screen_name  [...]<jupyter_text>Q2. Task B: CorrelationStatisticians and data analysts usually like to study about correlation between different observed variables. This helps uncover interesting patterns in the data such as causal relationships (e.g., snow on the road leads to increase in number of accidents). Correlation studies are important for multiple reasons:- While [correlation does not imply causation](https://en.wikipedia.org/wiki/Correlation_does_not_imply_causation), a lack of correlation implies a lack of causation. This can be used to rule out many causal relationships.- Correlation helps with prediction. The more closely related two variables are, the easier it is to predict one from the other.In this task, we will plot the friends_count (on y-axis) vs the followers_count (on x-axis) using the matplotlib package. [Here](http://matplotlib.org/examples/shapes_and_collections/scatter_demo.html) is an example to get started with scatter plots.<jupyter_code>import numpy as np
def plot_friends_vs_followers(users_df):
    """ Plots the friends_count (on y-axis) against the followers_count (on x-axis).
    Input:
        users_df (pd.DataFrame) : Dataframe containing Twitter user attributes,
                                    as returned by load_twitter_data_pandas()
    Output:
        (matplotlib.collections.PathCollection) : The object returned by the scatter plot function
    """
    followers_cnt = users_df.loc[:, 'followers_count'].tolist()
    friends_cnt = users_df.loc[:, 'friends_count'].tolist()
    ff = plt.scatter(followers_cnt, friends_cnt)
    print type(ff)
    plt.xlabel('Count of Followers')
    plt.ylabel('Count of Friends')
    return ff

# AUTOLAB_IGNORE_START
p = plot_friends_vs_followers(users_df)
plt.show()
# AUTOLAB_IGNORE_STOP<jupyter_output><class 'matplotlib.collections.PathCollection'><jupyter_text>Do you see a correlation between these two variables from your scatter plot? Let's measure this quantitatively using the [Pearson's correlation coefficient](https://en.wikipedia.org/wiki/Pearson_product-moment_correlation_coefficient). For a set of observations $(X,Y) = [(x_1,y_1), (x_2,y_2), ... , (x_n,y_n)]$, the Pearson's correlation coefficient is a measure of the linear dependence between two variables $X$ and $Y$, giving a value between +1 and −1 inclusive, where 1 is total positive correlation, 0 is no correlation, and −1 is total negative correlation.$r=r_{xy}={\frac {n\sum x_{i}y_{i}-\sum x_{i}\sum y_{i}}{{\sqrt {n\sum x_{i}^{2}-(\sum x_{i})^{2}}}~{\sqrt {n\sum y_{i}^{2}-(\sum y_{i})^{2}}}}}$Now, fill in the following function to compute the Pearson's correlation coefficient between friends_count and followers_count.<jupyter_code>def correlation_coefficient(users_df):
    """ Plots the friends_count (on y-axis) against the followers_count (on x-axis).
    Input:
        users_df (pd.DataFrame) : Dataframe containing Twitter user attributes,
                                    as returned by load_twitter_data_pandas()
    Output:
        (double) : correlation coefficient between friends_count and followers_count
    """
    n = len(users_df.index)
    xy_sum = 0
    x_sum = 0
    y_sum = 0
    xx_sum = 0
    yy_sum = 0
    
    for i in range(n):
        xi = users_df.loc[i, 'followers_count']
        yi = users_df.loc[i, 'friends_count']
        xy_sum = xy_sum + xi * yi
        x_sum = x_sum + xi
        y_sum = y_sum + yi
        xx_sum = xx_sum + (xi ** 2)
        yy_sum = yy_sum + (yi ** 2)
    
    x_sum = float(x_sum)
    y_sum = float(y_sum)
    xx_sum = float(xx_sum)
    yy_sum = float(yy_sum)
    xy_sum = float(xy_sum)

    r = (n * xy_sum - x_sum * y_sum) / (math.sqrt(n * xx_sum - x_sum ** 2) * math.sqrt(n * yy_sum - y_sum ** 2))
    return r

# AUTOLAB_IGNORE_START
print correlation_coefficient(users_df)
# AUTOLAB_IGNORE_STOP<jupyter_output><empty_output><jupyter_text>Q2. Task C: Degree distributionIn graph theory, the degree of a node is the number of connections it has to other nodes. A common statistic to look out for in the case of real world graphs is the degree distribution. Literature says degrees of nodes in real world graphs follow a [power law distribution](https://en.wikipedia.org/wiki/Power_law). The implication is that a scatter plot of num_users versus k (as we will define below) yields an almost straight line. In this task, we shall verify whether the given crawl of Twitter network satisfies this property.Let us call the number of friends a Twitter user has as his/her degree. The degree distribution is a histogram of the number of friends. Our task is to visualize this histogram. Use the default number of bins.<jupyter_code>def degree_distribution(edges_df):
    """ Plots the distribution of .
    Input:
        edges_df (pd.DataFrame) : Dataframe containing Twitter edges,
                        as returned by load_twitter_data_pandas()
    Output:
        (array, array, list of Patch objects) : Tuple of the values of the histogram bins, 
                        the edges of the bins and the silent list of individual patches used to create the histogram.
    """
    cnt_df = edges_df.groupby(['screen_name']).size()
    friends_num = cnt_df.tolist()
    (n, bins, patches) = plt.hist(friends_num)
    return (n, bins, patches)

# AUTOLAB_IGNORE_START
degree_distribution(edges_df)
# AUTOLAB_IGNORE_STOP<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup