크롤링 데이터 전처리 (2)

지난 포스팅에서는 크롤링한 데이터를 전처리하여 타자, 투수테이블을 구성했다. 

이번 포스팅은 적재된 데이터를 바탕으로 player 정보가 담긴 테이블을 만들어보려고한다. 

 

방법은 sql자체에서 필요컬럼과 중복제거를 하는 방식과 전체 데이터를 가져온 후 데이터프레임에서 전처리하는 방식중 

전체 데이터를 가져온 후 데이터 프레임으로 전처리하는 과정으로 진행하려고한다. 

 

player 테이블은 다음과 같이 구성했다. 

create table if not exists player_info(
	player_id INT auto_increment not null ,
	player_name varchar(10) not null,
	player_birth DATE,
	primary Key (player_id)
)

 

선수 고유의 id값과 이름이 중복되는 경우가 있을 수 있어서 선수의 생일 정보까지 필요하고  팀정보는 시즌 중간에 팀이 바뀌는 경우가 있기 때문에 넣지 않았다. 

 

이제 데이터를 넣는 과정이다.  우선 전체 데이터를 가져온다. 

conn = pymysql.connect(host = host, user = user, passwd=passwd, db = db, charset='utf8', port = port,cursorclass=pymysql.cursors.DictCursor)
cur = conn.cursor()
sql = '''select * from batting_info
'''
cur.execute(sql)

result = cur.fetchall()
batting = pd.DataFrame(result)

sql = '''select * from pitching_info
'''
cur.execute(sql)

result = cur.fetchall()
pitching = pd.DataFrame(result)

conn.close()

batting, pitching 데이터 프레임에 각각 넣어둔 후 필요한 컬럼만 가져온다. 

 

# 투수, 타자 df에서 필요한 컬럼만 가져오기
batting = batting[['player_name','player_birth']].drop_duplicates(['player_name','player_birth'])
pitching = pitching[['player_name','player_birth']].drop_duplicates(['player_name','player_birth'])
player = pd.concat([batting,pitching])

각 경기마다 선수의 데이터가 있으므로 1차로 중복제거를 진행해준다. 그러고 나서 player 데이터프레임에 데이터를 넣어두고 테이블에 적재를 했는데 이상한점이 생겼다.  player 데이터프레임에서 중복제거를 했더니 중복제거된 케이스가 11개 가 존재했던것이다. 왜 그럴까 생각을 해봤는데 예상가는 원인은 크게 2개였다. 

 

1. 동명이인선수가 생일까지 같은경우
   
   이 경우는 중복제거를 이름과 생일 2가지 컬럼으로 진행해서 발생했을 것이라고 가정했고, 중복제거 기준을 팀 컬럼까지 확장시켜서 진행해봤다. 투수 173명, 타자 194명으로 팀 컬럼을 포함시켰음에도 똑같이 10건이나 많았다. 동명이인도 매우 적은 케이스인데 생일까지 같은 케이스가 같은팀인데다가 10명이나 될리가 없다고 판단했고 2번째 원인으로 넘어갔다. 
2. 투타를 모두 소화하는 선수가 있는 경우 
   
   메이저리그의 오타니선수를 보면 충분히 가능한 케이스였고 연장전에 가서 타자가 부족한 경우 투수가 타격을 하는 경우가 있기 때문에 충분히 그럴듯해보이는 경우였다. 
   아래의 쿼리를 사용해서 확인해본 결과 정확히 10명의 선수가 나왔다.
    

select a.player_name, a.player_birth from (
select pi2.player_name , pi2.player_birth  from batting_info bi left outer join pitching_info pi2 on bi.player_name  = pi2.player_name and bi.player_birth = pi2.player_birth
) a
where 1=1
and  a.player_name is not null
group by a.player_name, a.player_birth
;

 

 

이 선수들중 이상민 선수를 제외한 나머지 선수들은 타격기록이 없었고 이상민 선수는 4월 19일 경기에서 타격기록이 있었다. 해당경기는 12회까지 연장전이 진행되면서 투수까지 타격에 동원되었던 것이었다! 이로써 중복의 원인은 해결되었다. 사실 동명이인의 선수가 생일까지 같은 경우가 발생하면 게다가 팀까지 같은 경우가 발생하면 현재 존재하는 데이터로는 구별할 방법이 없어서 매우 난감했을 것 같은데 실제 중복원인이 투수가 타격을 한 케이스여서 정말 다행이다.

 

 

마치며

다음 포스팅은 요일별 데이터를 주별데이터로 변환 한 뒤 기록을 집계하여 weekly테이블에 적재해보려고한다.