python操作excel(openpyxl,xlrd&xlwt)

Generalzy

已于 2024-10-07 10:01:16 修改

阅读量1.3k

点赞数 1

分类专栏： python 文章标签： python excel 开发语言

于 2023-03-23 22:35:01 首次发布

本文链接：https://blog.csdn.net/General_zy/article/details/129741159

版权

python 专栏收录该内容

56 篇文章 10 订阅

订阅专栏

对比xlwt,openpyxl,xlrd

图示

引用别人的一张图：
在这里插入图片描述

xlwt不支持写xlsx文件。
openpyxl不支持读xls文件。
xlrd支持读xls,xlsx文件。
推荐读文件用xlrd,写文件用openpyxl。

实测

准备了3.64 MB的excel文件。

from functools import wraps
from time import time
import openpyxl
import xlrd


def timer(func):
    @wraps(func)
    def inner(*args, **kwargs):
        tmp = time()
        res = func(*args, **kwargs)
        print(time() - tmp)
        return res

    return inner


@timer
def openpyxl_test():
    fp = openpyxl.open('题库.xlsx')
    sheet = fp['xxx']
    print(sheet.dimensions)
    for rows in sheet.iter_rows(min_row=1, max_row=8122, min_col=1, max_col=13):
        for cell in rows:
            pass
        pass


@timer
def xlrd_test():
    fp = xlrd.open_workbook('题库.xlsx')
    sheet = fp.sheet_by_name('xxx')
    print(sheet.nrows, sheet.ncols)
    for index in range(sheet.nrows):
        row = sheet.row_values(index)
        pass


if __name__ == '__main__':
    openpyxl_test()
    >>>
    A1:M8122
	7.71000599861145
	
    xlrd_test()
    >>>
    8122 13
	4.619961738586426

读取8000行数据，openpyxl用时7.71秒，xlrd用时4.62秒。

xlrd&xlwt读写操作

区别：

openyxl：可以对xlsx、xlsm文件进行读、写操作，主要对Excel2007年之后的版本(.xlsx)
xlrd：可以对xlsx、xls、xlsm文件进行读操作且效率高
xlwt：主要对xls文件进行写操作且效率高，但是不能执行xlsx文件

安装

读：pip3 install xlrd==1.2.0 （高版本不支持xlsx)
写：pip install xlwt

读操作

import xlrd

# 读取文件
df = xlrd.open_workbook('test.xlsx') 

# 显示所有sheet
print(df.sheet_names())
>>> ['Sheet1', 'Sheet2', 'Sheet3']

# 获取所有sheet对象
print(df.sheets())
[<xlrd.sheet.Sheet object at 0x000001995152D7C8>, <xlrd.sheet.Sheet object at 0x0000019951567648>, <xlrd.sheet.Sheet object at 0x0000019951567988>]

# 获取sheet对象
table=df.sheets()[0]
table=df.sheet_by_index(0)
table=df.sheet_by_name('name')

# 获取行列
row=table.nrows
col=table.ncols

# 获取整行，整列的值，行列值从0开始
table.row_values(num1)
table.column_values(num2)

# 获取单元格的值
cell=table.cell(0,0).value

写操作

import xlwt

# 创建excel文件
df2 = xlwt.Workbook()

# 新建表
table2=df2.add_sheet('name')
table2=df2.add_sheet('name',cell_overwrite_ok=True) 允许重复写一个单元格

# 写入数据
table2.write(0,0,'Python')

# 保存
df2.save('data2.xls')

excel时间处理xldate_as_tuple

在使用xlrd读取Excel表格中的日期格式时，读取出来的是一个浮点数，导致不能正确使用，而xldate_as_tuple方法可以很好地解决这个问题。

xlrd.xldate_as_tuple(xldate,datemode)

参数 xldate: 要处理的单元格值

参数 datemode:时间基准（0代表1900-01-01为基准，1代表1904-01-01为基准）；常使用1900为基准

返回值：返回一个元组，类似于（year,month,day,hour,minute,nearest_second）

但是，仅适用于date类型的excel单元格，不适用于time类型的单元格

xlrd.xldate.xldate_as_datetime(xldate,datemode)

参数 xldate: 要处理的单元格值

参数 datemode:时间基准（0代表1900-01-01为基准，1代表1904-01-01为基准）；常使用1900为基准

返回值：datetime对象，格式 year-month-day hour:minute:second

openpyxl读写操作

前提知识：
在这里插入图片描述
测试数据：

openpyxl

安装：pip install openpyxl

读取

from openpyxl import load_workbook

# 导入excel文件
workbook = load_workbook(filename='test.xlsx’) 
或
workbook = open("test.xlsx")

# 打印该文件所有表名
print(workbook.sheetnames)

# 切换表
sheet = workbook['sheet1']

# 打印该表单元格规模
print(sheet.dimensions)

# 取该表的某个单元格
cell = sheet['A1']

# 打印该单元格的坐标
print(cell.row, cell.column, cell.coordinate)

# 获取多个单元格
for cell in sheet['A1:A10']
for cell in sheet['A1:G10']

for rows in sheet.iter_rows(min_row=1,max_row=5,min_col=2,max_col=7):
	for cell in rows:
		print(cell.value,end='')
	print()

# 获取单元格对象的值,[必须是单元格对象]，如果取到元组是点不出value的
print(cell.value)

在这里插入图片描述

写入

# 保存
workbook.save('test.xlsx')

# 修改
cell = sheet['A1'] 
cell.value = 'A1'

# 追加到最后最后一行
sheet.append([15, None, None, 14, 'qq.com'])

# 添加公式
sheet['K11'] = '=AVERAGE(K1:K10)'

# 在第二行处增加4行
sheet.insert_rows(2,4)
# 在第二列处增加4列
sheet.insert_cols(2,4)

# 删除行
sheet.delete_rows(2,4)
# 删除列
sheet.delete_cols(2,4)

# 最后一定要保存一遍
workbook.save('test.xlsx')

# 创建excel
from openpyxl import Workbook
workbook = Workbook()

在这里插入图片描述

openpyxl优化模式

有时，你可能需要打开或写入极端大的 XLSX 文件，但通用的 openpyxl 程序无法处理这么大的负载。幸运的是，有两种模式可以使你在（几乎）恒定的内存消耗下读写无限量的数据。

只读模式：有时，你可能需要打开或写入极端大的 XLSX 文件，但通用的 openpyxl 程序无法处理这么大的负载。幸运的是，有两种模式可以使你在（几乎）恒定的内存消耗下读写无限量的数据。
```
from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data']

for row in ws.rows:
    for cell in row:
        print(cell.value)
```
只写模式：常规的 openpyxl.worksheet.worksheet.Worksheet 被替代成更快的 openpyxl.worksheet._write_only.WriteOnlyWorksheet 。当你想导出大量数据的时候请确保安装了 lxml 库.
```
>>> from openpyxl import Workbook
>>> wb = Workbook(write_only=True)
>>> ws = wb.create_sheet()
>>>
>>> # now we'll fill it with 100 rows x 200 columns
>>>
>>> for irow in range(100):
...     ws.append(['%d' % i for i in range(200)])
>>> # save the file
>>> wb.save('new_big_file.xlsx') # doctest: +SKIP
```
和普通工作簿不同的是，新创建的只写工作簿没有任何工作表；工作表只能由 create_sheet() 方法进行创建。

在只读工作簿中，只能由 append() 来添加行。无法使用 cell() 或 iter_rows() 对任意位置的单元进行读取或写入。

可以导出不限量的数据（即使超过 Excel 的处理上限），同时内存使用量小于10Mb。

一个只写工作簿只能保存一次。之后如果任何尝试保存和添加数据（append()）的操作都会会引发 openpyxl.utils.exceptions.WorkbookAlreadySaved 错误。

导出大数据，但内存又不够用的时候，可以给openpyxl加上只读或只写属性，并且安装lxml库（因为excel解压后也是xml格式的文件），按照官方文档的说法可以增加一些读写速度，但对于内存使用量小于10Mb，我感觉不可信，导出150MB的excel文件，kubectl top内存占用5G+。

另外可以手动调用gc去回收一些引用计数为0的变量，

import gc
# 调用del引用计数-1
del wb, ws
# 触发垃圾回收
gc.collect()

pandas

openpyxl，xlrd 都只可以读取filename，而不能从io种读取excel和csv，例如，无法将request.content作为输入源，所以引入了pandas。

用途

Pandas 可以从各种文件格式比如 CSV、JSON、SQL、Microsoft Excel 导入数据。
Pandas 可以对各种数据进行运算操作，比如归并、再成形、选择，还有数据清洗和数据加工特征。
Pandas 广泛应用在学术、金融、统计学等各个数据分析领域。

数据结构

Pandas 的主要数据结构是 Series （一维数据）与 DataFrame（二维数据），这两种数据结构足以处理金融、统计、社会科学、工程等领域里的大多数典型用例。

Series 是一种类似于一维数组的对象，它由一组数据（各种Numpy数据类型）以及一组与之相关的数据标签（即索引）组成。

DataFrame 是一个表格型的数据结构，它含有一组有序的列，每列可以是不同的值类型（数值、字符串、布尔型值）。DataFrame 既有行索引也有列索引，它可以被看做由 Series 组成的字典（共同用一个索引）。

Pandas 数据结构 - DataFrame

在这里插入图片描述
DataFrame 构造方法如下：

pandas.DataFrame( data, index, columns, dtype, copy)

参数说明：

data：一组数据(ndarray、series, map, lists, dict 等类型)。
index：索引值，或者可以称为行标签。
columns：列标签，默认为 RangeIndex (0, 1, 2, …, n) 。
dtype：数据类型。
copy：拷贝数据，默认为 False。
Pandas DataFrame 是一个二维的数组结构，类似二维数组。

使用列表创建

import pandas as pd

data = [['Google',10],['Codebaoku',12],['Wiki',13]]
df = pd.DataFrame(data,columns=['Site','Age'])
print(df)

输出结果如下：

        Site  Age
0     Google   10
1  Codebaoku   12
2       Wiki   13

使用 ndarrays 创建

import pandas as pd

data = {'Site':['Google', 'Codebaoku', 'Wiki'], 'Age':[10, 12, 13]}
df = pd.DataFrame(data)
print (df)

结果与上一致。

使用字典创建

import pandas as pd

data = [{'a': 1, 'b': 2}, {'a': 5, 'b': 10, 'c': 20}]
df = pd.DataFrame(data)
print(df)

输出结果如下：

   a   b     c
0  1   2   NaN
1  5  10  20.0

没有对应的部分数据为 NaN。

Pandas 可以使用 loc 属性返回指定行的数据，如果没有设置索引，第一行索引为 0，第二行索引为 1，以此类推：

import pandas as pd

data = {
  "calories": [420, 380, 390],
  "duration": [50, 40, 45]
}

# 数据载入到 DataFrame 对象
df = pd.DataFrame(data)

# 返回第一行
print(df.loc[0])
# 返回第二行
print(df.loc[1])

输出结果如下：

calories    420
duration     50
Name: 0, dtype: int64
calories    380
duration     40
Name: 1, dtype: int64

注意：返回结果其实就是一个 Pandas Series 数据。

也可以返回多行数据，使用 [[ … ]] 格式，… 为各行的索引，以逗号隔开：

import pandas as pd

data = {
  "calories": [420, 380, 390],
  "duration": [50, 40, 45]
}

# 数据载入到 DataFrame 对象
df = pd.DataFrame(data)

# 返回第一行和第二行
print(df.loc[[0, 1]])

输出结果为：

   calories  duration
0       420        50
1       380        40
注意：返回结果其实就是一个 Pandas DataFrame 数据。

可以指定索引值，如下范例：

import pandas as pd

data = {
  "calories": [420, 380, 390],
  "duration": [50, 40, 45]
}

df = pd.DataFrame(data, index = ["day1", "day2", "day3"])
print(df)

输出结果为：

      calories  duration
day1       420        50
day2       380        40
day3       390        45

Pandas 可以使用 loc 属性返回指定索引对应到某一行：

import pandas as pd

data = {
  "calories": [420, 380, 390],
  "duration": [50, 40, 45]
}

df = pd.DataFrame(data, index = ["day1", "day2", "day3"])

# 指定索引
print(df.loc["day2"])

输出结果为：

calories    380
duration     40
Name: day2, dtype: int64

Pandas CSV 文件

import pandas
from io import BytesIO

# 模拟request
buf = BytesIO()
with open("./test.csv", "rb") as f:
    for line in f:
        buf.write(line)

# 指针回溯
buf.seek(0)

res = pandas.read_csv(buf)
print(res)

输出结果如下：

   col1  col2  col3
0     1     2     3
1     4     5     6

也可以使用 to_csv() 方法将 DataFrame 存储为 csv 文件：

import pandas as pd 
   
# 三个字段 name, site, age
nme = ["Google", "Codebaoku", "Taobao", "Wiki"]
st = ["www.google.com", "www.codebaoku.com", "www.taobao.com", "www.wikipedia.org"]
ag = [90, 40, 80, 98]
   
# 字典
dict = {'name': nme, 'site': st, 'age': ag} 
     
df = pd.DataFrame(dict)
  
# 保存 dataframe
df.to_csv('site.csv')

使用

数据表信息查看

维度查看：

df.shape

数据表基本信息（维度、列名称、数据格式、所占空间等）：

df.info()

每一列数据的格式：

df.dtypes

某一列格式：

df['B'].dtype

空值：

df.isnull()

查看某一列空值：

df['B'].isnull()

查看某一列的唯一值：

df['B'].unique()

查看数据表的值：

df.values

查看列名称：

df.columns

查看前5行数据、后5行数据：

df.head() #默认前5行数据
df.tail()    #默认后5行数据

数据表清洗

1、用数字0填充空值：

df.fillna(value=0)

2、使用列prince的均值对NA进行填充：

df['prince'].fillna(df['prince'].mean())

3、清除city字段的字符空格：

df['city']=df['city'].map(str.strip)

4、大小写转换：

df['city']=df['city'].str.lower()

5、更改数据格式：

df['price'].astype('int')

6、更改列名称：

df.rename(columns={'category': 'category-size'})

7、删除后出现的重复值：

df['city'].drop_duplicates()

8 、删除先出现的重复值：

df['city'].drop_duplicates(keep='last')

9、数据替换：

df['city'].replace('sh', 'shanghai')

数据预处理

df1=pd.DataFrame({"id":[1001,1002,1003,1004,1005,1006,1007,1008], 
"gender":['male','female','male','female','male','female','male','female'],
"pay":['Y','N','Y','Y','N','Y','N','Y',],
"m-point":[10,12,20,40,40,40,30,20]})

数据表合并
1.1 merge
```
df_inner=pd.merge(df,df1,how='inner')  # 匹配合并，交集
df_left=pd.merge(df,df1,how='left')        #
df_right=pd.merge(df,df1,how='right')
df_outer=pd.merge(df,df1,how='outer')  #并集
```
1.2 append
```
result = df1.append(df2)
```
1.3 join
```
result = left.join(right, on='key')
```
1.4 concat
```
pd.concat(objs, axis=0, join='outer', join_axes=None, ignore_index=False,
	          keys=None, levels=None, names=None, verify_integrity=False,
	          copy=True)
```
- objs︰一个序列或系列、综合或面板对象的映射。如果字典中传递，将作为键参数，使用排序的键，除非它传递，在这种情况下的值将会选择
  （见下文）。任何没有任何反对将默默地被丢弃，除非他们都没有在这种情况下将引发 ValueError。
- axis: {0，1，…}，默认值为 0。要连接沿轴。
- join: {‘内部’、 ‘外’}，默认 ‘外’。如何处理其他 axis(es) 上的索引。联盟内、外的交叉口。
- ignore_index︰布尔值、默认 False。如果为 True，则不要串联轴上使用的索引值。由此产生的轴将标记
  0，…，n-1。这是有用的如果你串联串联轴没有有意义的索引信息的对象。请注意在联接中仍然受到尊重的其他轴上的索引值。
- join_axes︰索引对象的列表。具体的指标，用于其他 n-1 轴而不是执行内部/外部设置逻辑。 keys︰
  序列，默认为无。构建分层索引使用通过的键作为最外面的级别。如果多个级别获得通过，应包含元组。
- levels︰列表的序列，默认为无。具体水平（唯一值）用于构建多重。否则，他们将推断钥匙。
- names︰列表中，默认为无。由此产生的分层索引中的级的名称。
- verify_integrity︰布尔值、默认 False。检查是否新的串联的轴包含重复项。这可以是相对于实际数据串联非常昂贵。
- 副本︰布尔值、默认 True。如果为 False，请不要，不必要地复制数据。
```
例子：1.frames = [df1, df2, df3]
     2.result = pd.concat(frames)
```

2、设置索引列

df_inner.set_index('id')

3、按照特定列的值排序：

df_inner.sort_values(by=['age'])

4、按照索引列排序：

df_inner.sort_index()

5、如果prince列的值>3000，group列显示high，否则显示low：

df_inner['group'] = np.where(df_inner['price'] > 3000,'high','low')

6、对复合多个条件的数据进行分组标记

df_inner.loc[(df_inner['city'] == 'beijing') & (df_inner['price'] >= 4000), 'sign']=1

7、对category字段的值依次进行分列，并创建数据表，索引值为df_inner的索引列，列名称为category和size

pd.DataFrame((x.split('-') for x in df_inner['category']),index=df_inner.index,columns=['category','size'])

8、将完成分裂后的数据表和原df_inner数据表进行匹配

df_inner=pd.merge(df_inner,split,right_index=True, left_index=True)

数据提取

主要用到的三个函数：loc,iloc和ix，loc函数按标签值进行提取，iloc按位置进行提取，ix可以同时按标签和位置进行提取。

1、按索引提取单行的数值

df_inner.loc[3]

2、按索引提取区域行数值

df_inner.iloc[0:5]

3、重设索引

df_inner.reset_index()

4、设置日期为索引

df_inner=df_inner.set_index('date')

5、提取4日之前的所有数据

df_inner[:'2013-01-04']

6、使用iloc按位置区域提取数据

df_inner.iloc[:3,:2] #冒号前后的数字不再是索引的标签名称，而是数据所在的位置，从0开始，前三行，前两列。

7、适应iloc按位置单独提起数据

df_inner.iloc[[0,2,5],[4,5]] #提取第0、2、5行，4、5列

8、使用ix按索引标签和位置混合提取数据

df_inner.ix[:'2013-01-03',:4] #2013-01-03号之前，前四列数据

9、判断city列的值是否为北京

df_inner['city'].isin(['beijing'])

10、判断city列里是否包含beijing和shanghai，然后将符合条件的数据提取出来

df_inner.loc[df_inner['city'].isin(['beijing','shanghai'])]

11、提取前三个字符，并生成数据表

pd.DataFrame(df_inner['category'].str[:3])

数据筛选

使用与、或、非三个条件配合大于、小于、等于对数据进行筛选，并进行计数和求和。

1、使用“与”进行筛选

df_inner.loc[(df_inner['age'] > 25) & (df_inner['city'] == 'beijing'), ['id','city','age','category','gender']]

2、使用“或”进行筛选

df_inner.loc[(df_inner['age'] > 25) | (df_inner['city'] == 'beijing'), ['id','city','age','category','gender']].sort(['age'])

3、使用“非”条件进行筛选

df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age','category','gender']].sort(['id'])

4、对筛选后的数据按city列进行计数

df_inner.loc[(df_inner['city'] != 'beijing'), ['id','city','age','category','gender']].sort(['id']).city.count()

5、使用query函数进行筛选

df_inner.query('city == ["beijing", "shanghai"]')

6、对筛选后的结果按prince进行求和

df_inner.query('city == ["beijing", "shanghai"]').price.sum()

数据汇总

主要函数是groupby和pivote_table

1、对所有的列进行计数汇总
df_inner.groupby(‘city’).count()

2、按城市对id字段进行计数
df_inner.groupby(‘city’)[‘id’].count()

3、对两个字段进行汇总计数
df_inner.groupby([‘city’,‘size’])[‘id’].count()

4、对city字段进行汇总，并分别计算prince的合计和均值
df_inner.groupby(‘city’)[‘price’].agg([len,np.sum, np.mean])

数据统计

数据采样，计算标准差，协方差和相关系数

1、简单的数据采样
df_inner.sample(n=3)

2、手动设置采样权重
weights = [0, 0, 0, 0, 0.5, 0.5]
df_inner.sample(n=2, weights=weights)

3、采样后不放回
df_inner.sample(n=6, replace=False)

4、采样后放回
df_inner.sample(n=6, replace=True)

5、数据表描述性统计
df_inner.describe().round(2).T #round函数设置显示小数位，T表示转置

6、计算列的标准差
df_inner[‘price’].std()

7、计算两个字段间的协方差
df_inner[‘price’].cov(df_inner[‘m-point’])

8、数据表中所有字段间的协方差
df_inner.cov()

9、两个字段的相关性分析
df_inner[‘price’].corr(df_inner[‘m-point’]) #相关系数在-1到1之间，接近1为正相关，接近-1为负相关，0为不相关

10、数据表的相关性分析
df_inner.corr()

数据输出

分析后的数据可以输出为xlsx格式和csv格式

1、写入Excel
df_inner.to_excel(‘excel_to_python.xlsx’, sheet_name=‘bluewhale_cc’)

2、写入到CSV
df_inner.to_csv(‘excel_to_python.csv’)

Generalzy

关注

1
点赞
踩
11

收藏

觉得还不错? 一键收藏
打赏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录