[Excel 개념 정리 3]엑셀을 활용한 추론 통계 개념 정리 3️⃣ (데이터 전처리)

이번 글은 코드잇 강의를 수강하면서 배운 내용을 주로 하여 정리되어 있습니다. (코드잇 스프린트 데이터 애널리스트 트랙 1기 훈련생)

---

이번 글에서는 엑셀의 기초에 이어 엑셀에서의 데이터 전처리에 대한 내용 정리를 하려고 합니다.

이번 글에서는 범주형 변수 처리, 결측값 처리, 이상치 처리에 대한 내용을 다룰 예정입니다.

---

엑셀에서의 데이터 전처리

---

데이터 전처리(pre-processing)

데이터 전처리는 데이터를 통계적으로 분석하거나, 머신러닝을 통해 예측 모델을 만들 수 있도록 완벽한 상태로 가다듬는 과정을 말합니다.

데이터 전처리는 데이터 분석 과정의 최소 50%이상을 차지합니다.

데이터 전처리 단계에서 요구되는 과정은 범주형 변수 처리 / 결측치 처리 / 이상치 처리/ 데이터 병합(엑셀에서 이 과정은 까다롭다.)으로 나누어집니다.

• 데이터 병합은 원하는 정보들이 하나의 자료만으로 제공되지 않는 상황에 필요하며, 두 개 이상의 자료를 합쳐서 하나의 파일로 만들어주는 작업입니다.

---

범주형 변수 처리

범주형 변수 처리는 회귀 분석을 수행하기 전에 필요한 전처리 작업입니다.

(분산 분석에서는 범주형 변수 처리가 필요하지 않다, 애초에 데이터의 형태가 다르다.)

회귀분석에서 범주형 변수 처리를 하는 이유는 회귀 분석 모델이 데이터 값으로 범주형(문자) 변수를 인식하지 못하기 때문입니다.

→ 회귀분석은 독립변수인 x의 숫자가 한 단위 바뀔 때(1만큼 커질 때), 종속변수인 y의 숫자가 얼만큼 변화하는가를 나타내주기 때문에 문자형이 있을 경우 분석이 되지 않습니다.

 

엑셀에서 범주형 변수 처리를 하기 위해서는 우선, 열에 정확히 몇 개 범주의 데이터가 있는지 파악할 필요가 있습니다.

따라서, 어떤 문자 변수가, 총 몇 개 있는지를 먼저 알아봐야 합니다.

이 작업은 필터 기능을 통해 쉽게 할 수 있습니다.

필터를 만들어 몇 개의 범주가 있는지 확인했을 때, 총 4개의 범주 변수가 있는 것을 확인할 수 있었습니다(prof, bc, wc, 결측값)

범주형 변수 처리를 위해 결측값을 제외한 각 집단을 0, 1, 2로 재코딩 하겠습니다.

값의 재코딩은 홈 탭의 [찾기 및 선택] → [바꾸기]를 통해 진행합니다.

[바꾸기]를 들어가면 찾을 값과 바꿀 값을 선택할 수 있습니다.

찾을 내용을 첫번째 범주형 변수의 이름(prof)으로, 바꿀 내용을 0으로 넣고 ‘모두 바꾸기’를 누르면 첫번째 범주형 변수의 값들이 0으로 모두 바뀝니다.

똑같은 작업을 통해 두번째 변수(bc)와 세번째 변수(wc)도 바꿔줄 수 있습니다. (각각의 집단을 1과 2로 변환)

변환을 통해 범주형 변수 처리를 하고난 후, 필터를 통해 확인해보면 결측값인 NA를 제외한 나머지 집단이 숫자로 잘 바뀐 것을 알 수 있습니다.

이제 같은 방식을 통해 결측값인 NA는 빈칸으로 바꿔주면, 전처리 작업이 끝나게 됩니다.

 

하지만, 이건 한가지 방법일 뿐 집단 간에 차이를 정확하게 분석하기 위해서는, 결국 각 집단을 각각 하나의 열로 분리해서 만들어 주어야 합니다.

예를 들어, prof, bc, wc라는 열을 하나씩 만들어 준 다음, type이 prof인 경우, prof에 1, type이 prof가 아니면 0 이런식으로 코딩해주는 방식을 갖게 됩니다.

각 과정은 if 함수를 사용해서 해결할 수 있습니다.

즉, 범주형 변수 컬럼명의 셀의 값이 “첫번째 변수명”이면 1, 아니면 0으로 넣으라고 작성하면 됩니다.( =IF(상대참조=”첫번째 변수명”, 1, 0) )

동일한 과정을 bc와 wc에 대해서도 수행해주면 됩니다.

이 작업을 간편하게 하는 팁

1. ype 열을 클릭한 후, ‘ctrl 아래방향키’를 동시에 누르면, 해당 열의 마지막 셀로 이동합니다.
2. 오른쪽으로 한 칸 이동하면 prof 열의 마지막 셀일 텐데요, 여기서 ‘ctrl shift 위방향키’를 누르면 prof 열의 첫 값인 H2까지 전체 드래그가 됩니다.
3. ‘ctrl D’를 눌러주면, 첫 번째 셀의 수식이 전체로 적용됩니다.

---

결측값 처리

결측치는 통계 분석에서 매우 큰 방해물로 전체 변수들의 인과 관계에 왜곡을 일으키기 때문에 처리가 반드시 필요합니다.

결측값을 제거하는 방법

1. 결측치가 들어있는 행을 통째로 삭제 : 데이터 샘플이 충분히 크다는 전제, 대표성에 해가 되지 않음
2. 평균/중앙값/최빈값 등으로 대체하여 삽입 (오히려 값을 심하게 왜곡할 가능성이 있어 잘 사용하지 않는다)
3. 해당 열들의 값들을 기반으로 결측치를 예측

보통 많이 사용하는 방법은 1, 3번째 방법입니다.

---

결측값 들어있는 행 삭제

이 방법은 결측치가 있는 케이스를 그냥 표본 자체에서 제거해버리는 방식입니다.

이는 데이터 샘플이 충분히 크다는 전제 하에, 케이스 중 일부를 줄여도 대표성에 해가 되지 않기 때문에 어찌보면 가장 간편하면서도 안전한 방법입니다.

물론, 해당 케이스들을 없애버림으로써 대표성이 크게 깨지면 사용할 수 없으며, 여전히 데이터의 왜곡 가능성이 존재하기는 합니다.

결측값 들어있는 행 삭제 방법

우선, 홈 탭 → [찾기 및 선택] → [이동 옵션]을 들어갑니다.

이후, [필드 값 없음]을 체크하여 확인을 누르면 아래와 같이 결측치가 있는 곳이 전부 회색처리 된 것을 알 수 있습니다.

이제 이 셀들이 포함된 행들을 지우기 위해 ‘ctrl -(command -)’를 눌러 행 전체를 선택하면 실행할 수 있습니다.

이 과정을 수행하면 결측치를 포함하던 행이 전부 삭제할 수 있습니다.

---

해당 열들의 값들을 기반으로 결측치를 예측

이 방식은 조금 더 고급 기법을 통해 결측치를 예측해주는 방법입니다.

주로 머신러닝이나 통계적 기법을 통해 해당 변수의 값들 간 관계를 직선/곡선의 형태로 산출해 준 후, 이에 따라 결측치를 할당해주는 기법입니다.

2번의 방식보다는 보다 탄탄한 근거를 통해 값을 예측할 수 있기 때문에 두 번째로 많이 쓰이는 방식이지만, 여전히 값에 대한 왜곡의 가능성이 존재하기는 합니다.

해당 열들의 값들을 기반으로 결측치를 예측하는 방법

우선 결측값의 위 아래 값들을 드래그 한 후, 홈 탭의 [채우기] → [계열]을 선택합니다.

이후 열 기준으로, 선형에 체크한 다음 확인을 누르면, 아래와 같이 결측값들이 선형 관계에 따라 예측되어 채워진 것을 볼 수 있습니다.

이제 이 과정으로 결측값이 있는 부분마다 일일이 이 작업을 해주면 결측값 처리를 완료할 수 있습니다.

---

이상치 처리

이상치는 존재 자체가 값들의 평균에 큰 영향을 미치기 때문에 보통 분석 전에 제거해줄 필요가 있습니다.

이상치의 기준을 어떻게 잡을 것인가에 대해서는 여러 방법이 존재하며, 엑셀에서는 쉽게 구현 가능한 상자수염과 사분위수를 활용하겠습니다.

 

우선, 이상치의 유무를 상자 수염 그림을 통해 확인할 수 있습니다.

결과적으로 income에만 고소득자 이상치들이 존재함을 확인했습니다.

이제 income 열에서 이상치들이 있는 행을 데이터에 직접 표시하기 위해 사분위수를 활용해야 합니다.

먼저, 3분위수와 1분위수를 구해보겠습니다. 수식은 =QUARTILE(범위, 분위값) 과 같습니다.

여기서 범위는 income에 해당하는 F2:F103이며, 분위값은 각각 3, 1입니다. 이를 반영하여 각 분위수 값을 산출하겠습니다.

다음으로 Q3-Q1을 통해 IQR을 구한 후, Q3 + 1.5*IQR을 통해income 열에서 Max Outlier를 판별하는 기준점을 찾겠습니다.

이제, H열에 IF함수를 이용하여 income 값이 Max Outlier 판별값인 M2보다 큰지 여부를 표시하고, 전체 열에 확대하면 아래와 같이 이상치인 행을 표시할 수 있습니다.

이제 이상치 행들을 삭제하는 방법으로 TRUE인 행들을 직접 찾아서 없앨 수는 없기 때문에 TRUE인 행을 결측치, 즉 빈칸으로 만든 후 행을 통째로 삭제해야 합니다.

우선, TRUE를 바꾸기로 빈칸으로 만들고, 이동옵션에서 빈 셀을 표시합니다.

ctrl -(command -) 를 통해 행을 통째로 없애주면, 아래와 같이 이상치까지 제거하여 전처리를 완료하게 됩니다.

---

이번 글에서는 전 글에 이어 엑셀의 데이터 전처리 / 범주형 변수 처리 / 결측치 처리 / 이상치 처리가 포함된 내용을 정리했습니다.

엑셀은 데이터 분석이나 데이터를 다루는 경우에 모두 필수적인 프로그램으로 개념을 확실히 숙지하고 활용할 필요가 있습니다.

이번 글도 읽어주셔서 감사합니다.

---

출처 및 참고자료 : 코드잇 사이트 강의 '엑셀을 활용한 추론 통계 실습' https://www.codeit.kr/topics/da-sprint-excel