用Python輕鬆搞定Excel中的20個常用操作

用Python輕鬆搞定Excel中的20個常用操作

Excel與Python都是數據分析中常用的工具，本文將使用動態圖(Excel)+代碼(Python)的方式來演示這兩種工具是如何實現數據的讀取、生成、計算、修改、統計、抽樣、查找、可視化、存儲等數據處理中的常用操作！

— 01 —

數據讀取

說明：讀取本地Excel數據

Excel

Excel讀取本地數據需要打開目標文件夾選中該文件並打開

Pandas

Pandas支持讀取本地Excel、txt文件，也支持從網頁直接讀取表格數據，只用一行代碼即可，例如讀取上述本地Excel數據可以使用pd.read_excel(&34;)

— 02 —

數據生成

說明：生成指定格式/數量的數據

Excel

以生成10*2的0—1均勻分布隨機數矩陣為例，在Excel中需要使用rand()函數生成隨機數，並手動拉取指定範圍

Pandas

在Pandas中可以結合NumPy生成由指定隨機數(均勻分布、正態分布等)生成的矩陣，例如同樣生成10*2的0—1均勻分布隨機數矩陣為，使用一行代碼即可：pd.DataFrame(np.random.rand(10,2))

— 03 —

數據存儲

說明：將表格中的數據存儲至本地

Excel

在Excel中需要點擊保存並設置格式/文件名

Pandas

在Pandas中可以使用pd.to_excel(&34;)來將當前工作表格保存至當前目錄下，當然也可以使用to_csv保存為csv等其他格式，也可以使用絕對路徑來指定保存位置

— 04 —

數據篩選

說明：按照指定要求篩選數據

Excel

使用我們之前的示例數據，在Excel中篩選出薪資大於5000的數據步驟如下

Pandas

在Pandas中，可直接對數據框進行條件篩選，例如同樣進行單個條件(薪資大於5000)的篩選可以使用df[df[&39;]>5000]，如果使用多個條件的篩選只需要使用&(並)與|(或)操作符實現

— 05 —

數據插入

說明：在指定位置插入指定數據

Excel

在Excel中我們可以將光標放在指定位置並右鍵增加一行/列，當然也可以在添加時對數據進行一些計算，比如我們就可以使用IF函數(=IF(G2>10000,&34;,&34;))，將薪資大於10000的設為高，低於10000的設為低，添加一列在最後

Pandas

在pandas中，如果不藉助自定義函數的話，我們可以使用cut方法來實現同樣操作

bins = [0,10000,max(df[&39;])]

group_names = [&39;,&39;]

df[&39;] = pd.cut(df[&39;], bins, labels=group_names)

— 06 —

數據刪除

說明：刪除指定行/列/單元格

Excel

在Excel刪除數據十分簡單，找到需要刪除的數據右鍵刪除即可，比如刪除剛剛生成的最後一列

Pandas

在pandas中刪除數據也很簡單，比如刪除最後一列使用del df[&39;]即可

— 07 —

數據排序

說明：按照指定要求對數據排序

Excel

在Excel中可以點擊排序按鈕進行排序，例如將示例數據按照薪資從高到低進行排序可以按照下面的步驟進行

Pandas

在pandas中可以使用sort_values進行排序，使用ascending來控制升降序，例如將示例數據按照薪資從高到低進行排序可以使用df.sort_values(&34;,ascending=False,inplace=True)

— 08 —

缺失值處理

說明：對缺失值(空值)按照指定要求處理

Excel

在Excel中可以按照查找—>定位條件—>空值來快速定位數據中的空值，接著可以自己定義缺失值的填充方式，比如將缺失值用上一個數據進行填充。

Pandas

在pandas中可以使用data.isnull().sum()來檢查缺失值，之後可以使用多種方法來填充或者刪除缺失值，比如我們可以使用df = df.fillna(axis=0,method=&39;)來橫向/縱向用缺失值前面的值替換缺失值

— 09 —

數據去重

說明：對重複值按照指定要求處理

Excel

在Excel中可以通過點擊數據—>刪除重複值按鈕並選擇需要去重的列即可，例如對示例數據按照創建時間列進行去重，可以發現去掉了196 個重複值，保留了 629 個唯一值。

Pandas

在pandas中可以使用drop_duplicates來對數據進行去重，並且可以指定列以及保留順序，例如對示例數據按照創建時間列進行去重df.drop_duplicates([&39;],inplace=True)，可以發現和Excel處理的結果一致，保留了 629 個唯一值。

— 10 —

格式修改

說明：修改指定數據的格式

Excel

在Excel中可以選中需要轉換格式的數據之後右鍵—>修改單元格格式來選擇我們需要的格式

Pandas

在Pandas中沒有一個固定修改格式的方法，不同的數據格式有著不同的修改方法，比如類似Excel中將創建時間修改為年-月-日可以使用df[&39;] = df[&39;].dt.strftime(&39;)

— 11 —

數據交換

說明：交換指定數據

Excel

在Excel中交換數據是很常用的操作，以交換示例數據中地址與崗位兩列為例，可以選中地址列，按住shift鍵並拖動邊緣至下一列鬆開即可

Pandas

在pandas中交換兩列也有很多方法，以交換示例數據中地址與崗位兩列為例，可以通過修改列號來實現

— 12 —

數據合併

說明：將兩列或多列數據合併成一列

Excel

在Excel中可以使用公式也可以使用Ctrl+E快捷鍵完成多列合併，以公式為例，合併示例數據中的地址+崗位列步驟如下

Pandas

在Pandas中合併多列比較簡單，類似於之前的數據插入操作，例如合併示例數據中的地址+崗位列使用df[&39;] = df[&39;] + df[&39;]

— 13 —

數據拆分

說明：將一列按照規則拆分為多列

Excel

在Excel中可以通過點擊數據—>分列並按照提示的選項設置相關參數完成分列，但是由於該列含有[]等特殊字符，所以需要先使用查找替換去掉。

Pandas

在Pandas中可以使用.split來完成分列，但是在分列完畢後需要使用merge來將分列完的數據添加至原DataFrame，對於分列完的數據含有[]字符，我們可以使用正則或者字符串lstrip方法進行處理，但因不是pandas特性，此處不再展開。

— 14 —

數據分組

說明：對數據進行分組計算

Excel

在Excel中對數據進行分組計算需要先對需要分組的欄位進行排序，之後可以通過點擊分類匯總並設置相關參數完成，比如對示例數據的學歷進行分組並求不同學歷的平均薪資

Pandas

在Pandas中對數據進行分組計算可以使用groupby輕鬆搞定，比如使用df.groupby(&34;).mean()一行代碼即可對示例數據的學歷進行分組並求不同學歷的平均薪資，結果與Excel一致

— 15 —

數據計算

說明：對數據進行一些計算

Excel

在Excel中有很多計算相關的公式，比如可以使用COUNTIFS來統計薪資大於10000的崗位數量有518個

Pandas

在Pandas中可以直接使用類似數據篩選的方法來統計薪資大於10000的崗位數量len(df[df[&34;]>10000])

— 16 —

數據統計

說明：對數據進行一些統計計算

Excel

在Excel中有很多統計相關的公式，也有現成的分析工具，比如對薪資水平列進行描述性統計分析，可以通過添加工具庫之後點擊數據分析按鈕並設置相關參數

Pandas

在pandas中也有現成的函數describe快速完成對數據的描述性統計，比如使用df[&34;].describe()即可得到薪資列的描述性統計結果

— 17 —

數據可視化

說明：對數據進行可視化

Excel

在Excel中可以通過點擊插入並選擇圖表來快速完成對數據的可視化，比如製作薪資的直方圖，並且有很多樣式可以直接使用

Pandas

在Pandas中也支持直接對數據繪製不同可視化圖表，例如直方圖，可以使用plot或者直接使用hist來製作df[&34;].hist()

— 18 —

數據抽樣

說明：對數據按要求採樣

Excel

在Excel中抽樣可以使用公式也可以使用分析工具庫中的抽樣，但是僅支持對數值型的列抽樣，比如隨機抽20個示例數據中薪資的樣本

Pandas

在pandas中有抽樣函數sample可以直接抽樣，並且支持任意格式的數據抽樣，可以按照數量/比例抽樣，比如隨機抽20個示例數據中的樣本

— 19 —

數據透視表

說明：製作數據透視表

Excel

數據透視表是一個非常強大的工具，在Excel中有現成的工具，只需要選中數據—>點擊插入—>數據透視表即可生成，並且支持欄位的拖取實現不同的透視表，非常方便，比如製作地址、學歷、薪資的透視表

Pandas

在Pandas中製作數據透視表可以使用pivot_table函數，例如製作地址、學歷、薪資的透視表pd.pivot_table(df,index=[&34;,&34;],values=[&34;])，雖然結果一樣，但是並沒有Excel一樣方便調整與多樣

— 20 —

vlookup

說明：利用VLOOKUP查找數據

Excel

VLOOKUP算是EXCEL中最核心的功能之一了，我們用一個簡單的數據來進行示例

Pandas

在Pandas中沒有現成的vlookup函數，所以實現匹配查找需要一些步驟，首先我們讀取該表格

接著將該dataframe切分為兩個

最後修改索引並使用update進行兩表的匹配

以上就是使用Pandas來演示如何實現Excel中的常用操作的全部過程，其實可以發現Excel的優點就是大多由交互式的點擊完成數據處理，而Pandas則完全依賴於代碼，對於有些操作比如數據透視表，用Excel製作更加方便，而有些操作比如數據的分組、計算等，因Pandas可以與NumPy等其他優秀的Python庫結合而顯得更加強大，所以我們在處理數據時也需要正確選擇使用的工具！