大數據分析Python庫xlwings提升Excel工作效率教程

　　Excel在當今的企業(yè)中非常非常普遍。在AAA教育，我們通常建議出于很多原因使用代碼，并且我們的許多數據科學課程旨在教授數據分析和數據科學的有效編碼。但是，無論您偏愛使用大數據分析Python的程度如何，最終，有時都需要使用Excel來展示您的發(fā)現或共享數據。
 

　　但這并不意味著仍然無法享受大數據分析Python的某些效率!實際上，使用名為的庫xlwings，您可以使用大數據分析Python加快在Excel中的工作。
 

　　在本xlwings教程中，我們將逐步介紹如何在Excel中使用大數據分析Python來執(zhí)行和使用一些常見操作，例如根據特定條件刪除行，使用Excel函數和公式，自動填充，創(chuàng)建工作表，圖表等。在這篇文章中，您應該熟悉大數據分析Python的基本概念(對象，方法，屬性，函數)和大數據分析Python的語法，并且具有Excel和VBA的中級知識。
 

　　我們將使用一個數據集，其中包含有關稱為EuroMillions的歐洲彩票開獎的信息。這組數據是從下載該鏈接，它包含了所有的歐洲百萬彩票繪制一張，并包括，9月20日期。該鏈接上的可用數據應使用最新信息進行更新，直到您閱讀此帖子時為止，但是如果不可用，請使用CSV文件，其中包含截至9月20日該鏈接的數據。
 

　　在撰寫本文時，抽獎包括來自50個號碼池(編號1到50)中的五個號碼和lucky stars來自12個號碼池的兩個號碼。為了贏得大獎，參與者必須正確選擇所有抽獎號碼和幸運星。有史以來最大的大獎是1.9億歐元。(不過請注意，我們的數據集表示的是英鎊而不是歐元的贏利)。
 

　　在本教程中，我們將使用大數據分析Python和xlwings與Excel清理數據集，然后生成一些圖表以可視化哪些數字最常贏得歐洲百萬獎金。

　　第一列是開獎號碼，各列N1-L2是開獎號碼和幸運星(按繪制順序)，該Jackpot列是歐元的累積獎金，該Wins列告訴我們有多少投注下了大獎。
 

　　遇見 xlwings
 

　　xlwings是一個大數據分析Python庫，可在Excel實例中使用大數據分析Python的某些數據分析功能，包括對numpy數組以及pandasSeries和DataFrames的支持。與其他任何大數據分析Python庫一樣，它可以使用pip或通用方法安裝conda，但是如果需要其他詳細信息，可以在xlwings此處訪問文檔。
 

　　請注意，您需要在用于執(zhí)行本xlwings教程的計算機上安裝Microsoft Excel版本。
 

　　xlwings 對象
 

　　在xlwings有四個主要對象類型其是，在降低分層順序：App(代表一個Excel實例)， Book，Sheet和Range。除了這些，我們還將處理Chart和Shape對象。您可以在官方文檔中找到有關這些對象和其他對象的有用信息，但是我們將一次查看每個對象。
 

　　讓我們開始創(chuàng)建一個Book實例并命名它wb(工作簿)。

　　當您運行該代碼時，它應該看起來像這樣。

　　請注意，當代碼單元在Jupyter Notebook中運行時，Excel將自動啟動。
 

　　通過實例化一個Book對象，將App自動創(chuàng)建屬于我們的書本對象的對象。這是我們可以檢查所有打開的Excel實例的方法。
 

　　注意：我們不會在本教程的每個步驟中都包含gif圖像，因為我們不希望該頁面為互聯(lián)網連接速度慢或連接受限的人帶來麻煩。但是，隨后的代碼運行步驟應類似于上面的代碼：在Juypter中運行單元格時，Excel電子表格會根據我們運行的任何代碼進行更新。

　　該對象xw.apps是可迭代的。要檢查此迭代器中哪些工作簿屬于唯一實例，我們可以books像這樣調用其上的方法。

　　不出所料，唯一的實例是工作簿wb。我們在下面檢查這個事實。

　　同樣，我們可以檢查哪些表屬于該工作簿：

　　我們還可以通過工作表名稱來引用工作表：

　　我們可以將數據從某些大數據分析Python對象(例如列表和元組)移到Excel中。讓我們將數據框中的數據移動到表EuroMillions中。為此，我們將利用range創(chuàng)建一個范圍對象，該對象將來自DataFrame的數據存儲在Excel中的一系列單元格中，在這種情況下，從單元格A1開始：

　　外觀如下：

　　如我們所見，的索引列df也已移至Excel。讓我們清除此工作表的內容，然后復制不帶索引的數據。

　　能夠告訴我們表格的結束位置將很有用。更具體地說，我們需要最后一行包含數據的行。為此，我們可以使用對象的end方法和row屬性Range。
 

　　的row方法，這并不奇怪，返回row所述的Range對象。
 

　　該方法end將方向("up"(或1)，"right"(或2)，"left"(或(或))作為參數3，并返回另一個范圍對象，它模仿Excel中非常常見的動作。"down"4CTRL+Shift+Arrow

　　它簽出!
 

　　API屬性
 

　　并非所有Excel功能都可以作為本機xlwings功能使用。有時，我們必須找到解決方法來完成我們想要的事情。幸運的是xlwings，這對我們來說非常容易。在官方文檔的“ 缺少功能”部分中：
 

　　解決方法：本質上，xlwings只是Windows 上pywin32和Mac 上appscript的智能包裝。您可以通過調用api屬性來訪問基礎對象。底層對象將使用pywin32(非常像VBA)和appscript(不像VBA)的語法為您提供幾乎可以使用VBA進行的所有操作。但是除了難看之外，請記住，它使您的代碼平臺特定于(!)。Excel Visual Basic for Applications是各種現有Excel對象的豐富解釋源。
 

　　排序是缺少的一種功能xlwings。您可能已經注意到，記錄是從最新到最舊的抽簽排列的。在接下來的幾個步驟中，我們將顛倒順序。
 

　　對象ws.range("A2:N{row}".format(row=last_row))是Range對象。將api屬性附加到其上將產生一個VBA Range對象，該對象又可以訪問其VBA功能。
 

　　我們將使用此VBA對象的Sort屬性。在其最簡單的應用程序中，Sort采用兩個參數：要對表進行排序的列(作為VBA Range對象)和排序類型(無論是按升序還是降序排序)。第二個參數的參數文檔可在此處查看。我們將按升序排序。
 

　　將所有這些放在一起看起來像這樣：

　　運行后，它在屏幕上的顯示方式如下(請注意，第一列已更改，現在它以升序而不是降序排序。

　　分析我們的數據
 

　　我們在嘗試分析此數據集時會遇到的一個問題是，日期分散在三個不同的列中。我們需要將其壓縮到單個列中。為此，我們將使用大數據分析Python在Excel中適當地連接列。我們首先在相鄰的空白列中插入標題。

　　接下來，我們可以插入要用作字符串的Excel公式。注意：應使用哪種參數分隔符的具體信息取決于計算機的本地區(qū)域設置。在我看來，參數分隔符是一個逗號，這是我在本教程中使用的，但是在您看來，它可能是一個分號。

　　在第一個單元格中插入了公式之后，常規(guī)Excel工作流中的第二個特性是自動填充表格末尾的其余單元格。自動填充是VBA Range對象的一種方法。它以目標單元格為VBA Range對象和填充類型為參數。我們對枚舉為的默認值感興趣0。

　　這大致是此步驟后屏幕的外觀。請注意最右邊的新“日期”列。

　　我們還可以使用想要的填充類型的命名形式。為此，我們需要從模塊中檢索它，該模塊xlwings.constants包含大多數VBA屬性的枚舉參數的命名版本?；叵胍幌?，您始終可以通過打印來檢查可用屬性dir(xlwings.constants)。
 

　　(如果您不熟悉它，它dir是大數據分析Python的本機功能，它可以接受多種參數(模塊，類和常規(guī)對象(如列表和字符串)。例如，如果打印dir(some_list)出來的話)將為您提供所有方法和可以與列表一起使用的屬性。)
 

　　我們上面的操作也可以通過下面的代碼片段實現。

　　由于我們將經常使用它，因此我們將創(chuàng)建一個應用默認填充的函數，如下所示：
 

　　工作表
 

　　代表工作表中單元格的字符串
 

　　最后一行要填充。
 

　　為此，我們將介紹一種Range稱為的新方法get_address。它接受四個布爾參數，并返回一個字符串，該字符串標識具有不同詳細程度的范圍。這是該方法的一個令人鼓舞的示例。

　　現在我們定義我們的功能。

　　為了避免Excel進行不必要的計算，我們將剛插入列中的公式替換為O硬編碼值。我們這樣做在此之前，讓我們花點時間去思考什么樣的大數據分析Python對象的是Range.value，當Range是一個數組。

　　清單!讓我們看看它的前十個元素。

　　如果我們將此列表插入任意范圍，它將水平放置這些值，這不是我們想要的。為了垂直放置它們，我們需要使用帶有選項作為參數options的Range對象方法transpose=True，如下所示：

　　多年來，EuroMillions格式經歷了一些輕微的修改，最后一次更改發(fā)生在2016年9月24日。
 

　　從2016年9月24日起，幸運星的數量從11個池更改為12個池。為了進行有意義的分析，我們將僅考慮上次修改后發(fā)生的繪制。下一個代碼段查找修改之前發(fā)生的最后一個游戲并將其命名to_delete。
 

　　這是我們現在的位置：

　　準備好數據之后，我們現在將格式化該表。首先，將第一行的字體設置為bold。

　　我們可以通過Jackpot以百萬列格式設置列的大小。請注意，以下字符串格式取決于您計算機的本地區(qū)域設置。如果格式看起來很奇怪，請嘗試將逗號替換為點。有關Excel自定義格式的更多信息。

　　作為后續(xù)步驟的輔助步驟，我們將找到與具有數據的最后一列相對應的字母。

　　現在讓我們在標題單元格的底部添加邊框。與我們一直在做的類似，我們將使用該api屬性。另外，我們將需要對象的Border屬性Range，邊界方向枚舉和border的樣式。我們將-4119僅在標題單元格的底部(方向9)設置雙邊框(線條樣式)。

　　現在讓我們通過行和列自動擬合。

　　糟糕!這看起來有些壓縮，讓我們將所有列的寬度設置為J似乎最大的列的寬度。這是ColumnWidth我們在下面使用的文檔。

　　那看起來應該更好。我們已經完成了這張工作表!
 

　　讓我們創(chuàng)建add一個新的空白表，Frequencies并將其分配給大數據分析Python名稱frequencies。

　　我們將用表中剛剛組織的數據集中每個數字和每個幸運星的絕對頻率填充此表EuroMillions。

　　在下面，我們將為單元格中的頻率插入一個標題，B1并在單元格中B2輸入一個公式，該公式將計算in值A2在該范圍內出現的次數C2:G201。換句話說，我們將計算1列中發(fā)生了多少次N1-N5。之后，我們將自動填充列中的其余單元格，B以對其各自的行執(zhí)行相同的操作。
 

　　我們對幸運星也這樣做：

　　這是我們的新工作表應如何處理的：

　　我們正在接近我們的目標。讓我們創(chuàng)建一個名為的工作表Graphs。

　　現在，我們將創(chuàng)建一個Chart對象。這只會產生一個空白的白框，但是請放心!我們將立即使用該框來繪制數據圖表。
 

　　我們可以name將圖表制作成與工作表類似的圖表。該方法set_source_data允許我們通過傳入范圍對象來定義圖表的數據源。

　　Excel將嘗試猜測x軸應該是什么，但是我們可以Frequencies使用VBA Chart方法強制將其作為在其上創(chuàng)建的數字FullSeriesCollection。我們可以使用索引1的對象來編輯圖表nr_freq.api：

　　Excel非常擅長猜測用戶想要哪種圖表，但是如果萬一它猜測不正確，我們將強制它成為柱形圖。此處列出了各種類型的圖表。las，唯一將這些文件與chart_type屬性的可能值聯(lián)系起來的文檔是源代碼本身。

　　現在，我們將定義圖表的高度和寬度。度量單位將是點。

　　這是我們現在應該看到的：

　　該SetElement方法與參數一起2在圖表上方設置標題。在這里查看其他參數。

　　我們添加了最后的修飾。我們使用HasLegend屬性刪除圖例。

　　xlCategory作為參數1傳入Axes方法的類別，以及將TickLabelSpacing屬性設置為1，確?？梢燥@示軸的每個元素。

　　要完成這一格式圖，我們通過設置屬性刪除大綱Visible中的Line對象0。

　　這是我們將看到的：

　　下面我們對幸運星做了幾乎相同的事情。

　　最后，我們創(chuàng)建一個顯示累積獎金演變的時間序列圖。

　　然后，我們通過將TickLabels屬性NumberFormat設置為所需的外觀來固定垂直軸的“標簽”格式。

　　我們完成了!現在，我們保存文件并退出我們的Excel實例。