Python으로 Excel 대시보드 생성 자동화

데이터 과학 및 분석에 대해 배우는 모든 사람들에게 아직 자동화를 배우지 않았다면 이 기사가 당신의 삶을 쉽게 만들어 줄 것입니다!

작은 배경 이야기:

저는 데이터 분석가 및 데이터 과학 직책을 위해 6개월 동안 인터뷰에 참석했습니다. 모든 인터뷰는 일반적으로 동일한 패턴을 가지고 있거나 데이터 애호가가 하는 것처럼 관찰하기 시작했습니다. 일반적인 "자기 소개"가 하나 있으며 내 대답에 따라 추가 질문이나 내 이력서가 있습니다.

나는 데이터 분석 및 데이터 과학 요구 사항으로 회사를 도왔던 캡스톤 프로젝트의 일환으로 회사에서 인턴으로 일했습니다. 내 역할의 주요 측면은 매월 회사의 성과를 측정하기 위한 핵심 성과 지표(KPI)를 생성하는 것이었습니다. KPI가 무엇인지 모르는 사람에게 Wikipedia는 다음과 같이 말합니다.

자, 이 진술은 저를 인터뷰하는 사람이 흥미를 느끼고 그것이 제가 인터뷰하는 직책에 대해 수행해야 할 일과 어떻게 일치하는지 알려주고 그것에 대해 더 물어보기에 충분합니다. 그리고 내가 두려워하는 질문을 받게 될 것입니다.

"월간 보고서 생성을 위해 자동화를 구현했습니까?"

지금 이 순간, 저는 그들에게 솔직하게 말하고 싶습니다. 저는 그런 적이 없으며 그렇게 하지 않은 것을 정말 후회합니다! Python, SQL 및 Excel을 아는 사람이 삶을 쉽게 만들기 위해 자동화하지 않습니까? 진짜? 또한 소프트웨어 엔지니어링 배경이 있습니다!

그래서 오늘 변경하기로 했습니다. Excel 보고서 생성을 자동화했습니다. 드디어 해냈습니다!

진짜 물건과 약간의 보풀

이 기사를 통해 버튼 클릭 한 번으로 Excel 대시보드 생성 및 간단한 데이터 파이프라인 생성을 자동화하는 방법을 확인할 수 있습니다.

글쎄요, 이것은 단지 하나의 버튼이 아니라 몇 줄의 코드입니다!

데이터 분석 대시보드 생성 여정을 시작하는 모든 사람과 마찬가지로 저는 분석가 Alex의 YouTube에서 간편한 Excel 대시보드로 시작했습니다.

40분 분량의 이 비디오는 데이터의 아름다움을 이해하는 데 큰 도움이 되었습니다. 이번 영상에서 사용된 데이터셋을 이용해서 만들어진 대시보드 생성을 자동화 해보겠습니다.

파이썬 라이브러리 — openpyxl을 사용하겠습니다.

1. 먼저 엑셀 파일을 읽어보자.

#Reading the Excel file and the sheet name
file_name = 'Bike_Sales_Playground.xlsx'
bike_df = pd.read_excel(file_name,sheet_name='bike_buyers')

#We don't want to mess with our raw data, thus, making a copy of it into a sheet called Working_Sheet.
 with pd.ExcelWriter(file_name,#Name of the Workbook
                    engine='openpyxl',#Name of the engine
                    mode='a',#Append mode
                   if_sheet_exists="replace" #Replacing the sheet if it already exists
                   ) as writer:
  bike_df.to_excel(writer, sheet_name='Working_Sheet',index = False)#Setting index to False to avoid the unnecessary column Unnamed:0

 #Let's read the working sheet data into our dataframe
 bike_df = pd.read_excel(file_name,sheet_name='Working_Sheet')

#Dropping duplicates from the data
bike_df.drop_duplicates(keep='first', inplace=True, ignore_index=False)
    
#Replacing M to Married and S to Single in Marital Status column
bike_df['Marital Status'] = bike_df['Marital Status'].replace('M','Married').replace('S','Single')
    
#Replacing F to Female and M to Male in Gender column
bike_df['Gender'] = bike_df['Gender'].replace('F','Female').replace('M','Male')

#Viewing the changed column values
bike_df.head()

#Age is better in brackets
bike_df['Age brackets'] = bike_df['Age'].apply(lambda x: 'Less than 30' if x<=30 else('Greater than 55' if x>55 else '31 to 55'))

#Replacing Commute Distance value 10+ Miles to More than 10 Miles
bike_df['Commute Distance'] = bike_df['Commute Distance'].replace('10+ Miles','More than 10 Miles')

#Pivot table 1
#Average Income per Gender based on Purchased Yes or No
avg_gender_income_df = np.round(pd.pivot_table(bike_df,
               values = 'Income',
               index = ['Gender'],
               columns = ['Purchased Bike'],
               aggfunc = np.mean
              ),2)

#Now that we have made all changes in the dataframe, let's load it into the excel file
with pd.ExcelWriter(file_name,#Name of the Workbook
                    engine='openpyxl',#Name of the engine
                    mode='a',#Append mode
                   if_sheet_exists="replace" #Replacing the sheet if it already exists
                   ) as writer:  
    
    avg_gender_income_df.to_excel(writer, sheet_name='Average_Gender_Income')

# loading workbook and selecting sheet
wb = load_workbook(file_name)
sheet = wb['Average_Gender_Income']

# Bar chart creation
chart1 = BarChart()
chart1.type = "col"
chart1.style = 10
chart1.title = "Average Income by Gender and Purchase Data"
chart1.y_axis.title = 'Gender'
chart1.x_axis.title = 'Income'

#Attach the chart to the worksheet
data1 = Reference(sheet, min_col=2, min_row=1, max_row=3, max_col=3)#Including Headers
cats1 = Reference(sheet, min_col=1, min_row=2, max_row=3)#Not including headers
chart1.add_data(data1, titles_from_data=True)
chart1.dataLabels = DataLabelList() 
chart1.dataLabels.showVal = True
chart1.set_categories(cats1)
chart1.shape = 4
sheet.add_chart(chart1, "A10")
wb.save(file_name)

#Creating an empty Dataframe
title_df = pd.DataFrame()

#Now that we have made all changes in the dataframe, let's load it into the excel file
with pd.ExcelWriter(file_name,#Name of the Workbook
                    engine='openpyxl',#Name of the engine
                    mode='a',#Append mode
                   if_sheet_exists="replace" #Replacing the sheet if it already exists
                   ) as writer:  
    
    title_df.to_excel(writer, sheet_name='Dashboard')

# loading workbook and selecting sheet
wb = load_workbook(file_name)
sheet = wb['Dashboard']
for x in range(1,22):
    sheet.merge_cells('A1:R4')
    
cell = sheet.cell(row=1, column=1)  
cell.value = 'Bike Sales Dashboard'  
cell.alignment = Alignment(horizontal='center', vertical='center')   
cell.font  = Font(b=True, color="F8F8F8",size = 46)
cell.fill = PatternFill("solid", fgColor="2591DB")

#Adding all our pivot charts to the dashboard
sheet.add_chart(chart1,'A5')
sheet.add_chart(chart2,'J5')
chart3.width = 31
sheet.add_chart(chart3,'A20')
wb.save(file_name)

7. 이제 이 모든 것을 함수에 넣고 실행할 때입니다.

import Bikes_Sales_Report_Automation as auto

#Enter your filename here
auto.automate_excel_dashboard('Bike_Sales_Playground.xlsx')

요약:

실행 시 엑셀 대시보드를 자동으로 생성하는 간단한 함수를 만들었습니다. 대시보드에서 KPI를 생성하기 위해 매달과 같이 주기적으로 보고서를 생성하고 싶을 때 언제든지 이 기능을 실행할 수 있습니다.

제한 사항:

Alex가 자신의 비디오에서 가지고 있었던 멋진 Excel 슬라이서를 추가하는 방법을 알아낼 수 있으면 좋겠지만 openpyxl은 슬라이서를 추가하는 데 제한이 있습니다. Python을 사용하여 Excel 슬라이서를 추가하는 방법을 알고 있다면 알려주세요!

여기 에서 코드와 최종 출력을 확인 하십시오 .