当前位置: 首页 > 编程笔记 >

Python pandas对excel的操作实现示例

濮宇定
2023-03-14
本文向大家介绍Python pandas对excel的操作实现示例,包括了Python 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'],显示该数据列(column)的类型是 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')

在指定位置插入列

上面方法增加的列,位置都是放在最后。如果想要在指定位置插入列,要用 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') # 返回 TX
state_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 直接加载到 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对excel操作内容请搜索小牛知识库以前的文章或继续浏览下面的相关文章希望大家以后多多支持小牛知识库!

 类似资料:
  • 本文向大家介绍Java实现操作excel表格,包括了Java实现操作excel表格的使用技巧和注意事项,需要的朋友参考一下   最近老师布置了个任务,用Java对excel后缀名为xlsx的文件进行简单的增,删,改,查操作;虽说是个简单的程序,可作为刚接触的我来说还是有些磕磕碰碰。不过好在还是完成了,进行一个简单的总结。   首先导入了一个poi.jar 网上有很多这个资源可以下载 XSSFShe

  • 本文向大家介绍C#实现的Excel文件操作类实例,包括了C#实现的Excel文件操作类实例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了C#实现的Excel文件操作类。分享给大家供大家参考,具体如下: 更多关于C#相关内容感兴趣的读者可查看本站专题:《C#操作Excel技巧总结》、《C#程序设计之线程使用技巧总结》、《C#常见控件用法教程》、《WinForm控件用法总结》、《C#数据结构

  • 本文向大家介绍Android操作Excel文件的功能实现,包括了Android操作Excel文件的功能实现的使用技巧和注意事项,需要的朋友参考一下 Android中操作Excel文件导出报表时主要采用开源库jxl,最早用在java上,但也可用于Android。与之类似的POI,因为依赖库众多,所以只能用于java,而不能用于Android。 使用jxl需要在Android工程中导入jxl.jar包

  • 本文向大家介绍python实现读取excel文件中所有sheet操作示例,包括了python实现读取excel文件中所有sheet操作示例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了python实现读取excel文件中所有sheet操作。分享给大家供大家参考,具体如下: 表格是这样的   实现把此文件所有sheet中 标识为1 的行,取出来,存入一个字典。所有行组成一个列表。 更多关于

  • 本文向大家介绍ThinkPHP 框架实现的读取excel导入数据库操作示例,包括了ThinkPHP 框架实现的读取excel导入数据库操作示例的使用技巧和注意事项,需要的朋友参考一下 本文实例讲述了ThinkPHP 框架实现的读取excel导入数据库操作。分享给大家供大家参考,具体如下: 入口文件中: PHP: php读取excel表数据: 更多关于thinkPHP相关内容感兴趣的读者可查看本站专

  • 本文向大家介绍python3结合openpyxl库实现excel操作的实例代码,包括了python3结合openpyxl库实现excel操作的实例代码的使用技巧和注意事项,需要的朋友参考一下 一.相关说明: 1、openpyxl(可读写excel表)专门处理Excel2007及以上版本产生的xlsx文件;2007一下的版本为xls结尾的文件,需要使用 xlrd和xlwt库进行操作 2、excel表