[데이터분석] 구글 스프레드시트 활용하기 -2

Intro

이전 글: https://rubpcase.tistory.com/49

앞선 챕터에서 스프레드시트가 무엇인지 알아보고 간단하게 사용해보는 시간을 가졌다.
이번에는 조금 더 넘어가서 함수와 피벗 테이블(피봇 테이블)을 만들고 활용하는 시간을 가져보도록 할 것이다.
사실 이런 시트에서 사용하는 함수는 진짜 많은데, 여기선 아주 조금만 다룰 생각이다.
(컴활 1급 자격증 책 보면 알겠지만 외워야 할 함수가 너무 많다...)

함수 사용하기 1 (제조사 명 추출)

이번에는 시트에서 함수를 활용해 볼 생각이다.
스프레드시트에는 활용할 수 있는 다양한 함수들이 제공되고 있다.
앞선 글에서도 importdata()와 같은 친구를 활용했는데, 이게 함수라 보면 된다.

먼저 위와 같이 지난 번에 사용했던 엑셀의 데이터 뒷 부분에 company라는 항목을 적어주자.
그리고 다음의 명령을 입력해보자.

=find(" ", B2)

해당 find 명령은 B2셀의 데이터에서 빈칸이 몇 번째에 있는지를 알려주는 함수이다.
구조는 find("찾을 문자열", 셀 위치)라 보면 된다.
참고로 B2셀에는 Mazda RX4라는 값이 들어있을 것이다.

아무튼, 위와 같이 함수를 사용하게 되면 자동으로 값을 계산해 줄 것이다.
위에선 6이라는 값으로 나옴을 알 수 있다.
그리고, 자동 완성도 알아서 제안해주는데, 일단 우리는 다른 함수를 추가로 쓸 것이므로 넘어가자.

이번에는 위 그림과 같은 left 함수를 앞선 find 함수랑 섞어서 사용해보자.

=left(B2, find(" ", B2)-1)

left함수는 left(셀 위치, 정수값)의 구조로 사용된다.
해당 셀 위치에서 정수값 번째 이전의 문자(혹은 왼쪽의 문자)들을 모두 출력하는 함수이다.
다만, 우리가 띄어쓰기의 위치를 찾았기 때문에, find()값을 그대로 사용하게 되면 띄어쓰기까지 포함해서 출력되어버린다.
고로, find()-1의 값을 위와 같이 집어넣었다 보면 된다.

나머지 셀에도 기존에 했던 것처럼 끌어서 자동으로 함수를 채워넣으면 된다.
그런데, 보면 알겠지만 중간에 #VALUE!라는 오류가 뜨는 것을 확인할 수 있다.
이는 해당 시트에서 함수를 잘못 사용한 경우를 의미하며, 해당 셀의 데이터를 보면 다른 데이터와는 다르게 Valiant로, 띄어쓰기가 없다는 것을 알 수 있다.
즉, 띄어쓰기 문자를 찾지 못해서 find에서 오류가 발생했고, 그 이후의 left에서도 연달아 오류가 발생했다고 보면 된다.

그렇다고 값을 수동으로 기입할 수도 없는 터라, 함수를 변형하는 방법밖에 없다고 본다.
그러나, 위 오류를 처리할 수 있는 방법이 있다.
바로 iferror() 함수를 사용하면 된다.

iferror는 iferror(값 또는 수식, 오류 시 대신 나타낼 값 또는 식)의 구조로 쓴다.
즉, iferror에서 첫 번째 항에 오류가 없으면 해당 값을 출력하고, 오류가 나면 그 다음 값을 출력한다 보면 된다.

따라서 앞선 코드를 보강하기 위해서 다음과 같이 추가 코드를 작성했다.

=left(B2, iferror(find(" ", B2)-1, len(B2)))

iferror를 사용하되, 오류가 났을 경우 len(B2)를 출력하도록 설정했다.
여기서 len이라는 함수는 해당 문자열의 길이를 의미한다.
가령, Valiant라는 값은 문자가 7개니까 7로 출력이 될 것이다.

이제 위 함수로 모든 셀을 채워넣으면 위 그림과 같이 Valiant에 관해서도 정상적인 제조사 명을 얻을 수 있게 된다.

함수 사용하기 2 (평균은 넘을까 판별)

이번에는 mpg열의 데이터를 기준으로 함수를 작성해보려 한다.
mpg열의 평균값보다 데이터가 큰 경우를 판별하는 열을 만들어 볼 것이다.

우선, 아까 만든 company열 옆에 isoveravgmpg라는 열 이름을 붙여주자.
이후 위 그림과 같은 식을 해당 위치에 넣어보자.

=average(C2:C33)

위 average는 해당 범위의 모든 값의 평균을 구해주는 함수이다.
여기선 C2부터 C33까지의 모든 값의 평균을 계산해준다. (20.091정도로 계산된다.)
그런데, 위 함수를 그대로 나머지 칸에 끌어서 채워넣으면 문제가 발생한다.
평균을 구하는 셀 위치가 자동으로 바뀌어버린다는 문제점이 생긴다.

따라서 위와 같이 함수 구조를 바꿔야 한다.
$ 기호를 넣게 되면, 행 또는 열을 고정하겠다는 의미라 보면 된다.
우리는 일단 행/열 구분 없이 위치를 고정시킬 생각이니 저렇게 모든 위치에다 달러 표시를 넣어주면 된다.

이때, 수작업으로 달러를 삽입할 필요 없이 단축키 하나만 쓰면 자동으로 변환할 수 있다.
F4 단축키를 누르면 자동으로 위와 같은 형태로 바꿔준다.
혹시 궁금하면 F4키를 계속 눌러보기 바란다.
달러의 위치가 바뀔 것이다.
이와 관련해선 살짝 검색해서 그 의미를 파악하길 바란다.
(앞선 문단에서 잠깐 설명하긴 했지만, 그래도 이해가 안되면 찾아보는게 최고다)

아무튼, 달러 기호를 붙인 상태로 값을 끌어내리면 20.091의 값이 고정으로 나오는 것을 확인할 수 있다.

이제 마지막으로 if() 활용해서 평균을 넘는지 여부를 파악해보자.
if 함수는 if(조건식, True일 때 표현할 값/식, False일 때 표현할 값/식)의 구조로 쓴다.
여기선 해당 열의 mpg 데이터가 평균을 넘을 경우 "YES", 아니면 "NO"를 출력하도록 코드를 짰다.

=if(C2 > average($C$2:$C$33), "YES", "NO")

최종적으로 모든 행에다가 끌어서 함수를 적용해주면 된다.

피벗 테이블 만들기

마지막으로 피벗 테이블을 만들어보도록 하자.
피벗 테이블은 쉽게 말하면 데이터 요약본이라 보면 된다.
앞선 표를 봐선 어떤 값이 얼마나 있는지 판별하기 힘드니 피벗 테이블을 만든다고 보면 되겠다.

먼저 위 그림과 같이 데이터 전체를 선택하고, 삽입 -> 피봇 테이블을 선택해주자.
참고로 피벗 테이블 기능의 위치가 바뀐지 얼마 안 되어서 예전 버전과 차이가 있다.
그 다음 나오는 피봇 테이블 만들기 창에서 새 시트를 선택해주고 만들기를 눌러주자.

그러면 이렇게 심심한 형태로 새로운 시트에 이동하게 된다.
여기서 피봇 테이블 편집기를 변형해서 원하는 분포를 파악할 수 있다.
참고로 피봇 테이블은 연속형 변수보다는 이산형/범주형 변수를 행/열로 두는게 보기 편하다.
연속형/이산형/범주형 변수에 대한 설명은 여기선 생략한다.
아마 차후에 다룰 기회가 있을 것이라 본다.

아무튼 이제, 카 뷰레터의 개수(carb)와 기어의 수(gear)에 따른 배기량 정보(displ) 를 파악할 생각이다.
따라서 위와 같이 행에는 carb, 열에는 gear, 값에는 disp로 변수를 설정하고, 이때 disp는 AVERAGE(평균)로 요약하도록 설정해주자.
그러면 위와 같이 테이블이 형성될 것이다.

해당 테이블은 아까 말했듯, 카 뷰레터의 개수와 기어의 수에 따른 배기량 평균값을 나타낸다.
보면 알겠지만 결측치(빈칸)도 존재하는데, 이는 해당되는 값이 없기 때문이라 보면 된다.
가령, 카 뷰레터의 개수가 6개이고 기어의 수가 3개인 차량의 데이터가 없다라고 보면 되겠다.
또한, 각 행과 열의 끝에는 총계 평균도 나와있으니 이를 토대로 데이터를 파악하면 좋다.

여기서 더 나아가서 차트를 생성해보도록 하자.
차트 생성은 앞선 글에서도 다뤘는데, 여기서는 산점도 대신 막대 그래프를 활용할 생각이다.
위 사진처럼 총계를 제외한 나머지 데이터를 모두 선택한 후 삽입 -> 차트를 눌러서 차트를 만들어주자.

그러면 위와 같이 (마음에 드는 디자인은 아니나) 차트가 그려진다.
원한다면 각 봉에 해당하는 값을 표시할 수 있고, 색상도 바꿀 수 있다.
이건 직접 조정해보길 바란다.

마무리

이제 스프레드시트의 기능을 맛보기로 활용할 수 있는 수준까지 왔다.
이 이상은 필요한 기능이 있으면 직접 찾아보면서 사용해야 할 것이라 생각한다.
나중에 필요하다면 추가로 스프레드시트 관련 설명을 할 생각이나, 일단 현재 생각하기엔 여기까지 포스팅을 수행해도 괜찮을 것 같다 느낀다.