使用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中隐藏的列和行的数据。