Python pandas對excel的操作實現示例

最近經?？吹礁髌脚_里都有Python的廣告，都是對excel的操作，這里明哥收集整理了一下pandas對excel的操作方法和使用過程。本篇介紹 pandas 的 DataFrame 對列 (Column) 的處理方法。示例數據請通過明哥的gitee進行下載。

增加計算列

pandas 的 DataFrame，每一行或每一列都是一個序列 (Series)。比如：

import pandas as pddf1 = pd.read_excel(’./excel-comp-data.xlsx’);

此時，用 type(df1[’city’]，顯示該數據列（column）的類型是 pandas.core.series.Series。理解每一列都是 Series 非常重要，因為 pandas 基于 numpy，對數據的計算都是整體計算。深刻理解這個，才能理解后面要說的諸如 apply() 函數等。

如果列名 （column name）沒有空格，則列有兩種方式表達：

df1[’city’] df1.city

如果列名有空格，或者創建新列（即該列不存在，需要創建，第一次使用的變量），則只能用第一種表達式。

假設我們要對三個月的數據進行匯總，可以使用下面的方法。實際上就是創建一個新的數據列：

# 由于是創建，不能使用 df.Totaldf1[’Total’] = df1[’Jan’] + df1[’Feb’] + df1[’Mar’]

df1[’Jan’] 到 df1[’Mar’] 都是 Series，所以使用 + 號，可以得到三個 Series 對應位置的數據合計。

當然，也可以用下面的方式：

df1[’total’] = df1.Jan + df1.Feb + df1.Mar

增加條件計算列

假設現在要根據合計數 (Total 列)，當 Total 大于 200,000 ，類別為 A，否則為 B。在 Excel 中實現用的是 IF 函數，但在 pandas 中需要用到 numpy 的 where 函數：

df1[’category’] = np.where(df1[’total’] > 200000, ’A’, ’B’)

在指定位置插入列

上面方法增加的列，位置都是放在最后。如果想要在指定位置插入列，要用 dataframe.insert() 方法。假設我們要在 state 列后面插入一列，這一列是 state 的簡稱 (abbreviation)。在 Excel 中，根據 state 來找到 state 的簡稱 ，一般用 VLOOKUP 函數。我們用兩種方法來實現，第一種方法，簡稱來自 Python 的 dict。

數據來源：

state_to_code = {'VERMONT': 'VT', 'GEORGIA': 'GA', 'IOWA': 'IA', 'Armed Forces Pacific': 'AP', 'GUAM': 'GU', 'KANSAS': 'KS', 'FLORIDA': 'FL', 'AMERICAN SAMOA': 'AS', 'NORTH CAROLINA': 'NC', 'HAWAII': 'HI', 'NEW YORK': 'NY', 'CALIFORNIA': 'CA', 'ALABAMA': 'AL', 'IDAHO': 'ID', 'FEDERATED STATES OF MICRONESIA': 'FM', 'Armed Forces Americas': 'AA', 'DELAWARE': 'DE', 'ALASKA': 'AK', 'ILLINOIS': 'IL', 'Armed Forces Africa': 'AE', 'SOUTH DAKOTA': 'SD', 'CONNECTICUT': 'CT', 'MONTANA': 'MT', 'MASSACHUSETTS': 'MA', 'PUERTO RICO': 'PR', 'Armed Forces Canada': 'AE', 'NEW HAMPSHIRE': 'NH', 'MARYLAND': 'MD', 'NEW MEXICO': 'NM', 'MISSISSIPPI': 'MS', 'TENNESSEE': 'TN', 'PALAU': 'PW', 'COLORADO': 'CO', 'Armed Forces Middle East': 'AE', 'NEW JERSEY': 'NJ', 'UTAH': 'UT', 'MICHIGAN': 'MI', 'WEST VIRGINIA': 'WV', 'WASHINGTON': 'WA', 'MINNESOTA': 'MN', 'OREGON': 'OR', 'VIRGINIA': 'VA', 'VIRGIN ISLANDS': 'VI', 'MARSHALL ISLANDS': 'MH', 'WYOMING': 'WY', 'OHIO': 'OH', 'SOUTH CAROLINA': 'SC', 'INDIANA': 'IN', 'NEVADA': 'NV', 'LOUISIANA': 'LA', 'NORTHERN MARIANA ISLANDS': 'MP', 'NEBRASKA': 'NE', 'ARIZONA': 'AZ', 'WISCONSIN': 'WI', 'NORTH DAKOTA': 'ND', 'Armed Forces Europe': 'AE', 'PENNSYLVANIA': 'PA', 'OKLAHOMA': 'OK', 'KENTUCKY': 'KY', 'RHODE ISLAND': 'RI', 'DISTRICT OF COLUMBIA': 'DC', 'ARKANSAS': 'AR', 'MISSOURI': 'MO', 'TEXAS': 'TX', 'MAINE': 'ME'}

如果我們想根據 dict 的 key 找到對應的值，可以使用 dict.get() 方法，這個方法在找不到 key 的時候，不會拋出異常，只是返回 None。比如

state_to_code.get(’TEXAS’) # 返回 TXstate_to_code.get(’TEXASS’) # 返回 None

dict.get() 方法參數為 key，是一個標量值。我們并不能像下面這樣把整列都傳給這個方法，比如下面這樣：

df1[’abbrev’] = state_to_code.get(df1[’state’])

所以我們需要先構造一個 Series (abbrev)，然后把 abbrev 賦值給 df1[’abbrev’]：

abbrev = df1[’state’].apply(lambda x: state_to_code.get(x.upper()))df1[’abbrev’] = abbrev # 在后面插入列df1.insert(6, ’abbr’, abbrev) # 在指定位置插入列

apply() 函數值得專門寫一篇，暫且不細說。

Vlookup 函數功能實現

實現類似 Excel 的 VLookup 功能，可以用 dataframe.merge() 方法。為此，需要將 state_to_code 這個 dict 的數據加載到 DataFrame 中。這里提供兩種方法。

方法1： 把數據放在 excel 工作表中，然后讀取 Excel 文件加載。數據如下：

excel_file = pd.ExcelFile(’excel-comp-data.xlsx’)df_abbrev = pd.read_excel(excel_file, sheetname = ’abbrev’)df2 = df1.merge(df_abbrev, on=’state’) # 類似數據庫的 inner join，不匹配數據不會顯示

VLookup 函數根據位置來匹配，merge() 方法根據列名來匹配。因為上面語句中沒有指定連接類型，不匹配的記錄不會顯示。如果需要將 df1 的數據全部顯示出來，需要指定 merge() 方法的 how 參數：

df3 = df1.merge(df_abbrev, on=’state’, how=’left’) # 類似數據庫的 left join

方法2：直接將 state_to_code 加載到 DataFrame。但因為 state_to_code 全部是標量值 (scalar values)，方法有一點不同，如下：

# 將 state_to_code 直接加載到 DataFrameabbr2 = pd.DataFrame(list(state_to_code.items()), columns=[’state’, ’abbr’])

計算合計數

假如需要對各個月份以及月份合計數進行求和。pandas 可以對 Series 運行 sum() 方法來計算合計：

import pandas as pdimport numpy as npdf = pd.read_excel(’./excel-comp-data.xlsx’);df[’Total’] = df.Jan + df.Feb + df.Mar# sum_row 的類型是 pandas.core.series.Series, Jan, Feb 等成為 Series 的 indexsum_row = df[[’Jan’, ’Feb’, ’Mar’, ’Total’]].sum()

也可以將 sum_row 轉換成 DataFrame, 以列的方式查看。DataFrame 的 T 方法實現行列互換。

# 轉置變成 DataFramedf_sum = pd.DataFrame(data=sum_row).Tdf_sum

如果想要把合計數放在數據的下方，則要稍作加工。首先通過 reindex() 函數將 df_sum 變成與 df 具有相同的列，然后再通過 append() 方法，將合計行放在數據的后面：

# 轉置變成 DataFramedf_sum = pd.DataFrame(data=sum_row).T# 將 df_sum 添加到 dfdf_sum = df_sum.reindex(columns=df.columns)# append 創建一個新的 DataFramedf_with_total = df.append(df_sum, ignore_index=True)

分類匯總

Excel 的分類匯總功能，在數據功能區，但因為分類匯總需要對數據進行排序，并且分類匯總的數據與明細數據混在一起，個人很少用到，分類匯總一般使用數據透視表。

而在 pandas 進行分類匯總，可以使用 DataFrame 的 groupby() 函數，然后再對 groupby() 生成的 pandas.core.groupby.DataFrameGroupBy 對象進行求和：

df_groupby = df[[’state’,’Jan’, ’Feb’,’Mar’, ’Total’]].groupby(’state’).sum()df_groupby.head()

數據格式化

pandas 默認的數據顯示，沒有使用千分位分隔符，在數據較大時，感覺不方便。如果需要對數據的顯示格式化，可以自定義一個函數 number_format()，然后對 DataFrame 運行 applymap(number_format) 函數。applymap() 函數對 DataFrame 中每一個元素都運行 number_format 函數。number_format 函數接受的參數必須為標量值，返回的也是標量值。

# 數字格式化def number_format(x): return '{:,.0f}'.format(x) # 使用逗號分隔,沒有小數位formated_df = df_groupby.applymap(number_format)formated_df.head()

數據透視表

pandas 運行數據透視表，使用 pivot_table() 方法。熟練使用 pivot_table() 需要一些練習。這里只是介紹最基本的功能：

index 參數： 按什么條件進行匯總 values 參數：對哪些數據進行計算 aggfunc 參數：aggregation function，執行什么運算

# pivot table# pd.pivot_table 生成一個新的 DataFramedf_pivot = pd.pivot_table(df, index=[’state’], values=[’Jan’,’Feb’,’Mar’,’Total’], aggfunc= np.sum)

總結

Pandas可以對Excel進行基礎的讀寫操作

Pandas可以實現對Excel各表各行各列的增刪改查

Pandas可以進行表中列行篩選等

到此這篇關于Python pandas對excel的操作實現示例的文章就介紹到這了,更多相關Python pandas對excel操作內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！