#Project '파이썬으로 엑셀 크롤링하기'

 

엑셀 제목은 '20**년 **월 초과근무자 현황'. 매달 데이터 5000개가 저장되는데, 이를 합쳐서 1년 치, 2년 치 통계를 내야 했다. 즉, 김 아무개가 초과근무자 TOP 100 안에 있는지 찾아야 했다.

(예시)	부서	직책	이름	시간
1	총괄기획팀	대리	김아무개	3
2	해외사업팀	과장	박아무개	2

 

사용한 아이디어는 3가지이다.

왼쪽 순서대로 하나씩 실행한다.

• 첫 번째, 파이썬(openpyxl)을 이용해 24개의 파일을 하나로 통합한다.
• 두 번째, 통합한 파일에서 부서, 직책, 이름이 같으면 시간을 합친다.--> 개인별. xlsx
• 세 번째, 초과근무시간 TOP 부서를 알기 위해 부서가 같으면 시간을 합친다. --> 부서별. xlsx

 

1. openpyxl로 엑셀 파일 합치고 저장하기 (Can combine all sheet)

  구글링으로 openpyxl이라는 걸 알게 되었다. 생각보다 간편하다. 

openpyxl을 이용해 파일을 읽어온다.

가장 먼저 읽어올 파일들을 Todo라는 곳에 넣어뒀다. openpyxl은 경로에 있는 파일을 읽어오는 착한 친구다. 또 범위를 지정해주면 딱 그것만 읽는다. 가장 먼저 '직업' 데이터를 긁어왔다. 이렇게 파일을 읽어서 B라는 배열에 저장하면 준비 끝.

마지막으로 저장하는 코드

Workbook을 새로 만들어서 배열 B를 차곡차곡 쌓아준다(ws.append {i)). 이러면 새로운 엑셀 파일이 생긴다.

2. 부서별 총시간을 구한다. (Can merge total job)

  월별 데이터를 모두 모았으면, 개인별로 합치는 과정이 필요하다. 그전에 부서별로 확인을 먼저 해보았다. 이름을 고려하지 않고 부서만 고려하면 되어서 편했다. 엑셀 차트에서 '부서별 정렬'을 사용해 같은 부서가 붙어있도록 정렬했다. 이 부서가 바뀌는 순간까지 '시간'값을 모두 더하면 된다.

시간을 합쳐서 배열 B로 뽑아내자

먼저, Time_array로 모든 시간을 입력받고, 겹치는 걸 고려해서 Time_sort 배열로 옮기는 과정이다. i번째 이름을 봤을 때, i-1번째 값과 같으면, 두 개 시간을 합치면 된다. 더할 때는 temp_time에 값을 임시로 넣어두는 걸 이용했다. 이렇게 구해진 값을 results1과 result2에 각각 넣어주면 우리가 원하는 표의 배열이 완성된다.

추가적으로 배열의 크기가 [C2 : C8897]라고 직접 설정했다. 값이 없을 때까지 자동으로 범위를 할당하고 싶었는데, 그건 좀 어려웠다. 그래서 수작업으로 크기를 입력했다.

3. 최종적으로, 개개인 시간의 총합을 구하자. (can sort each person) 

  부서, 직책, 이름이 모두 같으면 '시간'을 더하는 방식으로 진행했다. 여기서 파일을 불러오기 전에 엑셀로 부서를 정렬했다. 그 이유는, 부서가 같다면 같은 이름을 가진 사람은 없을 것이라 생각했기 때문이다. (아마 확률적으로..? 사실 다 생각하기는 귀찮았다.) 이러면 이름-시간 관계만 생각하면 된다.

개개인 정렬은 배열의 크기가 5만개가 넘는다...

*최종 결과는 개인정보 때문에 올릴 수 없다. TOP 100 리스트가 나왔다.

**어려웠던 점

1. 엑셀도 행렬의 크기가 한정되어 있다는 사실을 처음 알았다. 많은 데이터를 가로로 배열하면 튕긴다. 세로로 배열해서 그 한계를 넘지 않도록 해야 한다. 

2. 한 번에 여러 개의 행을 저장하는 방법을 못 찾았다. 그래서 3개의 데이터가 필요하면, 3개의 엑셀 파일에 각각 저장해서 하나로 합치는 수작업이 필요했다.

3. 부서별 정렬을 할 때 엑셀의 도움을 받았다. 코딩을 하려면 너무 귀찮아서 안 했다. 코드 작성하기 전에 좀 찾아보면 쉽게 해결할 수 있다.

4. 노트북이 오래돼서 5만 개쯤 읽고 분석하는데 시간이 너무 오래 걸렸다. (대략 10분쯤). 왜 코드에서 최적화 문제가 생기는지 알 것 같다. 노트북 바꾸고 싶다.

 

-결론

회사에서 정보가 모이면 규모가 엄청 커진다. 통계내고 정리하는 게 쉽지 않을 것 같다. 엑셀에서도 이런 걸 지원해주면 좋을 것 같다. 파이썬에는 openpyxl과 같은 도구가 많으니 활용하면 좋을 것 같다.

+ 이 결과를 바탕으로 초과근무비 부정 수급자를 잡았다. 굉장히 뿌듯했다!