열 머리글이 2개인 데이터셋의 엑셀 전처리
엑셀 열 피벗 해제
개요
'열 머리글이 변수가 아닌 값'인 데이터셋은 보고 또는 발표용에 적합하고, 분석 또는 다양한 데이터 작업에는 적절하지 않은 구조의 데이터셋입니다. 바로 아래와 같은 데이터셋이 대표적인 사례이고 이러한 구조일 때 엑셀에서는 파워 쿼리 편집기의 열 피벗 해제 즉, Unpivot(언피벗)이라는 기능을 활용해서 간단하게 분석에 적합한 데이터셋으로 처리할 수 있습니다.
<초고속인터넷 회선 수>다중 열 머리글(Multiple Column Headers)
실무에서는 '열 머리글이 변수가 아닌 값'인 경우만으로도 분석이 어려운 데이터셋인데 머리글이 2행 이상으로 구성된 경우를 종종 만나볼 수 있습니다. 만나기 싫지만 만나야 했고 만났으니 이 상황을 처리해야만 분석할 수 있습니다.
머리글이 2행으로 구성된 경우는 데이터셋에 하나의 차원이 더해진 경우입니다. 아래의 데이터셋은 'IPTV 가입자 수'라는 '값을 사업체, 연도 및 형태별'로 집계한 데이터셋입니다. 이 데이터셋의 열은 연도(2021, 2020, 2019)와 형태(전체 가입자, 사업자용 가입자)를 기준으로 구분되어 있는데 각각은 모두 변수가 아닌 값입니다. 열 머리글의 개수를 떠나서 이미 Tidy Data가 아닌 분석에 적합하지 않은 데이터셋입니다.
<IPTV 사업체별 형태별 가입자 현황>데이터셋의 구조적 문제점
<IPTV 사업체별 형태별 가입자 현황> 데이터셋은 분석에 적합하지 않은 대표적인 데이터셋의 형태로써 아래와 같은 요소들이 구조적 문제입니다.
다중 열 머리글(1~2행)
열 머리글은 언제나 1행이어야 합니다. 이 데이터셋에서는 '연도 및 가입자 형태별'로 가입자 수를 보기 위해서 머리글을 추가한 구조를 갖고 있는데 경우에 따라 2행을 초과하는 복잡한 경우도 많습니다. 후술하겠지만, 이 데이터셋은 Tidy Data를 피벗하게 되면 만들 수 있는 결과물입니다.
변수가 아닌 값으로 열 구성(B~G열)
각각의 열은 변수(연도, 가입자 형태)로 구성되어야 합니다. 지금의 데이터 셋은 변수가 아닌 값(2021, 2020, 2019, 전체 가입자, 사업자용 가입자)로 구성되어 있습니다.
병합(A1:A2, B1:C1, D1:E1, F1:G1)
데이터셋의 모든 셀은 병합(Merge)되지 않아야 합니다. 전문적인 데이터 분석가 분들이 엑셀을 가장 혐오하는 기능 중 하나가 바로 병합입니다. 일반적으로 셀은 데이터가 저장되는 최소 단위로써 하나의 데이터는 하나의 셀에 대응되어야 합니다. 그런데 병합을 하게 되면 하나의 데이터가 2개 이상의 셀에 저장되어 데이터셋의 기본 원칙을 위배하게 되어 분석 결과를 왜곡시키거나 분석할 수 없게 됩니다.
특히 다중 열 머리글인 데이터셋에서는 흔하게 발생합니다.
계(A3)
데이터셋에서 머리글을 제외한 행(row)은 관측치(observation)로써 동일한 규칙과 기준에 따라 구성되어야 합니다. 예를 들어 지금의 데이터셋에서는 '사업체'라는 기준이 설정되었으므로 개별 사업자 하나가 하나의 행을 구성해야 하는데 '각 사업자의 가입자 수가 합계된 행'이 관측치와 동일하게 추가되는 것은 데이터셋의 기본적인 구조 기준에 어긋나게 됩니다. '각 사업자의 가입자 수가 합계된 행'이라는 것 역시 결과에서 유추한 것이지 이 또한 알기 어려운 경우도 많습니다.
열 피벗 해제(unpivot)
다중 열 머리글의 데이터셋 역시 엑셀 파워쿼리의 열 피벗 해제를 이용해서 분석에 적합한 Tidy Data로 만들 수 있습니다. 다만, 머리글이 1행일 때와 다르게 몇 단계가 추가됩니다. 이 과정은 하나의 공식처럼 기억하고 실무에서 적용해도 좋겠습니다.
표 만들기(머리글 제외)
데이터셋 전체를 선택하고 표로 변환합니다. 데이터셋 안에서 임의의 셀을 선택하고 Ctrl + A를 눌러서 전체 범위를 선택하고, 단축키 Ctrl + T를 입력해서 표 만들기를 실행하고 '머리글 포함'을 체크 해제하고 '확인' 버튼을 누릅니다.
데이터 가져오기(파워쿼리)
표로 변환하면 병합이 해제되어 위에서 아래로, 왼쪽에서 오른쪽으로 빈 셀이 채워진 데이터셋이 만들어 집니다. 이 표를 파워쿼리에서 편집하기 위해 데이터 가져오기를 실행해야 합니다. [데이터] 탭 → [데이터 가져오기 및 변환] 그룹 → [테이블/범위에서] 버튼을 누릅니다.
집계된 행 삭제(Filter)
데이터셋에 관측치가 아닌 행인 '계' 행을 제거합니다. [열1] 열의 필터 버튼을 누른 뒤 '계'를 체크 해제하고 확인 버튼을 누릅니다
엑셀에서의 필터는 선택하지 않은 행을 숨기는 기능을 하지만, 파워 쿼리 편집기에서의 필터는 해당 행을 제거하는 기능을 하는 차이가 있습니다. 아래와 같이 '계' 행이 제거된 것을 확인할 수 있습니다.
행/열 바꿈(Transpose)
테이블 전체의 행과 열을 바꿔줍니다. [변환] 탭 → [표] 그룹 → [행/열 바꿈] 버튼을 누릅니다.
첫 행을 머리글로 사용
행/열이 변경된 상태에서 임의의 머리글인 Column1, 2, … 5가 아닌 1행(첫 행)에 있는 값들을 머리글로 사용하도록 설정합니다. [홈] 탭 → [변환] 그룹 → [첫 행을 머리글로 사용] 버튼을 누릅니다.
아래와 같이 기존의 1행이 머리글로 사용되었습니다. 데이터베이스에서 머리글은 행의 개수(데이터의 개수)에 포함하지 않습니다.
열 피벗 해제(unpivot)
가장 중요한 단계입니다. 현재의 구조에서 '변수가 아닌 값'으로 구분되어 있는 열(3~5번째 열)을 피벗 해제해야 합니다. 3열부터 5열까지 모두 선택합니다. 3열의 머리글(㈜케이티(olleh tv))을 누르고, Shift 키를 누른 채로 5열의 머리글(㈜LG유플러스(U+ TV))을 누릅니다. 그리고 [변환] 탭 → [열] 그룹 → [열 피벗 해제] 버튼을 누릅니다.
기존의 머리글을 구성하던 사업체 이름이 '특성'이라는 열의 값으로 구성되었고, '값'이라는 열이 생성되어 기존의 행을 구성하던 값들이 '값 열'의 값으로 구성되었습니다. 이 머리글들은 기본값으로 설정되어 있는 이름입니다.
머리글 이름 변경
모든 열 머리글의 이름을 적절한 변수의 이름으로 변경합니다. 1열부터 '연도, 가입자 형태, 사업체, 가입자 수'로 변경합니다. 각 열 머리글을 더블 클릭하면 이름을 변경할 수 있습니다.
데이터 형식 변경
마지막 단계입니다. 각 열의 데이터 형식을 변경합니다. 파워 쿼리 편집기는 특정 기능을 실행(데이터 가져오기)하면 자동으로 데이터 유형을 탐색해서 변경하는 단계가 삽입됩니다. 가급적 작업 단계 중간에 자동으로 삽입되는 데이터 유형 변경은 삭제하는 것이 좋습니다. 쿼리를 수정할 때에 오류를 발생시키는 주요 원인이기 때문에 쿼리가 완성되고 마지막에 한 번만 수정해주는 것이 좋습니다.
연도와 가입자 수를 '정수'로 가입자 형태와 사업체를 '텍스트'로 변경합니다. 바로 옆에 있는 열을 선택할 때는 Shift 버튼을 누르고 선택하고, 떨어져 있는 열을 선택할 때는 Ctrl 버튼을 누르고 선택하면 됩니다.
데이터 형식 변경은 각 열의 머리글 왼쪽에 있는 버튼을 이용해서도 가능하고, [변환] 탭 → [열] 그룹 → [데이터 형식: ] 버튼을 눌러서 변경하고자 하는 유형을 선택해서 변경할 수 있습니다.
닫기 및 로드
변환 작업이 모두 끝났습니다. 이제 작업한 쿼리가 적용된 결과 데이터셋을 엑셀로 로드해야 합니다. [홈] 탭 → [닫기] 그룹 → [닫기 및 로드] 버튼을 누릅니다.
완성
엑셀 파일에 새로운 시트가 생성되고, 변환된 표가 삽입되었습니다.
최종 쿼리
let
원본 = Excel.CurrentWorkbook(){[Name="표1"]}[Content],
#"필터링된 행" = Table.SelectRows(원본, each ([열1] <> "계")),
#"행/열을 바꾼 테이블" = Table.Transpose(#"필터링된 행"),
#"승격된 헤더" = Table.PromoteHeaders(#"행/열을 바꾼 테이블", [PromoteAllScalars=true]),
#"피벗 해제된 열 수" = Table.UnpivotOtherColumns(#"승격된 헤더", {"사업체별", "사업체별(1)"}, "특성", "값"),
#"이름을 바꾼 열 수" = Table.RenameColumns(#"피벗 해제된 열 수",{{"사업체별", "연도"}, {"사업체별(1)", "형태"}, {"특성", "사업자"}, {"값", "가입자 수"}}),
#"변경된 유형" = Table.TransformColumnTypes(#"이름을 바꾼 열 수",{{"가입자 수", Int64.Type}, {"연도", Int64.Type}, {"형태", type text}, {"사업자", type text}})
in
#"변경된 유형"
