[BIGDATA] 2-1 크로스 집계의 기본
빅데이터의 탐색
크로스 집계의 기본
데이터 시각화에서 먼저 기본이 되는 것이 ‘크로스 집계’다.
트랜잭션 테이블, 크로스 테이블, 피벗 테이블 - ‘크로스 집계’의 개념
어떤 상품의 매출을 정리한 보고서의 행 방향(세로)으로는 ‘상품명’이 나열되고, 열 방향(가로)으로는 ‘매출 월’이 나열된다.
행과 열이 교차하는 부분에 숫자 데이터가 들어가기 때문에 이를 ‘크로스 테이블(cross table)’이라고 부른다.
엑셀 등의 스프레드시트에서 이러한 보고서를 직접 작성하는 사람들이 많다.
크로스 테이블은 사람들이 보기 편한 보고서이지만, 데이터베이스에서는 다루기 어려운 데이터 형식이다.
데이터베이스에 새로운 행을 추가하는 것은 간단하지만, 열을 늘리는 것은 간단하지 않다.
따라서, 보고서의 바탕이 되는 데이터는 행 방향으로만 증가하게 하고, 열 방향으로는 데이터를 증가시키지 않도록 해야 한다.
이것을 ‘트랜잭션 테이블(trainsaction table)’이라고 한다.
트랜잭션 테이블에서 크로스 테이블로 변환하는 과정을 ‘크로스 집계(cross tabulation)’라고 한다.
소량의 데이터를 크로스 집계하는데 편리한 것이 스프레드시트의 ‘피벗 테이블(pivot table)’ 기능이다.
여기서는 예로 Microsoft Excel을 이용한다.
피벗 테이블 기능에 의한 크로스 집계
예제 데이터로 다음의 판매 데이터를 사용한다.
데이터를 다각적으로 집계할 수 있도록 ‘점포 ID’와 ‘상품 ID’, ‘고객 ID’를 추가하고 있다.
크로스 집계하고 싶은 데이터의 범위를 선택하고 메뉴에 있는 [삽입]에서 [테이블]-[피벗 테이블]을 선택하여 새로운 시트를 만든다.
이때 피벗 테이블의 [열]에 ‘매출일’을, [행]에는 ‘점포 ID’와 ‘상품 ID’, [값]으로 ‘금액’을 넣으면 다음과 같은 크로스 테이블로 표시된다.
피벗 테이블에서는 행과 열이 교차하는 부분의 값은 자동으로 집계된다.
아무것도 지정하지 않으면 숫자 값의 합계가 계산되지만, 그 밖에도 ‘평균’과 ‘최댓값’, 이전 값과의 ‘차’ 등을 계산할 수 있다.
결과를 크로스 테이블에 정리할 뿐만 아니라 그것을 그래프로 시각화한 ‘피벗 그래프(pivot graph)’ 기능도 있다.
룩업 테이블 - 테이블을 결합하여 속성 늘리기
트랜젝션 테이블에 새로운 항목을 추가하는 것이 아니라 다른 테이블과 결합하고 싶은 경우도 있다.
예를 들면, ‘상품 ID’를 사용하여 ‘상품명’과 ‘상품 카테고리’를 참고하는 형태다.
이때 사용되는 것이 ‘룩업 테이블(lookup table)’이다.
예를 들어 다음과 같이 상품 정보를 하나의 테이블에 정리해두면 나중에 속성을 추가하거나 변경하는 것도 간단해진다.
트랜잭션 테이블과 룩업 테이블은 서로 독립적으로 관리할 수 있다.
트랜잭션 테이블은 업무 데이터베이스 등에서 가져오는 데 비해 룩업 테이블은 데이터 분석 용도에 따라 변경해도 상관 없다.
BI 도구에 의한 크로스 집계
자주 데이터를 살펴볼 때는 엑셀보다 BI 도구를 사용하는 것이 좋다.
Pandas에 의한 크로스 집계
스크립트로 크로스 집계를 실행하고 싶다면 pandas를 이용하는 것이 편리하다.
2개의 테이블을 결합하려면 merge()를 실행한다.
import pandas as pd
df1 = pd.read_excel(u'판매데이터.xlsx', u'판매이력')
df2 = pd.read_excel(u'판매데이터.xlsx', u'상품')
df3 = pd.merge(df1, df2, on=u'상품 ID')
df3
| 매출일 | 점포 ID | 상품 ID | 고객 ID | 금액 | 상품명 | 상품 카테고리 | |
|---|---|---|---|---|---|---|---|
| 0 | 2017-01-01 | 11 | 101 | 1001 | 57500 | 상품 A | 식료품 |
| 1 | 2017-02-01 | 12 | 101 | 1003 | 57500 | 상품 A | 식료품 |
| 2 | 2017-03-01 | 12 | 101 | 1003 | 60000 | 상품 A | 식료품 |
| 3 | 2017-01-01 | 11 | 102 | 1002 | 2400 | 상품 B | 전자제품 |
| 4 | 2017-02-01 | 11 | 102 | 1002 | 5800 | 상품 B | 전자제품 |
| 5 | 2017-03-01 | 11 | 102 | 1002 | 12400 | 상품 B | 전자제품 |
예상대로 칼럼이 추가되었기 때문에 이것을 pivot_talbe()로 크로스 집계한다.
df3.pivot_table(u'금액', [u'점포 ID', u'상품명'], u'매출일', aggfunc='sum')
| 매출일 | 2017-01-01 | 2017-02-01 | 2017-03-01 | |
|---|---|---|---|---|
| 점포 ID | 상품명 | |||
| 11 | 상품 A | 57500.0 | NaN | NaN |
| 상품 B | 2400.0 | 5800.0 | 12400.0 | |
| 12 | 상품 A | NaN | 57500.0 | 60000.0 |
다음 코드는 독자적으로 정의한 파이썬 함수로 상품 카테고리를 생성한다.
def category(row):
return {101:u'식료품'}.get(row[u'상품 ID'], u'그 외')
df1[u'상품 카테고리']=df1.apply(category, axis=1)
df1.head(2)
| 매출일 | 점포 ID | 상품 ID | 고객 ID | 금액 | 상품 카테고리 | |
|---|---|---|---|---|---|---|
| 0 | 2017-01-01 | 11 | 101 | 1001 | 57500 | 식료품 |
| 1 | 2017-01-01 | 11 | 102 | 1002 | 2400 | 그 외 |
SQL에 의한 테이블 집계 - 대량 데이터의 크로스 집계 사전 준비
대량의 데이터를 크로스 집계하려면 SQL을 사용하여 데이터 ‘집계(aggregation)’, 즉 sum()과 같은 ‘집계 함수(aggregate functions)’를 이용해 데이터양 감소를 고려할 필요가 있다.
데이터 집계 -> 데이터 마트 -> 시각화 - 시스템 구성은 데이터 마트의 크기에 따라 결정된다.
데이터의 집계와 시각화 사이에 있는 것이 데이터 마트다.
일반적으로 데이터 마트가 작을수록 시각화되는 것이 간단하지만, 동시에 원래 데이터에 포함된 정보를 잃어버리게 되어 시각화의 프로세스에서 할 수 있는 것이 적어진다.
이런 경우, 피벗 테이블과 BI 도구를 사용해 대화적인 데이터를 검색한다면 정보 부족으로 곤란한 상황에 처하게 된다.
반대로 데이터 집계의 프로세스에서 가능한 한 많은 정보를 남기게 되면, 데이터 마트가 거대화되어 결국에는 좋은 시각화를 할 수 없게 될 우려가 있다.
이것은 트레이드 오프(trade off)의 관계에 있으며, 필요에 따라 어느 정도의 정보를 남길 것인가를 결정해야 한다.
최종적으로는 ‘데이터 마트의 크기’에 따라 시스템 구성이 결정된다.