def pp(self, table_name, ref_table=None):
# instantiate database
db = DB()
db.initialise()
# instantiate dictionary and replace
g_dict = GameDictionary()
temp_df = self.original_df
temp_df['player'] = temp_df['player'].replace(g_dict.get_dict('player'))
temp_df[['champion', 'ban1', 'ban2', 'ban3', 'ban4', 'ban5']] = temp_df[['champion', 'ban1', 'ban2', 'ban3', 'ban4', 'ban5']].replace(g_dict.get_dict('champion'))
temp_df['team'] = temp_df['team'].replace(g_dict.get_dict('team'))
temp_df['league'] = temp_df['league'].replace(g_dict.get_dict('league'))
temp_df['date'] = temp_df['date'].apply(stringDate_to_strft)
league_list = ['LLN', 'CLS', 'IEM', 'GPL', 'DC', 'LFL']
temp_df = temp_df[~temp_df['league'].isin(league_list)].reset_index(drop=True)
if table_name == 'player':
temp_df = temp_df.dropna(subset=['player'])
player_list = list(temp_df['player'].unique())
return pd.DataFrame(data=player_list, columns=['player_name'])
elif table_name == 'champion':
temp_df = temp_df.dropna(subset=['champion'])
champ_table = pd.DataFrame(data=list(temp_df['champion'].unique()), columns=['champion_name'])
return champ_table
elif table_name == 'match':
ref_table = db.extend_idToValue(ref_table, 'team', 'home_team_id', rename={'team_name':'team_1'})
ref_table = db.extend_idToValue(ref_table, 'team', 'away_team_id', rename={'team_name':'team_2'})
ref_table = db.extend_idToValue(ref_table, 'league', 'league_id')
self.reference_table = ref_table
for idx, val in ref_table.copy().iterrows():
gameDivide = temp_df[(temp_df['date'].isin([(pd.to_datetime(val['date']) + timedelta(days=x)).strftime('%Y-%m-%d') for x in self.date_range])) & (temp_df['league']==val['league_name'])]
for gameID in gameDivide['gameid'].unique():
each_game = gameDivide[gameDivide['gameid']==gameID]
if each_game.empty:
continue
if (val['team_1'] in each_game['team'].unique()) & (val['team_2'] in each_game['team'].unique()):
if each_game['patch'].unique()[0]:
patch = each_game['patch'].unique()[0]
else:
patch = None
ref_table.loc[idx, 'patch'] = patch
ref_table = ref_table.where(pd.notnull(ref_table), None)
return ref_table.reset_index(drop=True)
elif table_name == 'set_match':
ref_table = db.extend_idToValue(ref_table, 'match')
ref_table = db.extend_idToValue(ref_table, 'team', 'home_team_id', rename={'team_name':'team_1'})
ref_table = db.extend_idToValue(ref_table, 'team', 'away_team_id', rename={'team_name':'team_2'})
ref_table = db.extend_idToValue(ref_table, 'league', 'league_id')
self.reference_table = ref_table
for idx, val in ref_table.copy().iterrows():
# 진도율 확인용
if idx % 3000 == 0:
print(idx)
gameDivide = temp_df[(temp_df['date'].isin([(pd.to_datetime(val['date']) + timedelta(days=x)).strftime('%Y-%m-%d') for x in self.date_range])) & (temp_df['league']==val['league_name'])]
for gameID in gameDivide['gameid'].unique():
each_game = gameDivide[gameDivide['gameid']==gameID]
if each_game.empty:
continue
if (val['team_1'] in each_game['team'].unique()) & (val['team_2'] in each_game['team'].unique()) & (val['set_number']==each_game['game'].unique()[0]):
if each_game['ckpm'].unique()[0]:
ckpm = each_game['ckpm'].unique()[0]
else:
ckpm = None
if each_game['gamelength'].unique()[0]:
gamelength = each_game['gamelength'].unique()[0]
else:
gamelength = None
ref_table.loc[idx, 'ckpm'] = ckpm
ref_table.loc[idx, 'game_length'] = gamelength
ref_table = ref_table.where(pd.notnull(ref_table), None)
return ref_table.reset_index(drop=True)
elif table_name == 'set_match_info_by_team':
ref_table = db.extend_idToValue(ref_table, 'set_match')
ref_table = db.extend_idToValue(ref_table, 'match')
ref_table = db.extend_idToValue(ref_table, 'team', 'home_team_id', rename={'team_name':'team_1'})
ref_table = db.extend_idToValue(ref_table, 'team', 'away_team_id', rename={'team_name':'team_2'})
ref_table = db.extend_idToValue(ref_table, 'team', 'team_id', rename={'team_name':'team'})
ref_table = db.extend_idToValue(ref_table, 'league', 'league_id')
self.reference_table = ref_table
# db connection 열렸나 체크용
player_dict = db.get_dict('player')['valueToID']
for idx, val in ref_table.copy().iterrows():
# 진도율 확인용
if idx % 3000 == 0:
print(idx)
gameDivide = temp_df[(temp_df['date'].isin([(pd.to_datetime(val['date']) + timedelta(days=x)).strftime('%Y-%m-%d') for x in self.date_range])) & (temp_df['league']==val['league_name'])]
for gameID in gameDivide['gameid'].unique():
each_game = gameDivide[gameDivide['gameid']==gameID]
if each_game.empty:
continue
if (val['team_1'] in each_game['team'].unique()) & (val['team_2'] in each_game['team'].unique()) & (val['set_number']==each_game['game'].unique()[0]):
for eachTeam in each_game['team'].unique():
each_team_df = each_game[each_game['team']==eachTeam]
if each_team_df.shape[0] != 6:
print('Shape[0] is not 6')
print(each_team_df)
continue
if each_team_df[each_team_df['position']=='team']['team'].values[0] == val['team']:
for _, val2 in each_team_df.iterrows():
if val2['position'] == 'top':
ref_table.loc[idx, 'top_player'] = val2['player']
elif val2['position'] == 'jng':
ref_table.loc[idx, 'jg_player'] = val2['player']
elif val2['position'] == 'mid':
ref_table.loc[idx, 'mid_player'] = val2['player']
elif val2['position'] == 'bot':
ref_table.loc[idx, 'bot_player'] = val2['player']
elif val2['position'] == 'sup':
ref_table.loc[idx, 'sup_player'] = val2['player']
else:
ref_table.loc[idx, 'result'] = val2['result']
ref_table.loc[idx, 'side'] = val2['side']
ref_table.loc[idx, 'ban1'] = val2['ban1']
ref_table.loc[idx, 'ban2'] = val2['ban2']
ref_table.loc[idx, 'ban3'] = val2['ban3']
ref_table.loc[idx, 'ban4'] = val2['ban4']
ref_table.loc[idx, 'ban5'] = val2['ban5']
ref_table.loc[idx, 'team_kills'] = val2['teamkills']
ref_table.loc[idx, 'team_deaths'] = val2['teamdeaths']
ref_table.loc[idx, 'team_double'] = val2['doublekills']
ref_table.loc[idx, 'team_triple'] = val2['triplekills']
ref_table.loc[idx, 'team_quadra'] = val2['quadrakills']
ref_table.loc[idx, 'team_penta'] = val2['pentakills']
ref_table.loc[idx, 'team_first_blood'] = val2['firstblood']
ref_table.loc[idx, 'team_kpm'] = val2['team kpm']
ref_table.loc[idx, 'team_first_dragon'] = val2['firstdragon']
ref_table.loc[idx, 'team_dragon_kills'] = val2['dragons']
ref_table.loc[idx, 'team_elder_kills'] = val2['elders']
ref_table.loc[idx, 'team_first_rift'] = val2['firstherald']
ref_table.loc[idx, 'team_rift_kills'] = val2['heralds']
ref_table.loc[idx, 'team_first_baron'] = val2['firstbaron']
ref_table.loc[idx, 'team_baron_kills'] = val2['barons']
ref_table.loc[idx, 'team_first_tower'] = val2['firsttower']
ref_table.loc[idx, 'team_tower_kills'] = val2['towers']
ref_table.loc[idx, 'team_first_midtower'] = val2['firstmidtower']
ref_table.loc[idx, 'team_first_three_towers'] = val2['firsttothreetowers']
ref_table.loc[idx, 'team_inhib_kills'] = val2['inhibitors']
ref_table.loc[idx, 'team_total_gold'] = val2['totalgold']
ref_table.loc[idx, 'team_earned_gold'] = val2['earnedgold']
ref_table.loc[idx, 'team_minion_kills'] = val2['minionkills']
ref_table.loc[idx, 'team_monster_kills'] = val2['monsterkills']
ref_table.loc[idx, 'team_goldat10'] = val2['goldat10']
ref_table.loc[idx, 'team_csat10'] = val2['csat10']
ref_table.loc[idx, 'team_golddiffat10'] = val2['golddiffat10']
ref_table.loc[idx, 'team_csdiffat10'] = val2['csdiffat10']
ref_table.loc[idx, 'team_goldat15'] = val2['goldat15']
ref_table.loc[idx, 'team_csat15'] = val2['csat15']
ref_table.loc[idx, 'team_golddiffat15'] = val2['golddiffat15']
ref_table.loc[idx, 'team_csdiffat15'] = val2['csdiffat15']
ref_table = ref_table.where(pd.notnull(ref_table), None)
player_dict = db.get_dict('player')['valueToID']
ref_table['top_player_id'] = ref_table['top_player'].replace(player_dict)
ref_table['jg_player_id'] = ref_table['jg_player'].replace(player_dict)
ref_table['mid_player_id'] = ref_table['mid_player'].replace(player_dict)
ref_table['bot_player_id'] = ref_table['bot_player'].replace(player_dict)
ref_table['sup_player_id'] = ref_table['sup_player'].replace(player_dict)
champion_dict = db.get_dict('champion')['valueToID']
ref_table['ban1_id'] = ref_table['ban1'].replace(champion_dict)
ref_table['ban2_id'] = ref_table['ban2'].replace(champion_dict)
ref_table['ban3_id'] = ref_table['ban3'].replace(champion_dict)
ref_table['ban4_id'] = ref_table['ban4'].replace(champion_dict)
ref_table['ban5_id'] = ref_table['ban5'].replace(champion_dict)
ref_table['wdl'] = ref_table['result'].apply(set_match_info_result_to_wdl)
return ref_table.reset_index(drop=True)
elif table_name == 'set_match_player_performance':
ref_table = db.extend_idToValue(ref_table, 'set_match_info_by_team')
ref_table = db.extend_idToValue(ref_table, 'player')
ref_table = db.extend_idToValue(ref_table, 'set_match')
ref_table = db.extend_idToValue(ref_table, 'match')
ref_table = db.extend_idToValue(ref_table, 'team', 'home_team_id', rename={'team_name':'team_1'})
ref_table = db.extend_idToValue(ref_table, 'team', 'away_team_id', rename={'team_name':'team_2'})
ref_table = db.extend_idToValue(ref_table, 'team', 'team_id', rename={'team_name':'team'})
ref_table = db.extend_idToValue(ref_table, 'league', 'league_id')
self.reference_table = ref_table
for idx, val in ref_table.copy().iterrows():
gameDivide = temp_df[(temp_df['date'].isin([(pd.to_datetime(val['date']) + timedelta(days=x)).strftime('%Y-%m-%d') for x in self.date_range])) & (temp_df['league']==val['league_name'])]
for gameID in gameDivide['gameid'].unique():
each_game = gameDivide[gameDivide['gameid']==gameID]
if each_game.empty:
continue
if (val['team_1'] in each_game['team'].unique()) & (val['team_2'] in each_game['team'].unique()) & (val['set_number']==each_game['game'].unique()[0]):
if each_game.shape[0] != 12:
print('Shape[0] is not 12')
print(each_game)
continue
each_player = each_game[each_game['player']==val['player_name']]
if each_player.empty:
continue
ref_table.loc[idx, 'kills'] = each_player['kills'].values[0]
ref_table.loc[idx, 'deaths'] = each_player['deaths'].values[0]
ref_table.loc[idx, 'assists'] = each_player['assists'].values[0]
ref_table.loc[idx, 'champion'] = each_player['champion'].values[0]
ref_table.loc[idx, 'double'] = each_player['doublekills'].values[0]
ref_table.loc[idx, 'triple'] = each_player['triplekills'].values[0]
ref_table.loc[idx, 'quadra'] = each_player['quadrakills'].values[0]
ref_table.loc[idx, 'penta'] = each_player['pentakills'].values[0]
ref_table.loc[idx, 'first_blood'] = each_player['firstblood'].values[0]
ref_table.loc[idx, 'total_gold'] = each_player['totalgold'].values[0]
ref_table.loc[idx, 'earned_gold'] = each_player['earnedgold'].values[0]
ref_table.loc[idx, 'total_cs'] = each_player['total cs'].values[0]
ref_table.loc[idx, 'minion_kills'] = each_player['minionkills'].values[0]
ref_table.loc[idx, 'monster_kills'] = each_player['monsterkills'].values[0]
ref_table.loc[idx, 'monster_kills_own_jg'] = each_player['monsterkillsownjungle'].values[0]
ref_table.loc[idx, 'monster_kills_enemy_jg'] = each_player['monsterkillsenemyjungle'].values[0]
ref_table.loc[idx, 'cspm'] = each_player['cspm'].values[0]
ref_table.loc[idx, 'goldat10'] = each_player['goldat10'].values[0]
ref_table.loc[idx, 'golddiffat10'] = each_player['golddiffat10'].values[0]
ref_table.loc[idx, 'goldat15'] = each_player['goldat15'].values[0]
ref_table.loc[idx, 'golddiffat15'] = each_player['golddiffat15'].values[0]
ref_table.loc[idx, 'csat10'] = each_player['csat10'].values[0]
ref_table.loc[idx, 'csdiffat10'] = each_player['csdiffat10'].values[0]
ref_table.loc[idx, 'csat15'] = each_player['csat15'].values[0]
ref_table.loc[idx, 'csdiffat15'] = each_player['csdiffat15'].values[0]
if idx % 10000 == 0:
print(idx)
try:
ref_table = ref_table.where(pd.notnull(ref_table), None)
ref_table = ref_table.dropna(subset=['player_id']).reset_index(drop=True)
ref_table = ref_table[ref_table['player_name'] != 'unknown player'].reset_index(drop=True)
ref_table['champion_id'] = ref_table['champion'].replace(db.get_dict('champion')['valueToID'])
except:
db = DB()
db.initialise()
ref_table = ref_table.where(pd.notnull(ref_table), None)
ref_table = ref_table.dropna(subset=['player_id']).reset_index(drop=True)
ref_table = ref_table[ref_table['player_name'] != 'unknown player'].reset_index(drop=True)
ref_table['champion_id'] = ref_table['champion'].replace(db.get_dict('champion')['valueToID'])
return ref_table.reset_index(drop=True)
