Dynamically skip all unwanted rows present above real header in excel through python

user_16976575 0
Viewed 313

I have a excel file which has some unwanted rows (both blank and some with text) before my real header. When I read it through pandas, the below code works fine.

df = pd.read_excel("C:/path.xlsx", skiprows=15)

BUT, problem is , the unwanted rows can change every time I pull the data. I do not want to manually check and change skiprows value. What is the easiest way of fixing it? I am a beginner so if you provide solution then explain bit in detail.

If it matters, my 1st column header is "No." always. Just in case you wanna use it as reference in code.

python-requests pandas python
1 Answers

Pandas is not the best library to do such operations. I would recommend using openpyxl with 'for loop'. I do not see any sample of your data, but if you really want to use pandas we can consider something like this:

Let's say we have such structure of the data:

enter image description here

Then:

  1. Read all data and do not skip any row:

    df = pd.read_excel("C:/path.xlsx")

  1. Find index of first row with 'No." in the first column and take everything till the end:

    df = df.iloc[df[df.iloc[:, 0].eq('No.')].index[0]:, :].reset_index(drop=True)

  2. Set first row as columns:

    df.columns = df.iloc[0]

  3. Drop first row (we do not need it because there are column names repeated there):

    df = df.drop(0).reset_index(drop=True)

After these operations we get our output:

print(df)

   No.   Column1  Column2   Column3
0    1         a        b         c
1    2         c        a         b
2    3         b        c         a
3    4         y        b         z
user_133401430
Related