[ 파워쿼리 ] 데이터 정규화

파워쿼리를 이용한 데이터 정규화를 진행해 보도록 하겠습니다.

공공데이터를 보는 중 예전에는 보지 못한 데이터가 있어서 살펴보려고 합니다.

최근 수능이 끝나서 이러한 자료도 올라오는 게 아닌가 하는 생각도 많이 듭니다.

데이터를 아래 공공데이터에서 다운 받으실 수 있습니다.

한국전문대학교육협의회_신입생 입시통계현황_20240328

 

---

데이터 불러오기

 

아래와 같이 데이터를 다운 받았습니다.

전문대학별 전공학과 모집에 관한 내용으로 보이는데 전처리를 진행해 보도록 하겠습니다.

 

 

파워쿼리 편집기로 데이터를 불러오도록 하겠습니다.

이번에는 엑셀이 아닌 Power BI로 작업 진행토록 하겠습니다.

( Power BI나 엑셀 파워쿼리 모두 동일하게 작업 가능하기 때문에 편하신 걸로 하시면 됩니다 )

해당 파일은 CSV 파일이기 때문에 텍스트로 불러와야 합니다.

[ 파워쿼리 ] 데이터 불러오기 ( with. CSV )

 

아래와 같이 파워쿼리 편집기로 데이터를 불러왔습니다.

제가 다크모드 편집을 좋아해서 검은색으로 보이는데 설정에서 변경 가능 하십니다.

 

첫 행을 헤더로 사용하도록 하겠습니다.

 

아래와 같이 첫 번째 열이 헤더로 올라간 것을 볼 수 있습니다

 

정규화 진행을 먼저 할 것이기 때문에 데이터 형식을 변경하지 않고 우선 진행토록 하겠습니다.

---

대학교 데이터 정규화

 

정규화라는 것은 vlookup의 lookup 테이블을 만드는 것을 의미합니다

데이터를 효율적으로 관리하기 위해서 하나의 테이블이 아닌 여러 개의 테이블로 만들어 모델링을 하기 위한 방법입니다.

데이터를 작업하면서 가장 중요한 것이 붙였다가 분리했다가 하는 것인데 

vlookup은 붙이는 작업이라면 이번에는 분리하는 반대의 작업입니다.

미분 vs 적분의 개념이라고 생각하시면 좋을 것 같습니다.

대학명 데이터를 살펴보니 대략적으로 알 수 있는 항목은 다음과 같습니다.

강원도립대학교는 공립이고 지역은 강원이다

한 번만 해도 알 수 있을 것 같은데 자꾸 반복이 되고 있습니다.

대학교 테이블로 별도로 분리하여 모델링을 진행해 보도록 하겠습니다.

 

전문대학 테이블에서 우클릭 후 참조를 클릭해 줍니다.

구분	복제	참조
공통점	테이블이 복사	테이블이 복사
차이	원본의 영향을 안 받음	원본의 영향을 받음

 

참조를 했을 때 장점은 데이터가 추가되면 해당 항목도 추가되는 장점이 있습니다.

하지만 똑같은 작업을 계속 반복하기 때문에 작업 속도에서는 비효율이 발생합니다.

데이터가 하나이기 때문에 참조로 작업을 진행하도록 하겠습니다.

( 데이터가 작다는 전제를 꼭 고려해 주십시오 )

 

테이블명을 대학교로 변경 후 [ 지역, 설립, 대학교 ]를 선택하여 다른 열 제거를 클릭해 줍니다.

 

 

이제 같은 항목이 지속적으로 반복이 되기 때문에 중복된 항목을 제거해 보도록 하겠습니다.

세 개의 컬럼을 선택 후 우클릭하여 중복된 항목 제거를 클릭해 줍니다.

 

모델링을 하기 위해서는 1:* ( 일 : 다 ) 항목을 유지해 주어야 하는데 완전 중복이 없는지 확인해 보도록 하겠습니다.

반응형

( * : * 연결도 있지만 추천하지는 않습니다 )

체크를 위해서 대학명에서 그룹화를 선택 후 확인을 눌러 줍니다.

 

중복이 없을 것이라고 생각했는데 중복된 대학이 존재합니다.

 

적용된 단계에서 [ 필터링된 행 ]으로 이동하여 해당 항목을 필터링해보도록 하겠습니다.

아래와 같이 지역별로 같은 대학의 이름이 존재하기 때문에 이러한 상황이 발생합니다.

 

이러한 상황에는 지역과 대학명을 열병합하여 join 컬럼을 만들어 모델링을 진행해야 하나

해당 항목이 2개 대학밖에 되지 않기 때문에 원본에서 값을 변경해 주도록 하겠습니다.

아까 참조로 했던 장점을 보여 드리도록 하겠습니다.

패턴을 보니 두 개의 대학모두 "경기" 지역이 있기 때문에 경기 지역을 변경하면 한 번에 처리가 가능할 것 같습니다.

대학명에 값 바꾸기를 누른 후 아무 항목이나 입력해 주도록 하겠습니다.

 

수식 창에 다음과 같이 입력 후 대학교 테이블로 이동해서 서영대학교를 필터링해 보도록 하겠습니다.

자세한 사항은 아래 글을 참조해 주십시오.

[ 파워쿼리 ] if, 다중 if ( 조건열 ) 사용하기

728x90

 

서영대학교가 분리된 것을 볼 수 있습니다.

 

그럼 대경대학교도 한번 해보도록 하겠습니다.

 

같은 작업을 한 번 더 반복해 줍니다.

 

중복된 항목 없이 모두 처리된 것을 알 수 있습니다.

 

이제 전문대학 쿼리에서 반복되는 컬럼인 [ 지역, 설립 ]을 지우기 위해서 참조로 했던 대학교 테이블을 지우고

이번에는 테이블 복제를 해 보도록 하겠습니다.

앞에서 작업했던 것을 한번 반복해 주겠습니다.

 

전문대학 테이블에서 [ 지역, 설립 ]은 대학교 테이블을 통해서 사용할 예정이니 삭제해 주도록 하겠습니다.

---

데이터 모델링

 

이제 모델링을 해 보도록 하겠습니다.

Power BI에서 모델 보기를 눌러 주도록 하겠습니다.

 

파워 BI에서 자동으로 모델링을 해 준 것을 볼 수 있습니다.

이번에는 그냥 확인을 누르지만 해당 항목이 맞는지 꼭 확인해 보셔야 합니다.

 

아래와 같이 지역별 대학교를 확인 가능 합니다.