<jupyter_start><jupyter_text>CenárioResumindo o relatado na fase de Business Understanding, a nossa equipa foi contratada pela Sony para desenvolver um algoritmo de previsão do ESRB rating, isto, de modo que a Sony na fase de desenvolvimento dos seus jogos, consigam desenvolver as funcionalidades e campanhas de marketing corretas de acordo com o ESRB pretendido. Assim, o nosso algoritmo fará reduzir os custos de produção do jogo, nomeadamente no que toca ao custo das funcionalidades e marketing, pois a empresa necessitará de reformular o jogo menos vezes, não sendo construídas acidentalmente funcionalidades erradas (ou seja, fora do âmbito do ESRB rating pretendido), e as campanhas de marketing poderão ser muito mais focadas para um público específico desde o início. Estes dois ganhos em eficiência refletem-se no aumento dos lucros da empresa. Pressupostos- **Todos os jogos incluem a PS4:** Uma vez que o cliente é a Sony, a desenvolvedora da PS4, há possibilidade de haver análises de jogos exclusivos (apenas PS4) assim como multiplataforma (PS4 e Xbox One). Assim torna-se imprescindível a adição de um filtro para a consola nas dashboards.- **Datas de lançamento e custos de produção dos jogos:** A Sony para a fase de Deployment, forneceu à equipa dois novos datasets com as datas de lançamento e os custos de produção dos jogos que complementam o dataset original (train.csv).- **Classificação ESRB final:** Os jogos são classificados pela organização ESRB e a classificação final apenas de aplica depois do jogo estar finalizado. Caso o jogo obtenha uma classificação não pretendida pela Sony, esta reformula o jogo, alterando e/ou eliminando funcionalidades e campanhas de marketing já planeadas. A reformulação do jogo implica custos de produção adicionais, esses que já estão pressupostos no novo dataset com os custos de produção. Assim este pressuposto complementa a nossa justificação para a redução dos custos de produção, uma vez que ao utilizar o nosso algoritmo, o jogo obterá a classificação pretendida a maior parte das vezes. Objetivo de Data MiningExatidão na previsão do ESRB Rating de um jogo Objetivo de NegócioAumentar os lucros na venda dos jogos Fator Crítico de SucessoDiminuir os custos na fase de produção de um jogo KPIsTodos os nossos KPIs são medidos em função da dimensão tempo (dim_date) do nosso Data Warehouse. A equipa identificou os seguintes KPIs:- **Novos gastos no desenvolvimento do jogo:** Estes gastos estão relacionados com atividades do desenvolvimento e criação do jogo, desde assets a peças musicais, o jogo existe graças a estes gastos.- **Novos gastos nas campanhas de marketing:**  Estes gastos são relacionados com as atividades de marketing do jogo, campanhas, anúncios, marketing digital, entre outros. Muitas vezes estas atividades começam "cedo" no ciclo de vida do jogo o que leva a falhas no âmbito do jogo e terem de ser refeitas aquando da averiguação do ESRB Rating que o jogo acabou por ter depois de estar terminado.- **Taxa de acerto:** A taxa de acertos reflete-se na quantidade de previsões corretas do nosso modelo face ao total de amostras.- **Taxa de erro:** A taxa de acertos reflete-se na quantidade de previsões erradas do nosso modelo face ao total de amostras. Projeto 1Ao realizar o projeto 1, concluimos e as **redes neuronais MLP Classifier é o melhor modelo**. Apresentando uma **exatidão de 87,54%** e um **tempo de treino de aproximadamente 16.56 segundos** para o treino de 4/5 do dataset **do cenário 2 de testes**.O cenário 2 apenas remove 4 features do dataset original (mild_language, mature_humor, console e use_of_drugs_and_alcohol), estes foram os atributos com um score menor que 10 na nossa análise de seleção univariada de atributos com o algoritmo chi2.Ainda, de modo a fiabilizar a nossa exatidão, **foi aplicada a cross validations, com 5 Stratified K-Folds**, isto é, todos os Folds possuiem a mesma quantidade da variável target, no nosso caso, do esrb_rating.De modo a melhorar os resultados, **fizemos a hisperparameterização** e antes de ser feito o treino dos dados, **aplicamos o SMOTE** aos mesmos, isto é, uma **técnica de oversampling** para balancear a quantidade de dados.![Cenário 2 de Testes](images/NB_results_scenery2.png) Projeto 2Para a previsão do ESRB rating de cada jogo a carregar no Data Warehouse foi utilizado o algoritmo com melhor exatidão identificado pela equipa na fase de Evaluation, o MLP Classifier.Uma vez que nos foi entregue um dataset com as datas de lançamento dos jogos, **o nosso cliente (Sony) pediu para prever todos os jogos com data de lançamento igual ou superior a 2019, sendo os restantes para treino do modelo**. A partir do SweetViz conseguimos afirmar que os novos dados de treino e teste representam, ordenadamente, 62% e 38% do dataset (train.csv). Devido a isto, o valor de exatidão alterou de 87,54% para 85.19%, porém permanece no mesmo nível que o anterior (exatidão entre 85%-89%).  Table Of Contents - [1. Generate Datasets (Extract)](genDatasets)  - [A. Generate Release Dates](genReleaseDates)  - [B. Generate Costs](genCosts)- [2. Data Warehousing (Transformation and Loading)](dw)  - [A. Generate Database](genDB)  - [B. Generate Tables](genTables)  - [C. Populate Dimensions](popDims)    - [i. dim_esrb_rating_scale](dim_esrb_rating_scale)    - [ii. dim_console](dim_console)    - [ii. dim_console](dim_console)    - [iii. dim_date](dim_date)  - [D. Populate Fact Table](popFact)    - [i. fact_game](fact_game)  - [E. Create Views](createViews)    - [i. view_game_prediction](view_game_prediction)<jupyter_code>import pandas as pd
import numpy as np
import http.client
import requests
import json
from datetime import datetime
import sweetviz as sv

import mysql.connector
import joblib

import warnings
warnings.filterwarnings("ignore")<jupyter_output><empty_output><jupyter_text>1. Generate Datasets (Extract)   A. Generate Release Dates - **Datas de Lançamento dos Jogos:** Utilizamos 2 APIs diferentes para a reunião das datas de lançamento de todos os jogos presentes no nosso dataset. Porém na implementação, quer de uma quer de outra, existiram sempre outliers como jogos não presentes na API, ou datas de lançamento erradas ou em falta. Por este mesmo motivo decidimos juntar as 2 APIs ao invés de utilizar apenas 1 e diminuir drasticamente o número de outliers. Para além que, jogos não identificados por ambas as APIs, foram adicionados à medida no ínicio do script.[Voltar ao Índice](index)<jupyter_code>games = pd.read_csv('data/train.csv')

# Neste momento estamos a ir buscar datas para 140 jogos diferentes a esta API
def getReleaseDateRAWG(gameTitle):
    res = requests.get(f"https://api.rawg.io/api/games?key=dc7364f0802a4a7bbcc2274673135d53&search={gameTitle}")
    date = res.json()['results'][0]['released']
    date = datetime.strptime(f'{date}', '%Y-%m-%d')
    return date

def insertValues(date):
    arrDates.append(date.date())
    arrYears.append(date.year)
    arrMonths.append(date.month)
    arrDays.append(date.day)


conn = http.client.HTTPSConnection("api.igdb.com")
headers = {
    'Client-ID': "d7an1titccgkj3owhu40nr9a2up0se",
    'Authorization': "Bearer <KEY>",
    'Content-Type': "text/plain"
}

arrTitles = []
arrConsoles = []
arrDates = []
arrYears = []
arrMonths = []
arrDays = []

for i in range(len(games)):
    row = games.loc[i,:]
    gameTitle = row['title']
    arrTitles.append(gameTitle)
    arrConsoles.append(row['console'])
    
    # Jogos não enocontrados em nenhum das duas APIs, então inserimos as suas release dates à medida
    if (gameTitle == "TRANSFORMERS:BATTLEGROUNDS"):
        date = datetime.strptime('2020-10-23', '%Y-%m-%d')
        insertValues(date)
        continue
    if (gameTitle == "Home Run Derby VR"):
        date = datetime.strptime('2018-04-26', '%Y-%m-%d')
        insertValues(date)
        continue
    if (gameTitle == "Dungeon of the Endless"):
        date = datetime.strptime('2018-04-26', '%Y-%m-%d')
        insertValues(date)
        continue
    if (gameTitle == "Another Sight"):
        date = datetime.strptime('2019-06-18', '%Y-%m-%d')
        insertValues(date)
        continue
    if (gameTitle == "Dusk Diver"):
        date = datetime.strptime('2019-10-29', '%Y-%m-%d')
        insertValues(date)
        continue
    if (gameTitle == "UPPERS"):
        date = datetime.strptime('2020-10-21', '%Y-%m-%d')
        insertValues(date)
        continue
    if (gameTitle == "One Piece: Grand Cruise"):
        date = datetime.strptime('2018-05-21', '%Y-%m-%d')
        insertValues(date)
        continue
    #
        
    payload = "fields name, release_dates.date, release_dates.platform.name, release_dates.human;\nlimit 100;\nsearch \"{}\";".format(gameTitle).encode('utf-8')
    conn.request("POST", "/v4/games", payload, headers)
    res = conn.getresponse()
    data = res.read()
    jsonObj = json.loads(data.decode("utf-8"))
    if not len(jsonObj) > 0:
        # Sem resposta da API igdb
        date = getReleaseDateRAWG(gameTitle)
        insertValues(date)

    else:
        try:
            if 'release_dates' not in jsonObj[0]:
                # Nao tem realease date na API igdb
                date = getReleaseDateRAWG(gameTitle)
                insertValues(date)
            else:
                ps = 0
                xbox = 0
                psDate = []
                xboxDate = []
                date = ""
                for i2 in jsonObj[0]['release_dates']:
                    if i2['platform']['name']=='PlayStation 3' or i2['platform']['name']=='PlayStation 4':
                        ps = 1
                        timestamp = i2['date']
                        psDate.append(timestamp)
                    elif i2['platform']['name']=='Xbox 360' or i2['platform']['name']=='Xbox One':
                        xbox = 1
                        timestamp = i2['date']
                        xboxDate.append(timestamp)
                     
                if len(psDate) > 0:
                    psDate = max(psDate)
                    psDate = datetime.fromtimestamp(timestamp)
                if len(xboxDate) > 0:
                    xboxDate = max(xboxDate)
                    xboxDate = datetime.fromtimestamp(timestamp)

                if ps == 1 and xbox == 1:
                    if psDate >= xboxDate:
                        date = psDate
                    else:
                        date = xbobxDate
                elif ps == 1 and xbox == 0:
                    date = psDate
                elif ps == 0 and xbox == 1:
                    date = xboxDate
                elif ps == 0 and xbox == 0:
                    date = getReleaseDateRAWG(gameTitle)

                insertValues(date)
        except:
            # Data invalida da API igdb
            date = getReleaseDateRAWG(gameTitle)
            insertValues(date)
    print(f"{i} - {gameTitle} - Release Date: {date.date()}")
data = {
    'title': arrTitles,
    'console': arrConsoles,
    'date': arrDates,
    'year': arrYears,
    'month': arrMonths,
    'day': arrDays
}
release_dates = pd.DataFrame(data)
release_dates
# Save to a csv
release_dates.to_csv('data/release_dates.csv', index=False, header=True)
# Generate report about release dates
my_report = sv.analyze(release_dates)
my_report.show_html(filepath='analysis-reports/sv_release_dates_report.html', open_browser=False)<jupyter_output><empty_output><jupyter_text>B. Generate Costs - **Custos de Produção:** [Segundo análises](https://www.gamevicio.com/noticias/2021/09/shawn-layden-fala-sobre-os-custos-irrealistas-de-us-200-milhoes-na-producao-de-jogos-aaa-atualmente/), o desenvolvimento de um jogo para a PS4 e Xbox One da classe AAA ronda entre os 120Milhões e 160M de dólares. Assim, estamos a assumir que os custos de produção para todos os nossos jogos estão entre 105M€ e 145M€. Para além disto também generalizamos que dos custos totais de produção, 30% são utilizados para atividades relacionadas com o marketing do jogo e 70% estão alocados para o desenvolvimento do jogo em si.[Voltar ao Índice](index)<jupyter_code>games = pd.read_csv('data/train.csv')

#https://www.gamevicio.com/noticias/2021/09/shawn-layden-fala-sobre-os-custos-irrealistas-de-us-200-milhoes-na-producao-de-jogos-aaa-atualmente/
# Custo de producao em euros
data = np.random.randint(105000000, 145000000, size=len(games))
production_costs = pd.DataFrame(data, columns=['production_cost'])

# Marketing 30%
# Desenvolvimento do jogo 70%
marketing_costs = []
development_costs = []
new_marketing_costs = []
new_development_costs = []
for i in range(len(production_costs)):
    row = production_costs.loc[i,:]
    marketing_cost =  round(row['production_cost'] * 0.30)
    development_cost = round(row['production_cost'] * 0.70)
    marketing_costs.append(marketing_cost)
    development_costs.append(development_cost)

production_costs.insert(1, 'marketing_cost', marketing_costs)
production_costs.insert(1, 'development_cost', development_costs)
production_costs
# Save to a csv
production_costs.to_csv('data/production_costs.csv', index=False, header=True)<jupyter_output><empty_output><jupyter_text>2. Data Warehousing (Transformation and Loading) **Durante a construção do modelo multidimensional foram definidas regras** de forma a permitir uma melhor leitura, organização e funcionamento do modelo:- Nomenclatura em inglês, no singular, em to lower case e com underscore em vez de espaços;- Prefixos de modo a identificar a função de especificas tabelas e atributos;- Atribuição de um número máximo de caracteres aos atributos que necessitam que seja definido um limite de caracteres, exceto às Natural Keys, tendo sido assim definida uma margem de 10 caracteres adicionais para todos estes atributos.![Modelo Multidimensional](images/modelo_multidimensional.png)Ao visualizar o modelo é passível identificar algumas **peculiaridades que o diferem dos demais**, são estas:- **Utilização de counters** na tabela de factos de modo a contar as linhas sinalizadas dado que alguns programas de criação de dashboards não retornam ou é complicado realizar este cálculo;- A tabela dimensão dim_esrb_rating_scale possui **different roles** devido ao facto de esta se referir a multiplos propósitos na tabela de factos (esrb_rating e pred_esrb_rating);- A tabela dimensão dim_esrb_rating_scale possui uma **Slowly Changing Dimensions Type 3**, isto é, permite-nos atualizar a dimensão sem ser necessário dar drop dos dados antigos;- **Utilização de Natural Keys (NK)** sempre que possível, uma coluna ou conjunto de colunas já existentes no(s) datasets com significado de negócio o que permite ser usada como parâmetro de pesquisa facilitando a etapa de ETL, diminuindo o número de queries e consequentemente o tempo para o carregamento dos dados da tabela de factos. **Tópicos A (Generate Database) e B (Generate Tables)**Antes de realizar o carregamento, criamos uma célula de código dedicada ao drop e criação, sequencialmente, da schema do Data Warehouse dentro do serviço MySQL. Seguidamente, recorrendo ao MySQL Workbench 8.0 CE, numa nova célula, foram feitas as queries de criação das tabelas de facto e dimensão e, posteriormente, implementadas em código dentro de outra célula do nosso notebook. Assim, sempre que necessário, podemos com apenas um clique executar o notebook, reiniciando assim todo o processo de data warehousing, sem nos preocuparmos com duplicação de dados nas tabelas com Surrogate Keys ou erros de duplicação de dados nas tabelas com Natural Keys.  A. Generate Database [Voltar ao Índice](index)<jupyter_code>dbName = "games_datawarehouse"

con = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    passwd="password"
)

mycursor = con.cursor()

mycursor.execute(f"DROP DATABASE IF EXISTS {dbName}")
mycursor.execute(f"CREATE DATABASE {dbName}")
mycursor.close()
con.close()<jupyter_output><empty_output><jupyter_text>B. Generate Tables [Voltar ao Índice](index)<jupyter_code>con = mysql.connector.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    passwd="password",
    database=dbName
)

mycursor = con.cursor()

mycursor.execute('''
CREATE TABLE IF NOT EXISTS `dim_console` (
  `NK_console` int NOT NULL,
  `description` varchar(24) NOT NULL,
  PRIMARY KEY (`NK_console`)
);
''')

mycursor.execute('''
CREATE TABLE IF NOT EXISTS `dim_date` (
  `NK_date` varchar(8) NOT NULL,
  `year` int NOT NULL,
  `month` int NOT NULL,
  `day` int NOT NULL,
  PRIMARY KEY (`NK_date`)
);
''')

mycursor.execute('''
CREATE TABLE IF NOT EXISTS `dim_esrb_rating_scale` (
  `NK_esrb_rating_scale` varchar(2) NOT NULL,
  `description` varchar(25) NOT NULL,
  `previous_description` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`NK_esrb_rating_scale`)
);
''')

mycursor.execute('''
CREATE TABLE IF NOT EXISTS `fact_game` (
  `SK_game` int NOT NULL AUTO_INCREMENT,
  `release_date` varchar(8) NOT NULL,
  `console` int NOT NULL,
  `esrb_rating` varchar(2) NOT NULL,
  `pred_esrb_rating` varchar(2) NOT NULL,
  `alcohol_reference` bit(1) NOT NULL,
  `animated_blood` bit(1) NOT NULL,
  `blood` bit(1) NOT NULL,
  `blood_and_gore` bit(1) NOT NULL,
  `cartoon_violence` bit(1) NOT NULL,
  `crude_humor` bit(1) NOT NULL,
  `drug_reference` bit(1) NOT NULL,
  `fantasy_violence` bit(1) NOT NULL,
  `intense_violence` bit(1) NOT NULL,
  `language` bit(1) NOT NULL,
  `lyrics` bit(1) NOT NULL,
  `mature_humor` bit(1) NOT NULL,
  `mild_blood` bit(1) NOT NULL,
  `mild_cartoon_violence` bit(1) NOT NULL,
  `mild_fantasy_violence` bit(1) NOT NULL,
  `mild_language` bit(1) NOT NULL,
  `mild_lyrics` bit(1) NOT NULL,
  `mild_suggestive_themes` bit(1) NOT NULL,
  `mild_violence` bit(1) NOT NULL,
  `no_descriptors` bit(1) NOT NULL,
  `nudity` bit(1) NOT NULL,
  `partial_nudity` bit(1) NOT NULL,
  `sexual_content` bit(1) NOT NULL,
  `sexual_themes` bit(1) NOT NULL,
  `simulated_gambling` bit(1) NOT NULL,
  `strong_language` bit(1) NOT NULL,
  `strong_sexual_content` bit(1) NOT NULL,
  `suggestive_themes` bit(1) NOT NULL,
  `use_of_alcohol` bit(1) NOT NULL,
  `use_of_drugs_and_alcohol` bit(1) NOT NULL,
  `violence` bit(1) NOT NULL,
  `title` varchar(89) NOT NULL,
  `production_cost` int unsigned NOT NULL,
  `marketing_cost` int unsigned NOT NULL,
  `development_cost` int unsigned NOT NULL,
  `new_production_cost` int NOT NULL,
  `new_marketing_cost` int NOT NULL,
  `new_development_cost` int NOT NULL,
  `right_pred_counter` int NOT NULL,
  `wrong_pred_counter` int NOT NULL,
  `game_counter` int NOT NULL,
  PRIMARY KEY (`SK_game`),
  KEY `FK4_pred_esrb_rating_idx` (`pred_esrb_rating`),
  KEY `FK3_esrb_rating_idx` (`esrb_rating`),
  KEY `FK2_console_idx` (`console`),
  KEY `FK1_release_date_idx` (`release_date`),
  CONSTRAINT `FK1_release_date` FOREIGN KEY (`release_date`) REFERENCES `dim_date` (`NK_date`),
  CONSTRAINT `FK2_console` FOREIGN KEY (`console`) REFERENCES `dim_console` (`NK_console`),
  CONSTRAINT `FK3_esrb_rating` FOREIGN KEY (`esrb_rating`) REFERENCES `dim_esrb_rating_scale` (`NK_esrb_rating_scale`),
  CONSTRAINT `FK4_pred_esrb_rating` FOREIGN KEY (`pred_esrb_rating`) REFERENCES `dim_esrb_rating_scale` (`NK_esrb_rating_scale`) ON DELETE RESTRICT ON UPDATE RESTRICT
);
''')<jupyter_output><empty_output><jupyter_text>C. Populate Dimensions   i. dim_esrb_rating_scale - A chave primária é uma Natural Key;- Possui different roles;- Possui uma Slowly Changing Dimension do tipo 3.[Voltar ao Índice](index)<jupyter_code># Applying Slowly Changing Dimensions type 3 by using "ON DUPLICATE KEY UPDATE" statement in the query

data = {
    'NK_esrb_rating_scale': ["RP", "EC", "E", "ET", "T", "M", "A", "ZZ"],
    'description': ["Rating Pending", "Early Childhood", "Everyone", "Everyone 10+", "Teen", "Mature", "Adult", "Not Rated"],
}
dim_esrb_rating_scale = pd.DataFrame(data)

for i in range(len(dim_esrb_rating_scale)):
    row = dim_esrb_rating_scale.loc[i,:]
    query = f'''
    INSERT INTO dim_esrb_rating_scale (NK_esrb_rating_scale, description)
    VALUES("{row["NK_esrb_rating_scale"]}","{row["description"]}")
    ON DUPLICATE KEY UPDATE
    NK_esrb_rating_scale = "{row["NK_esrb_rating_scale"]}",
    previous_description = description,
    description = "{row["description"]}"
    '''
    mycursor.execute(query)
    
con.commit()<jupyter_output><empty_output><jupyter_text>ii. dim_console - A chave primária é uma Natural Key.[Voltar ao Índice](index)<jupyter_code>data = {
    'NK_console': [0, 1],
    'description': ["PS4", "PS4 & Xbox One"],
}
dim_console = pd.DataFrame(data)

for i in range(len(dim_console)):
    row = dim_console.loc[i,:]
    query = f'''
    INSERT INTO dim_console (NK_console, description)
    VALUES({row["NK_console"]},"{row["description"]}")
    '''
    mycursor.execute(query)
    
con.commit()<jupyter_output><empty_output><jupyter_text>iii. dim_date - A chave primária é uma Natural Key, sendo esta uma agregação do ano, mês e dia.[Voltar ao Índice](index)<jupyter_code>release_dates = pd.read_csv('data/release_dates.csv')
release_dates = release_dates.drop_duplicates(subset=['year', 'month', 'day'])
release_dates.reset_index(drop=True, inplace=True)

for i in range(len(release_dates)):
    row = release_dates.loc[i,:]
    NK_date = str(row["year"]).zfill(4) + str(row["month"]).zfill(2) + str(row["day"]).zfill(2)
    query = f'''
    INSERT INTO dim_date (NK_date, year, month, day)
    VALUES("{NK_date}","{row["year"]}", "{row["month"]}", "{row["day"]}")
    '''
    mycursor.execute(query)
    
con.commit()<jupyter_output><empty_output><jupyter_text>D. Populate Fact Table   i. fact_game **A tabela de factos fact_game representa os jogos**, cada linha possuí um jogo, como atributos possui o título, as características (valores booleanos que indicam se o jogo possui ou não determinada característica, por exemplo, blood), o custo de produção, a data de lançamento, a consola e a classificação ESRB. Ainda, para os dados de teste foram adicionados novos atributos prevenientes do cálculo de todos os indicadores de desempenho (KPIs), tais como, a predição do ESRB rating e o novo custo de produção. Já para os dados de treino, uma vez que não são target para o cálculo das KPIs, foram aplicadas novas transformações de modo ao seu valor não ficar a null.Antes de inserir os factos, **foram aplicadas algumas transformações**:- Junção dos dois novos datasets (release_dates.csv e production_costs.csv) ao dataset principal (train.csv);- Adição de três novas colunas para o cálculo das KPIs remetentes ao novo custo de produção [new_production_cost (total), new_marketing_cost e new_development_cost];- Adição da coluna de previsão (pred_esrb_rating) e dois counters para o cálculo das KPIs taxa de acerto e taxa de erro;- Uma vez apenas foram calculadas as KPIs para os jogos com data de lançamento igual ou superior a 2019, utilizamos o valor "-1" para os atributos numéricos a null e o valor "ZZZZxlen" (último valor da escala alfabética consoante o tamanho da string) para os atributos strings a null.[Voltar ao Índice](index)<jupyter_code>games = pd.read_csv('data/train.csv')
# Applying the same transformations in the data preparation phase but
# this time we aren't transforming the ESRB rating into a numeric scale
# because we want to save the original value as a NK (Natural Key)
# Rename column strong_janguage to strong_language
games.rename(columns={'strong_janguage': 'strong_language'}, inplace=True, errors='raise')
#
release_dates = pd.read_csv('data/release_dates.csv')
production_costs = pd.read_csv('data/production_costs.csv')

arrReleaseDates = []
arrReleaseYears = []
arrProductionCosts = []
arrMarketingCosts = []
arrDevelopmentCosts = []
arrNewProductionCosts = []
arrNewMarketingCosts = []
arrNewDevelopmentCosts = []

pred_year = 2019

for i in range(len(games)):
    row_rd = release_dates.loc[i,:]
    row_pc = production_costs.loc[i,:]
    release_date = str(row_rd["year"]).zfill(4) + str(row_rd["month"]).zfill(2) + str(row_rd["day"]).zfill(2)
    release_year = row_rd["year"]
    production_cost = row_pc['production_cost']
    marketing_cost = row_pc['marketing_cost']
    development_cost = row_pc['development_cost']
    arrReleaseDates.append(release_date)
    arrReleaseYears.append(release_year)
    arrProductionCosts.append(production_cost)
    arrMarketingCosts.append(marketing_cost)
    arrDevelopmentCosts.append(development_cost)
    new_production_cost = -1
    new_marketing_cost = -1
    new_development_cost = -1
    # Cálculo das KPIs
    # Redução de custos entre 20% e 24% porque a accuracy do modelo atingiu 87.54%
    if (release_year >= pred_year):
        new_production_cost = round(((production_cost * (1-0.20)) + (production_cost * (1-0.24))) / 2)
        new_marketing_cost =  round(((marketing_cost * (1-0.20)) + (marketing_cost * (1-0.24))) / 2)
        new_development_cost = round(((development_cost * (1-0.20)) + (development_cost * (1-0.24))) / 2)
    arrNewProductionCosts.append(new_production_cost)
    arrNewMarketingCosts.append(new_marketing_cost)
    arrNewDevelopmentCosts.append(new_development_cost)
    
games.insert(35, 'release_date', arrReleaseDates)
games.insert(35, 'release_year', arrReleaseYears)
games.insert(35, 'production_cost', arrProductionCosts)
games.insert(35, 'marketing_cost', arrMarketingCosts)
games.insert(35, 'development_cost', arrDevelopmentCosts)
games.insert(35, 'new_production_cost', arrNewProductionCosts)
games.insert(35, 'new_marketing_cost', arrNewMarketingCosts)
games.insert(35, 'new_development_cost', arrNewDevelopmentCosts)
# Save to a csv
games.to_csv('data/data_merged.csv', index=False, header=True)
model = joblib.load('models/MLP_Classifier.pkl')

for i in range(len(games)):
    game_counter = 1
    row = games.loc[i,:]
    title = row["title"].replace('"', "^")
    
    pred = 'ZZ'
    if row["release_year"] >= pred_year:
        newData = np.array([[row['no_descriptors'],row['strong_language'],row['blood_and_gore'],row['fantasy_violence'],
        row['blood'],row['mild_fantasy_violence'],row['strong_sexual_content'],row['sexual_themes'],row['intense_violence'],
        row['suggestive_themes'],row['violence'],row['simulated_gambling'],row['sexual_content'],row['language'],
        row['mild_blood'],row['mild_suggestive_themes'],row['crude_humor'],row['mild_violence'],row['mild_lyrics'],
        row['cartoon_violence'],row['alcohol_reference'],row['lyrics'],row['drug_reference'],row['use_of_alcohol'],
        row['partial_nudity'],row['nudity'],row['mild_cartoon_violence'],row['animated_blood']]])
        
        pred = model.predict(newData)[0];
        if pred == 0:
            pred = 'E'
        elif pred == 1:
            pred = 'ET'
        elif pred == 2:
            pred = 'T'
        elif pred == 3:
            pred = 'M'
    
    right_pred_counter = 0
    wrong_pred_counter = 0
    if pred == "ZZ":
        right_pred_counter = -1
        wrong_pred_counter = -1
    elif pred == row["esrb_rating"]:
        right_pred_counter = 1
    else:
        wrong_pred_counter = 1
    
    query = f'''
    INSERT INTO fact_game (release_date, console, esrb_rating, pred_esrb_rating, alcohol_reference, animated_blood, blood,
    blood_and_gore, cartoon_violence, crude_humor, drug_reference, fantasy_violence, intense_violence, language, lyrics,
    mature_humor, mild_blood, mild_cartoon_violence, mild_fantasy_violence, mild_language, mild_lyrics, mild_suggestive_themes,
    mild_violence, no_descriptors, nudity, partial_nudity, sexual_content, sexual_themes, simulated_gambling, strong_language,
    strong_sexual_content, suggestive_themes, use_of_alcohol, use_of_drugs_and_alcohol, violence, title, production_cost,
    marketing_cost, development_cost, new_production_cost, new_marketing_cost, new_development_cost, 
    right_pred_counter, wrong_pred_counter, game_counter)
    VALUES("{row["release_date"]}", "{row["console"]}", "{row["esrb_rating"]}", "{pred}", {row["alcohol_reference"]},
    {row["animated_blood"]}, {row["blood"]}, {row["blood_and_gore"]}, {row["cartoon_violence"]}, 
    {row["crude_humor"]}, {row["drug_reference"]}, {row["fantasy_violence"]}, {row["intense_violence"]},
    {row["language"]}, {row["lyrics"]}, {row["mature_humor"]}, {row["mild_blood"]}, {row["mild_cartoon_violence"]},
    {row["mild_fantasy_violence"]}, {row["mild_language"]}, {row["mild_lyrics"]}, {row["mild_suggestive_themes"]},
    {row["mild_violence"]}, {row["no_descriptors"]}, {row["nudity"]}, {row["partial_nudity"]}, {row["sexual_content"]},
    {row["sexual_themes"]}, {row["simulated_gambling"]}, {row["strong_language"]}, {row["strong_sexual_content"]},
    {row["suggestive_themes"]}, {row["use_of_alcohol"]}, {row["use_of_drugs_and_alcohol"]}, {row["violence"]},
    "{title}", {row["production_cost"]}, {row["marketing_cost"]}, {row["development_cost"]}, {row["new_production_cost"]},
    {row["new_marketing_cost"]}, {row["new_development_cost"]}, {right_pred_counter}, {wrong_pred_counter}, {game_counter})
    '''
    mycursor.execute(query)
    
con.commit()<jupyter_output><empty_output><jupyter_text>E. Create Views   i. view_game_prediction - As vistas materializadas (views) servem para que no momento da construção das dashboards tenhamos que fazer menos instruções Select ao Data Warehouse. Uma view é uma tabela que possui todos os dados de várias tabelas, incluindo a de factos e as suas dimensões, necessários para a construção das dashboards. Desta forma, o carregamento das dashboards torna-se mais rápido e mais satisfatório para o utilizador.[Voltar ao Índice](index)<jupyter_code>mycursor.execute('''
CREATE TABLE IF NOT EXISTS view_game_prediction (
    SELECT FG.console, FG.esrb_rating, FG.pred_esrb_rating, FG.right_pred_counter, FG.wrong_pred_counter,
    STR_TO_DATE(CONCAT(DD.year,'-',DD.month,'-',DD.day), '%Y-%m-%d') AS release_date,
    DD.year, DD.month, FG.production_cost, FG.marketing_cost, FG.development_cost, FG.new_production_cost,
    FG.new_marketing_cost, FG.new_development_cost, FG.game_counter, DC.description AS console_description
    FROM fact_game FG, dim_console DC, dim_date DD
    WHERE FG.release_date = DD.NK_date AND FG.console = DC.NK_console AND DD.year >= 2019
    ORDER BY release_date
);''')
mycursor.execute('''
ALTER TABLE view_game_prediction
ADD COLUMN `rowId` INT NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (`rowId`)
;''')
con.commit()
# Close MySQL server connection
mycursor.close()
con.close()
# If we don't want to extract the MLPClassifier Model, we can just simply get the pred of the entire dataset using this function
from sklearn.neural_network import MLPClassifier

# Load functions
def get_pred(games, year=2019):
    data = games[['no_descriptors', 'strong_language', 'blood_and_gore', 'fantasy_violence', 'blood',
                          'mild_fantasy_violence', 'strong_sexual_content', 'sexual_themes', 'intense_violence',
                          'suggestive_themes', 'violence', 'simulated_gambling', 'sexual_content', 'language',
                          'mild_blood', 'mild_suggestive_themes', 'crude_humor', 'mild_violence', 'mild_lyrics',
                          'cartoon_violence', 'alcohol_reference', 'lyrics', 'drug_reference', 'use_of_alcohol',
                          'partial_nudity', 'nudity', 'mild_cartoon_violence', 'animated_blood', 'esrb_rating', 'release_year']]
    mapp = {'E':0, 'ET':1, 'T':2, 'M':3}
    data['esrb_rating'] = data['esrb_rating'].map(mapp)
    train = data[data['release_year'] < year]
    test = data[data['release_year'] >= year]

    X_train, y_train = train.iloc[:,0:28], train.iloc[:,28]
    X_test, y_test = test.iloc[:,0:28], test.iloc[:,28]

    clf = MLPClassifier(random_state=0, max_iter=1000, activation='tanh', alpha=0.05, hidden_layer_sizes=(20,), learning_rate= 'adaptive', solver= 'adam')
    clf.fit(X_train, y_train);
    joblib.dump(clf,'model.pkl')
    pred = clf.predict(X_test)
    #print(f"MLP Accuracy: {clf.score(X_test,y_test)}")
    #
    df = pd.DataFrame(pred, columns=['pred_esrb_rating'], index=test.index)
    df['pred_esrb_rating'] = df['pred_esrb_rating'].astype(str)
    mapp = {'0':'E', '1':'ET', '2':'T', '3':'M'}
    df['pred_esrb_rating'] = df['pred_esrb_rating'].map(mapp)
    #
    df2 = pd.DataFrame("ZZ", columns=['sample'], index=train.index)
    df_pre = pd.concat([df, df2], ignore_index=False, axis=1)
    df_pre['pred_esrb_rating'] = df_pre['pred_esrb_rating'].fillna("ZZ")
    df_pre.drop('sample', axis=1, inplace=True) 
    return df_pre<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11134705936663282, 'fraction_numerical': 0.01381836087772751, 'mean_word_length': 4.365587846763541, 'pattern_counts': {'":': 1, '<': 43, '<?xml version=': 0, '>': 50, 'https://': 3, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 2, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '210807', 'license': 'MIT', 'n_tokens_mistral': 11517, 'n_tokens_neox': 10841, 'n_words': 3111, 'path': 'jupyter-notebooks/ETL.ipynb', 'repo_name': 'JoaoGuedes01/ESRB_Rating_Predictor_CRISPDM', 'size': '32518'}