<jupyter_start><jupyter_text>Grabbing Raw Data from Twiiter<jupyter_code>import tweepy
import pandas as pd
import csv
import re 
import string
import webbrowser
from nltk.corpus import stopwords
import nltk
import spacy

import mysql.connector 
from mysql.connector import errorcode
import os
from csv import reader, writer
import random
import time
from mysql.connector import Error
%matplotlib inline
from wordcloud import WordCloud
from matplotlib import pyplot as plt
from textblob import TextBlob
import warnings
warnings.filterwarnings('ignore')
from collections import Counter
# nltk.download('stopwords')
# !pip install TextBlob

# function to display data of each tweet 

def printtweetdata(n, ith_tweet): 
    print() 
    print(f"Tweet {n}:") 
    print(f"Username:{ith_tweet[0]}") 
    print(f"Description:{ith_tweet[1]}") 
    print(f"Location:{ith_tweet[2]}") 
    print(f"Following Count:{ith_tweet[3]}") 
    print(f"Follower Count:{ith_tweet[4]}") 
    print(f"Total Tweets:{ith_tweet[5]}")
    print(f"Tweet ID:{ith_tweet[6]}")
    print(f"Source:{ith_tweet[7]}") 
    print(f"Tweet Date:{ith_tweet[8]}")
    print(f"Tweet Length:{ith_tweet[9]}")
    print(f"Likes Count:{ith_tweet[10]}")
    print(f"Retweet Count:{ith_tweet[11]}") 
    print(f"Tweet Text:{ith_tweet[12]}") 
    print(f"Hashtags Used:{ith_tweet[13]}") 
# function to perform data extraction 

def scrape(new_search, date_since, numtweet, filename): 
      
    # Creating DataFrame using pandas 
    db = pd.DataFrame(columns=['username', 'description', 'location', 'following', 
                               'followers', 'totaltweets', 'tweetid', 'source', 'tweetdate',
                               'tweetlength', 'likescount', 'retweetcount', 'text', 'hashtags']) 
      
    # We are using .Cursor() to search through twitter for the required tweets. 
    # The number of tweets can be restricted using .items(number of tweets) 
    tweets = tweepy.Cursor(api.search, q=new_search, lang="en", 
                           since=date_since, tweet_mode='extended').items(numtweet) 
     
    # .Cursor() returns an iterable object. Each item in  
    # the iterator has various attributes that you can access to  
    # get information about each tweet 
    list_tweets = [tweet for tweet in tweets] 
      
    # Counter to maintain Tweet Count 
    i = 1  
      
    # we will iterate over each tweet in the list for extracting information about each tweet 
    for tweet in list_tweets: 
        username = tweet.user.screen_name 
        description = tweet.user.description 
        location = tweet.user.location 
        following = tweet.user.friends_count 
        followers = tweet.user.followers_count 
        totaltweets = tweet.user.statuses_count 
        tweetid = tweet.id
        source = tweet.source
        tweetdate = tweet.created_at
        likescount = tweet.favorite_count
        retweetcount = tweet.retweet_count 
        hashtags = tweet.entities['hashtags'] 
        text = tweet.full_text
        
        tweetlength = len(text)
        
        hashtext = list() 
        for j in range(0, len(hashtags)): 
            hashtext.append(hashtags[j]['text']) 
        
        
        
        # Here we are appending all the extracted information in the DataFrame 
        ith_tweet = [username, description, location, following, 
                     followers, totaltweets, tweetid, source, tweetdate, tweetlength, likescount, retweetcount, text, hashtext] 
        db.loc[len(db)] = ith_tweet 
          
        # Function call to print tweet data on screen 
#         printtweetdata(i, ith_tweet) 
        i = i+1
#     filename = 'scraped_tweets3.csv'
      
    # we will save our database as a CSV file. 
    db.to_csv(filename) 

# Credentials
    
# Enter your own credentials obtained  
# from your developer account 
consumer_key = 'pW8pg7cVOVMALruxzTncmfD2J'
consumer_secret = '<KEY>'
access_token = '<KEY>'
access_secret  = '<KEY>'

auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_token, access_secret)

api = tweepy.API(auth, wait_on_rate_limit=True)


# Enter Hashtag and initial date 
print("Enter Twitter HashTag to search for") 
words = input() 
new_search = words + " -filter:retweets"
print("Enter Date since The Tweets are required in yyyy-mm--dd") 
date_since = input() 

# number of tweets you want to extract in one run 
numtweet = 1000
filename = "scraped_tweets8.csv"
scrape(new_search, date_since, numtweet, filename) 
print('Scraping has completed!') 
# #covid19vaccine since:2020-12-07 until:2020-12-10<jupyter_output>Enter Twitter HashTag to search for
#covid19vaccine since:2020-12-03 until:2020-12-04
Enter Date since The Tweets are required in yyyy-mm--dd
2020-12-03
Scraping has completed!<jupyter_text>DATA CLEANING<jupyter_code>df = pd.read_csv("all.csv")
df = df.loc[:, ~df.columns.str.startswith('Unnamed')]

print(len(df))
df.head()

df[['tweetdate']] = df['tweetdate'].str.split(' ',n=1,expand=True)[0]
df['len_hashtags'] = df['hashtags'].str.split(",").str.len()
df[['city','country']] = df['location'].str.split(',',n=1,expand=True)
df = df.dropna(subset=['country'])



def cleanTxt(text):
    text = re.sub(r'@[A-Z a-z 0-9]+', '', text)
    text = re.sub(r'#', '', text)
    text = re.sub(r'RT[\s] +', '', text)
    text = re.sub(r'https?:\/\/\S+', '', text)
    text = text.lower()
    text = re.sub('[^\w\s]','', text)
    text = re.sub('_','',text)
    emoji_pattern = re.compile("["
                           u"\U0001F600-\U0001F64F"  # emoticons
                           u"\U0001F300-\U0001F5FF"  # symbols & pictographs
                           u"\U0001F680-\U0001F6FF"  # transport & map symbols
                           u"\U0001F1E0-\U0001F1FF"  # flags 
                           u"\U00002702-\U000027B0"
                           u"\U000024C2-\U0001F251"
                           "]+", flags=re.UNICODE)
    
    text = emoji_pattern.sub(r'', text)
    

    return text

def space(comment):
    nlp = spacy.load('en', disable=['parser', 'ner'])
    doc = nlp(comment)
    return " ".join([token.lemma_ for token in doc])

def clean_country_values(df):
    states_ab = ['AL', 'AK', 'AS', 'AZ', 'AR', 'CA', 'CO', 'CT', 'DE', 'DC', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN', 'IA',
                   'KS', 'KY', 'LA', 'ME', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO', 'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC',
                   'ND','MP', 'OH', 'OK', 'OR', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN', 'TX', 'UT', 'VT', 'VI', 'VA', 'WA', 'WV', 
                   'WI', 'WY', 'US']
    states = ['Alabama', 'Alaska', 'American Samoa', 'Arizona', 'Arkansas', 'California', 'Colorado', 'Connecticut', 'Delaware', 
          'District of Columbia', 'Florida', 'Georgia', 'Guam', 'Hawaii', 'Idaho', 'Illinois', 'Indiana', 'Iowa', 'Kansas', 
          'Kentucky', 'Louisiana', 'Maine', 'Maryland', 'Massachusetts', 'Michigan', 'Minnesota', 'Mississippi', 'Missouri', 
          'Montana', 'Nebraska', 'Nevada', 'New Hampshire', 'New Jersey', 'New Mexico', 'New York', 'North Carolina', 
          'North Dakota', 'Northern Mariana Islands', 'Ohio', 'Oklahoma', 'Oregon', 'Pennsylvania', 'Puerto Rico', 
          'Rhode Island', 'South Carolina', 'South Dakota', 'Tennessee', 'Texas', 'Utah', 'Vermont', 'Virgin Islands', 
          'Virginia', 'Washington', 'West Virginia', 'Wisconsin', 'Wyoming']
    
    EUROPEAN_UNION = ['Austria', 'Belgium', 'Bulgaria', 'Croatia', 'Cyprus', 'Czechia', 'Denmark', 'Estonia', 'Finland', 
                      'France', 'Germany', 'Greece', 'Hungary', 'Ireland', 'Italy', 'Latvia', 'Lithuania', 'Luxembourg', 
                      'Malta', 'Netherlands', 'Poland', 'Portugal', 'Romania', 'Slovakia', 'Slovenia', 'Spain', 'Sweden']
    
    eu = [i.upper() for i in EUROPEAN_UNION]
    states = [i.upper() for i in states]
    df.drop(df[df['country'].str.isspace()].index, inplace = True) 
    
    df['country'] = df['country'].str.upper()

    df['country'] = df['country'].str.replace('\d+', '')
    df.loc[df['country'].str.contains('ENGLAND', case=False), 'country'] = 'ENGLAND'
    df.loc[df['country'].str.contains('CANADA', case=False), 'country'] = 'CANADA'
    df.loc[df['country'].str.contains('US', case=False), 'country'] = 'USA'

    df['country'] = df['country'].replace(
        dict.fromkeys(['UK'], 'England'), 
        regex=False
    )
    df['country'] = df['country'].replace(
        dict.fromkeys(states, 'USA'), 
        regex=False
    )
    df['country'] = df['country'].replace(
        dict.fromkeys(states_ab, 'USA'), 
        regex=False
    )
    df['country'] = df['country'].replace(
        dict.fromkeys(eu, 'EUROPE'), 
        regex=False
    )
    df['country'] = df['country'].str.replace('[^a-zA-Z]', '')
    df.drop(df[df['country'].str.isspace()].index, inplace = True) 
    return df
def clean_source(df):
    df['source'] = df['source'].str.upper()
    df.loc[df['source'].str.contains('IP', case=False), 'source'] = 'APPLE'
    df.loc[df['source'].str.contains('ANDROID', case=False), 'source'] = 'ANDROID'
    df.loc[df['source'].str.contains('WEB', case=False), 'source'] = 'WEB'

    return df
print('I started the cleaning process...., go get a coefee, this might take few minutes')
tweets = df
tweets['text'] = tweets['text'].apply(cleanTxt)
stop = stopwords.words('english')
tweets.drop(tweets[tweets['text'].str.isspace()].index, inplace = True) 

tweets['text'] = tweets['text'].apply(lambda x: " ".join(x for x in x.split() if x not in stop))

# print(tweets.source.value_counts())
tweets = clean_country_values(tweets)
tweets = clean_source(tweets)

tweets.to_csv("all_cleaned_and_source.csv")
mcw = Counter(" ".join(tweets["text"]).split()).most_common(10)
print(mcw)
fig = plt.figure()
words = [mcw[i][0] for i in range(len(mcw))]
counts = [mcw[i][1] for i in range(len(mcw))]
ax = fig.add_axes([0,0,1,1])
ax.bar(words, counts)
plt.show()<jupyter_output>[('covid19vaccine', 1908), ('vaccine', 716), ('covid19', 628), ('get', 221), ('first', 179), ('covid', 174), ('amp', 156), ('vaccines', 138), ('pfizer', 138), ('people', 129)]<jupyter_text>Sentimental Analysis<jupyter_code># Create a function to get the subjectivity

def getSubjectivity(tweetText):
    return TextBlob(tweetText).sentiment.subjectivity

# Create a function to get the polarity

def getPolarity(tweetText):
    return TextBlob(tweetText).sentiment.polarity
# Create two new columns

tweets['Subjectivity'] = tweets['text'].apply(getSubjectivity)
tweets['Polarity'] = tweets['text'].apply(getPolarity)
tweets.head()
# Plot the World Cloud

AllWords = '  '. join( [twts for twts in tweets['text']])
wordCloud = WordCloud(width = 500, height = 300, random_state = 21, max_font_size = 119).generate(AllWords)
plt.imshow(wordCloud, interpolation = "bilinear")
plt.axis('off')
plt.show
# Create a function to compute the negative, neutral, and positive analysis

def getAnalysis(score):
    if score < 0:
        return 'Negative'
    elif score == 0:
        return 'Neutral'
    else:
        return 'Positive'
    
tweets['Analysis'] = tweets['Polarity'].apply(getAnalysis)
tweets.head()
# # Print all of the positive tweets

# j = 1
# sortedtweets = tweets.sort_values(by=['Polarity'])
# for i in range(0, sortedtweets.shape[0]):
#     print(i)
#     if (sortedtweets['Analysis'][i] == 'Positive'):
#         print(str(j) + ') ' +sortedtweets['text'][i])
#         print()
#         j = j+1
# # Print all of the negative tweets

# j = 1
# sortedTweets = tweets.sort_values(by=['Polarity'], ascending = 'False')
# for i in range(0, sortedtweets.shape[0]):
#     if (sortedTweets['Analysis'][i] == 'Negative'):
#         print(str(j) + ') ' +sortedTweets['text'][i])
#         print()
#         j = j+1
# Plot the polarity and subjectivity

plt.figure(figsize = (8,6))
for i in range(0, tweets.shape[0]):
    plt.scatter(tweets['Polarity'][i], tweets['Subjectivity'][i], color = 'Purple')

plt.title('Sentiment Analysis')
plt.xlabel('Polarity')
plt.ylabel('Subjectivity')
plt.show()
# Get the percentage of positive tweets

ptweets = tweets[tweets.Analysis == 'Positive']
ptweets = ptweets['text']

# round((ptweets.shape[0] / tweets.shape[0]) * 100, 1)

# Get the percentage of negative tweets

ntweets = tweets[tweets.Analysis == 'Negative']
ntweets = ntweets['text']

round((ntweets.shape[0] / tweets.shape[0]) * 100, 1)
# Get the percentage of neutral tweets

neutweets = tweets[tweets.Analysis == 'Neutral']
neutweets = neutweets['text']

round((neutweets.shape[0] / tweets.shape[0]) * 100, 1)
# Show the value counts

tweets['Analysis'].value_counts

# Plot and visualize the counts
plt.title('Sentiment Analysis')
plt.xlabel('Sentiment')
plt.ylabel('Counts')
tweets['Analysis'].value_counts().plot(kind = 'bar')
plt.show()
tweets.to_csv("all_cleaned_src_analysis.csv")
xxx = pd.read_csv("all_cleaned_src_analysis.csv")
print(len(xxx))
tweets.head()<jupyter_output>1918<jupyter_text>Inserting the Data into MySQL<jupyter_code># Establishing a connection with MySQL and Reading the data
try:
    connection = mysql.connector.connect(host='localhost',
                                         user='root',
                                         password='<PASSWORD>')
    
    mySql_Create_Database_Query = """CREATE DATABASE IF NOT EXISTS twitter2"""

    mySql_Create_Table_Query = """CREATE TABLE IF NOT EXISTS twitter2.Tweets2 ( 
                                 UserName varchar(250) NOT NULL,
                                 Description text NULL,
                                 Location varchar(200) NULL,
                                 Following varchar(200) NULL,
                                 Followers varchar(200) NULL,
                                 TotalTweets varchar(200) NULL,
                                 TweetId varchar(19) NOT NULL,
                                 Source varchar(200) NULL,
                                 TweetDate text NULL,
                                 TweetLength varchar(200) NULL,
                                 LikesCount varchar(200) NULL,
                                 RetweetCount varchar(200) NULL,
                                 TweetText text NULL,
                                 Hashtags text NULL,

                                 PRIMARY KEY (TweetId)); """

    cursor = connection.cursor()
    
    result = cursor.execute(mySql_Create_Database_Query)
    result1 = cursor.execute(mySql_Create_Table_Query)
    
    print("Twitter Database created successfully ")
    print("Tweets Table created successfully ")

except mysql.connector.Error as error:
    print("Failed to create table in MySQL: {}".format(error))
finally:
    if (connection.is_connected()):
        cursor.close()
        connection.close()
        print("MySQL connection is closed")

infiletest = open("scraped_tweets.csv", encoding="utf8")
csvReader = reader(infiletest)
connection = mysql.connector.connect(host='localhost',
                                         database='twitter2',
                                         user='root',
                                         password='<PASSWORD>')
cursor = connection.cursor()<jupyter_output><empty_output><jupyter_text>('Tweets', 'CREATE TABLE `tweets` (\n  `TweetId` int NOT NULL,\n  `UserName` varchar(250) NOT NULL,\n  `Description` text,\n  `Location` varchar(45) DEFAULT NULL,\n  `Following` varchar(45) DEFAULT NULL,\n  `Followers` varchar(45) DEFAULT NULL,\n  `TotalTweets` varchar(45) DEFAULT NULL,\n  `Source` varchar(45) DEFAULT NULL,\n  `TweetDate` text,\n  `LikesCount` varchar(45) DEFAULT NULL,\n  `RetweetCount` varchar(45) DEFAULT NULL,\n  `TweetText` text,\n  `Hashtags` text,\n  PRIMARY KEY (`TweetId`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci')<jupyter_code># Insert the data into MySQL
for row in csvReader:
    cursor.execute("INSERT INTO twitter2.Tweets2 (UserName, Description,Location, Following ,Followers, TotalTweets,TweetId, Source, TweetDate, TweetLength, LikesCount, RetweetCount ,TweetText, Hashtags) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)", [row[1], row[2], row[3], row[4], row[5], row[6], row[7] ,row[8], row[9], row[10], row[11], row[12], row[13], row[14]])
    connection.commit()

    
# In case we needed to drop a table
def drop_table(table_name):
    sql_drop = f"DROP TABLE twitter2.{table_name}"
    cursor.execute(sql_drop)
# drop_table('Tweets')
# In case we need to delete the data from MySQL
def delete_operation():
    sql = "DELETE FROM twitter2.Tweets WHERE TweetId >= '0'" 

    try:
       # Execute the SQL command
       cursor.execute(sql)

       # Commit your changes in the database
       connection.commit()
    except:
       # Roll back in case there is any error
       connection.rollback()
try:
    cursor.execute("SELECT * FROM twitter2.Tweets;")

    myresult = cursor.fetchall()
    for i in myresult:
        print(i, '\n')
except:
    print("Table does not Exist")

from_sql = pd.DataFrame(myresult, columns =['UserName', 'Description', 'Location', 'Following', 'Followers','TotalTweets', 'TweetId', 'Source', 'TweetDate', 'TweetLength','LikesCount', 'RetweetCount', 'TweetText', 'Hashtags'])
from_sql.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15284298382889933, 'fraction_numerical': 0.019185069263316524, 'mean_word_length': 3.162605548854041, 'pattern_counts': {'":': 0, '<': 24, '<?xml version=': 0, '>': 24, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 29, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '250302', 'license': 'MIT', 'n_tokens_mistral': 6087, 'n_tokens_neox': 5627, 'n_words': 1352, 'path': 'Preparation.ipynb', 'repo_name': 'sfh22/Twitter-Sentiment-Analysis', 'size': '17371'}