[ 파워쿼리 ] 청계천 박물관 입장객수

지금까지 살펴본 파워쿼리 기능을 활용한 청계천 박물관 입장객수를 알아보도록 하겠습니다.

활용한 데이터는 공공데이터로써 파일은 아래 링크에서 다운 받으실 수 있습니다.

서울특별시_청계천박물관 입장객수_20230131

 

 

3개의 테이블로 구성된 데이터 이며 형식은 아래와 같습니다.

모든 데이터가 정규화되어 있으면 좋겠지만 주변에 있는 데이터는 항상 내가 원하는 대로 되어 있지 않습니다.

이것이 파워쿼리를 사용해야 하는 이유가 아닐까 싶습니다.

데이터를 살펴보니 일단 시트를 합치는 작업을 진행해야 할 것 같습니다.

 

시트를 합치는 방법은 아래에 있으니 사전에 살펴보시면 많은 도움이 되실 것입니다.

[ 파워쿼리 ] 엑셀 시트 데이터 합치기

---

데이터 불러오기

우선 데이터를 파워쿼리에서 작업하기 위해서 데이터를 불러 오도록 하겠습니다.

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

 

데이터를 가져올 때 아래와 같이 여러 항목을 선택해서 가져올 수 있겠지만 

한 번에 처리하기 위해서 Table 1만 선택 후 처리해 보도록 하겠습니다.

 

아래와 같이 Table 1만 선택해 주고 [ 데이터 변환 ]을 클릭하도록 하겠습니다.

 

다음과 같이 파워쿼리 편집창에 데이터가 올라온 것을 볼 수 있습니다.

구분 필터를 눌러서 우선 데이터를 살펴 보도록 하겠습니다.

날짜가 22.9.26일까지 있는 것을 볼 수 있습니다.

 

 

엑셀에서 살펴보니 Table 1 시트에 있는 항목만 불러온 것을 볼 수 있습니다.

Table 2, 3을 같이 합쳐야 좀 더 방문 트렌드를 알아 볼 수 있을 것 같습니다.

적용된 단계에서 [ 원본 ]을 클릭해 보도록 하겠습니다.

 

 

아래와 같이 모든 시트의 데이터가 각각의 테이블로 들어가 있는 것을 볼 수 있습니다.

우선 Table 1의 Data의 Table을 클릭해서 데이터를 탐색해 보도록 하겠습니다.

 

우선 헤더가 여러개로 나눠줘 있어 헤더터부 정리해 보도록 하겠습니다.

---

헤더 만들기

 

지금 헤더가 하나로 되어야 하는데 이중도 아닌 삼중으로 되어 있습니다.

헤더를 하나로 만드는 작업을 해 보도록 하겠습니다.

[ 홈 ] - [ 행 유지 ] - [ 상위 행 유지 ]를 클릭 후 상위 3개만 유지해 보도록 하겠습니다.

( 삼중 헤더니 3개를 유지하는 게 맞을 것 같습니다 )

 

아래와 같이 3개의 항목만 나오는 것을 볼 수 있습니다.

한눈에 보기 위해서 행/열을 바꿔 보도록 하겠습니다.

 

[ 변환 ] - [ 행/열 바꿈 ]을 클릭해 보도록 하겠습니다.

 

 

한번 살펴 보도록 하겠습니다.

데이터는 살펴보는 게 제일 중요한 것 같습니다.

1. 진짜 컬럼은 Column3인 것으로 생각됩니다.
2. 오전 / 오후 / 야간이 반복되는데 어떤 항목인지 구분을 위해서 Column2를 사용
3. 하지만 null 값이 있는 부분이 있기 때문에 Column1의 어린이, 외국인을 Column2로 이동

해당 순서로 생각을 하면서 헤더 전처리를 진행해 보도록 하겠습니다.

방법은 어려가지가 있으나 순서대로 한번 해 보도록 하겠습니다.

 

 

우선 적용된 단계에서 전 단계로 이동해 보도록 하겠습니다.

아까 어린이와 외국인을 Column2로 이동하기로 하였으니 Column3과

 

Column17의 항목을 아래로 채우기를 해 보도록 하겠습니다.

 

 

지금 보이는 것은 Column17만 되어 있는 것처럼 보이나 Column3도 선택이 되어 있는 상황입니다.

 

아래와 같이 데이터의 값이 아래로 채워지는 것을 볼 수 있습니다.

[ 파워쿼리 ] null 값 바꾸기 ( 결측값 처리 )

반응형

 

이번에는 적용된 단계에서 행/열을 바꾼 테이블을 클릭해 보도록 하겠습니다.

아래와 같이 Column2의 필요한 항목이 Colum1로 부터 불러온 것을 볼 수 있습니다.

 

 

Column2의 항목을 아래로 채워 주도록 하겠습니다.

우측과 같이 데이터 항목의 채워 진 것을 볼 수 있습니다.

총계도 외국인으로 채워져 있지만 소계, 총계를 삭제할 것이기 때문에 그냥 하도록 하겠습니다.

( T인 분들은 그냥 못 넘어가겠지만 참으십시오 )

 

소계, 총계를 지우기 위해서 살펴보니 Column3의 null 값이 소계, 총계인 것으로 생각됩니다.

해당 항목은 지워 주는 맞으나 헤더를 우선 만들기 위해서는 필요하니 우선 두도록 하겠습니다.

우선 if 함수를 사용해서 Column1의 총계 우측의 외국인을 총계로 변경해 주도록 하겠습니다.

절차는 우선 외국인을 총계로 바꾸는 작업을 진행해 줍니다.

 

수식창에 "총계"를 아래와 같은 수식으로 변경해 줍니다.

each if [ Column1  ] = "총계" then "총계" else _

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

 

아래와 같이 데이터가 정리되어 있는 것을 볼 수 있습니다.

 

Column1을 지워고 Column2와 Column3을 List로 변경해 보도록 하겠습니다.

각 열을 List로 만든 후 다시 List 형식으로 쌓는 Nested List라고 생각하시면 됩니다.

 

각 List의 값을 합쳐주기 위해서 List.Transform 함수를 사용해 보도록 하겠습니다.

아래와 같이 수식을 변경해 주니 Column이 List 형식으로 만들어진 것을 볼 수 있습니다.

적용된 단계를 column으로 변경해 줍니다. 

( 나중에 사용할 예정입니다 )

 

---

Nested Table 전처리 후 확장

 

fx를 눌러서 적용된 단계를 하나 추가해 주도록 하겠습니다.

해당 데이터를 아까 만든 헤더를 추가하고 상위 3개 행을 각각 지워 보도록 하겠습니다.

이번에는 Table.TransformColumns를 사용하도록 하겠습니다.

 

수식에 상위 3개 항목을 지울 수 있는 수식인 Table.Skip을 사용해서 상위 3개를 우선 지워줍니다.

 

List형 컬럼을 넣기 위해서 다음과 같은 수식을 입력해 주도록 하겠습니다.

아래와 같이 Data 각 테이블에 헤더가 추가된 것을 볼 수 있습니다.

해당 사항에 대한 장점은 기존 글에서 설명해 드렸으니 한번 읽어 보시면 좋을 것 같습니다.

[ 파워쿼리 ] PDF 파일 데이터 가져오기

728x90

 

Data 컬럼만 남기고 해당 컬럼을 확장해 보도록 하겠습니다.

우측에 날짜를 필터링 하니 모든 날짜가 들어와 있는 것을 볼 수 있습니다.

 

 

날짜 및 요일 컬럼 선택 후 다른 열 피벗 해제를 눌러 줍니다.

처음에 총계와 소계를 삭제하기로 했기 때문에 언더바가 없는 항목과 총계는 모두 필터링을 해 줍니다.

( 해주지 않으면 값이 2배가 될 것입니다 )

 

 

생성된 특성을 언더바 기준으로 열 분리를 해 주도록 하겠습니다.

[ 파쿼쿼리 ] 다양한 방법으로 열 분할 하기 ( 텍스트 분리하기 )

 

열 구분 진행 후 컬럼명을 변경해 주고 데이터 타입을 변경해 주고 닫기 및 로드를 눌러보도록 하겠습니다.

 

표로 만들지 않고 모델링을 통한 시각화를 진행해 보도록 하겠습니다.

---

 

데이터 시각화

 

[ 삽입 ] 피벗차트를 눌러서 해당 항목이 나오면 확인을 눌러 줍니다.

 

아래와 같이 요일별 방문 트렌드를 한번 보도록 하겠습니다.

지금은 파워쿼리만 진행하고 있기 때문에 파워 피벗에 대한 부분은 언급하지 않도록 하겠습니다.

주말 / 평일 중 주말 방문이 많고 평일에서는 수요일 > 목요일 순으로 많은 것을 볼 수 있습니다.

 

 

시계열을 한번 살펴보니 9/10, 12/13일에 방문이 많은 것을 볼 수 있습니다.

 

 

아마도 청소년의 방문이 주된 영향으로 보입니다.

 

방문 시간대는 오전에 많은 방문이 발생합니다.

 

그리고 시계열 트렌드 전체를 볼 때는 보이지 않았지만 하나하나 보니 경로의 방문 트렌드가 증가 추세를 보이고 있습니다.

그러나 아마 전체 방문자의 가중치가 작기 때문에 보이지 않았을 수 있습니다.

트렌드가 증가한다면 해당 부분에 대한 편의를 위한 대안이 필요할 것으로 보입니다.