How to make dataframe from different parts of an Excel sheet given specific keywords?

user_8235041 0
Viewed 37

I have one Excel file where multiple tables are placed in same sheet. My requirement is to read certain tables based on keyword. I have read tables using skip rows and nrows method, which is working as of now, but in future it won't work due to dynamic table length.

Is there any other workaround apart from skip rows & nrows method to read table as shown in picture?

I want to read data1 as one table & data2 as another table. Out of which in particular I want columns "RR","FF" & "WW" as two different data frames.

Appreciate if some one can help or guide to do this.

enter image description here

Method I have tried:

all_files=glob.glob(INPATH+"*sample*")
df1 = pd.read_excel(all_files[0],skiprows=11,nrows= 3)
    
df2 = pd.read_excel(all_files[0],skiprows=23,nrows= 3)

This works fine, the only problem is table length will vary every time.

enter image description here enter image description here

pandas dataframe
1 Answers

With an Excel file identical to the one of your image, here is one way to do it:

import pandas as pd

df = pd.read_excel("file.xlsx").dropna(how="all").reset_index(drop=True)

# Setup
targets = ["Data1", "Data2"]
indices = [df.loc[df["Unnamed: 0"] == target].index.values[0] for target in targets]


dfs = []
for i in range(len(indices)):
    # Slice df starting from first indice to second one
    try:
        data = df.loc[indices[i] : indices[i + 1] - 1, :]
    except IndexError:
        data = df.loc[indices[i] :, :]
    
    # For one slice, get only values where row starts with 'rr'
    r_idx = data.loc[df["Unnamed: 0"] == "rr"].index.values[0]
    data = data.loc[r_idx:, :].reset_index(drop=True).dropna(how="all", axis=1)

    # Cleanup
    data.columns = data.iloc[0]
    data.columns.name = ""
    dfs.append(data.loc[1:, :].iloc[:, 0:3])

And so:

for item in dfs:
    print(item)
# Output
     rr       ff          ww
1  car1  1000000     sellout
2  car2  1500000  to be sold
3  car3  1300000     sellout

     rr       ff          ww
1  car1  1000000     sellout
2  car2  1500000  to be sold
3  car3  1300000     sellout
user_112460560
Related