Automate Excel Reporting With Pandas
Page content
강의 홍보
- 취준생을 위한 강의를 제작하였습니다.
- 본 블로그를 통해서 강의를 수강하신 분은 게시글 제목과 링크를 수강하여 인프런 메시지를 통해 보내주시기를 바랍니다.
스타벅스 아이스 아메리카노를 선물로 보내드리겠습니다.
- [비전공자 대환영] 제로베이스도 쉽게 입문하는 파이썬 데이터 분석 - 캐글입문기
공지
제 수업을 듣는 사람들이 계속적으로 실습할 수 있도록 강의 파일을 만들었습니다. 늘 도움이 되기를 바라며. 참고했던 교재 및 Reference는 꼭 확인하셔서 교재 구매 또는 관련 Reference를 확인하시기를 바랍니다.
- 데이터는 코로나 데이터를 활용했다.
I. Overview
일부의 사람들이 R과 Python을 사용하지만, 대부분의 사람들은 엑셀을 사용한다. 피벗테이블은 모든 직장인이 알아야 하는 필수 용어로, 전체 데이터를 빠르게 요약해주는 일종의 skill이다.
오늘 배울 포스트는 피벗 테이블 작성과 파이썬에서 만들어진 그래프를 엑셀 파일에 포함하여 내보내는 예제를 준비하였다.
- 본 장에서는 피벗테이블에 대해서는 자세히 다루지는 않는다. 이전 포스트를 참고하기를 바란다.
II. 피벗 테이블 예제
- 국가별로 피벗테이블을 만들어보자.
(1) 라이브러리 불러오기
- 엑셀 자동화를 위한 라이브러리를 불러옵니다.
import pandas as pd
from tabulate import tabulate
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
Pandas모듈은 데이터 가공 및 엑셀 파일을 만드는 것과 관련이 있다.Openpyxl모듈은chart를 엑셀 파일에 추가할 때 필요하다.
(2) 데이터 가져오기
데이터는 코로나 데이터를 활용한다.1 데이터는 아래와 같은 변수로 구성이 되어 있다.
- dateRep, day, month, year: 기록된 날짜를 의미한다.
- cases: 한국에서는
확진자수로 정의되었다. - deaths: 사망자수이다.
- countriesAndTerritories, geoId, countryterritoryCode: 국가명과 연관이 있다.
url = 'https://raw.githubusercontent.com/chloevan/datasets/master/covid_19/covid_19.csv'
covid = pd.read_csv(url)
print(tabulate(covid.head(), tablefmt="pipe", headers="keys"))
| | dateRep | day | month | year | cases | deaths | countriesAndTerritories | geoId | countryterritoryCode | popData2018 |
|---:|:-----------|------:|--------:|-------:|--------:|---------:|:--------------------------|:--------|:-----------------------|--------------:|
| 0 | 2020/04/05 | 5 | 4 | 2020 | 35 | 1 | Afghanistan | AF | AFG | 3.71724e+07 |
| 1 | 2020/04/04 | 4 | 4 | 2020 | 0 | 0 | Afghanistan | AF | AFG | 3.71724e+07 |
| 2 | 2020/04/03 | 3 | 4 | 2020 | 43 | 0 | Afghanistan | AF | AFG | 3.71724e+07 |
| 3 | 2020/04/02 | 2 | 4 | 2020 | 26 | 0 | Afghanistan | AF | AFG | 3.71724e+07 |
| 4 | 2020/04/01 | 1 | 4 | 2020 | 25 | 0 | Afghanistan | AF | AFG | 3.71724e+07 |
covid.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8905 entries, 0 to 8904
Data columns (total 10 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 dateRep 8905 non-null object
1 day 8905 non-null int64
2 month 8905 non-null int64
3 year 8905 non-null int64
4 cases 8905 non-null int64
5 deaths 8905 non-null int64
6 countriesAndTerritories 8905 non-null object
7 geoId 8883 non-null object
8 countryterritoryCode 8824 non-null object
9 popData2018 8873 non-null float64
dtypes: float64(1), int64(5), object(4)
memory usage: 695.8+ KB
(3) 피벗테이블 만들기
- 판다스에는
.pivot_table()함수가 존재한다. - 각 국가별로 확진자수를 확인해보는 코드를 작성한다.
cases_by_country = pd.pivot_table(covid, index = 'countriesAndTerritories', values = 'cases', aggfunc='sum')
print(cases_by_country)
cases
countriesAndTerritories
Afghanistan 270
Albania 333
Algeria 1300
Andorra 466
Angola 10
... ...
Uzbekistan 266
Venezuela 144
Vietnam 240
Zambia 39
Zimbabwe 9
[204 rows x 1 columns]
- 먼저, 각각의 인수를 확인하자.
pivot_table()함수를 사용한다.covid는 데이터를 의미한다.index는 기준점이 되는 변수를 의미하며, 보통 문자열 변수를 작성한다.values는 계산하려는 변수를 의미하며, 보통 숫자열 변수를 작성한다.aggfunc는 기초통계함수가 온다. 여기에서는sum이 사용되었다. 참고로default는 평균을 의미하는mean이 사용된다.
(4) 피벗테이블 정렬
- 그런데, 정렬이 되어 있지 않으니 깔끔하게 정리된 것 같지가 않다.
cases를 기준으로 정렬을 해보자.
cases_by_country2 = cases_by_country.sort_values("cases", ascending=False).reset_index()
print(cases_by_country2)
countriesAndTerritories cases
0 United_States_of_America 312237
1 Spain 124736
2 Italy 124632
3 Germany 91714
4 China 82575
.. ... ...
199 Burundi 3
200 Bonaire, Saint Eustatius and Saba 2
201 Papua_New_Guinea 1
202 Falkland_Islands_(Malvinas) 1
203 Timor_Leste 1
[204 rows x 2 columns]
- 정렬을 위해서
sort_values()함수를 활용했다. - 내림차순으로 할지, 오름차순으로 할지 결정해야 하는데 기본적으로 오름차순으로 세팅이 되어 있기 때문에 내림차순으로 바꾸려면
ascending=False로 한다. - 행의 순서를 바꿔줘야 하기 때문에
reset_index()로 지정한다.
III. 엑셀 파일 만들기
- 피벗 테이블이 완성이 되었다면 이제, 엑셀 파일을 만들어보자.
file_path = ('/content/pandas_excel.xlsx')
cases_by_country2.to_excel(file_path, sheet_name = 'cases_by_country2', startrow=3)
!ls
pandas_excel.xlsx sample_data
- 실제 생성되었는지 확인해보자.
file_path는 파일명과 함께file_path가 저장되는 곳을 말한다.sheet_name는 시트명을 의미하고,startrow=3을 지정한 이유는headers를 추후에 지정하기 위해서다.
(1) 엑셀 파일 불러오기 및 제목 입력
- 만들어진 엑셀파일을
wb함수를 사용해서 불러온다. sheet1['A1']은 엑셀 열의 이름을 의미한다.- 각 열마다 텍스트를
# 엑셀 파일 불러오기
wb = load_workbook(file_path)
sheet1 = wb['cases_by_country2']
# 셀 텍스트 입력 및 스타일 적용
sheet1['A1'] = 'Top 5 Cases by Country'
sheet1['A2'] = 'Data is from EU Opne Data Portal'
sheet1['A4'] = 'NO'
sheet1['A1'].style = 'Title'
sheet1['A2'].style = 'Headline 2'
(2) 피벗테이블 Insert
- 작성된 피벗테이블을
for-loop를 활용하여 입력한다..style='Comma'은Cell값의 텍스트에 스타일을 주겠다는 뜻이며, 숫자이기 때문에1,000형태로 출력된다.
for i in range(5, 15):
sheet1[f'C{i}'].style='Comma'
(3) 그래프 작성
- 이제 본격적으로 그래프를 작성해본다.
# 그래프 작성하기
bar_chart = BarChart()
data = Reference(sheet1, min_col=3, max_col=3, min_row=4, max_row=15)
categories = Reference(sheet1, min_col=2, min_row=5, max_row=15)
bar_chart.add_data(data, titles_from_data=True)
bar_chart.set_categories(categories)
sheet1.add_chart(bar_chart, "F4")
bar_chart.title = 'Top 5 Cases by Country'
bar_chart.style = 3
wb.save(filename = file_path)
- 우선
BarChart()를 객체로 저장한다. data즉, value값과,category값에 해당하는 테이블을 찾는다.col은 테이블의 변수를 의미하고,row는 행을 의미한다.
IV. 결론
Pandas와 엑셀을 활용한 RPA 보고서의 첫발을 내딛었다. 영어로 된 것을 번역하지만, 설명이 조금 아쉬워서, 결국엔 패키지 공식 홈페이지를 가야 이해가 빨리 되었다.
다양한 Tutorial이 있으니, 꼭 openpyxl에서 확인 후 함수를 사용하기를 바란다.
V. Reference
Piepenbreier, N. (2020, April 16). Automate Your (Boring!) Excel Reporting with Python!. Retrieved from https://towardsdatascience.com/automate-excel-reporting-with-python-233dd61fb0f2
-
데이터 소스는
EU Opne Data Portal에 COVID-19 Coronavirus data에서 가져왔고 그중 일부를 csv로 변환하여github에 보관했다. ↩︎
