Twitter Data Analysis
In this project, I use SQL and Python to ucover insights from Elon Musk's tweets.
Data Preparation
## Import libraries
import snscrape.modules.twitter as sntwitter
import pandas as pd
import datetime
import plotly.express as px
import seaborn as sn
## Set a time horizon
current_time = datetime.datetime.now()
date = current_time.strftime("%Y-%m-%d")
## Set variables
twitter_username = 'elonmusk'
tweet_limit = 25000
twitter_query = "(from:" + twitter_username + ") until:" + date + " since:2006-03-21"
tweets = []
## Append tweets
for tweet in sntwitter.TwitterSearchScraper(twitter_query).get_items():
if len(tweets) == tweet_limit:
break
else:
tweets.append([tweet.date, tweet.username, tweet.content, tweet.replyCount, tweet.retweetCount, tweet.likeCount])
Error retrieving https://api.twitter.com/1.1/guest/activate.json: non-200 response (429)
4 requests to https://api.twitter.com/1.1/guest/activate.json failed, giving up.
Execution error
ScraperException: 4 requests to https://api.twitter.com/1.1/guest/activate.json failed, giving up.
## Create dataframe
Df = pd.DataFrame(tweets, columns=['Date', 'User', 'Tweet', 'Replies', 'Retweets', 'Likes'])
Df
Datedatetime64[ns, UTC]
2010-06-04 18:31:57+00:00 - 2022-11-27 21:39:06+00:00
Userobject
elonmusk100%
0
2022-11-27 21:39:06+00:00
elonmusk
1
2022-11-27 20:24:15+00:00
elonmusk
2
2022-11-27 20:19:47+00:00
elonmusk
3
2022-11-27 20:14:45+00:00
elonmusk
4
2022-11-27 19:00:41+00:00
elonmusk
5
2022-11-27 18:54:33+00:00
elonmusk
6
2022-11-27 18:48:00+00:00
elonmusk
7
2022-11-27 17:53:32+00:00
elonmusk
8
2022-11-27 17:11:21+00:00
elonmusk
9
2022-11-27 16:35:51+00:00
elonmusk
Insights
Timeseries
SELECT YEAR(Date) AS Year,
COUNT(Tweet) AS Tweets,
SUM(Replies) AS Replies,
SUM(Retweets) AS Retweets,
SUM(Likes) AS Likes
FROM Df
WHERE YEAR(Df.Date) >= YEAR(NOW()) - 8
GROUP BY YEAR(Date);
yearint64
2014 - 2022
Tweetsint64
186 - 4025
0
2022
4025
1
2021
3002
2
2020
3241
3
2019
2825
4
2018
2187
5
2017
1116
6
2016
725
7
2015
316
8
2014
186
fig = px.bar(sql_df_annual_ts, x="year", y="Tweets", title="Tweets Grouped Year", text_auto=True)
fig.update_traces(textfont_size=12, textposition="outside", cliponaxis=False)
fig.update_layout({'plot_bgcolor': 'rgba(0, 0, 0, 0)','paper_bgcolor': 'rgba(0, 0, 0, 0)'})
fig.show()
fig = px.bar(sql_df_annual_ts, x="year", y="Replies", title="Replies Grouped Year", text_auto=True)
fig.update_traces(textfont_size=12, textposition="outside", cliponaxis=False)
fig.update_layout({'plot_bgcolor': 'rgba(0, 0, 0, 0)','paper_bgcolor': 'rgba(0, 0, 0, 0)'})
fig.show()
fig = px.bar(sql_df_annual_ts, x="year", y="Retweets", title="Retweets Grouped Year", text_auto=True)
fig.update_traces(textfont_size=12, textposition="outside", cliponaxis=False)
fig.update_layout({'plot_bgcolor': 'rgba(0, 0, 0, 0)','paper_bgcolor': 'rgba(0, 0, 0, 0)'})
fig.show()
fig = px.bar(sql_df_annual_ts, x="year", y="Likes", title="Likes Grouped Year", text_auto=True)
fig.update_traces(textfont_size=12, textposition="outside", cliponaxis=False)
fig.update_layout({'plot_bgcolor': 'rgba(0, 0, 0, 0)','paper_bgcolor': 'rgba(0, 0, 0, 0)'})
fig.show()
Growth
SELECT ROUND((CAST(Sq1.Tweets AS DECIMAL) - Sq2.Tweets) / Sq2.Tweets * 100, 2) AS Tweets_PCT,
ROUND((CAST(Sq1.Replies AS DECIMAL) - Sq2.Replies) / Sq2.Replies * 100, 2) AS Replies_PCT,
ROUND((CAST(Sq1.Retweets AS DECIMAL) - Sq2.Retweets) / Sq2.Retweets * 100, 2) AS Retweets_PCT,
ROUND((CAST(Sq1.Likes AS DECIMAL) - Sq2.Likes) / Sq2.Likes * 100, 2) AS Likes_PCT
FROM (SELECT 1 AS _,
COUNT(Df.Tweet) AS Tweets,
SUM(Df.Replies) AS Replies,
SUM(Df.Retweets) AS Retweets,
SUM(Df.Likes) AS Likes
FROM Df AS Df
WHERE YEAR(Df.Date) = YEAR(NOW()))Sq1
LEFT
JOIN (SELECT 1 AS _,
COUNT(Df.Tweet) AS Tweets,
SUM(Df.Replies) AS Replies,
SUM(Df.Retweets) AS Retweets,
SUM(Df.Likes) AS Likes
FROM Df AS Df
WHERE Df.Date BETWEEN CONCAT(YEAR(NOW()) - 1, '-01-01 00:00:00') AND CONCAT(YEAR(NOW()) - 1, '-', MONTH(NOW()), '-', DAY(NOW()), ' 23:59:59.999999'))Sq2 ON Sq1._ = Sq2._
Tweets_PCTfloat64
Replies_PCTfloat64
0
51.32
186.45
Most Replied
Df[['Date', 'Tweet', 'Replies']].sort_values(by='Replies', ascending=False).head(10)
Datedatetime64[ns, UTC]
2022-04-25 16:12:30+00:00 - 2022-11-23 08:50:48+00:00
Tweetobject
What should Twitter do next?10%
Reinstate former President Trump10%
8 others80%
326
2022-11-18 17:38:07+00:00
What should Twitter do next?
310
2022-11-19 00:47:45+00:00
Reinstate former President Trump
3070
2022-04-28 00:56:58+00:00
Next I’m buying Coca-Cola to put the cocaine back in
3094
2022-04-25 16:12:30+00:00
I hope that even my worst critics remain on Twitter, because that is what free speech means
163
2022-11-23 08:50:48+00:00
What is best in life?
3093
2022-04-25 19:43:22+00:00
🚀💫♥️ Yesss!!! ♥️💫🚀 https://t.co/0T9HzUHuh6
2934
2022-05-09 00:51:26+00:00
If I die under mysterious circumstances, it’s been nice knowin ya
889
2022-10-28 03:49:11+00:00
the bird is freed
745
2022-11-04 14:28:20+00:00
Twitter has had a massive drop in revenue, due to activist groups pressuring advertisers, even though nothing has changed with content moderation and we did everything we could to appease the activists. Extremely messed up! They’re trying to destroy free speech in America.
282
2022-11-20 00:53:25+00:00
The people have spoken. Trump will be reinstated. Vox Populi, Vox Dei. https://t.co/jmkhFuyfkv
Most Retweeted
Df[['Date', 'Tweet', 'Retweets']].sort_values(by='Retweets', ascending=False).head(10)
Datedatetime64[ns, UTC]
2018-10-19 21:01:57+00:00 - 2022-11-19 00:47:45+00:00
Tweetobject
Next I’m buying Coca-Cola to put the cocaine back in10%
I hope that even my worst critics remain on Twitter, because that is what free speech means10%
8 others80%
3070
2022-04-28 00:56:58+00:00
Next I’m buying Coca-Cola to put the cocaine back in
3094
2022-04-25 16:12:30+00:00
I hope that even my worst critics remain on Twitter, because that is what free speech means
889
2022-10-28 03:49:11+00:00
the bird is freed
3093
2022-04-25 19:43:22+00:00
🚀💫♥️ Yesss!!! ♥️💫🚀 https://t.co/0T9HzUHuh6
13600
2018-10-19 21:01:57+00:00
Had to been done ur welcome https://t.co/7jT0f9lqIS
9849
2020-03-06 20:42:39+00:00
The coronavirus panic is dumb
881
2022-10-28 21:16:42+00:00
Comedy is now legal on Twitter
338
2022-11-18 04:23:16+00:00
https://t.co/rbwbsLA1ZG
310
2022-11-19 00:47:45+00:00
Reinstate former President Trump
3066
2022-04-28 01:57:27+00:00
Listen, I can’t do miracles ok https://t.co/z7dvLMUXy8
Most Liked
Df[['Date', 'Tweet', 'Likes']].sort_values(by='Likes', ascending=False).head(10)
Datedatetime64[ns, UTC]
2022-04-25 16:12:30+00:00 - 2022-11-18 04:23:16+00:00
Tweetobject
Next I’m buying Coca-Cola to put the cocaine back in10%
I hope that even my worst critics remain on Twitter, because that is what free speech means10%
8 others80%
3070
2022-04-28 00:56:58+00:00
Next I’m buying Coca-Cola to put the cocaine back in
3094
2022-04-25 16:12:30+00:00
I hope that even my worst critics remain on Twitter, because that is what free speech means
3067
2022-04-28 01:53:50+00:00
Let’s make Twitter maximum fun!
3093
2022-04-25 19:43:22+00:00
🚀💫♥️ Yesss!!! ♥️💫🚀 https://t.co/0T9HzUHuh6
3066
2022-04-28 01:57:27+00:00
Listen, I can’t do miracles ok https://t.co/z7dvLMUXy8
889
2022-10-28 03:49:11+00:00
the bird is freed
881
2022-10-28 21:16:42+00:00
Comedy is now legal on Twitter
338
2022-11-18 04:23:16+00:00
https://t.co/rbwbsLA1ZG
770
2022-11-02 19:47:39+00:00
https://t.co/kGncG7Hs3M
2934
2022-05-09 00:51:26+00:00
If I die under mysterious circumstances, it’s been nice knowin ya
Most Used Words
## Split sentences to individual words
split = Df.Tweet.str.split()
flattened_list = sum(split, [])
## Render all words to lowercase
flattened_list = [word.lower() for word in flattened_list]
## Remove stopwords
stopwords = ["0o", "0s", "3a", "3b", "3d", "6b", "6o", "a", "a1", "a2", "a3", "a4", "ab", "able", "about", "above", "abst", "ac", "accordance", "according", "accordingly", "across", "act", "actually", "ad", "added", "adj", "ae", "af", "affected", "affecting", "affects", "after", "afterwards", "ag", "again", "against", "ah", "ain", "ain't", "aj", "al", "all", "allow", "allows", "almost", "alone", "along", "already", "also", "although", "always", "am", "among", "amongst", "amoungst", "amount", "an", "and", "announce", "another", "any", "anybody", "anyhow", "anymore", "anyone", "anything", "anyway", "anyways", "anywhere", "ao", "ap", "apart", "apparently", "appear", "appreciate", "appropriate", "approximately", "ar", "are", "aren", "arent", "aren't", "arise", "around", "as", "a's", "aside", "ask", "asking", "associated", "at", "au", "auth", "av", "available", "aw", "away", "awfully", "ax", "ay", "az", "b", "b1", "b2", "b3", "ba", "back", "bc", "bd", "be", "became", "because", "become", "becomes", "becoming", "been", "before", "beforehand", "begin", "beginning", "beginnings", "begins", "behind", "being", "believe", "below", "beside", "besides", "best", "better", "between", "beyond", "bi", "bill", "biol", "bj", "bk", "bl", "bn", "both", "bottom", "bp", "br", "brief", "briefly", "bs", "bt", "bu", "but", "bx", "by", "c", "c1", "c2", "c3", "ca", "call", "came", "can", "cannot", "cant", "can't", "cause", "causes", "cc", "cd", "ce", "certain", "certainly", "cf", "cg", "ch", "changes", "ci", "cit", "cj", "cl", "clearly", "cm", "c'mon", "cn", "co", "com", "come", "comes", "con", "concerning", "consequently", "consider", "considering", "contain", "containing", "contains", "corresponding", "could", "couldn", "couldnt", "couldn't", "course", "cp", "cq", "cr", "cry", "cs", "c's", "ct", "cu", "currently", "cv", "cx", "cy", "cz", "d", "d2", "da", "date", "dc", "dd", "de", "definitely", "describe", "described", "despite", "detail", "df", "di", "did", "didn", "didn't", "different", "dj", "dk", "dl", "do", "does", "doesn", "doesn't", "doing", "don", "done", "don't", "down", "downwards", "dp", "dr", "ds", "dt", "du", "due", "during", "dx", "dy", "e", "e2", "e3", "ea", "each", "ec", "ed", "edu", "ee", "ef", "effect", "eg", "ei", "eight", "eighty", "either", "ej", "el", "eleven", "else", "elsewhere", "em", "empty", "en", "end", "ending", "enough", "entirely", "eo", "ep", "eq", "er", "es", "especially", "est", "et", "et-al", "etc", "eu", "ev", "even", "ever", "every", "everybody", "everyone", "everything", "everywhere", "ex", "exactly", "example", "except", "ey", "f", "f2", "fa", "far", "fc", "few", "ff", "fi", "fifteen", "fifth", "fify", "fill", "find", "fire", "first", "five", "fix", "fj", "fl", "fn", "fo", "followed", "following", "follows", "for", "former", "formerly", "forth", "forty", "found", "four", "fr", "from", "front", "fs", "ft", "fu", "full", "further", "furthermore", "fy", "g", "ga", "gave", "ge", "get", "gets", "getting", "gi", "give", "given", "gives", "giving", "gj", "gl", "go", "goes", "going", "gone", "got", "gotten", "gr", "greetings", "gs", "gy", "h", "h2", "h3", "had", "hadn", "hadn't", "happens", "hardly", "has", "hasn", "hasnt", "hasn't", "have", "haven", "haven't", "having", "he", "hed", "he'd", "he'll", "hello", "help", "hence", "her", "here", "hereafter", "hereby", "herein", "heres", "here's", "hereupon", "hers", "herself", "hes", "he's", "hh", "hi", "hid", "him", "himself", "his", "hither", "hj", "ho", "home", "hopefully", "how", "howbeit", "however", "how's", "hr", "hs", "http", "hu", "hundred", "hy", "i", "i2", "i3", "i4", "i6", "i7", "i8", "ia", "ib", "ibid", "ic", "id", "i'd", "ie", "if", "ig", "ignored", "ih", "ii", "ij", "il", "i'll", "im", "i'm", "immediate", "immediately", "importance", "important", "in", "inasmuch", "inc", "indeed", "index", "indicate", "indicated", "indicates", "information", "inner", "insofar", "instead", "interest", "into", "invention", "inward", "io", "ip", "iq", "ir", "is", "isn", "isn't", "it", "itd", "it'd", "it'll", "its", "it's", "itself", "iv", "i've", "ix", "iy", "iz", "j", "jj", "jr", "js", "jt", "ju", "just", "k", "ke", "keep", "keeps", "kept", "kg", "kj", "km", "know", "known", "knows", "ko", "l", "l2", "la", "largely", "last", "lately", "later", "latter", "latterly", "lb", "lc", "le", "least", "les", "less", "lest", "let", "lets", "let's", "lf", "like", "liked", "likely", "line", "little", "lj", "ll", "ll", "ln", "lo", "look", "looking", "looks", "los", "lr", "ls", "lt", "ltd", "m", "m2", "ma", "made", "mainly", "make", "makes", "many", "may", "maybe", "me", "mean", "means", "meantime", "meanwhile", "merely", "mg", "might", "mightn", "mightn't", "mill", "million", "mine", "miss", "ml", "mn", "mo", "more", "moreover", "most", "mostly", "move", "mr", "mrs", "ms", "mt", "mu", "much", "mug", "must", "mustn", "mustn't", "my", "myself", "n", "n2", "na", "name", "namely", "nay", "nc", "nd", "ne", "near", "nearly", "necessarily", "necessary", "need", "needn", "needn't", "needs", "neither", "never", "nevertheless", "new", "next", "ng", "ni", "nine", "ninety", "nj", "nl", "nn", "no", "nobody", "non", "none", "nonetheless", "noone", "nor", "normally", "nos", "not", "noted", "nothing", "novel", "now", "nowhere", "nr", "ns", "nt", "ny", "o", "oa", "ob", "obtain", "obtained", "obviously", "oc", "od", "of", "off", "often", "og", "oh", "oi", "oj", "ok", "okay", "ol", "old", "om", "omitted", "on", "once", "one", "ones", "only", "onto", "oo", "op", "oq", "or", "ord", "os", "ot", "other", "others", "otherwise", "ou", "ought", "our", "ours", "ourselves", "out", "outside", "over", "overall", "ow", "owing", "own", "ox", "oz", "p", "p1", "p2", "p3", "page", "pagecount", "pages", "par", "part", "particular", "particularly", "pas", "past", "pc", "pd", "pe", "per", "perhaps", "pf", "ph", "pi", "pj", "pk", "pl", "placed", "please", "plus", "pm", "pn", "po", "poorly", "possible", "possibly", "potentially", "pp", "pq", "pr", "predominantly", "present", "presumably", "previously", "primarily", "probably", "promptly", "proud", "provides", "ps", "pt", "pu", "put", "py", "q", "qj", "qu", "que", "quickly", "quite", "qv", "r", "r2", "ra", "ran", "rather", "rc", "rd", "re", "readily", "really", "reasonably", "recent", "recently", "ref", "refs", "regarding", "regardless", "regards", "related", "relatively", "research", "research-articl", "respectively", "resulted", "resulting", "results", "rf", "rh", "ri", "right", "rj", "rl", "rm", "rn", "ro", "rq", "rr", "rs", "rt", "ru", "run", "rv", "ry", "s", "s2", "sa", "said", "same", "saw", "say", "saying", "says", "sc", "sd", "se", "sec", "second", "secondly", "section", "see", "seeing", "seem", "seemed", "seeming", "seems", "seen", "self", "selves", "sensible", "sent", "serious", "seriously", "seven", "several", "sf", "shall", "shan", "shan't", "she", "shed", "she'd", "she'll", "shes", "she's", "should", "shouldn", "shouldn't", "should've", "show", "showed", "shown", "showns", "shows", "si", "side", "significant", "significantly", "similar", "similarly", "since", "sincere", "six", "sixty", "sj", "sl", "slightly", "sm", "sn", "so", "some", "somebody", "somehow", "someone", "somethan", "something", "sometime", "sometimes", "somewhat", "somewhere", "soon", "sorry", "sp", "specifically", "specified", "specify", "specifying", "sq", "sr", "ss", "st", "still", "stop", "strongly", "sub", "substantially", "successfully", "such", "sufficiently", "suggest", "sup", "sure", "sy", "system", "sz", "t", "t1", "t2", "t3", "take", "taken", "taking", "tb", "tc", "td", "te", "tell", "ten", "tends", "tf", "th", "than", "thank", "thanks", "thanx", "that", "that'll", "thats", "that's", "that've", "the", "their", "theirs", "them", "themselves", "then", "thence", "there", "thereafter", "thereby", "thered", "therefore", "therein", "there'll", "thereof", "therere", "theres", "there's", "thereto", "thereupon", "there've", "these", "they", "theyd", "they'd", "they'll", "theyre", "they're", "they've", "thickv", "thin", "think", "third", "this", "thorough", "thoroughly", "those", "thou", "though", "thoughh", "thousand", "three", "throug", "through", "throughout", "thru", "thus", "ti", "til", "tip", "tj", "tl", "tm", "tn", "to", "together", "too", "took", "top", "toward", "towards", "tp", "tq", "tr", "tried", "tries", "truly", "try", "trying", "ts", "t's", "tt", "tv", "twelve", "twenty", "twice", "two", "tx", "u", "u201d", "ue", "ui", "uj", "uk", "um", "un", "under", "unfortunately", "unless", "unlike", "unlikely", "until", "unto", "uo", "up", "upon", "ups", "ur", "us", "use", "used", "useful", "usefully", "usefulness", "uses", "using", "usually", "ut", "v", "va", "value", "various", "vd", "ve", "ve", "very", "via", "viz", "vj", "vo", "vol", "vols", "volumtype", "vq", "vs", "vt", "vu", "w", "wa", "want", "wants", "was", "wasn", "wasnt", "wasn't", "way", "we", "wed", "we'd", "welcome", "well", "we'll", "well-b", "went", "were", "we're", "weren", "werent", "weren't", "we've", "what", "whatever", "what'll", "whats", "what's", "when", "whence", "whenever", "when's", "where", "whereafter", "whereas", "whereby", "wherein", "wheres", "where's", "whereupon", "wherever", "whether", "which", "while", "whim", "whither", "who", "whod", "whoever", "whole", "who'll", "whom", "whomever", "whos", "who's", "whose", "why", "why's", "wi", "widely", "will", "willing", "wish", "with", "within", "without", "wo", "won", "wonder", "wont", "won't", "words", "world", "would", "wouldn", "wouldnt", "wouldn't", "www", "x", "x1", "x2", "x3", "xf", "xi", "xj", "xk", "xl", "xn", "xo", "xs", "xt", "xv", "xx", "y", "y2", "yes", "yet", "yj", "yl", "you", "youd", "you'd", "you'll", "your", "youre", "you're", "yours", "yourself", "yourselves", "you've", "yr", "ys", "yt", "z", "zero", "zi", "zz", "it’s", "i’m", "it.", "▶️", "*", "…", "/", "let’s", "&", "he’s", "here’s", "don’t", "you’re", "can’t", "i’ve", "ain’t", "i’ll", "that’s", "there’s", "&", "now:", "live:", "—", "@space_station.", "more:", "1", "-", "2", "3", "4", "5"]
flattened_list_nostops = [word for word in flattened_list if word not in stopwords]
## Remove tagged users
flattened_list_nostops_notags = [word for word in flattened_list_nostops if not word.startswith('@')]
arr = pd.Series(flattened_list_nostops_notags)
arr.value_counts().head(50)
Most Tagged Users
tagged_users = [word for word in flattened_list if word.startswith('@')]
arr2 = pd.Series(tagged_users)
arr2.value_counts().head(50)