트위치 조축회 데이터 시각화

트위치 방송중에 매주 토요일마다 피파온라인4 게임으로 스트리머들끼리 모여서 시합하는 컨텐츠의 데이터 시각화를 진행해보았다.

 

10명 스트리머 분들 모두 재미있고 좋아하는 분들이라 해당 컨텐츠가 오래 진행되길 바라면서 팬심으로 제작했다.

 

 

결과물

경기기록 대시보드

https://public.tableau.com/app/profile/.46525810/viz/_16779561031200/1?publish=yes

조축회 데이터

 

이적시장 대시보드

https://public.tableau.com/app/profile/.46525810/viz/__16780204519780/1?publish=yes 

조축회_거래

 

 

 

제작과정

---

이번에는 넥슨 - 피파온라인4 API를 사용해서 멤버들의 데이터를 가져왔다.

 

https://developers.nexon.com/fifaonline4

개발자센터

 

 

데이터 가져오기

api_key = '발급받은 API KEY 입력'
ua = UserAgent(verify_ssl=False)

headers = {
    'Authorization' : api_key,
    "User-Agent" :  ua.random
}

 

함수

#개인정보 가져오기
def fifa_personal_player(nickname): 
    url = f'https://api.nexon.co.kr/fifaonline4/v1.0/users?nickname={nickname}'    
    return requests.get(url, headers = headers)

#구매 내역 가져오기
def fifa_buy_history(accessId, tradetype='buy', offset = 0, limit=50): 
    url = f'https://api.nexon.co.kr/fifaonline4/v1.0/users/{accessId}/markets?tradetype={tradetype}&offset={offset}&limit={limit}'
    return requests.get(url, headers=headers)

#판매 내역 가져오기
def fifa_sell_history(accessId, tradetype='sell', offset = 0, limit=50):
    url = f'https://api.nexon.co.kr/fifaonline4/v1.0/users/{accessId}/markets?tradetype={tradetype}&offset={offset}&limit={limit}'
    return requests.get(url, headers=headers)

#매치 ID 가져오기
def fifa_rank_match_history(accessid, matchtype, offset=0, limit=20):
    '''
    matchtype : 40 : 친선경기, 50 : 랭크경기
    '''
    url = f'https://api.nexon.co.kr/fifaonline4/v1.0/users/{accessid}/matches?matchtype={matchtype}&offset={offset}&limit={limit}'
    return requests.get(url, headers=headers)

#매치 세부정보 가져오기
def fifa_match_info(matchid):
    url = f'https://api.nexon.co.kr/fifaonline4/v1.0/matches/{matchid}'
    return requests.get(url, headers=headers)

#피파온라인4 선수 DB
def fifa_get_players_code():
    url = 'https://static.api.nexon.co.kr/fifaonline4/latest/spid.json'
    return requests.get(url, headers=headers)

#피파온라인4 포지션 DB
def fifa_get_poisition_code():
    url = 'https://static.api.nexon.co.kr/fifaonline4/latest/spposition.json'
    return requests.get(url, headers=headers)

#피파온라인4 시즌 DB
def fifa_get_season_code():
    url = 'https://static.api.nexon.co.kr/fifaonline4/latest/seasonid.json'
    return requests.get(url, headers=headers)

#CODE -> 한글표시
def code_to_kr(value, col):
    df = fifa_dict[col]
    try:
      return df[df['id'] == value]['name'].values[0]
    except:
      return ''

#시즌 코드는 선수id에 들어가 있어서 분리하기 위한 함수
def div_season_code(value):
    return int(str(value)[:3])

# 사전 반전
def swap_dict(dicts):
    return {v:k for k,v in dicts.items()}
    
#선수 개인 스테이터스 만들기
def make_players_df(df):
    
    status = pd.DataFrame()
    for idx, g_player in enumerate(df['status']):
        tmp = pd.DataFrame(g_player,index=[idx])
        status = pd.concat([status, tmp])
    
    result = df.merge(status, left_index=True, right_index=True)
    result.drop('status', axis=1, inplace=True)
    
    result['season'] = result['spId'].apply(div_season_code)
    result['spId'] = result['spId'].apply(code_to_kr, args=['players'])
    result['season'] = result['season'].apply(code_to_kr, args=['season'])
    result['spPosition'] = result['spPosition'].apply(code_to_kr, args=['positions'])
    result = result[result['spPosition'] != 'SUB']
    
    result = result[['spId','season', 'spPosition', 'spGrade', 'shoot', 'effectiveShoot', 'assist',
       'goal', 'dribble', 'intercept', 'defending', 'passTry', 'passSuccess',
       'dribbleTry', 'dribbleSuccess', 'ballPossesionTry',
       'ballPossesionSuccess', 'aerialTry', 'aerialSuccess', 'blockTry',
       'block', 'tackleTry', 'tackle', 'yellowCards', 'redCards', 'spRating']]
    
    result.sort_values('spRating', ascending=False, inplace=True)
    
    return result

#매치 정보 가져오기
def game_playerDTO(json_data, morning_soccer_data):

  start_day = pd.to_datetime('2023-01-28T20:00:00')
  end_time = pd.to_datetime('2023-01-29T05:00:00')

  # 조축회끼리한 경기가 아닌경우
  if (json_data['matchInfo'][0]['accessId'] not in member_accessid.values()) or (json_data['matchInfo'][1]['accessId'] not in member_accessid.values()):
      return morning_soccer_data

  # 게임 바로 나갔을 경우
  if (json_data['matchInfo'][0]['matchDetail']['dribble'] < 20) or (json_data['matchInfo'][1]['matchDetail']['dribble'] < 20):
    return morning_soccer_data
  

  players_1 = pd.DataFrame(json_data['matchInfo'][0]['player'])
  players_2 = pd.DataFrame(json_data['matchInfo'][1]['player'])

  players_1 = make_players_df(players_1)
  players_2 = make_players_df(players_2)

  match_type = '비공식'
  week = 0
  game_date = pd.to_datetime(json_data['matchDate'])
  while True:
    if match_type == '비공식':
      if ((start_day + datetime.timedelta(days=7*week)) < game_date) and ((end_time + datetime.timedelta(days=7*week)) > game_date):
          match_type = '공식'
          
    diff = game_date - (start_day + datetime.timedelta(days=7*week))
    if diff.days < 0:
        break
    week+=1

  if week==0:
    return morning_soccer_data
    
  players_1['weeks'] = str(week)+'주차'
  players_2['weeks'] = str(week)+'주차'
  
  players_1['match_type'] = match_type
  players_2['match_type'] = match_type
  
  players_1['match_result'] = json_data['matchInfo'][0]['matchDetail']['matchResult']
  players_2['match_result'] = json_data['matchInfo'][1]['matchDetail']['matchResult']

  
  players_1['streamer'] = swap_dict(member_ids)[json_data['matchInfo'][0]['accessId']]
  players_2['streamer'] = swap_dict(member_ids)[json_data['matchInfo'][1]['accessId']]

  players_1['matchId'] = json_data['matchId']
  players_2['matchId'] = json_data['matchId']

  match_players = pd.concat([players_1, players_2])
  morning_soccer_data = pd.concat([morning_soccer_data, match_players])

  return morning_soccer_data.reset_index(drop=True)

def clacWeeks(value):
    start_day = pd.to_datetime('2023-01-28T20:00:00')
    end_time = pd.to_datetime('2023-01-29T05:00:00')

    week = 0
    while True:
      diff = value - (start_day + datetime.timedelta(days=7*week))
      if diff.days < 0:
          break
      week+=1

    if week==0:
      return 0
    return str(week) + '주차'

# 선수 이미지 가져오기 (액션이미지)
def getImgURL(value):
  # player_code = str(value)[3:]
  # pid = re.sub('^[0]*', '', player_code)
  # return f'https://fo4.dn.nexoncdn.co.kr/live/externalAssets/common/players/p{pid}.png'
  return f'https://fo4.dn.nexoncdn.co.kr/live/externalAssets/common/playersAction/p{value}.png'

# 거래내역 정보 정리
def gmae_tradeDTO(buy_data, sell_data, buy_df, sell_df, streamer):
  buy_history = pd.json_normalize(buy_data)
  sell_history = pd.json_normalize(sell_data)

# 새로운 데이터 필터링
  if len(buy_df) != 0:
    buy_history = buy_history[~buy_history['saleSn'].isin(buy_df['saleSn'].values)]
  if len(sell_df) != 0:
    sell_history = sell_history[~sell_history['saleSn'].isin(sell_df['saleSn'].values)]

  if len(buy_history) != 0:
    buy_history['tradeDate'] = pd.to_datetime(buy_history['tradeDate'])
    buy_history['img_url'] = buy_history['spid'].apply(getImgURL)
    buy_history['season'] = buy_history['spid'].apply(div_season_code)
    buy_history['season'] = buy_history['season'].apply(code_to_kr, args=['season'])
    buy_history['spid'] = buy_history['spid'].apply(code_to_kr, args=['players'])
    buy_history['week'] = buy_history['tradeDate'].apply(clacWeeks)
    buy_history['streamer'] = swap_dict(member_accessid)[streamer]

  if len(sell_history) != 0:
    sell_history['tradeDate'] = pd.to_datetime(sell_history['tradeDate'])  
    sell_history['img_url'] = sell_history['spid'].apply(getImgURL)
    sell_history['season'] = sell_history['spid'].apply(div_season_code)
    sell_history['season'] = sell_history['season'].apply(code_to_kr, args=['season'])
    sell_history['spid'] = sell_history['spid'].apply(code_to_kr, args=['players'])
    sell_history['week'] = sell_history['tradeDate'].apply(clacWeeks)
    sell_history['streamer'] = swap_dict(member_accessid)[streamer]

  buy_history = pd.concat([buy_df, buy_history]).sort_values('tradeDate').reset_index(drop=True)
  sell_history = pd.concat([sell_df, sell_history]).sort_values('tradeDate').reset_index(drop=True)

  return buy_history, sell_history

 

저장 데이터 가져오기

#fifa4 정보, 조축회 멤버 정보 가져오기
with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/member_accessid.pkl', "rb") as f:
    member_accessid = pickle.load(f)

with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/match_ids.pkl', 'rb') as f:
    matchs_record = pickle.load(f)

with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/buy_history.pkl', "rb") as f:
    buy_history = pickle.load(f)

with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/sell_history.pkl', "rb") as f:
    sell_history = pickle.load(f)

morning_soccer_data = pd.read_csv('/content/drive/MyDrive/ds_study/데이터 분석(피파)/morning_soccer_player_status.csv', index_col=0)

#선수 code-이름
player_df = pd.json_normalize(fifa_get_players_code().json())

#포지션 code-명칭
position_df = pd.json_normalize(fifa_get_poisition_code().json())
position_df = position_df.rename(columns={'spposition' : 'id', 'desc' : 'name'})

#시즌 code-시즌이름
season_df = pd.json_normalize(fifa_get_season_code().json())
season_df = season_df.rename(columns={'seasonId' : 'id', 'className' : 'name'})

fifa_dict = {'players' : player_df, 'positions' : position_df, 'season' : season_df}

#members = ['리오넬동숙', '자므도', '트루감독슈틸리케', '아자방', '감자나라배준식', 'v침대위의메시v', '단군', '자므도', '봉주르송도', '룩삼므','차돌짬뻥철면수심' ]
# members_id = []
# for member in members:
#     members_id.append(fifa_personal_player(member).json()['accessId'])
# member_ids = {members[i] : members_id[i] for i in range(len(members))}

멤버, 매치, 이적시장 이용내역은 기존에 탐색했던것들을 다시 불러오고 사전같은경우 업데이트시 새로운 정보가 생기기 때문에 API에서 새로 받아오게끔 설정하였다.

 

 

매치 탐색

#새로운 게임 정보 있는지 받아오기
# matchs_record = []
search_games = []
for m_id in member_accessid.values():
    tmp = fifa_rank_match_history(m_id, 40).json()
    for t in tmp:
        if t not in matchs_record:
            search_games.append(t)
        else:
            continue

search_games = list(set([ x for x in search_games if x not in matchs_record]))
matchs_record += search_games

with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/match_ids.pkl', 'wb') as f:
  pickle.dump(matchs_record, f)
  
  
  #새로운 매치의 정보 가져오기
  # morning_soccer_data = pd.DataFrame()
for search_id in tqdm(search_games):
    try:
        target = fifa_match_info(search_id).json()
        morning_soccer_data = game_playerDTO(target, morning_soccer_data)
    except Exception as e:
        print(e)

 

이적시장 내역 탐색

#buy_history = pd.DataFrame()
#sell_history = pd.DataFrame()


# !!!! 마지막 업데이트일 수정해줘야함 이건 어떻게 하지...
#end_time = pd.to_datetime('2023-01-29T05:00:00')
end_time = pd.to_datetime('2023-03-06T05:00:00')


for search_id in tqdm(member_accessid.values()):
  offset = 0 
  buy_flag = True
  sell_flag = True  

  while True:
    # try:
    if (buy_flag == False) and (sell_flag == False):
      break

    if buy_flag:
      tmp = fifa_buy_history(search_id, offset=offset).json()

      if len(tmp) > 1 :
        buy_target = tmp
        if pd.to_datetime(buy_target[0]['tradeDate']) < end_time:  # 받아온 데이터의 날짜가 기준일보다 작으면 False
          buy_flag = False
      else: # 받아온 데이터가 없으면 False
        buy_flag = False

    if sell_flag:
      tmp = fifa_sell_history(search_id, offset=offset).json()

      if len(tmp) > 1 :
        sell_target = tmp
        if pd.to_datetime(sell_target[0]['tradeDate']) < end_time:
          sell_flag = False
      else:
        sell_flag = False

    buy_history, sell_history = gmae_tradeDTO(buy_target, sell_target, buy_history, sell_history, search_id)
    offset+=50

    # except Exception as e:
    #     print(e)
      
    time.sleep(1)

with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/buy_history.pkl', "wb") as f:
  pickle.dump(buy_history, f)

with open('/content/drive/MyDrive/ds_study/데이터 분석(피파)/sell_history.pkl', "wb") as f:
  pickle.dump(sell_history, f)

buy_history['trade_type'] = '구매'
sell_history['trade_type'] = '판매'

trade_history = pd.concat([buy_history, sell_history]).sort_values('tradeDate').reset_index(drop=True)

trade_history.to_csv('/content/drive/MyDrive/ds_study/데이터 분석(피파)/trade_history.csv')

이 부분이 조금 문제인데 단순하게 코딩하다보니까 조금 지저분하게 된 느낌이....

 

여튼 이렇게 API를 통해서 정보를 받아와서 구글드라이브에 CSV파일로 저장해두었고 이를 Tableau를 사용해서 시각화 하였다.