【Python】pandas与Excel文件结合操作手册

Date: 2021.04.01
Author: jwensh

关键词： python pandas excel

1. 关于 pandas 会使用的依赖

Deprecated since version 1.2.0: As the xlwt package is no longer maintained, the xlwt engine will be removed from a future version of pandas. This is the only engine in pandas that supports writing to .xls files.

pandas通过两种方法选择Excel书写器:

要指定要使用的编写器，可以将引擎关键字参数传递给 to_excel和 ExcelWriter。内置引擎为：

要指定要使用的writer, 可以将引擎关键字参数传递给 to_excel和 ExcelWriter。内置引擎有：

• openpyxl: version 2.4 or higher is required 模块openpyxl源于PHPExcel，它提供了针对.xlsx文件的读写功能
• xlsxwriter
• xlwt （模块xlrd能用来析取.xls和.xlsx文件中的数据）

先来生成用于填充 pandas 中DataFrame的随机数，然后用这个DataFrame创建一个Excel文件，接着再用Excel文件重建DataFrame，并通过mean()方法来计算其平均值。对于Excel文件的工作表，我们既可以为其指定一个从0开始计数的索引，也可以为其规定一个名称。

  import numpy as np
    import pandas as pd
    from tempfile import NamedTemporaryFile

    np.random.seed(42)
    a = np.random.randn(365,4)
    tmpf = NamedTemporaryFile(suffix='.xlsx')

    df = pd.DataFrame(a)

    print tmpf.name
    df.to_excel(tmpf.name,sheet_name='Random Data')
    print "Means\n", pd.read_excel(tmpf.name, 'Random Data').mean()

• 通过to_excel()方法创建Excel文件，具体如下：
  df.to_excel(tmpf.name,sheet_name='Random Data') 将DataFrame里的内容写入tmpf.name里，并把表取名为Random Data
• 下面使用顶级read_excel()函数来重建DataFrame，代码如下：
  print "Means\n", pd.read_excel(tmpf.name, 'Random Data').mean() 读取tmpf.name里的Random Data表，并求每列的平均值

2. 理解to_excel操作

df = pandas.DataFrame()

df.to_excel()

Two-dimensional, size-mutable, potentially heterogeneous tabular data.

二维、大小可变、潜在异构的表格数据。

可理解为内存中的一个可以操作的数据表格，且能够像操作excel表格那样的简单，有很多功能。比较常用的：就是表格的数据更新、统计、筛选等等。 可以通过源码或者官方问题，来查看 pandas.DataFrame()的相关参数，去看他是怎么实现的。

这个是 NDFrame 类的方法，DataFrame 类继承了 class DataFrame(NDFrame, OpsMixin) ，所以 df 能直接使用；从源码上能看出，to_excel 方法里面会用到一个 ExcelWriter 对象来实现写出的功能，而它底层有个需要，需要根据安装的依赖来选择使用那个。

• 没有writer的情况下：

df.to_excel("./data.xlsx")

• 有writer的请求下：

writer = pd.ExcelWriter(os.path.join(ios_inspection_excel_file,'.xlsx'),engine='xlsxwriter')

df.to_excel(writer, sheet_name='Sheet1') # 注意

worksheet = writer.sheets['Sheet1']
writer.save()

注意：to_excel 方法里面有个判断，当你给他传 ExcelWriter 对象的时候，他就不自行保存操作了，而是交给writer

if isinstance(writer, ExcelWriter):
        need_save = False
else:
    # pandas\io\formats\excel.py:808: error: Cannot instantiate
    # abstract class 'ExcelWriter' with abstract attributes 'engine',
    # 'save', 'supported_extensions' and 'write_cells'  [abstract]
    writer = ExcelWriter(  # type: ignore[abstract]
    writer, engine=engine, storage_options=storage_options
    )
        need_save = True

3. 数据的删除、增加、更新

已有一个DataFrame 对象的情况下，且有表格数据

id姓名性别身高1aa男1222bb女160

def drop(
        self,
        labels=None,
        axis=0,
        index=None,
        columns=None,
        level=None,
        inplace=False,
        errors="raise",
    )

注意：输出是要在原表格删除，还是要生成副本操作，使用 inplace 参数来关注， false 的时候 返回副本，true 的时候 直接修改原表格。

• 根据 索引 删除 df1 = df.drop([0]) 删除第 0 行（header 不算在内）
• df1 = df1.drop(['姓名'],axis=1), 删除姓名那一列列
• 过滤删除行 (返回一个副本)： 这里使用的是全等于

df = df[df['身高'].isin([160])]

df = df[~df['身高'].isin([160])]

• 过滤删除列

cols=[x for i,x in enumerate(df.columns) if df.iat[0,i] == '男']
df.drop(cols,axis=1)

还有很多其他的用法，可以去官网查看

• 根据索引增加一行记录 df.loc[2] = [3, 'cc', '男'， 180] 需要区别（df.iloc 按位置选择）

df.loc  先用loc找到要更改的值，再用赋值（=）的方法实现更换值
df.iloc 用索引位置来查找

两个可以更换单行、单列、多行、多列的值

• 追加方式 df.append() （比上面的方式稍快些，还可以避免index的错误）

s = pd.Series({'id': 4, '姓名': 'DD', '身高': 150})

df = df.append(s, ignore_index=True)

df = df.append({'id': 4, '姓名': 'DD', '身高': 150}, ignore_index=True)

• 使用 concat 拼接两个 DataFrame

In [1]: df1 = pd.DataFrame(
   ...:     {
   ...:         "A": ["A0", "A1", "A2", "A3"],
   ...:         "B": ["B0", "B1", "B2", "B3"],
   ...:         "C": ["C0", "C1", "C2", "C3"],
   ...:         "D": ["D0", "D1", "D2", "D3"],
   ...:     },
   ...:     index=[0, 1, 2, 3],
   ...: )
   ...:

In [2]: df2 = pd.DataFrame(
   ...:     {
   ...:         "A": ["A4", "A5", "A6", "A7"],
   ...:         "B": ["B4", "B5", "B6", "B7"],
   ...:         "C": ["C4", "C5", "C6", "C7"],
   ...:         "D": ["D4", "D5", "D6", "D7"],
   ...:     },
   ...:     index=[4, 5, 6, 7],
   ...: )
   ...:

In [4]: frames = [df1, df2]

In [5]: result = pd.concat(frames, ignore_index=True)

at 、iat只能更换单个值
df1.at[0,'身高'] = 175   # iat 用来取某个单值,参数只能用数字索引
df1.iat[0,3] = 25       # at 用来取某个单值,参数只能用index和columns索引名称

4. 样式操作

官网推荐使用的: xlsxwriter 驱动

df = pd.DataFrame(data=valuesDict,index=[0])

writer = pd.ExcelWriter('./data.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
worksheet = writer.sheets['Sheet1']
worksheet.set_column("A:E", 25)
writer.save()

5. 后续更新

• 将数据写入 Excel （新建xlsx）

def to_excel():

    df = pd.DataFrame(columns=["ID", "名称", "链接", "分类", "问题点"])

    data_origin = Api()
    n = 0
    with open("./data/result.txt", 'r') as f:
        for i in f:

            df.loc[n] = [Id, title, url, categories, json.dumps(data['noPass'], ensure_ascii=False)]
            n += 1
    print(n)

    writer = pd.ExcelWriter('./data/问题点记录.xlsx',  engine='xlsxwriter')

    df.to_excel(writer, index=False, header=True, sheet_name='Sheet1')

    worksheet = writer.sheets['Sheet1']

    worksheet.set_column("A:E", 20)

    writer.save()

• https://pandas.pydata.org/docs/user_guide/io.html

Original: https://blog.csdn.net/u013948858/article/details/115375987
Author: jwensh
Title: 【Python】pandas与Excel文件结合操作手册