<jupyter_start><jupyter_text>Predict if a user will be an influencer on a tweet (> 100 retweets) based on volume in the following categoriesUSER PREDICTION ELEMENTSFIELD                             DESCRIPTIONtweet_user_statuses_count:        count - user tweetstweet_user_favourites_count:      count - user is a favorite of otherstweet_user_followers_count:       count - user is followed by otherstweet_user_friends_count:         count - user friendstweet_user_listed_count:          count - user added to other's interest list<jupyter_code># Python SQL toolkit and Object Relational Mapper
import sqlalchemy
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
# import necessary libraries
import os
from flask import Flask, render_template, jsonify, request, redirect

from pprint import pprint

#################################################
# Flask Setup
#################################################
app = Flask(__name__)

#################################################
# Database Setup
#################################################

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.sql.expression import func

#Probably don't need these from SQLAlchemy: asc, desc, between, distinct, func, null, nullsfirst, nullslast, or_, and_, not_

db_path_flask_app = "sqlite:///../python/data/twitter_trends.db"
app.config['SQLALCHEMY_DATABASE_URI'] = os.environ.get('DATABASE_URL', '') or db_path_flask_app
# app.config['SQLALCHEMY_DATABASE_URI'] = db_path_flask_app
# Flask-SQLAlchemy database
db = SQLAlchemy(app)
class Tweet(db.Model):    
    __tablename__ = 'tweets'    
    # Defining the columns for the table 'tweets',    
    # which will hold tweets associated the search terms in the 'trends' table,    
    # which are referred to in that table as "twitter_tweet_name" 
    
    id = db.Column(db.Integer, primary_key=True)    
    updated_at = db.Column( db.DateTime )    
    tweet_id = db.Column( db.Integer )    
    tweet_id_str = db.Column( db.String(50), unique=True, nullable=False ) 
    
    # tweet_search_term = db.Column(db.Integer, db.ForeignKey('trends.twitter_tweet_name') )    
    tweet_search_term = db.Column(db.String(250))    
    tweet_created_at = db.Column(db.String(100))    
    tweet_is_a_quote_flag = db.Column( db.Boolean )    
    tweet_is_a_retweet_flag = db.Column( db.Boolean )    
    tweet_entities_hashtags_count = db.Column( db.Integer )    
    tweet_entities_user_mentions_count = db.Column( db.Integer )    
    tweet_favorite_counts = db.Column( db.Integer )    
    tweet_retweet_counts = db.Column( db.Integer )    
    tweet_lang = db.Column( db.String(10) )    
    tweet_source = db.Column(db.String(250))    
    tweet_text = db.Column(db.String(250))    
    tweet_user_id = db.Column( db.Integer )    
    tweet_user_id_str = db.Column( db.String(50) )    
    tweet_user_created_at = db.Column(db.String(100))    
    tweet_user_lang = db.Column( db.String(10) )    
    tweet_user_name = db.Column( db.String(100) )    
    tweet_user_screen_name = db.Column( db.String(100) )    
    tweet_user_description = db.Column( db.String(250) )    
    tweet_user_statuses_count = db.Column( db.Integer )    
    tweet_user_favourites_count = db.Column( db.Integer )    
    tweet_user_followers_count = db.Column( db.Integer )    
    tweet_user_friends_count = db.Column( db.Integer )    
    tweet_user_listed_count = db.Column( db.Integer )    
    
    def __repr__(self):        
        return f"<Tweet {self.tweet_search_term}: {self.tweet_id} [updated_at: {self.updated_at}>"
# Database schema for Twitter 'locations' table
class Location(db.Model):
    __tablename__ = 'locations'
    
    # Defining the columns for the table 'locations',
    # which will hold all of the locations in the U.S. for which
    # top trends data is available, as well as location specific
    # info like latitude/longitude
    id = db.Column(db.Integer, primary_key=True)
    updated_at = db.Column( db.DateTime )
    woeid = db.Column(db.Integer, unique=True, nullable=False)
    twitter_country = db.Column(db.String(100))
    tritter_country_code = db.Column(db.String(10))
    twitter_name = db.Column(db.String(250))
    twitter_parentid = db.Column(db.Integer)
    twitter_type = db.Column(db.String(50))
    country_name = db.Column(db.String(250))
    country_name_only = db.Column(db.String(250))
    country_woeid = db.Column(db.Integer)
    county_name = db.Column(db.String(250))
    county_name_only = db.Column(db.String(250))
    county_woeid = db.Column(db.Integer)
    latitude = db.Column(db.Float)
    longitude = db.Column(db.Float)
    name_full = db.Column(db.String(250))
    name_only = db.Column(db.String(250))
    name_woe = db.Column(db.String(250))
    place_type = db.Column(db.String(250))
    state_name = db.Column(db.String(250))
    state_name_only = db.Column(db.String(250))
    state_woeid = db.Column(db.Integer)
    timezone = db.Column(db.String(250))

    my_trends = db.relationship('Trend', backref=db.backref('my_location', lazy=True))
    
    def __repr__(self):
        return f"<Location {self.name_full} [updated_at: {self.updated_at}>"

# Database schema for Twitter 'trends' table
class Trend(db.Model):
    __tablename__ = 'trends'
    
    # Defining the columns for the table 'trends',
    # which will hold all of the top trends associated with
    # locations in the 'locations' table
    id = db.Column(db.Integer, primary_key=True)
    updated_at = db.Column( db.DateTime )
    woeid = db.Column(db.Integer, db.ForeignKey('locations.woeid') )
    twitter_as_of = db.Column(db.String(100))
    twitter_created_at = db.Column(db.String(100))
    twitter_name = db.Column(db.String(250))
    twitter_tweet_name = db.Column(db.String(250))
    twitter_tweet_promoted_content = db.Column(db.String(250))
    twitter_tweet_query = db.Column(db.String(250))
    twitter_tweet_url = db.Column(db.String(250))
    twitter_tweet_volume = db.Column(db.Float)

    # With more investigation, determined this is an
    # incorrect usage of relationship method below - removing it
    # locations = db.relationship('Location', backref=db.backref('trends', lazy=True))
     
    def __repr__(self):
        return f"<Trend {self.my_location.name_full}: {self.twitter_tweet_name} [updated_at: {self.updated_at}>"

# In the app code for this route, there is no need to do anything special
a_tweet = "#OpeningDay"

# Return a list of all locations that have the specified tweet in its top trends
# and then sort the results by tweet volume in descending order
results = db.session.query(Trend, Location).join(Location) \
                    .filter(Trend.twitter_tweet_name == a_tweet ) \
                    .order_by( Trend.twitter_tweet_volume.desc() ).all()

loc_list = []
for r in results:
    #print(f"Trend Information for {r.Trend.woeid} {r.Location.name_full}: {r.Trend.twitter_tweet_name} {r.Trend.twitter_tweet_volume}")
    loc_info = {
        'woeid': r.Location.woeid,
        'latitude': r.Location.latitude,
        'longitude': r.Location.longitude,
        'name_full': r.Location.name_full,
        'name_only': r.Location.name_only,
        'name_woe': r.Location.name_woe,
        'county_name': r.Location.county_name,
        'county_name_only': r.Location.county_name_only,
        'county_woeid': r.Location.county_woeid,
        'state_name': r.Location.state_name,
        'state_name_only': r.Location.state_name_only,
        'state_woeid': r.Location.state_woeid,
        'country_name': r.Location.country_name,
        'country_name_only': r.Location.country_name_only,
        'country_woeid': r.Location.country_woeid,
        'place_type': r.Location.place_type,
        'timezone': r.Location.timezone,
        'twitter_type': r.Location.twitter_type,
        'twitter_country': r.Location.twitter_country,
        'tritter_country_code': r.Location.tritter_country_code,
        'twitter_parentid': r.Location.twitter_parentid,

        'twitter_as_of': r.Trend.twitter_as_of,
        'twitter_created_at': r.Trend.twitter_created_at,
        'twitter_name': r.Trend.twitter_name,
        'twitter_tweet_name': r.Trend.twitter_tweet_name,
        'twitter_tweet_promoted_content': r.Trend.twitter_tweet_promoted_content,
        'twitter_tweet_query': r.Trend.twitter_tweet_query,
        'twitter_tweet_url': r.Trend.twitter_tweet_url,
        'twitter_tweet_volume': r.Trend.twitter_tweet_volume
    }

    loc_list.append(loc_info)
results = db.session.query(Tweet.tweet_user_id_str, \
                        func.max(Tweet.id).label("id"), \
                        func.max(Tweet.tweet_user_id_str).label("tweet_user_id_str"), \
                        func.max(Tweet.tweet_user_statuses_count).label("tweet_user_statuses_count"), \
                        func.max(Tweet.tweet_user_favourites_count).label("tweet_user_favourites_count"), \
                        func.max(Tweet.tweet_user_followers_count).label("tweet_user_followers_count"), \
                        func.max(Tweet.tweet_user_friends_count).label("tweet_user_friends_count"), \
                        func.max(Tweet.tweet_user_listed_count).label("tweet_user_listed_count"), \
                        func.max(Tweet.tweet_retweet_counts).label("tweet_retweet_counts")) \
                        .group_by(Tweet.tweet_user_id_str) \
                        .all()        

tweet_list = []    

for r in results:        
    tweet_info = { 'id': r.id,            
                  'tweet_user_id_str' : r.tweet_user_id_str ,                                           
                  'tweet_user_statuses_count': r.tweet_user_statuses_count ,            
                  'tweet_user_favourites_count' : r.tweet_user_favourites_count ,            
                  'tweet_user_followers_count' : r.tweet_user_followers_count ,            
                  'tweet_user_friends_count' : r.tweet_user_friends_count ,            
                  'tweet_user_listed_count' : r.tweet_user_listed_count,    
                  'tweet_retweet_counts' : r.tweet_retweet_counts}        
    tweet_list.append(tweet_info)
pprint(len(tweet_list))
tweet = pd.DataFrame.from_dict(tweet_list)
tweet.head()
tweet.describe()
tweet["success"] = np.where(tweet["tweet_retweet_counts"] > 99, 1, 0)
tweet.head()
# Assign X (data) and y (target)
X = tweet.drop(["id","tweet_user_id_str","tweet_retweet_counts","success"], axis=1)
y = tweet["success"]
print(X.shape, y.shape)<jupyter_output>(32049, 5) (32049,)<jupyter_text>Split our data into training and testing<jupyter_code>from sklearn.model_selection import train_test_split

X_train, X_test, y_train, y_test = train_test_split(X, y, random_state=1, stratify=y)<jupyter_output><empty_output><jupyter_text>Scale the training and testing data<jupyter_code>from sklearn.preprocessing import StandardScaler
# Create a StandardScater model and fit it to the training data
# No need to scale y since it is just 0 and 1
X_scaler = StandardScaler().fit(X_train)
# y_scaler = StandardScaler().fit(y_train)
# Transform the training and testing data using the X_scaler and y_scaler models
X_train_scaled = X_scaler.transform(X_train)
X_test_scaled = X_scaler.transform(X_test)
# y_train_scaled = y_scaler.transform(y_train)
# y_test_scaled = y_scaler.transform(y_test)<jupyter_output>C:\Users\dadja\Anaconda3\lib\site-packages\ipykernel_launcher.py:2: DataConversionWarning: Data with input dtype int64 were all converted to float64 by StandardScaler.
  
C:\Users\dadja\Anaconda3\lib\site-packages\ipykernel_launcher.py:3: DataConversionWarning: Data with input dtype int64 were all converted to float64 by StandardScaler.
  This is separate from the ipykernel package so we can avoid doing imports until<jupyter_text>Create a Logistic Regression Model<jupyter_code>from sklearn.linear_model import LogisticRegression
classifier = LogisticRegression()
classifier<jupyter_output><empty_output><jupyter_text>Fit (train) or model using the training data<jupyter_code>classifier.fit(X_train_scaled, y_train)<jupyter_output>C:\Users\dadja\Anaconda3\lib\site-packages\sklearn\linear_model\logistic.py:433: FutureWarning: Default solver will be changed to 'lbfgs' in 0.22. Specify a solver to silence this warning.
  FutureWarning)<jupyter_text>Validate the model using the test data<jupyter_code>print(f"Training Data Score: {classifier.score(X_train_scaled, y_train)}")
print(f"Testing Data Score: {classifier.score(X_test_scaled, y_test)}")
# RESULTS OF RUN - No Scaling
# Training Data Score: 0.7661008487269096
# Testing Data Score: 0.7675028079371022

# RESULTS OF RUN - With Scaling
# Training Data Score: 0.7653103677816608
# Testing Data Score: 0.7660052414825907<jupyter_output><empty_output><jupyter_text>Make predictions<jupyter_code>predictions = classifier.predict(X_test_scaled)
print(f"First 10 Predictions:   {predictions[:10]}")
print(f"First 10 Actual labels: {y_test[:10].tolist()}")
pd.DataFrame({"Prediction": predictions, "Actual": y_test}).reset_index(drop=True)
confusion_matrix(y_test, predictions)
tn, fp, fn, tp = confusion_matrix(y_test, predictions).ravel()
print(f"tn: {tn:3d}   fp: {fp:3d}")
print(f"fn: {fn:3d}   tp: {tp:3d}")
# from joblib import dump, load
# dump(classifier, 'influencer.model')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11843876177658143, 'fraction_numerical': 0.016225512187827126, 'mean_word_length': 3.7144871342967924, 'pattern_counts': {'":': 2, '<': 29, '<?xml version=': 0, '>': 31, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '559030', 'license': 'MIT', 'n_tokens_mistral': 4706, 'n_tokens_neox': 4469, 'n_words': 852, 'path': 'resources/AIevalSQLite-Scale.ipynb', 'repo_name': 'SairaJahangir/Project-3-', 'size': '13374'}