<jupyter_start><jupyter_text>ABSTRACTWe will be converting the relational game_inventory database that we created on Assignment 2 to NoSQL database. We will be using mongodb atlas to store the data. We will also be storing the tweets for a specific game title on twitter collection in mongodb atlas.    Collections                                                                                                    We have two collections in the NoSQL database. One is Game_Inventory and twitter collection  Game_InventoryG_ID : the unique id for the game which is related to tweets we are collecting in mongodb database.G_NAME : Title of the GameGENRE : Genre of the GameDEVELOPER : Developer of the gamePLATFORM : Platform in which the game can be playedTOTAL_SHIPPED : Total no. of games shippedCRITIC_SCORE : Rating of the game based on critics	USER_SCORE : Rating of the game based on usersYEAR : the year when the game was published twitter_id : the unique id of the tweet generated by the twitter api.G_ID : the unique id for the game which is related to tweets we are collecting in mongodb database.tweet_text :  the tweet related to the gametweet_created : the time of the tweet when the tweet was postedtweet_loc : the location of the tweet when it was posteduser_name : the username of the twitter account who made the tweetuser_description : the description of the twitter accountretweets : no. of retweets of the tweetfav_count : count of users who favourited the tweetfollowers_count : no. of followers of the corresponding the twitterverified : checks whether the user is a verified account or not<jupyter_code>import pymongo 
from pymongo import MongoClient
from tweepy.streaming import StreamListener
from tweepy import OAuthHandler
from tweepy import Stream
import pandas as pd
import numpy as np
game_inventory = pd.read_csv('game_inventory.csv', index_col=0)
game_inventory.head(20)
genre = pd.read_csv('genre.csv',index_col=0)
genre.head()
developer = pd.read_csv('developer.csv', index_col=0)
developer.head()
publisher = pd.read_csv('publisher.csv', index_col=0)
publisher.head()
platform = pd.read_csv('platform.csv', index_col = 0)
platform.head()
game_inventory_mongo = pd.read_csv('game_inventory_mongo.csv',index_col = 0)
game_inventory_mongo.head() # This will be inserted into GameInventory collection in mongoDb<jupyter_output><empty_output><jupyter_text>To test that we are able to connect to our MongoDB cluster and database<jupyter_code># MongoDB atlas connection
client = pymongo.MongoClient('mongodb://ashwinjohn3:pymongo@gameinventory-shard-00-00-rczre.mongodb.net:27017,gameinventory-shard-00-01-rczre.mongodb.net:27017,gameinventory-shard-00-02-rczre.mongodb.net:27017/test?ssl=true&replicaSet=GameInventory-shard-0&authSource=admin&retryWrites=true&w=majority') # Add your short string and password as detailed in the section above
db = client.GameInventory # Creates a databse called GameInventory
print(db)
#Drop the twitter collection
try:
    result = db.twitter.drop()
    print (" Twitter collection dropped")
except:
    pass
collections = db.twitter # Creates a collection called twitter
print(collections)<jupyter_output>Database(MongoClient(host=['gameinventory-shard-00-01-rczre.mongodb.net:27017', 'gameinventory-shard-00-00-rczre.mongodb.net:27017', 'gameinventory-shard-00-02-rczre.mongodb.net:27017'], document_class=dict, tz_aware=False, connect=True, ssl=True, replicaset='GameInventory-shard-0', authsource='admin', retrywrites=True, w='majority'), 'GameInventory')
 Twitter collection dropped
Collection(Database(MongoClient(host=['gameinventory-shard-00-01-rczre.mongodb.net:27017', 'gameinventory-shard-00-00-rczre.mongodb.net:27017', 'gameinventory-shard-00-02-rczre.mongodb.net:27017'], document_class=dict, tz_aware=False, connect=True, ssl=True, replicaset='GameInventory-shard-0', authsource='admin', retrywrites=True, w='majority'), 'GameInventory'), 'twitter')<jupyter_text>Design Explanation:If it were a SQL database we could have stored the values into the database using the relational tables and replicate the orignal database using JOIN queries.Since we want to create a NoSQL database, we want to store the data in form of documments. Before we dig depper into how to create them, let us take a look at what documents and collections actully mean in terms of database designs. Collections:Collections in NoSQL are analogus to the Tables in the SQL databases. Insert, Update and Delete operations can be performed on a collection. Documents:Each record of a master table in Relational database can be considered as a document here. For example in the game inventory database we have data for each movie and we store all the other tables in relation with the key givven in the game inventory tables. In the NoSQL database, each game will be a stand alone doccument on its own. Creation of the documents:The below code is used for converting our tables into documents which will further be stored in a collection. We use nested for loops to store all the data related to a particular game in a single document. We have stored the game_inventory table from the Assignment 2 to collection named Game_Inventory, and we will be scrapping the tweets for the game Final Fantasy VII game into another collection called Twitter to get the corresponding tweets.<jupyter_code>consumer_key = 'e6a6AL8uzdrq0rk8gAUpkf9Am'
consumer_secret = '<KEY>'
access_token = '<KEY>'
access_token_secret = '<KEY>'
class MyStreamListener(StreamListener):
    """ A listener handles tweets that are received from the stream.
    This is a basic listener that just prints received tweets to stdout.
    """    
    def on_status(self, status):
        """When raw data is received from Twitter server, it is inserted into the MongoDB collection
        Args:
          status: Raw data from Twitter server
        """

        id_str = status.id_str
        created = status.created_at
        text = status.text
        fav = status.favorite_count
        name = status.user.screen_name
        description = status.user.description
        loc = status.user.location
        user_created = status.user.created_at
        no_of_retweets = status.retweet_count
        followers_count = status.user.followers_count
        verified = status.user.verified
        hashtag = status.entities['hashtags']
        # Load the data to MongoDB if its not a retweet
        if not 'RT @' in status.text :
            collections.insert_one({
                "_id": id_str,
                "G_ID": int(15), 
                "tweet_text": text,
                "tweet_created": created,
                "tweet_loc": loc,
                "user_name": name,
                "user_description" : description,
                "retweets" : no_of_retweets,
                'fav_count': fav,
                'followers_count': followers_count,
                'verified': verified,
                'tags': hashtag
            })
# 15 - game_id of Final Fantasy VII
    def on_error(self, status_code):
        """When error status code is received from Twitter server
        Args:
            status_code: Error code from Tiwtter
        Returns:
            False: If status code is 420 (when app is rate limited by twitter server)
            Nothing: All other status codes
        """

        print('error')
        if status_code == 420:
            print('encountered error')
            #returning False in on_data disconnects the stream
            return False
        
if __name__ == '__main__':
    """Main part of the code where the streamlistener is created and run"""

    myStreamListener = MyStreamListener()
    auth = OAuthHandler(consumer_key, consumer_secret)
    auth.set_access_token(access_token, access_token_secret)
    myStream = Stream(auth, myStreamListener)
    myStream.filter(track=['Final Fantasy VII', 'final fantasy 7','#ff7r','#FF7R','finalfantasy7'])<jupyter_output><empty_output><jupyter_text>Importing CSV files into mongoDB into gameDetails collection<jupyter_code>record_ = game_inventory_mongo.to_dict(orient = 'records')
gameinvent = db.Game_Inventory
result = gameinvent.insert_many(record_)<jupyter_output><empty_output><jupyter_text>Answering the questions  1. What are tags associated with a person, place or things?Here, we used 'Final Fantasy VII', 'final fantasy 7','ff7r','FF7R','finalfantasy7' as tags to scrap the tweets for the game 'Final Fantasy VII'.Tags associated with a game  can be related to name, genre and platform too. Tags used: 'Final Fantasy VII', 'final fantasy 7','ff7r','FF7R','finalfantasy7'<jupyter_code>tags = pd.DataFrame(columns=['Name','Genre','Platform'])
i=0
for doc in db.Game_Inventory.find():
    tags.loc[i] = [doc.get('G_NAME'),doc.get('GENRE'), doc.get('PLATFORM')]
    i += 1
tags<jupyter_output><empty_output><jupyter_text>2. What social media users are like other social media users in your domain?We can search for users where the locations are same.<jupyter_code>col = ['user_id','user_name','user_description','location','followers_count','verified']
location = pd.DataFrame(columns = col)
i=0
for tweet in db.twitter.find():
    location.loc[i] = [tweet.get('_id'),tweet.get('user_name'),tweet.get('user_description'),tweet.get('tweet_loc'),tweet.get('followers_count'),tweet.get('verified')]
    i += 1
loc_final = location.groupby('location')['location'].agg('count').sort_values(ascending = False)
loc_df = pd.DataFrame({'location':loc_final.index,'count':loc_final.values})
loc_df.head(10)<jupyter_output><empty_output><jupyter_text>From the above table we can see that almost 17 of the users have put Nibelheim as their tweeting location. It's pretty interesting since Nibelheim is a fictional city in Final Fantasy VII. This can show that most of the users who have tweeted about Final Fantasy VII are fans of the game.<jupyter_code>location.loc[location['verified'] == True].sort_values(by = 'followers_count',ascending = False)<jupyter_output><empty_output><jupyter_text>From the above table we can see that there are 8 twitter users where their accounts are verified. Verified accounts shows that accounts of public interest are authentic.  3. What people, places or things are popular in your domain?We can check the no. of followers count of each users to determine the popular people in our domain, as well as to check the no. of<jupyter_code>import pprint
flag = 0
col = ['user_name','followers_count']
foll = pd.DataFrame(columns = col)
i=0
for tweet in db.twitter.find():
    foll.loc[i] = [tweet.get('user_name'),tweet.get('followers_count')]
    i=i+1
foll = foll.sort_values(by ='followers_count',ascending = False)
# Showing the the top 5 popular users by followers count
foll.head(5)<jupyter_output><empty_output><jupyter_text>4. What people, places or things are trending in your domain? We can check the trending tweets of twitter by calculating various tweets with an average of favourites count and retweets count.<jupyter_code>trends = pd.DataFrame(columns=['user_id','user_name','favourites_count','retweets_count','followers_count'])
i=0
for tweet in db.twitter.find():
    trends.loc[i] = [tweet.get('_id'),tweet.get('user_name'),tweet.get('fav_count'),tweet.get('retweets'),tweet.get('followers_count')]
    i += 1
trnd_fav = trends.groupby('favourites_count')['favourites_count'].agg('count').sort_values(ascending = False)
trnd_retweets = trends.groupby('retweets_count')['retweets_count'].agg('count').sort_values(ascending = False)
trnd_fav_df = pd.DataFrame({"fav_count":trnd_fav.index, "count":trnd_fav.values})
trnd_retweets_df = pd.DataFrame({'retweet_count':trnd_retweets.index,'count':trnd_retweets.values})
trnd_fav_df
trnd_retweets_df<jupyter_output><empty_output><jupyter_text>From the above data, we can see that all the tweets that we have scrapped only have 0 favourite count and 0 retweet count. Since we scrapped tweets that were created recently, that might be one reason why the tweets have 0 favourite count and 0 retweet. Instead of using favourites count and retweet count, we can count the no. of followers a user has to show that since they have a large no. of followers their post will reach a lot of audience.<jupyter_code>foll.head(5) # People
loc_df.head() # Location<jupyter_output><empty_output><jupyter_text>Printing the collection twitter using pprint<jupyter_code>for doc in db.twitter.find():
    pprint.pprint(doc)<jupyter_output>{'G_ID': 15,
 '_id': '1246909930600386561',
 'fav_count': 0,
 'followers_count': 34,
 'retweets': 0,
 'tweet_created': datetime.datetime(2020, 4, 5, 21, 17, 48),
 'tweet_loc': None,
 'tweet_text': '@ShutUpPabs @Tidux Absolutely. And fair, published despite '
               'being vetoed by Koch Media for scoring Final Fantasy XV… '
               'https://t.co/K1fnba4Apq',
 'user_description': 'Chica de San Sebastian viviendo en Madrid. Amante de los '
                     'videojuegos y sobre todo de Lara Croft y su Tomb Raider '
                     'y de la saga Mortal Kombat',
 'user_name': 'EdeniaKombat',
 'verified': False}
{'G_ID': 15,
 '_id': '1246910086762483712',
 'fav_count': 0,
 'followers_count': 190,
 'retweets': 0,
 'tweet_created': datetime.datetime(2020, 4, 5, 21, 18, 25),
 'tweet_loc': '一緒に行こう次のステージへ',
 'tweet_text': 'さとみくん⇢4/10\n'
               'FINAL FANTASY VII REMAKE\n'
               'クリアするまで寝れまてんするかもだって♡♥( ᐢ. ̫ .ᐢ )',
 'user_description': '【@satoniya_ 】空リプ➩#ででん[...]<jupyter_text>Printing the collection Game_Inventory using pprint<jupyter_code>for doc in db.Game_Inventory.find():
    pprint.pprint(doc)<jupyter_output>{'CRITIC_SCORE': 8.2,
 'DEVELOPER': 'Nintendo EAD',
 'GENRE': 'Racing',
 'G_ID': 1,
 'G_NAME': '<NAME>',
 'PLATFORM': 'Wii',
 'TOTAL_SHIPPED': 37.14,
 'USER_SCORE': 9.1,
 'YEAR': 2008,
 '_id': ObjectId('5e8abfd4bdab9db39ac4f7a6')}
{'CRITIC_SCORE': 8.0,
 'DEVELOPER': 'Nintendo EAD',
 'GENRE': 'Sports',
 'G_ID': 2,
 'G_NAME': '<NAME>',
 'PLATFORM': 'Wii',
 'TOTAL_SHIPPED': 33.09,
 'USER_SCORE': 8.8,
 'YEAR': 2009,
 '_id': ObjectId('5e8abfd4bdab9db39ac4f7a7')}
{'CRITIC_SCORE': 9.1,
 'DEVELOPER': 'Nintendo EAD',
 'GENRE': 'Platform',
 'G_ID': 3,
 'G_NAME': '<NAME>.',
 'PLATFORM': 'DS',
 'TOTAL_SHIPPED': 30.8,
 'USER_SCORE': 8.1,
 'YEAR': 2006,
 '_id': ObjectId('5e8abfd4bdab9db39ac4f7a8')}
{'CRITIC_SCORE': 8.6,
 'DEVELOPER': 'Nintendo EAD',
 'GENRE': 'Platform',
 'G_ID': 4,
 'G_NAME': '<NAME>. Wii',
 'PLATFORM': 'Wii',
 'TOTAL_SHIPPED': 30.22,
 'USER_SCORE': 9.2,
 'YEAR': 2009,
 '_id': ObjectId('5e8abfd4bdab9db39ac4f7a9')}
{'CRITIC_SCORE': 5.9,[...]
{'fraction_non_alphanumeric': 0.10282882510840388, 'fraction_numerical': 0.02181843210131461, 'mean_word_length': 4.474755086661643, 'pattern_counts': {'":': 8, '<': 53, '<?xml version=': 0, '>': 53, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 5, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '470902', 'license': 'MIT', 'n_tokens_mistral': 4873, 'n_tokens_neox': 4536, 'n_words': 1429, 'path': 'Assignment 3 .ipynb', 'repo_name': 'ashwinjohn3/Assignment-3', 'size': '14706'}