大數(shù)據(jù)分析Python庫xlwings提升Excel工作效率教程

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

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

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

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

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

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

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

　　遇見 xlwings
 

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

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

　　xlwings 對象
 

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

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

　　當(dāng)您運(yùn)行該代碼時(shí)，它應(yīng)該看起來像這樣。

　　請注意，當(dāng)代碼單元在Jupyter Notebook中運(yùn)行時(shí)，Excel將自動(dòng)啟動(dòng)。
 

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

　　注意：我們不會(huì)在本教程的每個(gè)步驟中都包含gif圖像，因?yàn)槲覀儾幌Ｍ擁撁鏋榛ヂ?lián)網(wǎng)連接速度慢或連接受限的人帶來麻煩。但是，隨后的代碼運(yùn)行步驟應(yīng)類似于上面的代碼：在Juypter中運(yùn)行單元格時(shí)，Excel電子表格會(huì)根據(jù)我們運(yùn)行的任何代碼進(jìn)行更新。

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

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

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

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

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

　　外觀如下：

　　如我們所見，的索引列df也已移至Excel。讓我們清除此工作表的內(nèi)容，然后復(fù)制不帶索引的數(shù)據(jù)。

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

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

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

　　它簽出!
 

　　API屬性
 

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

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

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

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

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

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

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

　　分析我們的數(shù)據(jù)
 

　　我們在嘗試分析此數(shù)據(jù)集時(shí)會(huì)遇到的一個(gè)問題是，日期分散在三個(gè)不同的列中。我們需要將其壓縮到單個(gè)列中。為此，我們將使用大數(shù)據(jù)分析Python在Excel中適當(dāng)?shù)剡B接列。我們首先在相鄰的空白列中插入標(biāo)題。

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

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

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

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

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

　　我們上面的操作也可以通過下面的代碼片段實(shí)現(xiàn)。

　　由于我們將經(jīng)常使用它，因此我們將創(chuàng)建一個(gè)應(yīng)用默認(rèn)填充的函數(shù)，如下所示：
 

　　工作表
 

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

　　最后一行要填充。
 

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

　　現(xiàn)在我們定義我們的功能。

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

　　清單!讓我們看看它的前十個(gè)元素。

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

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

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

　　這是我們現(xiàn)在的位置：

　　準(zhǔn)備好數(shù)據(jù)之后，我們現(xiàn)在將格式化該表。首先，將第一行的字體設(shè)置為bold。

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

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

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

　　現(xiàn)在讓我們通過行和列自動(dòng)擬合。

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

　　那看起來應(yīng)該更好。我們已經(jīng)完成了這張工作表!
 

　　讓我們創(chuàng)建add一個(gè)新的空白表，F(xiàn)requencies并將其分配給大數(shù)據(jù)分析Python名稱frequencies。

　　我們將用表中剛剛組織的數(shù)據(jù)集中每個(gè)數(shù)字和每個(gè)幸運(yùn)星的絕對頻率填充此表EuroMillions。

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

　　我們對幸運(yùn)星也這樣做：

　　這是我們的新工作表應(yīng)如何處理的：

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

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

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

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

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

　　現(xiàn)在，我們將定義圖表的高度和寬度。度量單位將是點(diǎn)。

　　這是我們現(xiàn)在應(yīng)該看到的：

　　該SetElement方法與參數(shù)一起2在圖表上方設(shè)置標(biāo)題。在這里查看其他參數(shù)。

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

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

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

　　這是我們將看到的：

　　下面我們對幸運(yùn)星做了幾乎相同的事情。

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

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

　　我們完成了!現(xiàn)在，我們保存文件并退出我們的Excel實(shí)例。