공공데이터 api 활용 데이터 연결 및 관계 설정
큰 틀은 Tableau에서 MySQL을 연결하여 실시하는 것이다. 기본적으로는 Tableau Desktop을 활용하여 여러 테이블을 가져와 관계설정을 위해 join을 진행하면 된다. 하지만 이번에는 Tableau Prep Builder를 사용하여 관계 및 기본적인 정제를 실시한다.
1. 데이터 연결
먼저 데이터베이스를 연결하면 6개의 테이블이 생성되어 있다. 4개 정도만 사용할 것이지만 6개가 불러와졌다.
2. 관계설정
먼저 orginfo(기관 정보)와 programinfo(기관 및 프로그램 정보)의 관계(join)를 설정한다. join은 programinfo를 바탕으로 orginfo를 만들었으므로 inner join을 활용하면 된다. join할 컬럼은 orgName이다. 드래그하게 되면 다음과 같이 나타난다.
이후 조인한 데이터를 정리한다. id가 숫자형으로 되어있기 때문에 문자열로 변경해주고 orgAddress, programinfo의 orgName삭제, 분석에 사용하지 않을 매니져이름, 이메일, 전화번호 모두 삭제한다. 그리고 row number를 위해 만든 idx열의 데이터 타입도 문자열로 변경했다.
그다음은 grade_dummy데이터와 조인을 실시했다. grade_dummy 테이블은 sql을 통해 초중고 프로그램을 더미처리해놓은 테이블이다. 조인은 inner join을 실시하고 조인에 사용할 필드는 idx이다.
조인한 데이터를 정리한다. grade_dummy테이블의 orgName, target 필드를 삭제하고 row_index필드의 이름을 변경했다.
마지막으로 회차와 관련된 datelist와의 조인을 실시한다. datejoin과 join할 필드는 pgCode이다. 그리고 left outer join을 실시한다. 이유는 pgCode는 프로그램들 중 실시된 데이터만 회차가 존재하기 때문이다.
조인 후에 데이터 불필요한 필드를 제거하고, 필드명을 한국어로 변경했다.
도로명 주소의 경우 도/광역시, 시/구/군 정도로 분리했다. 시작일, 종료일 등은 날짜형 데이터로, 시구군 등의 데이터는 지리를 나타내는 데이터 타입으로 변경하였다.
3. 데이터 정제
데이터를 살펴보니 roadAddress에 값이 이상한게 있다. 그리고 주소를 시, 도, 등으로 분할했는데, 이상하게 분리된 것들이 있었다. 그래서 다시 찬찬히 정제를 진행했다.
road address에서 모두 앞에 대한민국을 붙여준다. 그리고 한국어가 아닌 글자로 되어있는 것들은 더블클릭하여 지워준다.
그리고 대한민국을 모두 입력해주고 roadAddress안에 대한민국이라는 단어가 포함된 문자열만 참으로 표현해봤더니 참 밖에 안나온다. 2972개가 모두 대한민국이 앞에 붙어있단 이야기.
일단 1차 정제 잘 끝났고, 이제 roadAddress를 분리한다. 대한민국 / 경기도 / 김포시 이런 식으로
값분할 -> 사용자 지정분할 -> 스페이스를 활용해 1차 분할한다.
이번엔 경기도, 김포시 등으로 분리한다. 분리시 이상한 애들이 있기 때문에 그 데이터들은 손으로 직접 변경한다. 시군구인데 청소년수련관이라고 되어있는애들, 차량등록사업소라고 되어있는 애들 등등....
최종적으로 도로명 주소가 다음과 같이 분리되었다. 이후 시구군과 시도만 남기고 삭제한다.
그리고 db에 저장할 수도 있지만 나는 태블로 확장자인 하이퍼로 저장한다.