Automatizando a criação de painéis do Excel com Python

Para todos aqueles que estão aprendendo sobre ciência e análise de dados, se você ainda não aprendeu automação, este artigo facilitará sua vida!

Um pouco de história:

Estou participando de entrevistas há 6 meses para cargos de analista de dados e ciência de dados. Todas as minhas entrevistas geralmente têm o mesmo padrão, ou melhor, comecei a observá-las como qualquer entusiasta de dados faz. Há um “fale-me sobre você” que é comum e com base na minha resposta, há outras perguntas sobre ele ou meu currículo.

Estagiei em uma empresa como parte de meu projeto principal, onde ajudei a empresa com seus requisitos de análise de dados e ciência de dados. Um aspecto importante da minha função era gerar indicadores-chave de desempenho (KPIs) para medir o desempenho da empresa mensalmente. Para quem não sabe o que é KPI, é isso que a Wikipédia diz:

Agora, essa declaração é suficiente para que a pessoa que está me entrevistando fique intrigada e me diga como isso se alinha com o que deve ser feito para o cargo para o qual estou sendo entrevistado e me pergunte mais sobre isso. E então, a pergunta que eu temo seria feita.

“Você implementou automação para gerar seus relatórios mensais?”

Neste momento, só quero ser honesto com eles e dizer-lhes que não o fiz e como me arrependo muito de não o ter feito! Uma pessoa que conhece Python, SQL e Excel não automatiza para facilitar sua vida? Sério? Além disso, tenho experiência em engenharia de software!

Então, decidi mudar isso hoje. Eu automatizei a geração de meus relatórios de Excel. Finalmente consegui!

O Real Stuff e apenas um pouco de Fluff

Através deste artigo, você verá como podemos automatizar a geração de um painel do Excel e criar um pipeline de dados simples com apenas um clique de um botão.

Bem, não é apenas um botão — são algumas linhas de código!

Como qualquer pessoa que inicia sua jornada de criação do painel de análise de dados, comecei com o painel de Excel fácil do meu amigo Alex, o analista, no youtube.

Este vídeo de 40 minutos me ajudou muito a entender a beleza dos dados. Vou usar o conjunto de dados usado neste vídeo e automatizar a criação do painel feito.

Estarei usando a biblioteca python — openpyxl

1. Vamos ler o arquivo excel primeiro.

#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. É hora de colocar tudo isso em uma função e executá-la

import Bikes_Sales_Report_Automation as auto

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

Resumo:

Criamos uma função simples que ao ser executada gera um dashboard excel automaticamente. Você pode executar esta função sempre que desejar gerar um relatório periodicamente, como a cada mês, para gerar KPIs em um painel.

Limitações:

Eu gostaria de descobrir como adicionar o cortador de Excel legal que Alex tinha em seu vídeo, mas o openpyxl tem uma limitação para adicionar um cortador. Se você sabe como adicionar fatiadores do Excel usando python, me avise!

Confira o código e a saída final aqui .