[Python] What's wrong with my python script to separate out tables in excel that have a blank...

I have multiple tables where it's like

Column A Column B
Cell 1 Cell 2
Cell 3 Cell 4

---Blank row---

Column A Column B
Cell 1 Cell 2
Cell 3 Cell 4

--- Blank row---

Column A Column B
Cell 1 Cell 2
Cell 3 Cell 4

all in one worksheet in a workbook that has data on multiple worksheets in one workbook in Excel.

I researched (aka Googled/ChadGPT'd/Anaconda'd) the code needed to parse out each table, which is below.

import pandas as pd

def separate_excel_tables(excel_file_path, sheet_name=Sec_10.xlsx):

#Separates tables from an Excel sheet where tables are separated by blank rows.

# Args:
file_path= Users\name\Downloads
sheet_name =Sec_10.xlsx
'''
Returns:
list: A list of pandas DataFrames, each representing a separated table.
'''
df = pd.read_excel(file_path, sheet_name=sheet_name, header=0)

# Find the indices of blank rows
blank_row_indices = df[df.isnull().all(axis=1)].index.tolist()

# Add the start and end of the DataFrame for boundary conditions
table_boundaries = [-1] + blank_row_indices + [len(df)]

separated_tables = []
for i in range(len(table_boundaries) - 1):
start_row = table_boundaries + 1
end_row = table_boundaries[i+1]

# Extract the potential table
potential_table = df.iloc[start_row:end_row]

# Check if the potential table is not empty after removing blank rows
if not potential_table.empty:
# Assume the first non-blank row is the header
header_row = potential_table.iloc[0]
data_rows = potential_table.iloc[1:]

# Create a DataFrame with the correct header
table_df = pd.DataFrame(data_rows.values, columns=header_row.values)

# Drop any rows that might still be entirely blank within the table
table_df = table_df.dropna(how='all')

if not table_df.empty:
separated_tables.append(table_df)

return separated_tables

# Example usage:
# excel_file = 'your_excel_file.xlsx'
# tables = separate_excel_tables(excel_file)

# # You can now iterate through the 'tables' list to access each DataFrame
# for i, table in enumerate(tables):
# print(f"Table {i+1}:\n{table}\n")`
here


What I expected was a table on in each data frame that I could then download as a csv or excel worksheet.

Continue reading...