[R] 어느 날, 엑셀 계산이 맞지가 않는다?
의식의 흐름대로 쓰는 - 왜 R인가? MS Excel로는 안 될까?(1)
올해 초 부서를 옮기고 나서, 데이터를 다룰 일이 생겼다.
업무 분류가 썩 명확하진 않지만 세간의 직무로 나눠보자면 '데이터 엔지니어'와는 약간 다른 '데이터 분석가' 정도의 일이었다.
물론 그때 그 일이 내 일이라고 말한 사람은 없었지만 그런 업무가 있었을 뿐이고. 자연스레 하게 됐다.
엔지니어로부터 처음 넘겨받아 파악한 자료는 수백만 줄짜리 데이터였다.(대략 300~400만 건이었던 것으로 기억한다. 이하 숫자들은 글의 흐름상 문제가 없을 만한 임의의 값으로 적었다.)
확인해야 할 여러 문장들과 각 문장의 빈도가 집계된,
"문장","횟수"
1 "안녕하세요. 반갑습니다.",1246793
2 "하우아유 아임파인땡큐 앤유?",325878
3 "고맙습니다.",133345
...
3000000 "여기까지 데이터가 있을 줄은 상상도 못했죠?", 1
이런 내용들이었다.
파일 형식은 쉼표 즉 comma(,)로 분리된 CSV 파일이었고 데이터 확인을 위해 MS 엑셀에서 열었다. 300만(!)줄이긴 하지만 컬럼 수는 두 개밖에 안 된다. 용량은 대략 수십 MB쯤이었는데 잠깐의 로-딩 후에 파일이 열렸다. A열에 문장, B열에 숫자.
세 줄 요약은커녕 30줄도, 300줄도 아니고 300만줄이라니.
봐도봐도 한 눈에 안 들어오고 데이터가 너무 많다.
뭔가 데이터를 가공할 필요가 있었다.
그래서 총 얼마지?
=SUM(B:B)
4,000만? 그렇군.
Top 1은 얼마나 비중을 차지할까?
'...?'
잠깐 머뭇했지만 이내
=B2/SUM(B:B)*100
5.678%? 그렇네.
물론 SUM을 매번할 필요는 없으니까 C1에 '=SUM(B:B)'을 입력하고 C2에는 '=B2/$C$1*100' 이라고 절대값을 넣으면 되겠다.
C2셀의 우하단 더블클릭=자동채우기를 이용해서 전체 데이터의 비율들을 구했다.
CPU 점유율이 100%를 나타내고 버벅이기 시작한다. 그래도 큰 문제 없이 계산 끝.
5.678%, 3.098%, 1.876%, ...
최상위는 얼추 알아보겠지만 저~기 아래로 내려가면 0.00012%, 0.00011%, 0.00011%다.
생각해보면 이런 비중이 무슨 의미가 있겠으며, 차트로 나타내기도 어렵다.
누적합을 구해보자. 이렇게 해서 상위 50%, 절반만 살펴보는 것도 괜찮을 듯싶다.
D열에 현재 행 숫자 + 위에서 누적되어 내려온 누계를 적으면 되겠다.
D2에는 우선 '=B2'를 넣고 다음줄부터는
=B3+D2
순환참조까지는 아니지만 앞의 연산 결과가 뒤의 연산에 영향을 주는 수식이 됐다. 이쯤되면 컴퓨터가 현저히 느려지기 시작한다.
잠깐 퀴즈, 누적합에 이어 그 다음 누적비율은 어떻게 구할까?
.
.
.
=D2/$C$1*100
이전과 딱히 다를 게 없다.
수백만*n개의 수식이 열심히 돌고 있다.
한 줄이라도 추가/삭제하거나 중간에 하나라도 숫자가 바뀌면 연산을 열심히 다시 돌리는 엑셀을 볼 수 있다.
뭐 그래도 PC 성능이 나쁘지 않다면,
한두 번 하고 말 일이면 그냥 이렇게 써도 충분하다.
...고 생각했다.
그런데 문제가 생겼다.
처음에 구한 전체 합 '=SUM(B:B)'이 RAW 데이터에서 집계돼 넘겨받은 숫자와 다르다?
어디서 잘못된 걸까?
[본문에 쓴 수식 참고용] 엑셀 파일
*다음 글 : 어느 날, 엑셀의 한계에 봉착했을 때
