<jupyter_start><jupyter_text>Module 1 Project <NAME>Part TimeInstructor: <NAME> URL: https://akwon100.github.io/module1_project_ds-pt<jupyter_code>'''
Organization:
We split each cell with the following labels:
1. Import
2. Webscraping
3. Making Dataframe
4. Cleaning Dataframe
5. Organizing data
6. Visualization
For each label we add a small description if necessary

For this project we need not use databases HOWEVER, they can be used and is often a good idea to use when
handling large amounts of data. Hence at the end after all questions are anwered we show how to create a table 
and insert the information scraped for those who want to use databases. The scraped data will have a (*) pairing 
with the cells at the end 

Note: This is the final version of the code, hence none of the cells will be run
as they have already been run and tested in a draft version
'''
#Import
from bs4 import BeautifulSoup
import requests
import urllib.parse
import json
#WEBSCRAPING: FUNCTIONS IMDB VIA API 
#Getting movies by title 

# Constants
API_KEY = '<KEY>
API_URL_SEARCH_MOVIE = 'https://imdb-api.com/en/API/SearchMovie/' + API_KEY
API_URL_SEARCH_TITLE = 'https://imdb-api.com/en/API/Title/' + API_KEY

# Logging
movie_number = 0
found_id_count = 0
id_not_found = 0
movie_not_found = 0
total_added = 0


def fetchMovieByTitle(movie_title, movie_year, keys, catch):
    '''
    Parameters: movie_title: <str> string to find movie id  
                movie_year: <str or int> string or int to find movie id 
                keys: <list or tuple> list of keys of dictionary of response object
                catch: <list or tuple> an empty list to catch results
    Returns: response object and appends to catch
    '''
    encoded_movie_title = urllib.parse.quote(str(movie_title), safe='/', encoding=None, errors=None)
    url = API_URL_SEARCH_MOVIE + encoded_movie_title

    response = requests.request("GET", url)

    if response.status_code != 200:
        print(response.status_code, 'cannot find movie:', movie_title)

        # Logging
        global id_not_found
        id_not_found = id_not_found + 1
        return False
    else:
        print(response.status_code, 'movie found', movie_title)
        data = json.loads(response.text)
        getMovieIdFromResults(data.get('results'), movie_title, movie_year, keys, catch)

        # Logging
        global found_id_count
        found_id_count = found_id_count + 1
        return True

def getMovieIdFromResults(api_result, movie_title, movie_year, keys, catch):
    '''
    Parameters: api_result:<dict> response obj
                movie_title: <str> string to find movie id  
                movie_year: <str or int> string or int to find movie id 
                keys: <list or tuple> list of keys of dictionary of response object
                catch: <list or tuple> an empty list to catch results
    Returns: response object and appends to catch
    '''
    for result in api_result:
        if type(result) is dict:
            get_title = result.get('title')
            get_year = result.get('description')
            get_id = result.get('id')

            if str(movie_title) in str(get_title) and str(movie_year) in str(get_year):
                fetchMovieDataFromId(get_id, keys, catch)
                return True
            else:
                print('the movie', str(movie_title), 'was not found')

                # Logging
                global movie_not_found
                movie_not_found = movie_not_found + 1
                return True
        else:
            print('the result is not a dict')
            return True


def fetchMovieDataFromId(movie_id, keys, catch):
    '''
    Parameters: movie_id: <str> imdb movie id
                keys: <list or tuple> list of keys of dictionary of response object
                catch: <list or tuple> an empty list to catch results
    Returns: response object and appedns to catch
    '''
    global total_added
    #all data is present
    if total_added >= 2000:
        return True

    url = API_URL_SEARCH_TITLE + movie_id
    response = requests.request("GET", url)

    if response.status_code != 200:
        print(response.status_code, 'cannot find movie for id:', movie_id)
        return False
    else:
        results = json.loads(response.text)
        log_results = []
        result = [movie_id] + [results.get(key) for key in keys if results.get(key) != '']
        catch.append(tuple(result))
        log_results.append(result)    

        # Logging
        total_added = total_added + 1
        print('appending', log_results, total_added)
        
#WEBSCRAPING: Functions scraping IMDB via beautiful soup

#Letting sites know we are a browser
def iAmBrowser(url):
    headers = requests.utils.default_headers()
    headers.update({'User-Agent': 'Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:52.0) Gecko/20100101 Firefox/52.0',})
    page = requests.get(url, headers=headers)
    soup = BeautifulSoup(page.content ,'lxml')
    return soup

#making dictionary to create dataframe with metascore, metacritic, maturity rating, imdb rating, num of imdb votes 
def making_list(movie_id, catch):
    '''
    Parameters: movie_id: <str> imdb movie id
                catch:<list or tuple> empty list to catch result
    Returns: <list> catch
    '''
    metascore = retrieveMetascore(movie_id)[0]
    metacritic = retrieveMetascore(movie_id)[1]
    mrating = retrieveMaturityRating(movie_id)
    imdbScore = retrieveImdbrating(movie_id)[0]
    imdbVotes = retrieveImdbrating(movie_id)[1]
    result = (movie_id, metascore, metacritic, mrating, imdbScore, imdbVotes)
    catch.append(result)
    return catch

#retrieving metascore for each movie 
def retrieveMetascore(movie_id):
    '''
    Parameters: movie_id: <str> string to get url for movie
    Returns: metascore:<str> metascore of movie and number of metacritics
    '''
    url = 'https://www.imdb.com/title/' + movie_id + '/criticreviews?ref_=tt_ov_rt'
    soup = iAmBrowser(url)
    
    metascore = soup.find('span', {'itemprop': 'ratingValue'})
    if metascore:
        metascore = metascore.text.strip()
        #print('appending metascore:' + metascore)
    
    else:
        metascore = 'None'
        #print('Not found')
    
    metacritics = soup.find('span', {'itemprop': 'ratingCount'})
    if metacritics:
        metacritics = metacritics.text.strip()
        #print('appending metacritic:' + metacritics)
    else:
        metacritics = 'None'
        #print('Not found')
    
    return metascore, metacritics

#retrieving maturity rating for each movie
def retrieveMaturityRating(movie_id):
    '''
    Parameters: movie_id:<str> movie id
    Returns: <str> maturity rating
    '''
    url = 'https://www.imdb.com/title/' + movie_id + '/parentalguide?ref_=tt_stry_pg'
    soup = iAmBrowser(url)
    
    MaturityRating = soup.find(id='mpaa-rating')
    
    if MaturityRating:
        MaturityRating = MaturityRating.find_all("td")[1].string.split()[:2]
        #print('appending Mrating')
        
    else:
        MaturityRating = 'None'
        #print('Not found')
        
    return listToString(MaturityRating)

#retrieving Imdb rating and number of votes for each movie 
def retrieveImdbrating(movie_id):
    '''
    Parameters: movie_id:<str> movie id
    Returns: <str> imdb score and number of imdb voters
    '''
    url = "https://www.imdb.com/title/" + movie_id + "/ratings"
    soup = iAmBrowser(url)
    
    numVotes = soup.find('div',{'class': 'allText'})
    if numVotes:
        numVotes = numVotes.text.strip()
        #print('appending numVotes')
        
    else:
        numVotes = 'None'
        #print('Not found')
        
    rating = soup.find('div',{'class': 'allText'})
    if rating:
        rating = rating.text.strip()
        #print('appending rating')
    
    else:
        rating = 'None'
        #print('Not found')
    
    return numVotes.split()[0], listToString(rating.split()[-3:])

def listToString(s):   
    str1 = ""   
    for item in s:  
        str1 += ' ' + item     
    return str1

#retrieving imdb movie id for each movie from top 100 of each year
def top_100(url, catch):
    '''
    Parameters: url: <url> url to request
                catch: <list or tuple> empty list or tuple to catch results
    Returns: appends movie ids to catch
    '''
    soup = iAmBrowser(url)
    Movie = soup.find('div', attrs = {'class': 'lister list detail sub-list'})
    Movie = Movie.find('div', attrs = {'class' : 'lister-list'})
    items = Movie.find_all('div', class_= 'lister-item mode-detail')
    
    for item in items:
        movie_id = item.find('div', attrs={'class': 'lister-item-image ribbonize'})
        movie_id = str(item)
        for strings in str(movie_id).split():
            if 'data-tconst' in strings:
                movie_id = strings.split('=')
                movie_id = movie_id[1].split('>')
                movie_id = movie_id[0].strip('\"')
                catch.append(movie_id)
    #catch = list(dict.fromkeys(catch))
    #return catch
#WEBSCRAPING: functions scraping IMDB via beautiful soup

#function which retrieves votes 
def retrieveVotes(movie_id):
    '''
    Parameters: movie_id:<str> movie id
    Returns: <list> list of number of votes 
    '''
    url = "https://www.imdb.com/title/" + movie_id + "/ratings"
    soup = iAmBrowser(url)
    
    table = soup.find('table')
    
    if not table:
        votes_list.append((movie_id , 0, 0, 0, 0, 0, 0, 0, 0, 0, 0))
    
    result =[]
    rows = table.find_all('tr')    
    for tr in rows:
        votes = tr.find('div',{ 'class':'leftAligned'}).text.strip()
        if votes != 'Votes':
            votes = int(votes.replace(',', ''))
            result.append(votes)
            print('appending Votes')
        
    reverse_votes = result[::-1]
    return reverse_votes

#function which makes the tuple of votes we've found 
def makeVotesTuple(movie_id, title, catch):
    '''
    Parameters: movie_id:<str> movie id 
                title:<str> title of movie
                catch:<list> empty list to catch results
    Returns: appends results of retrieveVotes() to list catch
    '''
    catch.append((movie_id, title) + tuple([i for i in retrieveVotes(movie_id)]))
'''
Procedure (A):
1. We will first scrape the movie ids of 100 movies sorted by popularity for each year for the past ten years
2. We then scrape using API: directors, genres, titles, boxoffice, year
3. We then scrape using beautiful soup: metascore, imdb-score, maturity rating
4. We then scrap using beautiful soup: imdb vote chart from 1-10
'''
#WEBSCRAPING: we are going to scrape the movie ids (see procedure(A) 1)
#top 100 movies sorted by popularity for each year for past ten years 
url_2019 = 'https://www.imdb.com/list/ls041214362/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2018 = 'https://www.imdb.com/list/ls047677021/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2017 = 'https://www.imdb.com/list/ls023426386/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2016 = 'https://www.imdb.com/list/ls063924870/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2015 = 'https://www.imdb.com/list/ls073386152/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2014 = 'https://www.imdb.com/list/ls058177122/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2013 = 'https://www.imdb.com/list/ls053040009/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2012 = 'https://www.imdb.com/list/ls006206951/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2011 = 'https://www.imdb.com/list/ls000463584/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'
#url_2010 = 'https://www.imdb.com/list/ls009654807/?sort=moviemeter,asc&st_dt=&mode=detail&page=1'

top_100_2019 = []
#top_100_2018 = []
#top_100_2017 = []
#top_100_2016 = []
#top_100_2015 = []
#top_100_2014 = []
#top_100_2013 = []
#top_100_2012 = []
#top_100_2011 = []
#top_100_2010 = []
top_100(url_2019, top_100_2019)  
#top_100(url_2018, top_100_2018)  
#top_100(url_2017, top_100_2017)  
#top_100(url_2016, top_100_2016)  
#top_100(url_2015, top_100_2015)  
#top_100(url_2014, top_100_2014)  
#top_100(url_2013, top_100_2013)  
#top_100(url_2012, top_100_2012)  
#top_100(url_2011, top_100_2011)  
#top_100(url_2010, top_100_2010)  

#(*) see cell 1 at the end 
#WEBSCRAPING: we will use the combined ids obtained to retrieve data (see procedure(A) 2) 
#top_100_10yrs = top_100_2019+top_100_2018+top_100_2017+top_100_2016+top_100_2015+top_100_2014+top_100_2013+top_100_2012+top_100_2011+top_100_2010
top100_10yrs = []
keys = ['title', 'genres', 'directors', 'year', 'boxOffice']
for movie_id in list(set(top_100_2019))[:10]:
    fetchMovieDataFromId(movie_id, keys, top100_10yrs)
#top100_10yrs
#(*) see cell 2 at the end
#WEBSCRAPING: we will use the combined ids obtained to retrieve data (see procedure(A) 3)
top100_10yrs2 = []
for movie_id in list(set(top_100_2019))[:10]:
    making_list(movie_id, top100_10yrs2)
#top100_10yrs2
import pandas as pd
#(*) see cell 1 at the end 
#MAKING DATAFRAME: Before proceeding to procedure 4 we will make a dataframe called all.csv (see prcedure(B) 1)

#separate boxoffice which is a dictionary with the rest of data
CONFIG = {'MOVIE_INFO_COLUMN_NAMES' : ['ids', 'titles', 'genres', 'directors', 'year'],
          'MOVIE_RATING_COLUMN_NAMES' : ['ids', 'metascore', 'metacritics', 'mrating', 'imdbvotes','imdbScore'],
          'VOTES_COLUMN_NAMES' : ['ids', 'title','one', 'two', 'three','four', 'five', 'six', 'seven', 'eight', 'nine', 'ten']
         }

boxOffice = [x[5] for x in top100_10yrs]
listToDf = [(x[0],x[1],x[2],x[3], x[4]) for x in top100_10yrs]

#make each into a dataframe
listToDf_df = pd.DataFrame(listToDf, columns = CONFIG.get('MOVIE_INFO_COLUMN_NAMES'))
boxoffice_df = pd.DataFrame(boxOffice)

#concatenate the two dataframes
movie_info_df = pd.concat([listToDf_df, boxoffice_df], axis =1, join = 'inner')

#make the dataframe of the retrieved ratings 
movie_ratings_df = pd.DataFrame(top100_10yrs2, columns = CONFIG.get('MOVIE_RATING_COLUMN_NAMES'))

#merge the two dataframes on ids
all_df = movie_info_df.merge(movie_ratings_df, on = 'ids')

#save this dataframe as all.csv
#all_df.to_csv(r'<insert path here>/all.csv', mode='w', header=True, index=False)
all_df
#WEBSCRAPING: we will use the combined ids obtained to retrieve data (see procedure(A) 4)
votes_list = []
list_movie_id = all_df['ids']
list_titles = all_df['titles']
for movie_id, title in zip(list_movie_id, list_titles):
    makeVotesTuple(movie_id, title, votes_list)
#votes_list
'''
Procedure(B):
1. We make a dataframe called all_df and save as all.csv which includes data from procedure(A)1,2,3
2. We make a dataframe called votes_df and save as votes.csv which includes data from procedure(A)4
'''
#MAKING DATAFRAME: making dataframe votes_df and saving as votes.csv (see procedure(B)2)
votes_df = pd.DataFrame(votes_list, columns = CONFIG.get('VOTES_COLUMN_NAMES'))

#save dataframe
#votes_df.to_csv(r'<insert path here>/votes.csv', mode='w', header=True, index=False)
votes_df<jupyter_output><empty_output><jupyter_text>Question 1: What is the 'best' genres and maturity rating pair?<jupyter_code>'''
Procedure(C):
We want to clean data according to how we want to answer our first question
1.  a)We organize to see if there are any genres which trend by counting genre for each year. 
    b)Visualize using a bar graph for each year see if there are any trends in genre.
    c)Save image as All_graphs.
    d) this will determine which combination will be a good combination for genres

2.  a)We calculate average profit for each genre and maturity rating pair.
    b)Visualize using bar graph for profit vs genre,maturity rating pair.
    c)Save image as Genre_profit.

3.  a)We calculate average metascore and imdb score for each genre and maturity rating pair.
    b)Visualize using bargraph for score vs genre,maturity rating pair.
    c)Save image as Genre_score.

4.  a)We calculate average skewness for each genre and maturty rating pair.
    b)Visualize using bargraph for skewness vs genre,maturity rating pair.
    c)Save image as Genre_skew

We take the average since there are no major outliers from observation. 
'''
#ORGANIZING DATA: function which counts genre per year (see procedure(C)1)
genre_dict = {'Action':0, 'Adult':0, 'Adventure':0, 'Animation':0,'Biography':0,
              'Comedy':0, 'Crime':0, 'Documentary':0, 'Drama':0, 'Family':0, 'Fantasy':0,
              'Film_Noir':0, 'Game_Show':0, 'History':0, 'Horror':0, 'Musical':0, 'Music':0, 
              'Mystery':0,'News':0, 'Reality_TV':0, 'Romance':0, 'Sci_Fi':0, 'Short':0, 'Sport':0, 
              'Talk_Show':0,'Thriller':0, 'War':0, 'Western':0}

def counting_genre(value):
    for key in genre_dict.keys():
        if key in value:
            genre_dict[key] = genre_dict[key]+ 1
            return True
#ORGANIZING DATA: we separate into each year (see procedure(C)1)
all2019_df = all_df[all_df['year']== '2019']
#all2018_df = all_df[all_df['year']== '2018']
#all2017_df = all_df[all_df['year']== '2017']
#all2016_df = all_df[all_df['year']== '2016']
#all2015_df = all_df[all_df['year']== '2015']
#all2014_df = all_df[all_df['year']== '2014']
#all2013_df = all_df[all_df['year']== '2013']
#all2012_df = all_df[all_df['year']== '2012']
#all2011_df = all_df[all_df['year']== '2011']
#all2010_df = all_df[all_df['year']== '2010']
#all2019_df
#ORGANIZING DATA: counting genre for each year (see procedure(C)1)
#NOTE: we will have to run this for each year separately and produce graphs separately 
all2019_df['genres'].apply(lambda x: counting_genre(x))
#IMPORT: for visualization
import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline
import numpy as np
import sys
from PIL import Image
#VISUALIZATION: bar graph shows which is most reoccuring genre (see procedure(C)1)
#NOTE: we will have to run this for each year separately Look at visualizations folder
objects = genre_dict.keys()
y_pos = np.arange(len(objects))
performance = genre_dict.values()
plt.rcParams.update({'font.size': 13})
plt.subplots(figsize=(12,9))
plt.barh(y_pos, performance, align = 'center', alpha = 1.0)
plt.yticks(y_pos, objects)
plt.ylabel('Genres')
plt.xlabel('Count')
plt.title('Genre Popularity in 2019')
#plt.savefig('Genre_2019.png', bbox_inches = 'tight')
plt.show()
#the next step please skip: there are ways to graph side by side via matplotlib rather than to concatenate images
#DO NOT RUN
#VISUALIZATION: concatenating all images (see procedure(C)1)
#the functions below were borrowed from https://note.nkmk.me/en/python-pillow-concat-images/
im1 = Image.open('MoreData/2010_12.jpg')
im2 = Image.open('MoreData/2013_15.jpg')
im3 = Image.open('MoreData/2016_18.jpg')
im4 = Image.open('MoreData/Genre_2019.png')
im_list = [im1, im2, im3, im4]


#function to concatenate multiple images horizontally
def get_concat_h_multi_resize(im_list, resample=Image.BICUBIC):
    min_height = min(im.height for im in im_list)
    im_list_resize = [im.resize((int(im.width * min_height / im.height), min_height),resample=resample)
                      for im in im_list]
    total_width = sum(im.width for im in im_list_resize)
    dst = Image.new('RGB', (total_width, min_height))
    pos_x = 0
    for im in im_list_resize:
        dst.paste(im, (pos_x, 0))
        pos_x += im.width
    return dst

#function to concatenate multiple images vertically 
def get_concat_v_multi_resize(im_list, resample=Image.BICUBIC):
    min_width = min(im.width for im in im_list)
    im_list_resize = [im.resize((min_width, int(im.height * min_width / im.width)),resample=resample)
                      for im in im_list]
    total_height = sum(im.height for im in im_list_resize)
    dst = Image.new('RGB', (min_width, total_height))
    pos_y = 0
    for im in im_list_resize:
        dst.paste(im, (0, pos_y))
        pos_y += im.height
    return dst

#saving image
get_concat_v_multi_resize(im_list).save('MoreData/All_graphs.jpg')
#DATACLEANING
all_df['budget'] = all_df['budget'].astype(str)
all_df['budget'] = all_df['budget'].apply(lambda x: x.replace('$', ''))
all_df['budget'] = all_df['budget'].apply(lambda x: x.replace(',', ''))
all_df['budget'] = all_df['budget'].apply(lambda x: x.replace('(estimated)', ''))

all_df['grossUSA'] = all_df['grossUSA'].astype(str)
all_df['grossUSA'] = all_df['grossUSA'].apply(lambda x: x.replace('$', ''))
all_df['grossUSA'] = all_df['grossUSA'].apply(lambda x: x.replace(',', ''))

all_df['cumulativeWorldwideGross'] = all_df['cumulativeWorldwideGross'].astype(str)
all_df['cumulativeWorldwideGross'] = all_df['cumulativeWorldwideGross'].apply(lambda x: x.replace('$', ''))
all_df['cumulativeWorldwideGross'] = all_df['cumulativeWorldwideGross'].apply(lambda x: x.replace(',', ''))

#DATA CLEANING
def convertUSD(value):
    if 'EUR' in value:
        return float(value.replace('EUR',''))*(1.09)
    elif 'NOK' in value:
        return float(value.replace('NOK',''))*(0.096)
    elif 'JPY' in value:
        return float(value.replace('JPY',''))*(0.0093)
    elif 'DKK' in value:
        return float(value.replace('DKK',''))*(0.15)
    elif 'GBP' in value:
        return float(value.replace('GBP',''))*(1.25)
    elif 'INR' in value:
        return float(value.replace('INR',''))*(0.013)
    elif 'KRW' in value:
        return float(value.replace('KRW',''))*(0.00082)
    elif 'CAD' in value:
        return float(value.replace('CAD',''))*(0.71)
    elif 'AUD' in value:
        return float(value.replace('AUD',''))*(0.64)
    else:
        return value
    
all_df['budget'] = all_df['budget'].apply(lambda x: convertUSD(x))
#DATA CLEANING
all_df = all_df[all_df['budget'] != '']

#DATA CLEANING
def fillWithNaN(value):
    if value == '':
        return np.nan
    else:
        return value
all_df['grossUSA'] = all_df['grossUSA'].apply(lambda x: fillWithNaN(x))
all_df['cumulativeWorldwideGross'] = all_df['cumulativeWorldwideGross'].apply(lambda x: fillWithNaN(x))
all_df
#DATA CLEANING
all_df['grossUSA'] = all_df['grossUSA'].astype(float)
all_df['cumulativeWorldwideGross'] = all_df['cumulativeWorldwideGross'].astype(float)
all_df['budget'] = all_df['budget'].astype(float)
#DATA CLEANING
sns.scatterplot(x = 'grossUSA', y = 'cumulativeWorldwideGross', data= all_df)
#there exist a correlation
#DATA CLEANING
all_df['cumulativeWorldwideGross'] = all_df['cumulativeWorldwideGross'].dropna()
no_null_df = all_df[all_df['grossUSA'] != 0 ]
no_null_df['ratio'] = no_null_df['grossUSA']/no_null_df['cumulativeWorldwideGross']
mean = no_null_df['ratio'].mean()
#DATA CLEANING
all_df['grossUSA'].fillna(all_df['cumulativeWorldwideGross']* mean, inplace = True)

#ORGANIZING DATA
all_df['profit'] = (0.4*(all_df['cumulativeWorldwideGross'] - all_df['grossUSA']) + 0.6*(all_df['grossUSA']))-all_df['budget']
all_df['cumulativeWorldwideGross'].fillna(0)
all_df = all_df[all_df['cumulativeWorldwideGross']!= 0]
#DATA CLEANING
#check if its normal distribution:
#pearson formula using median 
def pearsonSkew(median, mean, std):
    p = 3*(mean-median)/std
    return p

dom_med = no_null_df['grossUSA'].median()
dom_mean = no_null_df['grossUSA'].mean()
dom_std = no_null_df['grossUSA'].std()

pearsonSkew(dom_med, dom_mean, dom_std)
#MAKING DATAFRAME: making data frame to separate genres (see procedure(C)2)
df_list=[]
for index, rows in all_df.iterrows(): 
    my_list =[rows[i] for i in all_df.columns]  
    if len(my_list[2].split(',')) > 1:
        for item in my_list[2].split(','): 
            df_list.append([*my_list[:2],item,*my_list[3:]])   
#MAKING DATAFRAME: making dataframe separted genres (see procedure(C)2)
singleGenre_df = pd.DataFrame(df_list, columns = all_df.columns)
#singleGenre_df
# we drop information we dont need
singleGenre_df = singleGenre_df.drop(['openingWeekendUSA'], axis=1)
singleGenre_df
#DO NOT RUN
#CLEANING DATAFRAME: We only want movies with a maturty rating (see procedure(C)2) 
singleGenre_df['mrating'] = singleGenre_df['mrating'].fillna(' N o n e')
singleGenre_df = singleGenre_df[singleGenre_df['mrating'] != ' N o n e']
#MAKING DATAFRAME: groupby genre and maturity rating mean (see procedure(C)2)
singleGenre_df.groupby(['genres', 'mrating']).mean()
#VISUALIZATION: Genre and Maturity-rating vs Profit (see procedure(C)2)
df = singleGenre_df.groupby(['genres', 'mrating'])['profit'].mean()
ax = df.plot(kind='bar', figsize=(25,13), color="indigo", fontsize=10);
ax.set_alpha(0.8)
ax.set_title("genre and Maturity-rating vs Profit", fontsize=22)
ax.set_ylabel("genre and m-rating", fontsize=15);

#saving image
#plt.savefig('Genre_Profit.png', bbox_inches = 'tight')
plt.show()
#CLEANING DATAFRAME: We want imdbscore and metascore as floats, (see procedure(C)3)
singleGenre_df['imdbScore'] = singleGenre_df['imdbScore'].apply(lambda x: x[:4])
singleGenre_df['imdbScore'] = singleGenre_df['imdbScore'].astype(float)
#we want imdbScore to be similar to metascore so we multiply by 10 so both scores are out of 100
singleGenre_df['imdbScore'] = singleGenre_df['imdbScore'].apply(lambda x: x)
singleGenre_df['metascore'] = singleGenre_df['metascore'].fillna(0)

#We only look at those values which are not 0
singleGenre_df['metascore'].astype(float)
singleGenre_df = singleGenre_df[singleGenre_df['metascore'] !=0]
singleGenre_df['metascore'] = singleGenre_df['metascore'].apply(lambda x: float(x))
singleGenre_df
#MAKING DATAFRAME: groupby genre and maturity rating via mean. (see procedure(C)3)
singleGenre_df.groupby(['genres', 'mrating']).mean()
#VISUALIZATION: Genre Maturity rating vs Score (see procedure(C)3)
x= singleGenre_df[['genres','mrating','metascore','imdbScore' ]]
y= x.set_index(['genres', 'mrating'])
z=y.groupby(['genres', 'mrating']).mean()
ax = z.plot(kind= 'bar', figsize=(25,8), stacked=True)
ax.set_title("Genre and Maturity-rating vs Scores", fontsize=30)
ax.set_ylabel("Scores", fontsize=20)

#save image
ax.set_xlabel("Genre and Maturity-rating", fontsize=15)
plt.savefig('Genre_score.png', bbox_inches = 'tight')
#ORGANIZING DATA: functions to calculate skewness for each movie (see procedure(C)4)

#calculating Bowleyskewness or Yules coeff
def bowleySkew(data, freq):
    '''
    Parameters: data:<list> list of data
                freq:<list> frequency of data
    Returns: <float> Bowley's skewness
    '''
    Q1 = calculateValue(25,data,freq)
    Q2 = calculateValue(50,data,freq)
    Q3 = calculateValue(75,data,freq)
    Yule_coeff = (Q3 + Q1 - 2*Q2)/(Q3-Q1)
    return round(Yule_coeff,4)

#calculating value num
def calculateValue(percentile, data, freq):
    '''
    Parameters: percentile: <int> percentile
                data:<list> data
                freq:<list> frequency of data
    Returns: <float> value number 
    '''
    cum_freq = cumulativeFrequency(freq)
    total = cum_freq[-1]
    value_num = (percentile/100)*(total+1)
    if type(value_num) == float:
        value_num = round(value_num)
        if value_num <= cum_freq[0]:
            return data[0]
        else:
            for x in range(0,len(cum_freq)):
                if value_num >= cum_freq[x-1] and value_num <= cum_freq[x]:
                    return data[x]

#calculating cumulative freq
def cumulativeFrequency(freq):
    '''
    Parameters: freq:<list> frequency of data
    Returns: <list> cumulative frequency
    '''
    cum_freq = []
    cum_freq.append(freq[0])
    for x in range(1, len(freq)):
        cum_freq.append(cum_freq[x-1] + freq[x])
    return cum_freq
        
      
#MAKING DATAFRAME: adding skewness to votes_df (see procedure(C)4)
skew_list =[]
votes = [1,2,3,4,5,6,7,8,9,10]
for index, rows in votes_df.iterrows(): 
    my_list =[rows.one, rows.two, rows.three, rows.four, rows.five, rows.six, rows.seven, rows.eight, rows.nine, rows.ten] 
    skew_list.append(bowleySkew(votes, my_list))
    
votes_df['skewness']= skew_list
votes_df = all_df.merge(votes_df, on = 'ids')
votes_df
#MAKING DATAFRAME: function separating into genres again (see procedure(C)4)

df_list2=[]
for index, rows in votes_df.iterrows(): 
    my_list =[rows[i] for i in votes_df.columns]  
    if len(my_list[2].split(',')) > 1:
        for item in my_list[2].split(','): 
            df_list2.append([*my_list[:2],item,*my_list[3:]]) 

skewd_df = pd.DataFrame(df_list2, columns = votes_df.columns)
skewd_df
#CLEANING DATAFRAME: cleaning mrating same as singleGenres (see procedure(C)4)
skewd_df['mrating'] = skewd_df['mrating'].fillna(' N o n e')
skewd_df.mrating.unique()
skewd_df = skewd_df[skewd_df['mrating'] != ' N o n e']
#VISUALIZATION: Genre Maturity rating vs skew (see procedure(C)4)
df = skewd_df.groupby(['genres','mrating'])['skewness'].mean()
ax = df.plot(kind='bar', figsize=(25,8), color="indigo", fontsize=10);
ax.set_alpha(0.8)
ax.set_title("Genre vs skewness", fontsize=30)
ax.set_ylabel("Skewness", fontsize=20)
ax.set_xlabel("Genre", fontsize=15)
plt.savefig('Genre_skew.png', bbox_inches = 'tight')
plt.show()<jupyter_output><empty_output><jupyter_text>ANSWER 1: ((Sci-fi, Action), PG-13), ((Crime, Action), PG), ((Mystery,action),PG)<jupyter_code>'''
Answer: Via the visualization we get the following information:
1. Throughout the years Action seems to appear the most, whereas horror and drama seem to lose trend
2. We look at top 5-6 movies from each: genre and maturity rating vs (1: profit, 2: skewness, 3: scores)
If a movie appears twice or three times in each we take that as a successful genre and maturty rating pair:

What we found: (Sci-fi, PG13),(Crime, PG), (Mystery,PG), (Documentary, rated R)
Note: we want to make a movie that can profit off of product placement and possible merchandising.
Hence we will discard (Documentary, rated R)

Since Action was a reoccuring genre with most count throughout ten years the pair 
(Sci-fi, action), (Crime, action), (Mystery, action) is most likely a good combination of genres

'''<jupyter_output><empty_output><jupyter_text>Question2:  What are the top directors for such genre/maturity-rating pair?<jupyter_code>'''
Procedure(D):
There is not much difference to the procedures in D with procedures in C
1. We identify the directors of genre/maturity-rating pair
2. See director vs profit (the procedure is similar to that of Procedure (C)1) save as director_score.png
3. directors vs scores (the procedure is similar to that of Procedure (C)2) save as director_profit.png

'''
#DO NOT RUN
#MAKING DATAFRAME: See procedure(D) 1
MysteryPG_df = singleGenre_df[(singleGenre_df['genres'] == ' Mystery')&(singleGenre_df['mrating'] == ' Rated PG')]
SciFiPG13_df = singleGenre_df[(singleGenre_df['genres'] == ' Sci-Fi')&(singleGenre_df['mrating'] == ' Rated PG-13')]
CrimePG_df = singleGenre_df[(singleGenre_df['genres'] == ' Crime')&(singleGenre_df['mrating'] == ' Rated PG')]

pieces = (MysteryPG_df,SciFiPG13_df,CrimePG_df)
df_final = pd.concat(pieces, ignore_index = True)
#DO NOT RUN
#VISUALIZATION: directors vs score
x= df_final[['directors','metascore','imdbScore' ]]
y= x.set_index(['directors'])
ax = y.plot(kind= 'bar', figsize=(25,8), stacked=True)
ax.set_title("Directors vs Scores", fontsize=30)
ax.set_ylabel("Scores", fontsize=20)
ax.set_xlabel("Directors", fontsize=15)

#save image
plt.savefig('director_score.png', bbox_inches = 'tight')
#DO NOT RUN
#VISUALIZATION: directors vs profit
x= df_final[['directors','profit' ]]
y= x.set_index(['directors'])
ax = y.plot(kind= 'bar', figsize=(25,8), color = 'green')
ax.set_title("Directors vs profit", fontsize=30)
ax.set_ylabel("profit", fontsize=20)
ax.set_xlabel("Directors", fontsize=15)

#save image
plt.savefig('director_profit.png', bbox_inches = 'tight')<jupyter_output><empty_output><jupyter_text>ANSWER 2: <NAME> and <NAME> ((Sci-fi, Action), PG-13)<jupyter_code>'''
Answer: From our findings it seems <NAME> and <NAME> is a good candidate for director
They specialize in Sci-Fi PG-13

Note: Most movies if not all movies contain multiple genres, any movie which contains Sci-Fi as one of its genre
is a movie of genre Sci-Fi. 
The movie which awarded <NAME> and <NAME> a successful Sci-Fi PG-13 movie was Avengers:EndGame

So one should be careful what this genre/maturty pairing really means 
'''<jupyter_output><empty_output><jupyter_text>QUESTION 3: Is there a correlation between scores and profit?<jupyter_code>'''
Procedure(E):
1. Since imdb scores, metascore as well as profit ranges are very different we first normalize using 
z-score normalization. 

2. Visualize using a scatter plot the correlation between profit and scores
'''
singleGenre_df.drop_duplicates('ids', keep = 'first')
#ORGANIZING DATA: function calculating z-score. (See procedure(D) 1)
#normalize using z-score normalization
metascore_mean = round(singleGenre_df['metascore'].mean(),3)
metascore_std = round(singleGenre_df['metascore'].std(),3)

imdb_mean = round(singleGenre_df['imdbScore'].mean(),3)
imdb_std = round(singleGenre_df['imdbScore'].std(),3)

profit_mean = round(singleGenre_df['profit'].mean(),3)
profit_std = round(singleGenre_df['profit'].std(),3)

def zScoreNormalization(value, mean, std):
    z= (value - mean)/std
    return z
    
#metascore_mean
#metascore_std
#imdb_mean
#imdb_std #= 0 PROBLEM
#profit_mean
#profit_std
singleGenre_df
singleGenre_df['metascore'] = singleGenre_df['metascore'].apply(lambda x: zScoreNormalization(x, metascore_mean, metascore_std))
singleGenre_df['imdbScore'] = singleGenre_df['imdbScore'].apply(lambda x: zScoreNormalization(x, imdb_mean, imdb_std))
singleGenre_df['profit'] = singleGenre_df['profit'].apply(lambda x: zScoreNormalization(x, profit_mean, profit_std))

#DO NOT RUN
#VISUALIZATION: scatter plot, score vs profit (See procedure(D) 1)
fig, ax = plt.subplots()

sns.scatterplot(x = 'imdbScore', y = 'profit', data= singleGenre_df, ax=ax)
sns.scatterplot(x = 'metascore', y = 'profit', data= singleGenre_df, ax=ax)
ax.legend(labels = ['metascore', 'imdbScore'])
ax.set_xlabel("Scores", fontsize=15)

#save image
#plt.savefig('score_profit.png', bbox_inches = 'tight')
#Below are some extra visualizations one can make 
singleGenre_df.corr()
#heatmap
sns.heatmap(singleGenre_df.corr(),cmap='magma',linecolor='white',linewidths=1)
#plt.savefig('heatmap.png', bbox_inches = 'tight')
#heat map
f, ax = plt.subplots(figsize=(10,10))
sns.set(font_scale=1.35)
cmap = sns.diverging_palette(1000, 0, as_cmap=True)
matrix = np.triu(singleGenre_df.corr())
ax.set_title('correlations', y=1.2, fontsize=16, ha='center')
sns.heatmap(singleGenre_df.corr(), cmap='magma', vmax=1, center=0,
            square=True, linewidths=.2, ax=ax, annot=True, mask=matrix,
            cbar_kws={'label': 'Colorbar'})
plt.savefig('heatmap2.png', bbox_inches = 'tight')
#cluster map
sns.clustermap(singleGenre_df.corr(),cmap='coolwarm',standard_scale=1)
#plt.savefig('clustermap.png', bbox_inches = 'tight')
#regression plot: helpful when trying to approximate correlation
sns.lmplot(x='imdbScore',y='profit',data=singleGenre_df)
#We exclude showing violin plot because this example data set is so small it makes little sense
#fig, ax = plt.subplots(figsize=(30,8))
#sns.violinplot(x="genres", y="profit", data=singleGenre_df,palette='rainbow', ax=ax)
#plt.savefig('violinplot.png', bbox_inches = 'tight')
#box and whisker plot: good when looking for outliers 
fig, ax = plt.subplots(figsize=(40,10))
plot = sns.boxplot(x="genres", y="profit", data=singleGenre_df,palette='rainbow', ax=ax)
plot.set(xlabel = "genres", ylabel='profit', title='genre v. profit');
#plt.savefig('boxplot.png', bbox_inches = 'tight')
#RIDGEPLOT
sns.set(style="white", rc={"axes.facecolor": (0, 0, 0, 0)})

# Create the data
df = singleGenre_df

# Initialize the FacetGrid object
pal = sns.cubehelix_palette(10, rot=-.25, light=.7)
g = sns.FacetGrid(df, row='genres',hue="genres", aspect=15, height=.5, palette=pal)

# Draw the densities in a few steps
g.map(sns.kdeplot, "metascore", clip_on=False, shade=True, alpha=1, lw=1.5, bw=.2)
g.map(sns.kdeplot, "metascore", clip_on=False, color="w", lw=2, bw=.2)
g.map(plt.axhline, y=0, lw=2, clip_on=False)

def label(x, color, label):
    ax = plt.gca()
    ax.text(0, .2, label, fontweight="bold", color=color,
            ha="left", va="center", transform=ax.transAxes)


g.map(label, "metascore")

g.fig.subplots_adjust(hspace=-.25)

# Remove axes details that don't play well with overlap
g.set_titles('')
g.set(yticks=[])
g.despine(bottom=True, left=True)
plt.savefig('Ridgeplot.png', bbox_inches = 'tight')<jupyter_output><empty_output><jupyter_text>ANSWER 3: Not exactly<jupyter_code>'''
Answer:
there is not as much evidence to provide that better scores implies better profit
ex: that we've seen Crime PG gives good profit but poor votes
'''<jupyter_output><empty_output><jupyter_text>MYSQL<jupyter_code>'''
We did not make use of databases but:
If one wants to use a database to store the data scraped off the web via mysql:

note: one can store to a database especially if the data being scraped is very large, it is more efficient
to store to a database and call from the database 
'''
#IMPORT mysql.connector and connect
import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    passwd = <<PASSWORD>>,
    database = 'moviedb'
    autocommit = True
)

mycursor = mydb.cursor()
mycursor.execute('CREATE DATABASE moviedb')
#Create table: movieInfo 
mycursor.execute("""CREATE TABLE movieInfo (ids VARCHAR(255), 
                 title VARCHAR(255), genres VARCHAR(255), directors VARCHAR(255), year INTEGER(10))""")
#add data to table: ids, title, genres, directors, years
def insertToMovieInfo(data_list):
    '''
    Parameters: data_list:<list> list of tuples to append to table
    Returns: appends to table
    '''
    for item in data_list:
        
        #INSERT INTO tablename, (columns) VALUES (%s)
        insert = "INSERT INTO movieInfo (ids, title, genres, directors, year) VALUES (%s, %s, %s, %s, %s)"

        mycursor.execute(insert, item)
        #mydb.commit()
#(*) cell 1: the list of movies, top100_10yrs 
#we take tuple with ids, title, genres, directors, year 
boxOffice = [x[5] for x in top100_10yrs]
listToDf = [(x[0],x[1],x[2],x[3], x[4]) for x in top100_10yrs]

insertToMovieInfo(listToDf)  
#how to retrieve
mycursor.execute("SELECT * FROM movieInfo")
myresult = mycursor.fetchall()

for x in myresult:
      print(x)
#create separate table for boxoffice 
mycursor.execute("""CREATE TABLE movieBoxoffice (ids, VARCHAR(255), budget VARCHAR(255), 
                 opening_gross VARCHAR(255), USAgross VARCHAR(255), total_gross VARCHAR(255))""")
                 
#turn boxOffice into tuple
boxOffice_tuple = []
for item in boxOffice:
        ids = item[0]
        box_office = item[1]
        data = (ids, box_office.get('budget'), box_office.get('openingWeekendUSA'), 
            box_office.get('grossUSA'), box_office.get('cumulativeWorldwideGross'))
        boxOffice_tuple.append(data)
#insert function
def insertToMovieBoxOffice(data_list):
    '''
    Parameters: data_list:<list> list of tuples to append to table
    Returns: appends to table
    '''
    for item in data_list:
        insert = "INSERT INTO movieInfo (ids, title, genres, directors, year) VALUES (%s, %s, %s, %s, %s)"
    mycursor.execute(insert, item)
#insert
insertToMovieBoxOffice(boxOffice_tuple)
#Create table for scores
mycursor.execute("""CREATE TABLE movieScores (ids, VARCHAR(255), metascore VARCHAR(255), 
                 metacritic VARCHAR(255), mrating VARCHAR(255), imdbVotes VARCHAR(255), imdbScore VARCHAR(255)""")
#insert function 
def insertToMovieScore(data_list):
    '''
    Parameters: data_list:<list> list of tuples to append to table
    Returns: appends to table
    '''
    for item in data_list:
        insert = """INSERT INTO movieScores (ids, metascore, metacritic, mrating, imdbVotes, imdbScore) 
        VALUES (%s, %s, %s, %s, %s)"""
        mycursor.execute(insert, item)
#insert         
insertToMovieScore(top100_10yrs2)
#Create table for votes
mycursor.execute("""CREATE TABLE movieVotes (ids VARCHAR(255), title VARCHAR(255), one VARCHAR(255), 
                 two VARCHAR(255), three VARCHAR(255), four VARCHAR(255), five VARCHAR(255), six VARCHAR(255),
                 seven VARCHAR(255), eight VARCHAR(255), nine(255), ten(255)
                 """)
#(*) cell three 
def insertToMovieVotes(data_list):
    '''
    Parameters: data_list:<list> list of tuples to append to table
    Returns: appends to table
    '''
    for item in data_list:
        insert = """INSERT INTO movieVotes (ids, title, one, two, three, four, five, six, seven,
        eight, nine, ten) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
        mycursor.execute(insert, item)
#How to Merge:
#note: one could have made a "mega" table with ALL information which is better, so no joining is necessary 
mycursor.execute("""SELECT ids FROM movieInfo LEFT JOIN movieBoxOffice ON ids""")

#Creating dataframe 
#When one has a large table it is a nice way to select subtables from a large table
mycursor.execute("SELECT ids,title,genres FROM movieInfo")
myresult = mycursor.fetchall()
dataframe = []

for x in myresult:
    dataframe.append(x)
df = pd.DataFrame(dataframe)
#dont forget to close when you're done
mydb.close()<jupyter_output><empty_output><jupyter_text>Sqlite3<jupyter_code>import sqlite3

conn = sqlite3.connect('MovieDB.db')  # create a new database 
mycursor = conn.cursor() # The database will be saved in the location where your notebook file is saved

#create table - MovieInfo
mycursor.execute('''CREATE TABLE MovieInfo4
             ([ids] text, [title] text, [genres] text, [directors] text, [year] integer)''')

#create table - boxoffice 
mycursor.execute('''CREATE TABLE movieBoxoffice4 ([generated_id] INTEGER PRIMARY KEY, [ids] text, [budget] text, [opening_gross] text, [USAgross] text, [total_gross] text)''')

#Create table - scores
mycursor.execute('''CREATE TABLE movieScores ([ids] text, [metascore] text, [metacritic] text, [mrating] text, [imdbVotes] text, [imdbScore] text)''')

#Create table - Votes
mycursor.execute('''CREATE TABLE Votes([generated_id] INTEGER PRIMARY KEY,[ids] text, [title] text, [one] integer, [two] integer, [three] integer, [four] integer, [five] integer, [six] integer, [seven] integer, [eight] integer, [nine] integer, [ten] integer)''')
        
                 
conn.commit()
#add data to table: ids, title, genres, directors, years
c.executemany('INSERT INTO MovieInfo (ids, title, genres, year) VALUES (?, ?, ?, ?)', listToDf)

#one can do the same to insert info to all other tables. 


# join tables
mycursor.execute('''
SELECT ids FROM movieInfo 
LEFT JOIN movieBoxOffice ON ids
          ''')

#getting info
mycursor.execute('''
SELECT DISTINCT *
FROM movieInfo
          ''')

#turning to dataframe
df = pd.DataFrame(c.fetchall(), columns=['ids', 'title','directors', 'genres', 'year'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13222484295088857, 'fraction_numerical': 0.027416454543331543, 'mean_word_length': 4.3267819380519965, 'pattern_counts': {'":': 1, '<': 93, '<?xml version=': 0, '>': 96, 'https://': 18, 'lorem ipsum': 0, 'www.': 14, 'xml': 1}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '501071', 'license': 'MIT', 'n_tokens_mistral': 15827, 'n_tokens_neox': 14655, 'n_words': 3931, 'path': 'Module1ProjectRunExample.ipynb', 'repo_name': 'akwon100/module1_project', 'size': '42874'}