Automatisation de la création de tableaux de bord Excel avec Python

À tous ceux qui apprennent la science des données et l'analyse, si vous n'avez pas encore appris l'automatisation, cet article va vous faciliter la vie !

Une petite histoire :

Je passe des entretiens depuis 6 mois maintenant pour des postes d'analyste de données et de science des données. Toutes mes interviews ont généralement le même schéma ou plutôt j'ai commencé à les observer comme le fait n'importe quel passionné de données. Il y a un "parlez-moi de vous" qui est courant et basé sur ma réponse, il y a d'autres questions dessus ou sur mon CV.

J'ai effectué un stage dans une entreprise dans le cadre de mon projet Capstone où j'ai aidé l'entreprise à répondre à ses exigences en matière d'analyse de données et de science des données. Un aspect majeur de mon rôle était de générer des indicateurs de performance clés (KPI) pour mesurer la performance de l'entreprise sur une base mensuelle. À tous ceux qui ne savent pas ce qu'est le KPI, voici ce que dit Wikipedia :

Maintenant, cette déclaration est suffisante pour que la personne qui m'entretient soit intriguée et me dise comment cela correspond à ce qui doit être fait pour le poste pour lequel je suis interviewé et me demande plus à ce sujet. Et puis, la question que je redoute serait posée.

"Avez-vous mis en place une automatisation pour générer vos rapports mensuels ?"

En ce moment, je veux juste être honnête avec eux et leur dire que ce n'est pas le cas et que je regrette vraiment de ne pas l'avoir fait ! Une personne connaissant Python, SQL et Excel n'automatise pas pour se faciliter la vie ? Vraiment? De plus, j'ai une formation en génie logiciel!

J'ai donc décidé de changer cela aujourd'hui. J'ai automatisé la génération de mes rapports Excel. Je l'ai enfin fait !

The Real Stuff et juste un peu Fluff

À travers cet article, vous verrez comment nous pouvons automatiser la génération d'un tableau de bord Excel et créer un pipeline de données simple d'un simple clic sur un bouton.

Eh bien, ce n'est pas qu'un bouton, c'est quelques lignes de code !

Comme tous ceux qui commencent leur parcours de création de tableaux de bord d'analyse de données, j'ai commencé avec le tableau de bord Excel facile de mon homme Alex l'analyste sur youtube.

Cette vidéo de 40 minutes m'a beaucoup aidé à comprendre la beauté des données. Je vais utiliser le jeu de données utilisé dans cette vidéo et automatiser la création du tableau de bord made.

Je vais utiliser la bibliothèque python - openpyxl

1. Lisons d'abord le fichier excel.

#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. Il est temps de mettre tout cela dans une fonction et de l'exécuter

import Bikes_Sales_Report_Automation as auto

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

Résumé:

Nous avons créé une fonction simple qui, lorsqu'elle est exécutée, génère automatiquement un tableau de bord Excel. Vous pouvez exécuter cette fonction chaque fois que vous souhaitez générer un rapport sur une base périodique, comme chaque mois, pour générer des KPI dans un tableau de bord.

Limites:

J'aimerais pouvoir comprendre comment ajouter le trancheur Excel cool qu'Alex avait dans sa vidéo, mais openpyxl a une limitation à l'ajout d'un trancheur. Si vous savez comment ajouter des trancheuses Excel à l'aide de python, faites-le moi savoir !

Découvrez le code et la sortie finale ici .