Python pandas处理Excel数据：列操作、条件计算与透视表实战
本文详解Python pandas库对Excel文件的读写与列处理技巧。涵盖新增计算列、条件判断列（如np.where）、指定位置插入列及Vlookup功能模拟（merge）。此外，介绍数据格式化、行列转置、分类汇总（groupby）及数据透视表（pivot_table）的高级应用，助力高效数据处理。
pandas处理Excel概述与列基础
近期各类平台频繁出现关于Python处理Excel内容的推广，本文明哥整理汇总了pandas处理Excel的核心方法与流程。本节重点讲解pandas中DataFrame对象对数据列（Column）的操作机制。示例数据可通过明哥的gitee仓库获取。
新增计算列与条件列处理
新增计算列
在pandas中，DataFrame对象的每一行或每一列均被视为一个序列（Series）。例如：
import pandas as pd
df1 = pd.read_excel('./excel-comp-data.xlsx');此时，使用type(df1['city']可查看该数据列的类型为pandas.core.series.Series。理解每一列本质上是Series至关重要，因为pandas基于numpy构建，其数据计算均为向量化整体运算。深入理解这一点，有助于后续掌握apply()等函数的用法。
若列名（column name）不含空格，则引用列有两种写法：
df1['city']
df1.city若列名包含空格，或需创建新列（即该列尚不存在，首次使用的变量），则必须使用第一种写法。
假设需对三个月的数据进行汇总，可采用以下方法。实质是创建一个新的数据列：
# 由于是创建，不能使用 df.Total
df1['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')
指定位置插入列与Vlookup模拟
在指定位置插入列
上述方法新增的列默认位于末尾。若需在指定位置插入列，需使用dataframe.insert()方法。假设需在state列后插入一列，内容为state的简称（abbreviation）。在Excel中，通常使用VLOOKUP函数根据state查找简称。此处提供两种实现方式，第一种方式中，简称数据来源于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"}若需根据字典的key查找对应值，可使用dict.get()方法。该方法在找不到key时不会抛出异常，而是返回None。例如：
state_to_code.get('TEXAS') # 返回 TX
state_to_code.get('TEXASS') # 返回 Nonedict.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 直接加载到 DataFrame
abbr2 = pd.DataFrame(list(state_to_code.items()), columns=['state', 'abbr'])数据汇总、格式化与透视表应用
计算合计数
若需对各月份及月份合计数进行求和，pandas可对Series运行sum()方法来计算合计：
import pandas as pd
import numpy as np
df = pd.read_excel('./excel-comp-data.xlsx');
df['Total'] = df.Jan + df.Feb + df.Mar
# sum_row 的类型是 pandas.core.series.Series, Jan, Feb 等成为 Series 的 index
sum_row = df[['Jan', 'Feb', 'Mar', 'Total']].sum()
也可将sum_row转换为DataFrame，以列的形式查看。DataFrame的T方法可实现行列互换。
# 转置变成 DataFrame
df_sum = pd.DataFrame(data=sum_row).T
df_sum
若需将合计数置于数据下方，需稍作处理。首先通过reindex()函数使df_sum与df拥有相同的列，再通过append()方法将合计行追加至数据末尾：
# 转置变成 DataFrame
df_sum = pd.DataFrame(data=sum_row).T
# 将 df_sum 添加到 df
df_sum = df_sum.reindex(columns=df.columns)
# append 创建一个新的 DataFrame
df_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 生成一个新的 DataFrame
df_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操作内容，请搜索本站历史文章或继续浏览下方相关文章。感谢大家的支持！
本站声明
本文内容仅供参考，不代表本站观点。转载或引用请注明出处。如发现内容问题，请通过联系我们页面反馈。
