Excel 활용 통합, 정렬, 조건부 서식 색조 활용 빅데이터 분석(feat, 코로나 인구밀도, 코로나데이터 상관분석 시각화 및 현황 분석) 2020-05-04

 

 

 

출처 - https://coronaboard.kr/ 코로나 완치(%)데이터 주목

 

 

대한민국의 현재 코로나 완치율은(85.3%)이다. 필자의 4월 포스팅에서는 코로나 완치율이 70% 후반에서 80% 초반대 였던 것으로 기억한다. 코로나의 종식 선언은 코로나 완치율의 상승(85%~95%)과 연관지어 고려되어 질 수 있다.

 

 

필자는 이전 포스팅을 통해 지속적으로 완치(%)데이터가 코로나 종식율을 판단하는 지표로 활용될 수 있다는 점을 언급한 바 있다.

 

 

 

https://datacenter-nmworld.tistory.com/entry/%EB%8C%80%ED%95%9C%EB%AF%BC%EA%B5%AD-%EC%BD%94%EB%A1%9C%EB%82%98-%EC%83%81%ED%99%A9%EC%9D%80-%EC%A2%85%EC%8B%9D%EC%9D%84-%ED%96%A5%ED%95%B4-%EA%B0%80%EA%B3%A0-%EC%9E%88%EC%9D%84%EA%B9%8Cfeat-%EC%BD%94%EB%A1%9C%EB%82%98-%EC%A2%85%EC%8B%9D%EC%9C%A8-%EC%88%98%EC%B9%98%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EB%B6%84%EC%84%9DCorona-Virus-Analysis-3

대한민국 코로나 상황은 종식을 향해 가고 있을까?(feat. 코로나 종식율 수치)_코로나 통계 데이터 분석(Corona Virus Analysis)

코로나 완치율(격리해제/확진자 비율) + 확진자 증가 고려 => 코로나 종식률 지수

2020년 4월 7일 기준 대한민국 코로나 지역별 현황(누적) 출처:  https://datacenter-nmworld.tistory.com/entry/대한민국-코로나-상황은-종식을-향해-가고-있을까feat-코로나-종식율-수치코로나-통계-데이터-분석Corona-Virus-Analysis-3  [통계 & 데이터 & 이야기]

 

 

 

지속적으로 사회적 거리두기를 지속하고 검사 등의 의료활동을 활발하게 시행한 결과로 인해 코로나 종식을 향해 대한민국은 세계적으로 모범이 되는 모습을 보이고 있다고 생각하며, 앞으로 경제적으로 포스트 코로나 상황이 발생할 것으로 전망되고 있지만 돌파구를 잘 찾아낼 수 있기를 바라고 있다.

 

 

 

출처 - 구글링 : 코로나 사회적 거리두기 5월

 

 

 

 

금일의 포스팅은..

 

 

코로나 통계 - 인구밀도와 코로나 통계의 상관관계)

 

 

이전 포스팅에서 언급했던 코로나 통계에서 인구밀도가 미치는 영향과 엑셀을 활용하여 데이터를 시각화하여 간단히 수행할 수 있는 상관분석과 해당 활동의 중요성과 효율성에 대해 작성해 보고자 한다.

 

 

 

해당 주제와 관련하여 필자는 두 포스팅을 먼저 살펴보는 것을 추천한다.

 

 

 

(1) 인구밀도가 높은 싱가포르의 코로나 상황에 대한 포스팅,

https://nmland.tistory.com/entry/%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%ED%98%84%ED%99%A9-%EB%B6%84%EC%84%9Dfeat-%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%EC%A6%9D%EA%B0%90%EC%9C%A8-%EB%B0%8F-%ED%86%B5%EA%B3%84-%EB%B3%80%EB%8F%99%EC%84%B1%EB%B6%84%ED%8F%AC-%EB%AF%B8%EA%B5%AD-%EC%8B%B1%EA%B0%80%ED%8F%AC%EB%A5%B4-%ED%95%9C%EA%B5%AD-%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%EC%A0%95%EA%B7%9C%EB%B6%84%ED%8F%AC-%EA%B7%A0%EB%93%B1%EB%B6%84%ED%8F%AC-%ED%95%B4%EC%84%9D-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%A0%84%EC%B2%98%EB%A6%AC%EC%9D%98-%EC%A4%91%EC%9A%94%EC%84%B1%EB%AA%A8%EC%88%98-%ED%91%9C%EB%B3%B8-%ED%86%B5%EA%B3%84-%EC%9D%B8%EC%82%AC%EC%9D%B4%ED%8A%B8-2020-04-25

2020-04-25코로나 통계 현황 분석(FEAT, 코로나 통계 증가량 및 통계 변동성(분포), 미국, 싱가포르, 한국 코로나 통계, 정규분포, 균등분포 해석, 데이터 전처리의 중요성(모수, 표본), 통계 인사이트

 

 

(2) 빅데이터의 시대에 있어 여전히 엑셀로 데이터 학습을 시작하는 것에 대한 이점

https://nmland.tistory.com/entry/%EC%BB%B4%ED%93%A8%ED%84%B0-%ED%99%9C%EC%9A%A9%EB%8A%A5%EB%A0%A5-%EC%9E%90%EA%B2%A9%EC%A6%9D-1%EA%B8%89-%EC%B7%A8%EB%93%9D-%ED%9B%84%EA%B8%B0-feat-%EB%B9%85%EB%8D%B0%EC%9D%B4%ED%84%B0

컴퓨터 활용능력 자격증 1급 취득 후기 (feat. 빅데이터)

 

 

 

바로 제작 방법에 대해 들어가보자.

 

 

 

엑셀로 해당 데이터를 만드는데 중요한 핵심은 2 가지 이다.

 

(1) 인구밀도와 코로나 통계 데이터를 어떻게 합칠 것인가?

 

 

 

(2) 어떻게 상관분석(예 - 특정 수치가 증가(확진자)하는 경향을 보일 때 다른 수치가 증가(인구밀도)하는 상관관계를 가질까?)을 구현할까?

 

 

 

 

 

(1) 인구밀도와 코로나 통계 데이터를 어떻게 합칠 것인가?

 

 

 

 

엑셀에서 통합은 데이터 베이스의 조인과 같이 동일한 그룹(값)을 기준으로 두 데이터를 합쳐준다.

 

 

 

 

엑셀의 통합 도구 세부 설정

 

 

통합을 클릭하면 함수, 참조, 모든 참조영역, 사용할 레이블, 데이터 연결이 있다.

 

함수는 데이터를 합할 때 특정 연산을 하도록 도와주는 기능이다.

 

 

 

 

엑셀의 통합 함수 설정

 

 

데이터에서 원하는 연산을 해서 합칠 수 있도록 하는 기능이며 필자는 단순히 인구밀도와 코로나 데이터들(확진자, 사망자, 격리해제 등)을 합치는 목적으로 이를 활용하였기에 합계를 놓고 열의 이름을 달리하였다.

 

 

 

엑셀 사용할 레이블(데이터 베이스의 키 값) 설정을 위한 국가 데이터 변환하기

 

 

 

 

필자가 가져온 인구밀도 데이터와 코로나를 위해 필자가 이용하는 국가 데이터의 국가명은 조금 차이가 있었다. 아마 데이터가 정말 잘 정리되어 있는 상황이 아니라면 이런 난감한 상황을 겪을 것이다.

 

엑셀 - 수식활용하여 기준 레이블 값 설정하기

 

 

필자는 국가를 기준으로 인구밀도와 코로나 데이터를 합치고자 하는 목적을 가지고 있으며 상위 이미지와 같이 국가명에 붙어있는 별표와 띄어쓰기로 인해 다른 데이터로 인식하는 오류를 제거해야만 했다.

 

 

 

해결사항 (1) 별표 제거

사용 함수 - TRIM

 

해결사항 (2) 띄어쓰기 제거

사용 함수 - SUBSTITUTE

 

 

 

 

이렇게 사용할 레이블 값을 활용할 수 있게 데이터를 변환한 다음 사용할 레이블을 설정하고 참조에 합칠 데이터를 집어넣으면...

 

 

 

 

엑셀을 활용해 인구밀도와 코로나 통계 데이터 병합 성공

 

 

생각보다는 골치아픈 부분이 있어서 굉장히 오래걸렸다.

(특히 확진자의 증가량이 같이 표시된 데이터의 처리 - 빨간색으로 표시된 오른쪽 데이터들을 수식을 활용하여 누적데이터에서 제거하고 따로 추가시켰다.)

 

다음 단계인 상관분석을 위해서 해당 데이터를 분리할 수 밖에 없었다.

 

 

(2) 어떻게 상관분석(예 - 특정 수치가 증가(확진자)하는 경향을 보일 때 다른 수치가 증가(인구밀도)하는 상관관계를 가질까?)을 구현할까?

 

 

 

엑셀을 활용하여 필자가 제안하는 상관분석 방법은 실제 통계적인 방법을 활용하는 산점도나 상관분석 수치(상관계수)를 활용하는 방법이 아니다.

위의 두 방법은 유용하다. 하지만...

컴퓨터 공학과가 아니면 프로그래밍(파이썬 혹은 R)을 해야해서 어렵고...

통계학이나 수학이 어려우면 상관계수를 이해하기 어려울 것이다.

 

 

 

 

 

 

다만 증가하고 감소하는 추세에 관련하여 세부적인 데이터나 전체적인 흐름을 판단하기에 매우 유용한 도구이며,

 

실제 업무 현장에서도 통계적 인사이트를 얻기위해 유용한 도구가 되지 않을까 생각한다.

 

 

 

엑셀로 상관분석 - 조건부 서식(색조)

 

 

 

 

 

특정 셀 범위를 드래그하여 색조 기능을 클릭하면??

 

 

 

 

 

엑셀의 조건부 서식의 색조 기능을 활용한 상관분석

 

수치가 높으면 빨간색수치가 낮으면 파란색으로

수치의 크기를 색깔이 진해지는 정도(음영)로 표현하였으며

 

 

 

 

이를 이용하여 두 데이터가 같은 색상의 경향을 가지면 양의 상관성 빨강, 파랑의 반대의 흐름을 보이면 음의 상관을 보인다는 분석으로 활용될 수 있다.

 

 

 

음영 및 색상의 특성에 대한 포스팅 참조

https://www.hisour.com/ko/shades-of-red-23763/

빨간색 음영

 

 

 

 

 

 

해당 결과를 보면 필자가 이전 포스팅에서 언급했던 내용에 대한 상관성에 대해 보다 깊은 이야기를 할 수 있다.

 

https://nmland.tistory.com/entry/%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%ED%98%84%ED%99%A9-%EB%B6%84%EC%84%9Dfeat-%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%EC%A6%9D%EA%B0%90%EC%9C%A8-%EB%B0%8F-%ED%86%B5%EA%B3%84-%EB%B3%80%EB%8F%99%EC%84%B1%EB%B6%84%ED%8F%AC-%EB%AF%B8%EA%B5%AD-%EC%8B%B1%EA%B0%80%ED%8F%AC%EB%A5%B4-%ED%95%9C%EA%B5%AD-%EC%BD%94%EB%A1%9C%EB%82%98-%ED%86%B5%EA%B3%84-%EC%A0%95%EA%B7%9C%EB%B6%84%ED%8F%AC-%EA%B7%A0%EB%93%B1%EB%B6%84%ED%8F%AC-%ED%95%B4%EC%84%9D-%EB%8D%B0%EC%9D%B4%ED%84%B0-%EC%A0%84%EC%B2%98%EB%A6%AC%EC%9D%98-%EC%A4%91%EC%9A%94%EC%84%B1%EB%AA%A8%EC%88%98-%ED%91%9C%EB%B3%B8-%ED%86%B5%EA%B3%84-%EC%9D%B8%EC%82%AC%EC%9D%B4%ED%8A%B8-2020-04-25

2020-04-25코로나 통계 현황 분석(FEAT, 코로나 통계 증가량 및 통계 변동성(분포), 미국, 싱가포르, 한국 코로나 통계, 정규분포, 균등분포 해석, 데이터 전처리의 중요성(모수, 표본), 통계 인사이트

 

 

 

 

 

필자가 해당 결과를 보고 든 생각은 두가지다.

 

 

(1) 국가 별 인구밀도데이터와 코로나 데이터의 병합 및 상관분석은 큰 의미가 없다. 특정 인구가 집중되어 있는 세부적인 데이터가 필요하다.

 

 

 

 

 

출처 - https://www.cdc.gov/coronavirus/2019-ncov/cases-updates/cases-in-us.html

 

 

 

확진자가 가장 많은 미국의 케이스를 보면 다음과 같이 코로나가 걸린 지역이 집중되어(?) 있는 것을 알 수 있다.(동서부 기준으로 보면 되겠다.)

 

 

 

미국같이 큰 땅덩어리를 기준으로는 아무리 인구가 많아도 인구밀도 수치가 낮을 수 밖에 없으며 당연히 확진자 수치와 비교했을 때 인구밀도와의 상관성을 비교하기에는 어려움이 있다.

 

 

 

 

 

1) 이에 대하여 인구밀도와 코로나 통계와의 상관성은 없다.

 

 

 

라는 결론을 내리거나

 

 

 

2) 서로 다른 국가적 상황(인구밀도를 공평하게 고려한 통계가 있을까?)

을 고려하여 코로나 통계와 비교

 

하는 방법이 있을 수 있겠다.

 

 

 

2)번과 함께 필자가 상관분석을 통해 생각해보게 된 사실은 다음과 같다.

 

 

 

 

 

(2) 미국은 중국의 영향을 무지막지하게 받는 나라다.

 

 

 

 

 

해당 사실은 부정확할 수 있다. 필자는 미국에 코로나가 확산된 근본적인 원인에 대해 잘 알지 못하며 통계 데이터만 가지고 이야기를 하고 있는 상황이다.

 

통계상으로 봤을 때 중국 데이터와 비교하여 느낀점은

 

미국이 코로나의 진원지처럼 보인다는 점이다.

 

실제로 다른 사이트에서 긁어온 시각 자료들을 봤을 때 미국의 코로나 상황은 전국적으로 퍼져있다.

 

 

 

 

 

 

실제 진원지인 중국의 데이터(그리고 인도)에서 미국과 비교하여 두드러지는 점(파란색 주목)은 발생율 데이터이다.

 

그리고 중국, 인도는 미국보다 인구가 많은 두나라이다.

 

https://ko.wikipedia.org/wiki/%EC%9D%B8%EA%B5%AC%EC%88%9C_%EB%82%98%EB%9D%BC_%EB%AA%A9%EB%A1%9D

인구순 나라 목록 - 위키백과, 우리 모두의 백과사전

 

 

다시말해서

 

 

 

1) 코로나의 발생율 데이터는 인구 데이터의 영향을 많이 받는 수치라고 생각 할 수 있으며,

(인구가 많으면 발생율 데이터는 낮아진다)

 

 

 

2) 미국은 이러한 인구의 영향력(?)을 이겨내고 확진자 및 특히 발생율에서 상위권을 보이고 있다.

 

 

 

면적이 비교적 적은 유럽의 국가들에 의해 1등(스페인?)을 차지하지는 못한 모습이지만, 미국 코로나 상황의 심각성을 보여주는 통계이며, 해당 통계는 필자가 언급한 내용에 대해 여러가지 답을 내려준다고 생각한다.

 

 

이전의

 

2) 서로 다른 국가적 상황(인구밀도를 공평하게 고려한 통계가 있을까?)

 

 

 

을 고려하여 코로나 통계와 비교

 

이를 고려하기 위해서는 인구밀도와 인구 수를 모두 고려한 코로나 통계가 필요하다는 것이 필자의 제안이다. 관련하여는 지표를 새로 제작해야 하는 노력이 필요하며, 해당 상관분석에서 파악할 수도 있겠지만,

그 난이도가 높아지면서 효율성이 좋다고 볼 수 없을 것 같다.

 

 

 

 

 

이외에도 해당 상관분석을 통해 국가들의 코로나 통계 경향(ex - 빨간색) 중 중간에 파란색을 보이는 비정상적인 수치들을 통해 특정 지역의 상황 및 특성을 생각해 볼 수 있으며

(예 - 이란의 치료중 데이터는 확진자가 높은 다른 국가의 코로나 데이터들과 비교해 두드러지며, 이란의 치료 통계 혹은 치료 상황에 비정상적인 상황이 발생하고 있음을 알려준다.) 

 

 

 

데이터를 이용하는데 있어 도구(엑셀이냐 파이썬이냐)보다는 그 데이터를 활용하는 능력과 함께

기존에 없었던 데이터 관련 기능(엑셀의 통합 및 조건부 서식의 색조)의 중요성을 활용하는 역량이 업무 효율성 및 분석 활동에 중요하지 않나

 

생각해본다.

 

 

 

 

 

결론

 

(1) 엑셀을 이용하여 통계적 지식(상관분석), 프로그래밍 능력(파이썬, R)없이 빅데이터 분석의 핵심인 상관분석을 시행할 수 있다.

 

(2) 엑셀을 이용한 간단한 분석을 위해서는 모든 데이터들이 동일한 규칙(예 - 나라 이름 입력의 통일성 유지)에 의해 저장되어 병합이 쉽도록 제공되어 지는 것이 매우 중요하다.

(빅데이터 관련 업계 종사자들이 가져야할 중요한 마음가짐)