<jupyter_start><jupyter_text>i.	What	are	people	saying	about	me	(somebody)?<jupyter_code>sql = "select avg(favorite_count) from 622158584_tweets"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>These data are scrapped from twitter with twitterid = 622158584. By searching messi, we got three userid who have at least posted with messi once. So these three at least have one chance to post similar post.This user got average 0.2063 favoite per post among 223 posts. People barely know him. ii.	How	viral	are	my	posts?<jupyter_code>sql = "select avg(retweet_count) from 622158584_tweets"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>This user got an average of 404 retweets per post. This is a big number but nor really big in twitter. So this user seems not to be known by others. iii.	How	much	influence	to	my	posts have?<jupyter_code>sql = "select count(retweet_count) from 622158584_tweets where retweet_count>1000 && favorite_count>1000"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>Favorite count at 1000 is not a really high level in twitter. This user seems to have no tweet meeting this line. So we have to lower the level<jupyter_code>sql = "select max(favorite_count), max(retweet_count)from 622158584_tweets"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>The user got a max favorite count of 28 and a max retweet count of 41285.<jupyter_code>sql = "select count(retweet_count) from 622158584_tweets where retweet_count>10000"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>Since this user got a max retweet of 40k+, we set the limit to 10k and get the result that this large retweet only happened once. The user may not be very influenced among similar users or even not influential at all. iv.	What	posts are	like	mine?<jupyter_code>import pymysql  
import  pymysql.cursors
import numpy as np
import pandas as pd
connection = pymysql.connect(host='localhost',  
                             user='root',  
                             password='<PASSWORD>',  
                             db='sample',  
                             port=3306,  
                             charset='latin1')
cursor = connection.cursor()
sql = "select text from similarposts"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>Assume a user is using hashtag messi,barcelona. We can search tweets by hashtag [messi,barcelona]. All the results are stored in the file named similarposts. So these tweets are using at least two same tags with the user. These posts are like those of the user. v.	What	users	post like	me?<jupyter_code>sql = "select user_screen_name from similarposts"
cursor.execute(sql)
result = cursor.fetchall();
result<jupyter_output><empty_output><jupyter_text>These are the user screen name of the posts above which are like those of the user. So these peopel are using similar tags with the user. We can assume they post like the user. vi.	Who	should	I	be	following? From the question above, we got three users named fabio_adorno and ForeignPolicy and whalaySiS. Through twitter api we can check whether they followed each other.<jupyter_code>import twitter
CONSUMER_KEY = 'ilrMBG1phrX3iT3BCP6rbB0uS'
CONSUMER_SECRET = '<KEY>'
OAUTH_TOKEN = '<KEY>'
OAUTH_TOKEN_SECRET = '<KEY>'
auth = twitter.oauth.OAuth(OAUTH_TOKEN, OAUTH_TOKEN_SECRET,
                           CONSUMER_KEY, CONSUMER_SECRET)

twitter_api = twitter.Twitter(auth=auth)
print(twitter_api)
source = "fabio_adorno"
target = "foreignpolicy"

#-----------------------------------------------------------------------
# perform the API query
# twitter API docs: https://dev.twitter.com/rest/reference/get/friendships/show
#-----------------------------------------------------------------------
result = twitter_api.friendships.show(source_screen_name = source,
                                  target_screen_name = target)

#-----------------------------------------------------------------------
# extract the relevant properties
#-----------------------------------------------------------------------
following = result["relationship"]["target"]["following"]
follows   = result["relationship"]["target"]["followed_by"]

print ("%s following %s: %s" % (source, target, follows))
print ("%s following %s: %s" % (target, source, following))
source = "fabio_adorno"
target = "whalaySiS"

#-----------------------------------------------------------------------
# perform the API query
# twitter API docs: https://dev.twitter.com/rest/reference/get/friendships/show
#-----------------------------------------------------------------------
result = twitter_api.friendships.show(source_screen_name = source,
                                  target_screen_name = target)

#-----------------------------------------------------------------------
# extract the relevant properties
#-----------------------------------------------------------------------
following = result["relationship"]["target"]["following"]
follows   = result["relationship"]["target"]["followed_by"]

print ("%s following %s: %s" % (source, target, follows))
print ("%s following %s: %s" % (target, source, following))
source = "whalaySiS"
target = "foreignpolicy"

#-----------------------------------------------------------------------
# perform the API query
# twitter API docs: https://dev.twitter.com/rest/reference/get/friendships/show
#-----------------------------------------------------------------------
result = twitter_api.friendships.show(source_screen_name = source,
                                  target_screen_name = target)

#-----------------------------------------------------------------------
# extract the relevant properties
#-----------------------------------------------------------------------
following = result["relationship"]["target"]["following"]
follows   = result["relationship"]["target"]["followed_by"]

print ("%s following %s: %s" % (source, target, follows))
print ("%s following %s: %s" % (target, source, following))
import pymysql  
import  pymysql.cursors
import numpy as np
import pandas as pd
connection = pymysql.connect(host='localhost',  
                             user='root',  
                             password='<PASSWORD>',  
                             db='sample',  
                             port=3306,  
                             charset='utf8')
cursor = connection.cursor()
sql = "SELECT tag1, count(*) FROM gai595406250_tweets group by tag1 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag2, count(*) FROM gai595406250_tweets group by tag2 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag3, count(*) FROM gai595406250_tweets group by tag3 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag4, count(*) FROM gai595406250_tweets group by tag4 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>This is the frequency of hashtags used by whalaysIs. We ca do this on another two users.<jupyter_code>sql = "SELECT tag1, count(*) FROM gai46791653_tweets group by tag1 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag2, count(*) FROM gai46791653_tweets group by tag2 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag3, count(*) FROM gai46791653_tweets group by tag3 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag4, count(*) FROM gai46791653_tweets group by tag4 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>Seemly this user fabio_adorno may like travelling.<jupyter_code>sql = "SELECT tag1, count(*) FROM gai26792275_tweets group by tag1 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag2, count(*) FROM gai26792275_tweets group by tag2 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag3, count(*) FROM gai26792275_tweets group by tag3 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>Looks like user fabio_adorno should follow ForeignPolicy even they are familiar in other domain than la liga. And yes, fabio_adorno has followed ForeignPolicy. He made a right choice. The users here can be replaced by any user and we can find the most similar ones. vii.	What	topics	are	trending	in	my	domain? Our domain is set to be La Liga. So we search by la liga and top three la liga player to make sure the posts retrieved are in the domain.<jupyter_code>for tweet in tweepy.Cursor (api.search, q = ['la liga','messi','Messi','Ronaldo','ronaldo'], lang = "en").items(2000): 
    results.append(tweet)<jupyter_output><empty_output><jupyter_text>We found that our domain itself is not often used as a tag. So we search by the top 2 player in La Liga Messi and Ronaldo. So we search by messi and ronaldo and count the tags.<jupyter_code>import pymysql  
import  pymysql.cursors
import numpy as np
import pandas as pd
connection = pymysql.connect(host='localhost',  
                             user='root',  
                             password='<PASSWORD>',  
                             db='sample',  
                             port=3306,  
                             charset='utf8')
cursor = connection.cursor()
sql = "select * from hashcount1"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "select * from hashcountsample"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>We can see the toppest ones are the trends in the domain. By adding player and team in the search criteria, we can get more tweets and are more likely to fetch waht's really trending. viii.	What	keywords/	hashtags	should	I	add	to	my	post? Assume i'm using messi very often. I can search by messi and count all the hashtags using with messi as a trend. In a period, these keywords/hashtags should i add to my post.<jupyter_code>import pymysql  
import  pymysql.cursors
import numpy as np
import pandas as pd
connection = pymysql.connect(host='localhost',  
                             user='root',  
                             password='<PASSWORD>',  
                             db='sample',  
                             port=3306,  
                             charset='latin1')
cursor = connection.cursor()
sql = "select * from countmessi"
cursor.execute(sql);
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>We can see that Iniesta shows very often in the tweet. It occured two times of the second one. So we should add this hashtag when posting. ix.	Should	I	follow	somebody	back? This question is just like the sixth one. We find that ForeignPolicy and fabio_adorno are somewaht alike. We also find that ForeignPolicy hasn't followed fabio_adorno. So to the scale we scrapped like around 200 per person. It's not very likely to find two with a lot of same favorites. So we think ForeignPolicy may consider follow back. x.	What	is	the	best	time	to	post?<jupyter_code>import pymysql  
import  pymysql.cursors
import numpy as np
import pandas as pd
connection = pymysql.connect(host='localhost',  
                             user='root',  
                             password='<PASSWORD>',  
                             db='sample',  
                             port=3306,  
                             charset='latin1')
cursor = connection.cursor()
sql = "select  created_at,count(*) from similarposts1 GROUP by created_at ORDER by created_at desc"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>We count tweets posted per hour which using messi and find that at noon in USA tweets raise to the highest amount. This may be caused by at that time in Spain is La Liga time. People tend to post more tweets when watching games. Also we find that many people tend to post tweets before sleep which shows as a raise at 21 o'clock. xi.	Should	I	add	and	picture	or	url	to	my	post?<jupyter_code>sql = "select count(*) from messi where text like '%http%'"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output><jupyter_text>We got aroung 1k tweets mentioning messi but only 3 of them attached a url.<jupyter_code>from pandas import read_csv
x = read_csv('3GerardPique_facebook_statuses.csv',encoding='UTF-8')
y = x['status_type'];
y = pd.DataFrame(y)
y
counts = pd.DataFrame(y['status_type'].value_counts())
counts
x = read_csv('BarcelonaDNA_facebook_statuses.csv',encoding='UTF-8')
y = x['status_type'];
y = pd.DataFrame(y)
counts = pd.DataFrame(y['status_type'].value_counts())
counts
x = read_csv('LaLiga_facebook_statuses.csv',encoding='UTF-8')
y = x['status_type'];
y = pd.DataFrame(y)
counts = pd.DataFrame(y['status_type'].value_counts())
counts<jupyter_output><empty_output><jupyter_text>We count the use of media type on facebook. We choose the account of player, fanclub and official site. As we can see, facebook with plain text seldom appears. Most of them carry a video or a photo. So for Facebook, it's better to com up with some media tpe rather than posting a long plain text. xii.	What’s	my	reach? To answer this question. We decide to scrap a user's follower and search posts of each follower. By counting the tags they usually use, we try to find out that whether they are from other domains. Fot instance, we look for messi's follower and find their posts.After a tough lookup, we got a user LuisTGS(PS4).<jupyter_code>import pandas as pd
from pandas import read_csv
import tweepy
import csv
CONSUMER_KEY = 'ilrMBG1phrX3iT3BCP6rbB0uS'
CONSUMER_SECRET = '<KEY>'
ACCESS_TOKEN = '<KEY>'
ACCESS_TOKEN_SECRET = '<KEY>'
def get_all_tweets(user_id):
    
    auth = tweepy.OAuthHandler(CONSUMER_KEY, CONSUMER_SECRET)
    auth.set_access_token(ACCESS_TOKEN, ACCESS_TOKEN_SECRET)
    api = tweepy.API(auth)

    alltweets = []
    new_tweets = api.user_timeline(user_id=user_id, count=200)
    alltweets.extend(new_tweets)
    oldest = alltweets[-1].id - 1
    while len(new_tweets) > 0:
        print ("getting tweets before %s" % (oldest))
        new_tweets = api.user_timeline(user_id=user_id, count=200, max_id=oldest)
        print(new_tweets[0])
        alltweets.extend(new_tweets)
        oldest = alltweets[-1].id - 1
        print ("...%s tweets downloaded so far" % (len(alltweets)))
        outtweets = [[tweet.id_str, tweet.created_at, tweet.text.encode("utf-8"),tweet.favorite_count,tweet.retweet_count,tweet.entities.get('hashtags')] for tweet in alltweets]
        outtweets = pd.DataFrame(outtweets)
        outtweets.to_csv('%s_tweets.csv' % user_id, index=True, encoding='UTF-8')

if __name__ == '__main__':
            try:
                get_all_tweets('2518106781')
            except tweepy.TweepError as e:
                print ('Failed to run the command on that user, Skipping...')
            except IndexError as e:
                print ('List index out of range, Skipping...')
import csv
import pandas as pd
import sys, getopt, pprint
from pymongo import MongoClient
user_id = '2518106781'
csvfile = open('%s_tweets.csv' % user_id,'r')

sql = "SELECT tag1, count(*) FROM gai2518106781_tweets group by tag1 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag2, count(*) FROM gai2518106781_tweets group by tag2 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result
sql = "SELECT tag3, count(*) FROM gai2518106781_tweets group by tag3 order by count(*) desc"
cursor.execute(sql)
result = cursor.fetchall()
result<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup