Mengotomatiskan Pembuatan Dasbor Excel dengan Python

Untuk semua yang belajar tentang ilmu data dan analitik di luar sana, jika Anda belum mempelajari otomasi, artikel ini akan membuat hidup Anda mudah!

Sedikit Cerita Belakang:

Saya telah menghadiri wawancara selama 6 bulan sekarang untuk posisi analis data dan ilmu data. Semua wawancara saya biasanya memiliki pola yang sama atau lebih tepatnya saya mulai mengamati mereka seperti yang dilakukan oleh penggemar data mana pun. Ada satu "ceritakan tentang diri Anda" yang umum dan berdasarkan jawaban saya, ada pertanyaan lebih lanjut atau resume saya.

Saya magang di sebuah perusahaan sebagai bagian dari proyek batu penjuru saya di mana saya membantu perusahaan dengan analitik data dan persyaratan ilmu datanya. Aspek utama dari peran saya adalah menghasilkan Key Performance Indicators (KPIs) untuk mengukur kinerja perusahaan setiap bulan. Untuk siapa saja yang tidak tahu apa itu KPI, inilah yang dikatakan Wikipedia:

Sekarang, pernyataan ini cukup membuat orang yang mewawancarai saya tertarik dan memberi tahu saya bagaimana hal itu sejalan dengan apa yang harus dilakukan untuk posisi yang saya wawancarai dan bertanya lebih lanjut tentang hal itu. Dan kemudian, pertanyaan yang saya takutkan akan diajukan.

“Apakah Anda menerapkan otomatisasi untuk membuat laporan bulanan?”

Saat ini, saya hanya ingin jujur ​​kepada mereka dan memberi tahu mereka bahwa saya belum melakukannya dan betapa saya sangat menyesal tidak melakukannya! Seseorang yang mengetahui Python, SQL, dan Excel tidak mengotomatisasi untuk membuat hidup mereka mudah? Betulkah? Plus, saya memiliki latar belakang rekayasa perangkat lunak!

Jadi, saya memutuskan untuk mengubahnya hari ini. Saya otomatis membuat laporan excel saya. Saya akhirnya berhasil!

Barang Asli dan hanya sedikit Fluff

Melalui artikel ini, Anda akan melihat bagaimana kami dapat mengotomatiskan pembuatan dasbor excel dan membuat saluran data sederhana hanya dengan satu klik tombol.

Ya, ini bukan hanya satu tombol — ini adalah beberapa baris kode!

Seperti siapa pun yang memulai perjalanan pembuatan dasbor analitik data, saya memulai dengan dasbor excel mudah milik Alex the Analyst di youtube.

Video berdurasi 40 menit ini sangat membantu saya dalam memahami keindahan data. Saya akan menggunakan kumpulan data yang digunakan dalam video ini dan mengotomatiskan pembuatan dasbor yang dibuat.

Saya akan menggunakan pustaka python — openpyxl

1. Mari kita baca file excelnya terlebih dahulu.

#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. Saatnya memasukkan semua ini ke dalam fungsi dan menjalankannya

import Bikes_Sales_Report_Automation as auto

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

Ringkasan:

Kami membuat fungsi sederhana yang ketika dijalankan menghasilkan dasbor excel secara otomatis. Anda dapat menjalankan fungsi ini kapan pun Anda ingin membuat laporan secara berkala seperti setiap bulan untuk membuat KPI di dasbor.

Keterbatasan:

Saya berharap bisa mengetahui cara menambahkan pemotong excel keren yang dimiliki Alex di videonya, tetapi openpyxl memiliki batasan untuk menambahkan pemotong. Jika Anda tahu cara menambahkan pemotong excel menggunakan python, beri tahu saya!

Lihat kode dan keluaran akhir di sini .