<jupyter_start><jupyter_text>Importing a csv file which contains imdb id<jupyter_code>import csv
import pandas as pd
with open("D:\\Msc Data Analytics\\GitHub Repo\\video-streaming-data-analysis\\data\\Netflix\\imdb.csv") as csv_file_imdb:
    csv_reader = pd.read_csv(csv_file_imdb, delimiter = ",")
csv_reader
len(csv_reader["imdbid"].isnull())<jupyter_output><empty_output><jupyter_text>Fetching data from api using imdb id taken from the csv file<jupyter_code>csv_reader.dropna(subset = ["imdbid"], inplace = True)
import requests
import pymongo
myclient = pymongo.MongoClient("localhost:27017")

def imdbapi():
    global myclient
    for count, itemId in enumerate(csv_reader["imdbid"]):
        #myclient = pymongo.MongoClient("localhost:27017")
        mydb = myclient["ImdbData"]
        mycol = mydb["imdb_data"]


        url = "https://movie-database-imdb-alternative.p.rapidapi.com/"

        querystring = {"i":itemId,"r":"json"}

        headers = {
            'x-rapidapi-host': "movie-database-imdb-alternative.p.rapidapi.com",
            'x-rapidapi-key': "<KEY>"
            }

        response = requests.request("GET", url, headers=headers, params=querystring)
        tempJson = response.json()
        tempJson["_id"] = count
        mycol.insert_one(tempJson)

imdbapi()
# converting the mongodb data into a dataframe
def jsontodf():
    mydb = myclient["ImdbData"]
    mycol = mydb["imdb_data"]
    imdb = pd.DataFrame(list(mycol.find()))
    imdb
    del imdb["_id"]
    del imdb["Ratings"] # we are getting ratings double times so we are going to delete this 
    return imdb
    
imdb = jsontodf()
#imdb<jupyter_output><empty_output><jupyter_text>Postgresql Installation<jupyter_code>import psycopg2
from sqlalchemy import create_engine
import io
def connpostgre():
    try:
        dbConnection = psycopg2.connect(
            password = "<PASSWORD>",
            host = "localhost",
            port = "5432",
            user = "postgres")

    except (Exception , psycopg2.Error) as dbError :
        print("Error while connecting to PostgreSQL", dbError)

    else:
        dbConnection.set_isolation_level(0)
        dbCursor = dbConnection.cursor()
        dbCursor.execute('CREATE DATABASE imdb_data;')
        dbCursor.close()
        print("Connection established")

    finally:
        if(dbConnection): 
            dbConnection.close()
connpostgre()
# DF To postgresql
def insertintopostgre():
    engine = create_engine('postgresql+psycopg2://postgres:1234@localhost:5432/imdb_data')
    imdb.to_sql("imdbdata",engine, if_exists='replace', index = False)
insertintopostgre()
import pandas as pd
import pandas.io.sql as sqlio
def fetchfrompostgre():
    sql="""SELECT * FROM imdbdata;"""
    try:
        dbConnection = psycopg2.connect(
            password = "<PASSWORD>",
            host = "localhost",
            port = "5432",
            user = "postgres",
            database="imdb_data")
        imdb_dataframe = sqlio.read_sql_query(sql, dbConnection)
    except (Exception , psycopg2.Error) as dbError :
        print ("Error", dbError)
    finally:
        if(dbConnection): dbConnection.close()
        return imdb_dataframe
imdb_dataframe = fetchfrompostgre()
new = imdb_dataframe
new<jupyter_output><empty_output><jupyter_text>Data PreprocessingFirst step in this process is removing null values from the dataframe here DVD, Website, Production, BoxOffice all have null values so we would be removin null values<jupyter_code>#data.isnull().sum() # this tells us how many null values are there in the particular column in the dataframe
# DVD,, Website, Production, BoxOffice all have 129 Null values
new.isnull().sum()
#data.isnull().sum() # this tells us how many null values are there in the particular column in the dataframe
# DVD,, Website, Production, BoxOffice all have 129 Null values<jupyter_output><empty_output><jupyter_text>Production Column<jupyter_code># Replacing N/A Value With Unknown in Production
new['Production']=new.Production.replace({'N/A':'Unknown'},  regex=True)
new.Production.unique
new['Production'].value_counts()
new<jupyter_output><empty_output><jupyter_text>imdbVotes Column<jupyter_code>import numpy as np
from numpy import nan as NA
new.imdbVotes.unique()
new['imdbVotes']=new.imdbVotes.replace({'N/A': NA},  regex=True)

new.imdbVotes.unique()
# new['imdbVotes']=new.imdbVotes.replace({',': ''},  regex=True)
# new["imdbVotes"] = new["imdbVotes"].fillna(new["imdbVotes"].median())
# new['imdbVotes'].median()
new['imdbVotes']=new.imdbVotes.replace({',': ''},  regex=True)
# new["imdbVotes"] = new["imdbVotes"].astype(int)
new.imdbVotes.unique()
new["imdbVotes"] = new["imdbVotes"].fillna(new["imdbVotes"].median())<jupyter_output><empty_output><jupyter_text>Year Column<jupyter_code>new["Year"].replace({"2019–": "2019","2017–":"2017","2018–2019":"2018"}, inplace=True)
new.Year.unique()
new["Year"].replace({"2016–": "2016","2018–":"2018","2017–2019":"2018"}, inplace=True)
new.Year.unique()
new["Year"].replace({"2009–": "2009"}, inplace=True)
new.Year.unique()
new["Year"] = new["Year"].astype(int)
import matplotlib.pyplot as plt
plt.hist(new['Year'], color = 'blue', edgecolor = 'black',
         bins = 10)
plt.xlabel ("Years")
plt.ylabel ("No of Movies ")
plt.show()<jupyter_output><empty_output><jupyter_text>Runtime Column<jupyter_code># Replacing min Values with Blank for Runtime Column
new['Runtime']=new.Runtime.replace({'min':''},  regex=True)<jupyter_output><empty_output><jupyter_text>imdbRating Column<jupyter_code>new.imdbRating.unique()
import numpy as np
from numpy import nan as NA

new['imdbRating']=new.imdbRating.replace({'N/A': NA},  regex=True)
new.imdbRating.unique()
new["imdbRating"] = new["imdbRating"].fillna(new["imdbRating"].median())
new["imdbRating"] = new["imdbRating"].astype(float)<jupyter_output><empty_output><jupyter_text>Counting how many occurences each genre has<jupyter_code>new.Genre.unique()
new['Genre'].value_counts()
# Replacing N/A Value With Unknown in Genre
new['Genre']=new.Genre.replace({'N/A':'Unknown'},  regex=True)
new['Genre'].value_counts()
#Type	totalSeasons	Response	DVD	BoxOffice
new['Type'].value_counts()
# The Column is not that significant so we are going to delete the column
# del new['Response']<jupyter_output><empty_output><jupyter_text>Visualisations Using Matplotlib<jupyter_code>import matplotlib.pyplot as plt
## Histogram for Type Category
values = new['Type']
plt.hist(values, bins = 2)
plt.show()
new.sort_values('Runtime')
new['Runtime']=new.Runtime.replace({'N/A': NA},  regex=True)
# new["Runtime"] = new["Runtime"].fillna(new["Runtime"].median())
# new['Runtime'].value_counts()

new['imdbRating'].plot(kind='hist', bins=10)

year = new[['Year','Title']].groupby('Year').count()
year.nlargest(10,'Title')
new['Rated'].value_counts()
tr = new[['Rated','Title']].groupby('Rated').count()
#print(tr)
tr_new = tr.drop('N/A')
print(tr_new)
Rating = tr_new.nlargest(10,'Title')
Rating.plot(kind='area', title='Content Rating Visualization')
plt.xlabel('Content Rating')
plt.ylabel('Title Count')
genre = new[['Genre','Title']].groupby('Genre').count()
# genre.plot(kind='bar', title='Content Rating Visualization')
genrenew = genre.nlargest(10,'Title')
genrenew.plot(kind='bar', title='Genre Occurences')
plt.xlabel('Genre')
plt.ylabel('Title Count')
plt.show()
import squarify
unique_values_state =  np.unique(genrenew['Title'])
st = genrenew['Title'].value_counts()
squarify.plot(sizes=st,label=unique_values_state, alpha=0.7)
plt.show()
genrenew
production = new[['Production','Title']].groupby('Production').count()
prodnew = production.drop('Unknown')
tableprod = prodnew.nlargest(10,'Title')
tableprod.plot(kind='bar', title='Production Houses vs Title Count')
plt.xlabel('Production Houses')
plt.ylabel('Title Count')
plt.show()
pip install wordcloud
from wordcloud import WordCloud, STOPWORDS, ImageColorGenerator
plt.rcParams['figure.figsize'] = (10, 10)
wordcloud = WordCloud(stopwords=STOPWORDS,background_color = 'white', width = 1000,  height = 1000, max_words = 121).generate(' '.join(new['Genre']))
plt.imshow(wordcloud)
plt.axis('off')
plt.title('Most Popular Genre',fontsize = 40)
plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1544081831466147, 'fraction_numerical': 0.016439357038480273, 'mean_word_length': 5.351894818252127, 'pattern_counts': {'":': 9, '<': 48, '<?xml version=': 0, '>': 48, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '555364', 'license': 'MIT', 'n_tokens_mistral': 2935, 'n_tokens_neox': 2742, 'n_words': 558, 'path': 'src/IMDb_Api_analysis.ipynb', 'repo_name': 'NCIRL-Dublin/mining-youtube-data', 'size': '8245'}