엑셀 열 피벗 해제(unpivot)
분석할 수 없는 데이터와 Tidy Data
분석에 적합하지 않은 데이터셋
실무에서 데이터 작업을 하다 보면 분석을 할 수 없는 상황을 만날 때가 있습니다. 이러한 상황은 다양한 유형과 원인이 있을 수 있습니다. 우리는 데이터가 비어 있거나 이상치를 나타내는 등 데이터의 오류인 상황은 어느 정도 익숙하고 그만큼 대처를 할 수 있습니다. 그런데 '분석에 적합하지 않은 데이터셋의 구조'가 원인일 때는 상황 파악과 원인 및 해결방법을 짐작조차 못할 때가 많습니다.
데이터셋과 데이터셋의 구성요소
데이터셋은 데이터들가 모여서 이루어진 집합으로 보통 '표'라고 부르기도 합니다.
엑셀에서는 셀(cell)이 모여서 만들어진 범위(range)를 데이터셋이라고 볼 수 있습니다.
데이터셋은 숫자 또는 문자로 된 값(Values)의 집합이고, 모든 값은 변수(variable)와 관측치(observation) 중 하나에 해당합니다.
아래 데이터셋은 6개의 값으로 이루어져 있는데 '이름'과 '몸무게'는 변수이고 나머지 '홍길동', '70', '전우치', '65'는 관측치에 해당하므로 이 데이터셋은 총 6개의 값을 갖고 있는데 그 값은 2개의 변수, 4개의 관측치로 구성된다고 볼 수 있습니다.
<표1: 6개의 값으로 구성된 데이터셋>변수: 모든 개체(entity)를 측정하고 설명하는 동일한 기본 속성(몸무게)을 의미합니다.
관측치: 모든 개체에 대해서 동일한 단위(kg, 원, 개…)를 갖는 측정(관찰, 수집)된 값을 의미합니다.
분석에 적합한 표준 데이터셋: Tidy Data
분석에 적합한 데이터셋의 요건은 아래와 같습니다.
그리고 이 요건들에 모두 부합하는 분석에 적합한 데이터셋을 Tidy Data라고
1. 각 변수는 열을 형성합니다.
2. 각 관측치는 행을 형성합니다.
3. 관측 단위의 각 유형은 표를 구성합니다.
열 머리글이 변수가 아닌 값인 데이터셋
분석에 적합하지 않은 데이터셋의 구조는 여러 가지가 있지만, 가장 대표적인 구조는 '열 머리글이 변수가 아닌 값인 경우(Column headers are values, not variable names)' 입니다. 이것을 Wide Format이라고도 합니다(지칭하는 용어는 여러 가지 조금씩 다를 수 있습니다)
<표2: 2명의 6개월간 몸무게 변화를 기록한 데이터셋>표2의 데이터셋은 우리에게 익숙한 구조입니다. 하지만 이 데이터셋으로 피벗 테이블을 이용해서 분석을 하려고 하는 순간 아래와 같은 화면을 마주하게 되면서 당황하게 됩니다. 월별 몸무게의 변화를 분석하려고 하면 1월부터 6월까지 6개의 필드를 추가해야 합니다.
고작 6개월치의 데이터이기에 망정이지 36개월치의 데이터라면 더욱 막막하고 답답한 상황입니다. 설령 '내 마우스 컨트롤이 왕년에 프로게이머급이었지!'라고 하며, 6번의 드래그앤드롭을 해서 피벗 테이블을 만들어봤자 그 결과가 다소 이상합니다.
뭔가 이상한 것 같아서 행/열 전환을 시도해봤지만 결과가 맞는 것도 같고 아닌 것도 같고 미심쩍스럽습니다.
이 구조는 앞서 분석에 적합한 표준 데이터셋의 요건 중 '각 변수는 열을 형성합니다.'라는 첫 번째 요건을 위배하여 변수가 아니라 값이 열을 형성한 경우입니다. 즉, 원본 데이터의 열 머리글인 '1월, 2월 ... 5월, 6월'은 어떠한 변수의 측정값(Observation)이지 변수 그 자체를 나타내는 값(Variable)이 아니어서 하나의 변수로 구성되어야 하는 데이터셋이 마치 6개의 변수로 관측된 것처럼 넓게(wide) 만들어진 구조인 것입니다.
이 데이터셋은 보고서나 발표의 목적으로 사용된는 데이터셋입니다. 즉, 그 자체로 잘못되었거나 오류라기 보다는 분석에 적합하지 않은 데이터셋이고, 이 데이터셋으로 분석 작업을 하려면 구조의 변형이 필요합니다.
Unpivot, Melting
'열 머리글이 변수가 아닌 값인 데이터셋'을 Tidy Data로 변환하는 작업을 언피벗(Unpivot) 또는 멜팅(Melting)이라고 합니다. <표2>에서 열 머리글인 '1월, 2월… 5월, 6월'은 '월'이라는 변수의 값으로써 하나의 열에 구성되어야 하므로 현재와 같이 월별로 6개의 열이 아니라 하나의 열에 값으로 구성되어야 합니다.
이것은 마치 현재의 열 머리글이 시계 방향으로 90도 회전해서 행이 열로 전환되는 것과 같이 보여집니다. 다만, 단순히 행/열 전환(Transpose)이 아니라 홍길동 행도 1월부터 6월, 전우치 행도 1월부터 6월까지의 값을 각각 가져야 하므로 전체 행 수는 현재의 행 수(2)에 언피벗 대상 열의 수(6)를 곱한 값(12)으로 증가하게 됩니다.
엑셀 열 피벗 해제(파워 쿼리)
엑셀에서 열 피벗 해제(Unpivot; 언피벗)를 하는 방법을 알아보겠습니다.
엑셀 언피벗 작업은 엑셀 파워 쿼리 편집기에서 쉽고 빠르게 할 수 있습니다.
① 표 변환
언피벗하고자 하는 범위를 표로 변환합니다. 단축키 [Ctrl + T]를 사용하거나, [삽입] 탭 > [표] 그룹 > [표] 를 선택합니다. 범위가 잘 선택되었고, 머리글 포함이 체크되었는지 확인 후 [확인] 버튼을 누릅니다.
② 데이터 가져오기
변환된 표를 파워 쿼리 편집기로 가져옵니다. 변환된 표를 선택하고 [데이터] 탭 > [가져오기 및 변환] 그룹 > [테이블/범위에서] 버튼을 선택합니다.
③ 열 피벗 해제
[구분]열의 열을 선택(열 머리글인 '구분'을 클릭)하고 [변환] 탭 > [열]그룹 > [열 피벗 해제] 버튼의 오른쪽의 삼각형 메뉴 버튼을 클릭하고, [다른 열 피벗 해제]를 선택합니다. 현재 선택한 [구분]열을 제외하고 나머지 열(1월부터 6월)을 피벗 해제 즉, 열을 행으로 변환해야 하기 때문입니다.
④ 열 머리글 변경하기
열 피벗 해제가 실행된 결과를 확인할 수 있습니다. 새로 만들어진 열의 머리글은 '특성'과 '값'이라고 기본 설정됩니다. 열 머리글을 변경하겠습니다. 현재의 열 머리글을 더블 클릭하면 열 머리글을 변경할 수 있습니다. 왼쪽부터 차례대로 이름, 월, 몸무게로 변경합니다. 이 단계에서 필요한 경우 데이터 형식도 변경하는데 현재의 데이터 형식에서는 변경할 형식이 없습니다.
⑤ 닫기 및 로드
파워 쿼리 편집기는 엑셀의 뒤에서 실행되며, 실행되는 동안 엑셀 시트를 선택하거나 작업할 수 없습니다. 파워 쿼리 편집기에서 작업이 끝나면 편집기를 '닫고' 쿼리 결과를 엑셀로 '로드'해야 합니다. [홈] 탭 > [닫기] 그룹 > [닫기 및 로드] 버튼을 눌러서 로드를 진행합니다.
아래와 같이 새로운 시트에 열 피벗 해제된 데이터셋이 표의 형태로 로드된 것을 확인할 수 있습니다.
Long Format의 효용
열 피벗 해제되어 만들어진 데이터셋이 바로 Long Format입니다. 이 데이터셋으로 피벗 테이블을 만들어 보면 앞서 Wide Format에서 1월부터 6월까지의 6개의 필드가 '월'이라는 필드하나로 통합된 것을 볼 수 있습니다. 그리고 행 영역에 [이름], 열 영역에 [월], 값 영역에 [몸무게] 필드를 추가하면 열 피벗 해제 이전의 Wide Format과 같은 모양을 만들 수 있습니다.
Wide Format은 Long Format의 데이터셋을 피벗하여 만들 수 있는 구조이고, Wide Format의 데이터셋은 언피벗(열 피벗 해제)하여 다시 Long Format으로 돌아갈 수 있는 관계라는 것을 알 수 있습니다.
Wide Format이 틀렸거나 잘못된 데이터셋이라는 말이 아닙니다. Wide Format은 사람이 인지하기에 더 좋아서 보고서나 발표에 사용하기 적합합니다. 또한, 우리가 직접 수기로 또는 입력하며 관측값을 기록해야 하는 상황에서도 적합할 수 있습니다.
Long Format의 데이터셋은 Tidy Data의 요건에 부합하는 구조이며 데이터베이스의 기본 형식으로 엑셀뿐만 아니라 거의 모든 분석 도구에서 데이터 분석을 하기에 적합합니다. 만약 지금의 예제가 6개월의 데이터였는데 만약 36개월의 데이터였다면 기존의 Wide Format 데이터셋으로는 연단위, 분기단위 분석을 하기 어렵습니다. 따라서, 주어진 데이터가 Tidy Data인지 아닌지를 인지하기 위해서 꼭 필요하며 중요한 요소는 '열 머리글이 변수가 아닌 값인지'를 판단하는것입니다.
유튜브에서 영상으로 감상할 수 있습니다.
