회사에서 업무를 하다보면 여러 개의 엑셀을 하나로 합쳐야 하는 경우가 있죠.
매일 반복되는 엑셀 수합 작업을 파이썬으로 자동화할 수 없을까요!?
1시간 걸릴 일을 10초만에 끝내고 놀 방법을 알아봅시다.
4개 지역구(강남구, 마포구, 용산구, 성동구)의 여러 편의점 브랜드들의 가맹점 수 및 기간 데이터가 있다고 가정해봅시다.
(해당 데이터는 실습을 위해 가상의 수치를 입력한 데이터입니다.)
편의점들은 각자의 코드를 가지고 이를 기반으로 나중에 편의점끼리 데이터를 합쳐야 합니다.
편의점 코드 기준으로 4개 지역 별로 값 계산해야 합니다.
- 가맹점수, 유지월수 : 지역 별로 값 계산, % 계산,
- 평균가맹월수 : 유지월수/가맹점수 비율 계산
Raw 데이터에는 편의점 이름은 없고 코드만 있으므로, 우리는 이 코드를 활용해서 최종적으로 편의점 별로 데이터를 계산해야 합니다.
그래서 최종적으로 output으로 얻고 싶은 데이터는 다음과 같습니다.
다음 순서대로 엑셀 파이썬 자동화 프로그래밍을 진행할 예정입니다.
0.데이터 전처리
1. 편의점 코드_편의점 매칭 작업
2. 편의점 별 값 합치기 : groupby
3. 평균가맹월수 채워넣기
4. 비율값 넣기
5. 칼럼별 합계,평균 채워넣기
6. 다시 멀티 컬럼으로 변경
7. 엑셀로 저장
0. 데이터 전처리
먼저 데이터를 읽어와야 겠죠. four_region이라는 변수에 4개 지역에서 수집되는 편의점 데이터를 저장합니다.
Raw data가 멀티 컬럼의 데이터라 우선은 header=[1]로 주고 1번째 컬럼으로 작업을 진행합니다.
그리고 마지막에 다시 원래의 멀티 컬럼으로 변경하는 작업을 할 예정입니다.
데이터를 살펴보면 216,217,218번째 행은 합계를 나타냅니다. 이 합계행들은 엑셀-파이썬 계산을 하는데 걸림돌이 되므로 삭제할 것입니다.
작업의 편리성을 위해 우리가 읽어온 데이터의 컬럼명들을 변경해줍니다.
그리고 합계 행들은 편의점 코드 값들이 NaN값입니다.
NaN값의 데이터 타입이 float라는 것을 이용해서 이 합계 행들을 삭제할 수 있습니다.
215행까지 남기고 그 이후 행들은 삭제되었습니다.
여기까지 데이터 전처리는 끝났습니다. 이제 앞으로는 편의점 코드 별 편의점 매칭작업을 할 것입니다.
1. 편의점 코드 별 편의점 매칭 작업
groupby를 사용하기 위해서 위처럼 편의점 컬럼을 추가해야 합니다. 그 작업을 해보겠습니다.
편의점 별 코드 리스트를 저장해두고, 이 코드와 편의점 이름을 매칭할 것입니다.
미리 만들어둔 편의점 빈 컬럼에 다음 반복문 코드로 편의점 이름 값을 넣도록 합니다.
2. 편의점 별 값 합치기 : groupby
드디어 groupby를 하기 위한 모든 조건이 갖춰졌습니다.
이제 편의점 컬럼 값들로 groupby를 해서 값들을 합계 내 보겠습니다.
다만 가맹점수, 유지월수는 합계지만, 평균가맹월수는 비율이므로 각각 따로 계산할 것입니다.
3. 평균가맹월수 채워넣기
4. 비율값 넣기
5. 칼럼별 합계,평균 채워넣기
6. 다시 멀티 컬럼으로 변경
7. 엑셀로 저장
엑셀을 파이썬을 이용해서 데이터를 수합하는 방법을 알아보았습니다.
물론 처음에는 코드로 구현하는게 더 어렵겠지만,
한번 파이썬으로 자동화를 해두면 다음부터는 엑셀 단순 반복 작업이 쉬워질 것입니다.
실제로 코드 실행하는 shift+enter 5번 만에 모든 엑셀 데이터 계산 작업이 종료되는 것을 확인할 수 있었습니다.
회사 업무에 이런 자동화 시스템을 많이 구현해 둔다면 훨씬 빠르고 정확한 업무가 가능할 것입니다.
시작은 어렵지만 그 끝은 쉬워지리라!