python - 使用 Pandas 在excel文件中搜索column_names的起始列和行

Question

我正在分析一个以 Excel 文件形式发布年度报告的组织生成的 excel 文件。每年，列名(Year、A1、B1、C1 等)都保持不变。但是每年该组织都会发布那些以不同行号和列号开头的列名。

每年我都会手动搜索起始行和列，但考虑到要浏览的报告年数，这是一项乏味的工作。

所以我想要这样的东西:

...

  df = pd.read_excel('test.xlsx')

  start_row,start_col = df.find_columns('Year','A1','B1')

...

谢谢。

Answer 1

假设您的桌面上有三个前缀为 Yearly_Report 的 .xlsx 文件，当它们在 python 中组合时，在读入一个数据帧后看起来像这样: df = pd.concat([pd.read_excel(f, header=None) for f in yearly_files]):

    0   1   2   3   4   5   6   7   8   9   10
0   A   B   C   NaN NaN NaN NaN NaN NaN NaN NaN
1   1   2   3   NaN NaN NaN NaN NaN NaN NaN NaN
0   NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1   NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2   NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
3   NaN NaN A   B   C   NaN NaN NaN NaN NaN NaN
4   NaN NaN 4   5   6   NaN NaN NaN NaN NaN NaN
0   NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1   NaN NaN NaN NaN NaN NaN NaN NaN A   B   C
2   NaN NaN NaN NaN NaN NaN NaN NaN 4   5   6

如您所见，列和值分散在各个列和行中。以下步骤将为您提供所需的结果。首先，您需要pd.concat 文件和.dropna 行。然后，在删除所有具有 NaN 值的单元格之前，使用 .T 转置数据帧。接下来，使用另一个转置 .T 恢复数据帧。最后，简单地命名列并删除与列标题相同的行。

import glob, os
import pandas as pd
main_folder = 'Desktop/'
yearly_files = glob.glob(f'{main_folder}Yearly_Report*.xlsx')
df = pd.concat([pd.read_excel(f, header=None) for f in yearly_files]) \
                    .dropna(how='all').T \
                    .apply(lambda x: pd.Series(x.dropna().values)).T
df.columns = ['A','B','C']
df = df[df['A'] != 'A']
df

输出:

    A   B   C
1   1   2   3
4   4   5   6
2   4   5   6