2.3 엑셀과 스프레드 시트로 타이타닉 데이터 분석하기

1. 데이터와 엑셀 구조 2. 데이터 선택과 결측치 처리 3. 피벗 테이블 3.1 피벗테이블 도구 1) 분석탭 2) 디자인탭 4. 문제풀이 문제1) 티켓등급(Pclass)에 따른 생존율 구하기 문제2) 좌석 등급별 남녀 생존율을 구하기 문제3) 생존자별 평균요금(Fare)나타내기 문제4) 승선한 항구(Embarked)에 따른성별(Sex)별 인원 수 나타내기 문제5) 각 성별(Sex)에 따른 생존율 계산하기 문제6) 각 항구(Embarked)에 따른 생존율 계산하기 5. Google 스프레드 시트로 분석하기

💡

아래 데이터는 train.csv를 사용했습니다. 전 챕터에서 다운로드 받아주세요.

1. 데이터와 엑셀 구조

엑셀을 이용하여 타이타닉 데이터를 분석하기 전에 데이터의 구조에 대하여 알아보겠습니다.

데이터의 구조는 매우 중요합니다. 여기서 각각의 분류대로 Column 할당된다는 것을 기억하세요. 만약 Row로 되어 있는 값을 붙여넣고 싶다면, 반드시 Column으로 변환하여 삽입해야 합니다. 여기서 독립 변수는 원인, 종속 변수는 결과라고 이해해주세요. 이 부분은 뒤에서 머신러닝을 다룰 때 좀 더 자세하게 다룹니다. 머신러닝 부분을 할 때, 어떤 변수들을 독립변수로 선정할지, 어떤 변수를 종속변수로 설정할지 선택해야 합니다.

엑셀은 기본적으로 행(Row), 열(Column), 셀(Cell), 시트(Sheet)로 구성되어 있습니다.

행(Row) : 세로줄

열(Column) : 가로줄

셀(Cell) : 엑셀 중간 빨간색 네모 박스, 값을 입력하는 공간

시트(Sheet) : 가장 아래 빨간색 네모 박스, 각각의 새로운 페이지

독립변수 : 독립적으로 영향을 받지 않는 변수, 원인

종속변수 : 의존 변수, 영향을 받는 변수, 결과

이 챕터는 기본적으로 엑셀로 데이터를 다루지만 Google 스프레드 시트를 함께 다룹니다. 구글 스프레드 시트를 사용하시는 분은 이 챕터 가장 마지막 장을 확인해주세요.

데이터를 좀 더 살펴볼까요? 중간중간 비어있는 값들이 보이시죠? 이러한 부분을 '결측치'라고 합니다. 결측치말고도 이상치가 있을 수도 있는데, 예를 들어 나이가 1000이 들어가 있다면 이성적으로, 논리적으로 들어가면 안되는 값이기 때문에 이상치에요.

2. 데이터 선택과 결측치 처리

데이터를 구했다면, 가장 먼저 사용할 데이터와 사용하지 않을 데이터를 구분해야 합니다. 엑셀 분석에서는 Ticket을 사용하지 않습니다. 데이터를 삭제해보세요.

이렇게 사용하지 않는 데이터를 처리하였다면 결측치를 처리하도록 하겠습니다 우선 홈에 우측 상단에 있는 필터를 활성화시켜주세요.

그러면 아래와 같이 값을 필터링 할 수 있게 됩니다. age를 클릭하시고 모두 선택을 해제해주세요.

필드값 없음만 체킹하신 다음 몇 개의 값이 결측치인지 파악해보도록 하겠습니다.

F라고 되어 있는 부분에 마우스를 올려놓으시고 클릭하시면 하단에 몇 개의 레코드가 있는지 보여집니다.

177개의 레코드가 비어있군요. 어느 값으로 채워야 할까요? 엑셀에서는 나이에 평균을 구해 넣도록 하겠습니다. 결측치는 자동 배제됩니다.

다시 위로 올라가 age 필드에서 필터를 눌러 모두 선택해주세요. 그리고 893번 Row로가서 F893번 셀에 =AVERAGE(F2:F892) 수식을 입력하도록 하겠습니다.

29.69912값이 나오고, 그 값을 채워넣도록 하겠습니다. 다시 위로 올라가 필터를 누르신 후 (필드 값 없음)을 선택하시고 모든 셀에 29.67값을 입력해주세요. 저는 소수점 3번째 자리에서 반올림 하였습니다. 하나의 값만 입력하고, 드래그 하시면 됩니다.

다시 (전체 선택) 을 눌러보시면, 결측치가 제대로 채워진 것을 볼 수 있습니다. 평균값도 조금 바뀐 것을 볼 수 있습니다.

3. 피벗 테이블

피벗 테이블은 많은 데이터 중에 원하는 값을 선택하여 표를 만들어 주는 방법입니다. 메뉴→삽입→피벗테이블을 선택해줍니다. 피벗테이블을 처음 만들 때는 새 워크시트를 선택해줍니다.

확인을 누르시면 아래와 같은 화면이 나오게 됩니다.

3.1 피벗테이블 도구

아래와 같이 PassengerId와 Survived, Pclass를 선택한 후 각각을 열, 행, 값으로 배치해주세요. 여기서 주의할 점은 값에 PassengerId는 합계가 아니라 개수입니다.

여기서는 상단에 있는 분석탭과 디자인탭에 대해 알아보도록 하겠습니다. 이 부분은 데이터 분석에 있어서 그리 중요한 부분은 아니니 바로 문제풀이 챕터로 넘어가셔도 좋습니다.

1) 분석탭

피벗 테이블 : 피벗테이블의 이름을 설정할 수 있습니다.

활성 필드 : 필드의 크기 설정 및 선택 된 필드가 표시됩니다.

그룹 : 그룹 선택, 해제, 필드를 할 수 있습니다.

필터 : 슬라이서 삽입, 시간 표시 막대를 삽입 할 수 있습니다.

데이터 : 새로 고침, 데이터 원본을 변경할 수 있습니다.

동작 : 피벗 테이블 지우기, 이동, 선택을 할 수 있습니다.

계산 : 계산 필드, 계산 순서, 보고서 등을 작성할 수 있습니다.

도구 : 피벗 테이블에 연결된 데이터를 차트로 표현해 주고, 사용자의 데이터에 적합한 피벗 테이블을 추천해줍니다.

2) 디자인탭

디자인 탭은 피벗테이블에 대한 전체 디자인을 바꿀 수 있는 곳입니다.

부분합 : 부분합을 선택/해제 및 표시할 위치를 선택할 수 있습니다.

총합계 : 사용자가 원하는 총합계의 구역을 선택할 수 있습니다.

보고서 레이아웃 : 보고서의 다양한 형식을 선택할 수 있습니다

빈 행 : 각 항목에 빈 줄은 삽입/제거 할 수 있습니다.

피벗 테이블 스타일 옵션 : 머리글의 선택 및 줄무늬를 선택할 수 있습니다.

피벗 테이블 스타일 : 피벗 테이블의 다양한 색상을 선택할 수 있습니다.

4. 문제풀이

문제를 풀면서 피벗테이블에 대하여 좀 더 알아 보겠습니다.

문제1) 티켓등급(Pclass)에 따른 생존율 구하기

아래처럼 생존율을 구해보세요! 엑셀 수식을 사용해보세요. 새로운 값이 들어갈 곳을 더블 클릭 한 후 등호(=)로 시작하시면 됩니다.

여기서 여러분만에 가설을 세워보세요. 예를 들어, 아래와 같은 가설은 맞는 가설일까요?

1등급 객실에 탄 사람은 구조 확율이 높았다.

아래에서 확인해보도록 하겠습니다.

수식을 사용하지 않고 아래처럼 직접 입력해도 됩니다.

문제2) 좌석 등급별 남녀 생존율을 구하기

위에서 세운 가설은 간단한 데이터 분석으로도 아니라는 것을 알 수 있습니다. 1등석 남성의 경우 3등석 여성의 생존율보다 낮습니다. 자, 본격적인 가설은 여기서 세우지 않도록 하겠습니다. 하지만 여러분들이 분석을 하시면서도 여러개의 합리적 가설을 세우는 것은 매우 중요한 문제입니다.