发布于 2年前

使用openpyxl排除Excel隐藏行和列加载数据到Pandas Dataframe

默认的pandas加载excel数据

pandas提供了read_excel()函数加载excel数据。使用如下:

>>> import pandas as pd
>>> df = pd.read_excel("data/titanic.xlsx")
>>> df

在做数据处理有时需要隐藏excel中的列和行。但直接使用pandas.read_excel(),还是会读取excel中隐藏列和行,不能排除隐藏的行和列。

使用openpyxl排除隐藏行和列

openpyxl是一个第三方包,可以使用它来读写Excel 2010 xlsx/xlsm/xltx/xltm相关的文件。openpyxl不是Python内置包,所以要使用它读excel,首先要安装openpyxl。

>>> pip install openpyxl

使用 openpyxl 加载 Excel和sheet

安装完openpyxl后,使用load_workbook()加载workbook

>>> import openpyxl    
# 打开excel的workbook
>>> workbook = openpyxl.load_workbook("data/titanic.xlsx")

workbook表示整个excel文件,可以通过.sheetnames来获取excel的所有sheet名列表

# 获取excel所有的sheet列表
>>> sheet_names = workbook.sheetnames

# 通过sheet名创建worksheet 
>>> worksheet = workbook[sheet_names[0]]

找出隐藏的行

worksheet都对象的row_dimensions可以获取所有的行,我们可以使用每行的hidden属性来列出所有隐藏行的index,代码如下:

>>> hidden_rows_idx = [
        row - 2 
        for row, dimension in worksheet.row_dimensions.items() 
        if dimension.hidden
    ]
>>> print(hidden_rows_idx)
[4, 9, 14, 19]

需注意的是,这里使用row - 2而非row,原因是要找到对应于 Pandas DataFrame 的索引,而不是Excel的索引。

找出隐藏的列

worksheet对象对应列的是.column_dimension,同样使用.hidden来获取所有隐藏的列。

>>> hidden_cols = [
        col 
        for col, dimension in worksheet.column_dimensions.items() 
        if dimension.hidden
    ]
>>> print(hidden_cols)
['F', 'I', 'K']

因为在excel中,列是使用字母表示的,这里需要把它们转换为数字索引:

>>> hidden_cols_idx = [
string.ascii_uppercase.index(col_name) 
for col_name in hidden_cols
]

接着在df中根据列索引找到df中的列名:

>>> hidden_cols_name = df.columns[hidden_cols_idx].tolist()
>>> print(hidden_cols_name)
['Age', 'Ticket', 'Cabin']

排除Pandas dataframe中隐藏的列和行

这里使用了df的drop()函数,丢弃df隐藏的列和行

# 移除隐藏列
>>> df.drop(hidden_cols_name, axis=1, inplace=True)
# 移除隐藏的行
>>> df.drop(hidden_rows_idx, axis=0, inplace=True)
最后,记得重置索引 
>>> df.reset_index(drop=True, inplace=True)
>>> df

至此,就完成了排除excel中隐藏的列和行的数据。

©2020 edoou.com   京ICP备16001874号-3