5/28 엑셀 파워쿼리(1)

박스 안의 정보는 데이터에 대한 기초 강의이므로, 엑셀 파워쿼리에 대해서 알고 싶은 사람은 스킵해도 된다. 

데이터 리터러시

 

데이터의 존재 -> 데이터로부터 올바른 정보를 얻는 능력 -> 정보를 활용할 수 있는 도메인 지식 -> 도출된 결과로 현상을 혁신할 수 있는 지혜

∴ 읽고, 해석하고, 통계를 적용해서 증거로서 통계를 사용할 줄 아는 능력. 

 

1. 데이터 읽기 : 데이터가 어떻게 생겨야하는지에 대한 이론적 지식이 있어야함

• 결측치 : 빠진 data
• 이상치 : 보통 관측된 데이터의 범위에서 많이 벗어난 아주 작은 값이나 큰 값

2. 데이터 작업하기 : 데이터를 수집하고 관리하는 능력

• 자유학기제 도입의 효과를 알아보고 싶다 → 자유학기제 도입 전후 학업성취도와 학업 만족도의 차이를 비교
• 우리회사와 타 회사의 경쟁력을 비교해 보고 싶다 → 주력상품과 서비스, 직원 수, 분기별 매출, 연간 성장률, 시장 점유율 등
• 다음 대통령에 누가 당선될지 예측해보고 싶다 → 여론조사 결과, 정당지지도 분석 등

3. 데이터로 소통하기 : 내가 원하는 결과를 찾기 위해 보기 좋게 시각화

 

역사상 최고로 꼽히는 데이터 시각화의 예 10가지를 소개하는 사이트

https://www.tableau.com/ko-kr/learn/articles/best-beautiful-data-visualization-examples

데이터의 아름다움: 역사상 최고로 꼽히는 10가지 데이터 시각화의 예

 

cf. 도시 구역 지도에 작은 막대 그래프를 사용해서 사망률을 표현함. 그 결과, 콜레라의 피해를 가장 많이 입은 세대들은 모두 같은 우물을 식수로 사용하고 있다는 것을 발견한 사례.

 

* 북극성 지표(North Star Metric)

- 팀의 목표와 방향, 그리고 성공을 가늠하는 주요 척도

- 적절한 북극성 지표 선정하는 법 : 고객 가치를 반영한다. 고객의 입장에서 바라보고 직관적이고, 선행지표인 것이 좋은 지표. 

ex. 30명을 follow한 트위터 사용자

특정 중요 지점을 넘었을 때 어떤 높은 가치를 지닌 결과가 나타날 가능성이 커지는 지표

cf. 선행지표가 중요한 이유

https://brunch.co.kr/@bluemarble/36

PM이 선행지표를 찾아야 하는 이유

 

만약, 1인 가구 증가 관련 데이터 프로젝트 기획을 한다면, 

 → '1인 가구의 무엇을 위해서 일하고싶은지'를 명확하게 하고,

수집해야할 데이터는,

- 지역별 1인가구 현황

- 통계청 자료

- 주로 사는 품목

- 리서치 보고서

- 판매 담당자 의견

- POS 데이터 등등

 

어떤 데이터를 모을 것인가? 5W1H

1. 누가 - 개인정보(생년월일, 성별, 거주지), 멤버십 등급

2. 언제 - 연/월/일/시/분/초 & 그 일자의 요일, 공휴일 여부 외

3. 어디서 - 매장이름, 주소, 상권, 위경도 외

4. 무엇을 - 제품 기준정보, categorization, 속성 정보, 발주일시 등

5. 어떻게 - 결제수단, 쿠폰사용여부, 이벤트 참여여부 외

6. 왜 - SNS 분석, 설문조사, 심층면접(FGI) 등

 

데이터를 얻을 수 있는 곳

공공데이터 포털

https://www.data.go.kr/index.do

 

√ 데이터의 출처가 깨끗하고 사용가능한 것인가?

√ 데이터에 오류, 변수가 얼마나 포함되어 있고 추가되지 않은 데이터는 없는가?

 

Extract Transform Load 데이터 관리 도구 - GA, SQL, Python 등

 

데이터 처리 과정 

목표 설정 → 데이터 확인(전처리) → 데이터 분석방법 선정 

 

목적에 따른 데이터 분석 방법

• 현상파악 - 기술통계, 시각화
• 인과관계 - 회귀분석, 경로분석, 구조방정식

        ex. 페이스북과 유튜브 중 광고 효과가 더 좋은 쪽은?

• 유사도분석 - 상관분석, 군집분석

       ex. 인스타, 유튜브, 넷플릭스 / 쇼핑몰에서 상품 추천하기

• 예측/분류 - 회귀분석, 결정트리, 서포트 벡터 머신(SVM)

       ex. x가 달라졌을 때 y가 어떻게 달라질까, 1년 후 오늘 S전자 주가 때려 맞추기 / 데이터가 들어오면 A일까 B일까를 분류 / 사람의 손글씨 인식하기(OCR)

 

      cf. '보통 이미지 가지고 하는 건 분류 문제가 맞다.'

 

엑셀 파워 쿼리

- 엑셀 파워 BI중 하나

'데이터 전처리 도구'로서의 파워쿼리 : 다운받은 Raw Data를 빠르게 변환하거나 분산되어 있는 특정 폴더의 데이터를 한 Sheet에 취합할 때 유용. 

데이터 전처리란, " 비정형 데이터(Unstructured Data) → 정형화된 테이블 (Structured Table) " 로 만드는 것. 한 열에는 한 데이터만 담는 것이 목표

 *cf. Orange3는 Mining도구이지, 전처리 도구가 아니다.

 

파워 쿼리 실습해보기

1. 아래의 데이터(~_20221231.csv)를 다운받는다. 

https://www.data.go.kr/data/15007122/fileData.do#layer_data_infomation

국민건강보험공단_건강검진정보_20221231

 

2. 데이터 가져오기>파일>텍스트/CSV로 다운받은 데이터를 연다. 

3. 위 화면처럼 나오면 '데이터 변환'을 누른다. 

 

아래 화면처럼 보이는 화면이 파워쿼리이다.

데이터가 굉장히 크므로, 필요한 데이터만 추출해서 쓰도록 하겠다.

4. 쓰지 않는 열을 모두 선택한 후, 마우스 우클릭해서 뜨는 박스에서 '열 제거'를 하면 된다. 

혹은 쓰고 싶은 열들만 모두 선택한 후, 마우스 우클릭해서 뜨는 박스에서 '다른 열 제거'를 하면, 선택된 열을 제거한 다른 열들은  제거된다. 

5. 작업을 다 하면 맨 좌측 상단에 '닫기 및 로드'를 하면 파워쿼리 편집기가 닫힌다. 

6. 각 성별의 합계를 보고 싶으면, 일단 성별 열을 선택한다.

 

7. 삽입>피벗테이블>확인 을 누른다. 그러면 우측에 '피벗 테이블 필드'가 뜬다. 그다음 성별 체크박스에 체크하고, '성별'을 '행'에 드래그한다. 

그러면 아래와 같은 테이블이 뜬다. 남자가 1로 코딩되어 있고, 여자가 2로 코딩되어 있으므로, 남자는 515,301명, 여자는 969,398명이다. 

8. 삽입>피벗차트해서 다음과 같은 원형 차트를 그릴 수도 있다.  

9. 삽입>차트>모든차트>상자수염 으로 해서 다음 그림과 같이 체중과 신장에 대한 박스수염 차트를 그릴 수도 있다. 

여기서 X는 중위값, 실선은 평균, 점은 이상치이다. 

 

데이터 수집 

• 웹 크롤러 ' 체계적, 자동화된 방법으로 탐색하고 정보를 자동적으로 수집하는 컴퓨터 프로그램
• 웹 크롤링 - 자동적으로 화면에 있는 data를 가져오고, 실시간으로 연동된다. 자동으로 데이터를 업데이트 함으로써 데이터의 최신 상태를 유지한다. 
• 웹 스크래핑 - scrapping 하는 시점에서의 데이터만 갖고 오기. 특정 요구 사항을 처리하는데 사용. 자동화가 되지 않는다. 링크, HTML tag 등은 스크래핑하지 않고, 안에 들어있는 텍스트만 꺼냄.

 

데이터 스크래핑 하는 법 (두 가지)

1. 무료 데이터 추출 스크래핑 확장자, 리스틀리

https://www.listly.io/ko

이 사이트에 가입하고 크롬 확장자에 리스틀리를 추가해서, 데이터 추출을 원하는 웹사이트에 방문하면 리스틀리 로고를 클릭하면 된다. 엑셀 파일 형식으로 데이터를 추출할 수 있다. 

리스틀리 | 단숨에 웹데이터 추출!

 

2. 엑셀에서

데이터>데이터가져오기>기타원본에서>웹 으로 해서 뜨는 박스에 변환하고 싶은  url을 입력하면, 창이 뜬다. 그 창에서 '데이터 변환'을 누르고 '닫기 및 로드'를 누르면 된다. 

 

[실습 1] 대한민국 아카데미 국제영화상 출품작 데이터 추출하기

• 실습 data 웹 (대한민국의 아카데미 국제영화상 출품작)

https://ko.wikipedia.org/wiki/%EB%8C%80%ED%95%9C%EB%AF%BC%EA%B5%AD%EC%9D%98_%EC%95%84%EC%B9%B4%EB%8D%B0%EB%AF%B8_%EA%B5%AD%EC%A0%9C%EC%98%81%ED%99%94%EC%83%81_%EC%B6%9C%ED%92%88%EC%9E%91

대한민국의 아카데미 국제영화상 출품작 - 위키백과, 우리 모두의 백과사전

 1. 데이터>데이터가져오기>기타원본에서>웹 으로 해서 뜨는 박스에 변환하고 싶은  url을 입력

 2. 열린 창에서 '데이터 변환'을 누르면 파워쿼리 창이 뜬다. 

 3. 첫 행이 중복되어 있으므로, '첫 행을 머리글로 사용'하여 첫 행을 없애준다.

 4. 연도 열을 선택한 다음 '열 분할'을 클릭하여 기본 설정된 대로 분할해준다.

 5. 연도 열(맨 첫번째 열)에서 변환>추출>범위> 에서 시작인덱스 '0' 문자 수 '4'로 하여 년도만 추출해준다. 

 6. 회차 열(두 번째 열)을 선택한 다음, 변환>추출>구분기호 사이 텍스트를 선택한다.

 7. 아래와 같은 화면에서 시작 구분 기호를 '(', 종결 구분 기호를 ')'으로 입력해준다. 

 8. '회'도 없애고 싶으면 5번처럼 해주면 된다. 

 9. 홈>닫기 및 로드 를 하면 다음과 같이 정리된 데이터를 얻을 수 있다. 

 

cf. 유용한 뉴스 분석 웹사이트

https://www.bigkinds.or.kr/

빅카인즈(BIG KINDS)

 

 

[실습 2] 멜론 월간차트로 '발라드'와 '트로트'간의 좋아요 수 비교하기

 

발라드 TOP 100 차트

https://www.melon.com/chart/month/index.htm?classCd=GN0100

Melon

 

트로트 TOP 100 차트

https://www.melon.com/chart/month/index.htm?classCd=GN0700

Melon

 

1. 발라드 TOP 100차트 링크를 복사하여, 엑셀에

데이터>데이터가져오기>기타원본에서>웹 으로 해서 뜨는 박스에 발라드 TOP 100 차트 url을 입력

 2. 열린 창에서 '데이터 변환'을 누르면 파워쿼리 창이 뜬다. 

 3. 좋아요 열 선택>마우스 우클릭>다른 열 제거 를 선택하여 좋아요 열을 제외한 다른 열을 제거해준다. 

 4. 변환>추출>범위> 에서 시작인덱스 '9' 문자 수 '7'로 하여 좋아요 수 추출해준다. 

 5. 변환>'데이터형식:텍스트'를 클릭하여 '정수'로 바꿔준다. 

 6. 변환>통계>합계를 해준다. 

 7. 맨 좌측 상단의 '테이블로'를 클릭한다. 

 8. 홈>닫기 및 로드 를 하면 다음과 같이 정리된 데이터를 얻을 수 있다. 

발라드의 TOP 100 차트의 좋아요 수를 합하면 13,486,097 개이다. 

 9. 트로트도 1.~8.까지의 순서대로 해보면 다음과 같이 정리된 데이터를 얻을 수 있다.

트로트의 TOP 100 차트의 좋아요 수를 합하면 2,268,894 개이다. 

 

결론 : 좋아요 수를 볼 때, 멜론 이용자들이 트로트보다 발라드를 더 좋아하는 것을 알 수 있다.