5/29 엑셀 파워쿼리(2) - API (feat. 공공데이터포털)
오늘 배운 점 : api가 구동되는 방식, 엑셀 단축키(행/열모두선택), 슬라이서
오늘 내가 잘한 점 : 끝까지 포기하지 않고 성실하게 자습한 점, 클로바노트를 보조 도구로 활용한 점
앞으로 개선해야 할 점 : -
오늘은 엑셀 파워쿼리 실습 두 번째 시간이다.
앞부분 데이터 활용에 관한 부분은 접은 글로 해놓았으니, 필요하면 보고 참고하면 좋겠다.
데이터를 활용할 때에 부딪히는 문제
데이터를 활용 할 때에, 우리는 우리가 가진 데이터가 우리가 가진 목적/메시지에 맞지 않는다는 문제에 부딪히곤 한다.
데이터 분석을 위해 점검해야할 3단계는 다음과 같다.
<데이터 분석을 위해 점검해야할 3단계>
1. 현재 필요한 상황이 명확한가?
2. 필요한 데이터가 정의되었는가?
3. 목적에 따른 적절한 분석기법과 역량이 준비되었는가?
→ 분석 목적을 먼저 최대한 세분화한 이후, 데이터 분석해서 문제를 해결해야한다.
목적 세분화 기법
* 이슈트리
이슈트리란, 하나의 질문을 여러 구성 요소로 분할하고, 왼쪽에서 오른쪽으로 갈수록 점점 세부 정보가 담기는 트리 형태의 세분화 기법이다. 문제의 잠재적인 해결방안을 찾아내는 데에 유용한 기법이다.
Level 1 Level 2 Level 3
ex)동네 분식집 수익 개선을 위한 방안은? > 매출 증대 > 내점 고객 매출 증대 > 내점 고객 수 증가 / 내점 고객단가 증가 / 내점고객 회전율 증가
참고) '이슈 트리 작성 잘 하는 법'에 관한 글
이슈 트리 작성 잘하는 법
이선웅의 일 잘하는 법 이야기 #2 | 일을 잘한다는 말은 무엇일까? 직장 생활을 하다 보면 누구는 참 “일 잘한다”라는 소리를 듣고, 누구는 그렇지 못하다는 평가를 받는다. 어떤 기준이 이렇게
brunch.co.kr
이 때, 체크해야할 부분은 다음과 같다.
1. MECE하게 되었는지 체크 (Mutually Exclusively Collectively Exhaustive)
2. 우선순위 이슈 선별하기(가장 중요한 이슈 해결에 주력) - 분석의 유효성이 있거나, 답이 있을만한 곳을 분석해야함.
어떤 데이터를 찾아야할까?
문제1) "취준생은 우울하다. 얼마나 우울할까?"를 볼 수 있는 데이터를 수집하려고 할 때,
수집 가능 데이터는,
취업준비생 대상 우울 정도 설문조사 결과 / 구직포기자의 우울경험률(%) / 20-30대 우울증 환자 수
가 있다.
ex. 취준생>온라인에서 활동하는 취준생>우울감을 호소하는 취준생. 취업 커뮤니티 'ㅠㅠ'키워드 언급량
→ 문제에 포함된 추상적 개념을 데이터(지표)로 바꿔서 볼 줄 아는 사고가 중요!
문제2) 우리나라의 전국구 발전 정도를 알고 싶다
ex. 전국시군구단위 버거지수 = (버거킹 개수 + 맥도날드 개수 + KFC 개수 ) / 롯데리아 개수
데이터 활용은 Top-Down 방식으로 하는 게 좋다.
비즈니스>서비스>데이터 순으로, 비즈니스의 목적을 먼저 분명히 하고, 그 다음 세부적으로 어떤 서비스를 제공할 것인지 생각하고, 이에 맞는 데이터를 찾는 방식이 Top-Down 방식의 데이터 활용이다.
(데이터>서비스>비즈니스'는 뻔한 방안이 되기 쉬울뿐 아니라, 유용한 서비스 창출이 어려움. 비즈니스>서비스>데이터로 가는 것이 좋음)
API란?
API(Application Programming Interface, 응용 프로그램 프로그래밍 인터페이스)앱과 앱끼리 호출하는 방식(프로그램끼리 호출하는 방식). 그때그때 필요한 때에 데이터를 받는 방식.
- 네이버 개발자 사이트에 가면 네이버 검색어 데이터라든가 쇼핑 데이터라든가 뉴스라든가 이런 것들도 다 API로 가져다가 실시간으로 가져다 쓸 수 있다.
국민연금 가입 사업장 내역 API로 엑셀쿼리에서 구동하기
실습 : 삼성전자 직원 수 알아보기
1. 공공데이터포털>검색창에 '국민연금 가입 사업장'이라고 검색>오픈API 탭을 선택>XML이라고 되어있는 data를 선택>활용신청
국민연금공단 국민연금 가입 사업장 내역 오픈 API 링크
https://www.data.go.kr/data/3046071/openapi.do
2. 마이페이지> 데이터활용> OpenAPI >활용신청현황>'활용신청상세기능정보'에서 '사업장 정보조회 서비스' 행에서 '확인'버튼>미리보기
를 하면 새 창이 뜨는데, 그 주소의 url을 복사해둔다. 그리고 엑셀에서
데이터>데이터가져오기>기타원본에서>웹 을 클릭해서 복사해둔 url을 붙여넣고, body 부분을 '데이터 변환'을 클릭하면 다음과 같은 파워쿼리 창이 뜬다.
3. 빨간색으로 표시된 부분이 data가 더 있다는 뜻이다. 'drilling'이라고 한다. 따라서 데이터가 더 있는 'items' 열을 제외하고 다른 열은 삭제한다(열 선택>마우스 우클릭>다른 열 제거).
4. 빨간 색으로 표시된 부분을 클릭>확인>빨간 색으로 표시된 부분 재클릭 하면 아래 좌측과 같은 그림이 뜬다. 여기서 또 '확인' 버튼을 눌러준다. 그러면 아래 우측과 같은 데이터들이 뜬다.
5. 공공데이터포털(마이페이지> 데이터활용> OpenAPI >활용신청현황>'기본정보') 에서 아래 문서를 다운받는다. 그 문서에는 위 우측 열들이 의미하는 것들(ex. 'item.item.dataCrtYm')이 무엇인지를 포함하여 API의 상세 정보들이 들어가 있다.
bzowr_rgst_no는 사업자 등록번호이며, seq는 식별번호이다. 또한 jnngpCnt는 해당 사업장의 국민연금 가입자수(직원 수)이다.
2번에서 복사한 url은 기본 url(삼성전자로지텍주식회사의 api)이었으므로, 우리는 삼성전자의 api url을 찾아야한다.
따라서
삼성전자의 사업자등록번호를 찾아서 입력해줄 것이다. 그러면 jnngpCnt 영역에 가입자수(직원 수)를 알 수 있을 것이다.
6. 삼성전자 사업자등록번호 검색
7. 사업자등록번호 124-81-00998 중에서 앞자리 6자리만 입력해서 요청하면 되므로, '124810'과 사업장명인 '삼성전자'를 공공데이터포탈 마이페이지> 데이터활용> OpenAPI >활용신청현황>'활용신청상세기능정보'에서 '사업장 정보조회 서비스' 입력해준다. 그리고 '미리보기'를 클릭하고 나오는 창에서 url을 복사해둔다.
그리고 엑셀에서
데이터>데이터가져오기>기타원본에서>웹 을 클릭해서 복사해둔 url을 붙여넣고, body 부분을 '데이터 변환'을 클릭해준다(2번과 동일한 과정).
8. 3.,4.번을 반복한다. 그러면 아래 좌측과 같은 데이터가 있는데, 202201이 가장 최신임을 알 수 있다. 따라서 우리는 이에 해당하는 seq(식별번호)를 찾아 입력해 또 한번 api를 가져올 것이다. (이 데이터는 api가 두 번 감싸고 있는 구조이다.) 아래 우측처럼, 식별번호는 '37046715'이다.
9. 식별번호 '37046715' 를 마이페이지> 데이터활용> OpenAPI >활용신청현황>'활용신청상세기능정보'에서 '상세 정보조회' 의 빈칸에 입력해준다. 그리고 '미리보기' 누르면 뜨는 창의 <jnngpCnt></jnngpCnt>태그에서 우리는 111,073명이라는 것을 확인 수 있다.
엑셀
1. 행 또는 열 모두 선택 : Ctrl+Shift+화살표
첫 번째 셀을 선택한 후 Ctrl+Shift+화살표 키를 눌러 행 또는 열의 셀을 선택할 수 있다. 이때 행의 셀을 선택할 때는 오른쪽 화살표 또는 왼쪽 화살표를 누르고 열의 셀을 선택할 때는 위쪽 화살표 또는 아래쪽 화살표를 누른다.
2. '데이터 분석'이 안 보이는 경우, 생성하기
파일>옵션>추가기능> '관리'에서 '이동'을 클릭>분석도구팩'에 체크.3. 기술통계표 생성하기데이터분석>기술통계법 에서 입력범위로 1번(Ctrl+Shift+화살표)를 활용하여 열 전체를 선택, 아래 우측처럼 체크박스에 체크하여 테이블 생성.
4. 데이터에서 최대값과 최소값 뽑아내기
최대값 뽑아내는 함수 : =MAX()
최소값 뽑아내는 함수 : =MIN()
5. 현 데이터에서 파워쿼리 불러오기
6. 슬라이서 활용하기
슬라이서를 활용하면 데이터를 보고싶은 만큼만 볼 수 있도록 편집이 가능하다.
- 쿼리를 불러온 다음, 아래 그림처럼 슬라이서를 삽입한다.
그 결과, 아래 그림처럼 원하는 년도 (2012년 1~3월)의 데이터만 보는 것이 가능해졌다. 만일 만든 차트가 있다면, 슬라이서가 그 차트에도 활용이 되므로 매우 유용한 기능이다.
