Search for starting column and row of column_names in excel file using pandas

user_5862812 0
Viewed 240

I'm analyzing excel files generated by an organization who publishes yearly reports in Excel files. Each year, the column names (Year, A1, B1, C1, etc) remain identical. But each year the organization publishes those column names that start at different row numbers and column numbers.

Each year I manually search for the starting row and column, but it's tedious work given the number of years of reports to wade through.

So I'd like something like this:

...

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

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

...

Thanks.

pandas excel python
2 Answers

Soething Like this not totally sure what you are looking for df = pd.read_excel('test.xlsx')

for i in df.index:
    print(df.loc[i,'Year'])
    print(df.loc[i, 'A1'])
    print(df.loc[i, "B1"])
Admin1

Let's say you have three .xlsx files on your desktop prefixed with Yearly_Report that when combined in python look like this after reading into one dataframe with something like: 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

As you can see, the columns and values are scattered across various columns and rows. The following steps would get you the desired result. First, you need to pd.concat the files and .dropna rows. Then, transpose the dataframe with .T before removing all cells with NaN values. Next, revert the dataframe back with another transpose .T. Finally, simply name the columns and drop rows that are equal to the column headers.

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

output:

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