포지션정보넣으면 승률+밴픽률순서로 출력

롤의 포지션별로 승률과 밴픽률을 합산하면 어떤 챔프가 좋은 챔프인지 알 수 있지 않을까?

라는 생각으로 만들어본 코드

 

만들면서 생각하지 못했던 부분은 ban_rate는 전체챔프가 다들어있어서 편향되는 경우가 많았다. 일단 값을 승률에 비해서 1/100로 줄였다.

 

from google.cloud import bigquery
from google.oauth2 import service_account
from google.cloud import storage
import ast
import pandas as pd
import numpy as np
import pyarrow

credentials = service_account.Credentials.from_service_account_file('admin.json')
bigquery_client = bigquery.Client(credentials=credentials, project='cslee-323908')
storage_client = storage.Client(credentials=credentials, project='cslee-323908')
def bigquery_select(qry) :
    query_job = bigquery_client.query(qry)
    results = query_job.result()
    final_rst = []
    for rst in results:
        final_rst.append(rst[0])
    return final_rst

def f2(x):
    return x[1]

def position_winban(position):
    for i in range(5):
        qry = f"SELECT player_{i}_championId from `게임정보테이블` where win_team = 100 and player_{i}_individualPosition = '{position}' and Duration >= 1500000"
        temp = bigquery_client.query(qry).to_dataframe()
        temp.rename(columns={f'player_{i}_championId': 'ChampionId'}, inplace=True)
        if i == 0 :
            win_100 = temp
        else:
            win_100 = pd.concat([win_100, temp])

        qry = f"SELECT player_{9-i}_championId from `게임정보테이블` where win_team = 200 and player_{9-i}_individualPosition = '{position}' and Duration >= 1500000"
        temp = bigquery_client.query(qry).to_dataframe()
        temp.rename(columns={f'player_{9-i}_championId': 'ChampionId'}, inplace=True)
        if i == 0:
            win_200 = temp
        else:
            win_200 = pd.concat([win_200, temp])

    winteam = pd.concat([win_100, win_200], axis = 0)

    for i in range(10):
        qry = f"SELECT player_{i}_championId from `게임정보테이블` where player_{i}_individualPosition = '{position}' and Duration >= 1500000"
        temp = bigquery_client.query(qry).to_dataframe()
        temp.rename(columns={f'player_{i}_championId': 'ChampionId'}, inplace=True)
        if i == 0:
            all = temp
        else:
            all = pd.concat([all, temp])


    # 픽된 챔피언 데이터프레임을 리스트로 변환
    winteam = np.array(winteam).flatten().tolist()
    all2 = np.array(all).flatten().tolist()

    win_rate = {}
    for k in set(all2):
        if winteam.count(k) > 100:
            win_rate[k] = ((winteam.count(k) / all2.count(k)) * 100)


    # target_game_data 가져와서 데이터프레임으로
    qry = "SELECT win_team, player_0_championId, player_1_championId, player_2_championId, player_3_championId, player_4_championId, player_5_championId, player_6_championId, player_7_championId, player_8_championId, player_9_championId from `cslee.target_game_data` "
    df = bigquery_client.query(qry).to_dataframe()

    # 픽된 챔피언 데이터프레임을 리스트로 변환
    df2 = df.drop('win_team', axis=1)
    df3 = np.array(df2)
    df4 = np.array(df3).flatten().tolist()

    rst_all = []

    blue_qry = "select blue_ban_list from `cslee.target_game_data` "
    result = bigquery_select(blue_qry)
    for i in result:
        rst_all = rst_all + ast.literal_eval(i)

    red_qry = "select red_ban_list from `cslee.target_game_data` "
    result = bigquery_select(red_qry)
    for i in result:
        rst_all = rst_all + ast.literal_eval(i)

    result = df4 + rst_all

    banpick_result = {}
    for k in set(result):
        banpick_result[k] = (((rst_all.count(k) + df4.count(k)) / (len(df4)/10)) * 1)

    qry = "select id, key from cslee.ChampData"
    champdata = bigquery_client.query(qry).to_dataframe()
    champname = champdata.drop('key', axis=1)
    allcham = champdata.drop('id', axis=1)
    allcham2 = np.array(allcham)
    chamlist = np.array(allcham2).flatten().tolist()
    champname = np.array(champname)
    champname = np.array(champname).flatten().tolist()

    score = {}
    for i,j in zip(chamlist,champname):
        try:
            score[j] = (win_rate[i] + banpick_result[i])
        except:
            pass
            # print('데이터가 없습니다.')

    answer = dict(sorted(score.items(), reverse=True, key=f2))


    return answer

print(position_winban('UTILITY'))

라이엇게임데이터를 가지고 챔프 분석을 하는 경우 트롤, 실력차이에 의해 게임이 쉽게 터져버린 경우를 생각해야되는데 그정도 여유가 없어서 25분 이상 게임을 가지고 분석하는 데에 사용하였다.

 

지금은 빅쿼리 데이터들을 모두 지워서 직접 코드를 돌려본 것을 첨부할 수는 없지만 당시 성공했던 스크린 샷 화면을 공유하겠습니다.