0

I've read in the below excel workbook which has 40 sheets. The below reads in all the worksheets:

df = pd.read_excel(file_path, sheet_name = None)

All the worksheets have identical columns, but the relevant columns start at different rows in each worksheet, so I'm writing the below to create a df from each sheet:

df2 = df[Sheet_Name]
df2.columns = df2.iloc[20]

I could replicate this code 40 times with the relevant row index, but there has to be a function with a loop that can clean the code.

I was thinking of having 2 lists, sheet name & row index, which the function iterates over to create a separate df for each worksheet.

Is this possible?

Thanks for your help

Tickets2Moontown
  • 117
  • 1
  • 10

1 Answers1

0

It should be possible using next()

new_dfs = list()
indexes = iter([20, 12, 20])
for sheet in df:
    new_df = df[sheet]
    new_df.colunms = new_df.iloc[next(indexes)]
    new_dfs.append(new_df)

I did not run this code, but the idea is worth trying

Haas
  • 1
  • 2
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 04 '22 at 18:19