How to parse dataframes from an excel sheet with many tables (using Python, possibly Pandas)

Question

I'm dealing with badly laid out excel sheets which I'm trying to parse and write into a database.

Every sheet can have multiple tables. Though the header for these possible tables are known, which tables are gonna be on any given sheet is not, neither is their exact location on the sheet (the tables don't align in a consistent way). I've added a pic of two possible sheet layout to illustrate this: This layout has two tables, while this one has all the tables of the first, but not in the same location, plus an extra table.

What I do know:

1. All the possible table headers, so each individual table can be identified by its headers
2. Tables are separated by blank cells. They do not touch each other.

My question Is there a clean way to deal with this using some Python module such as pandas?

My current approach:

I'm currently converting to .csv and parsing each row. I split each row around blank cells, and process the first part of the row (should belong to the leftmost table). The remainder of the row is queued and later processed in the same manner. I then read this first_part and check whether or not it's a header row. If it is, I use it to identify which table I'm dealing with (this is stored in a global current_df). Subsequent rows which are not header rows are fed into this table (here I'm using pandas.DataFrame for my tables).

Code so far is below (mostly incomplete and untested, but it should convey the approach above):

class DFManager(object): # keeps track of current table and its headers
    current_df = None
    current_headers = []

    def set_current_df(self, df, headers):
        self.current_headers = headers
        self.current_df = df


def split_row(row, separator):
    while row and row[0] == separator:
       row.pop(0)
    while row and row[-1] == separator:
       row.pop()

    if separator in row:
        split_index = row.index(separator)
        return row[:split_index], row[split_index:]
    else:
        return row, []


def process_df_row(row, dfmgr):
    df = df_with_header(row) # returns the dataframe with these headers

    if df is None: # is not a header row, add it to current df
        df = dfmgr.current_df
        add_row_to_df(row, df)
    else:
        dfmgr.set_current_df(df, row)


# this is passed the Excel sheet
def populate_dataframes(xl_sheet):
    dfmgr = DFManager()
    row_queue = Queue()
    for row in xl_sheet:
        row_queue.put(row)

    for row in iter(row_queue.get, None):
        if not row:
            continue

        first_part, remainder = split_row(row)
        row_queue.put(remainder)

        process_df_row(first_part, dfmgr)

Answer 1

This is such a specific situation that there is likely no "clean" way to do this with a ready-made module.

One way to do this might use the header information you already have to find the starting indices of each table, something like this solution (Python Pandas - Read csv file containing multiple tables), but with an offset in the column direction as well.

Once you have the starting position of each table, you'll want to determine the widths (either known a priori or discovered by reading until the next blank column) and read those columns into a dataframe until the end of the table.

The benefit of an index-based method rather than a queue based method is that you do not need to re-discover where the separator is in each row or keep track of which row fragments belong to which table. It is also agnostic to the presence of >2 tables per row.

Answer 2

I written code to merge multiple tables separated vertically, having common headers in each table. I am assuming unique headers should name does not end with dot integer number. '''

    def clean(input_file, output_file):
        try:
            df = pd.read_csv(input_file, skiprows=[1,1])
            df = df.drop(df.columns[df.columns.str.contains('unnamed',case = False)],axis=1)
            df = rename_duplicate_columns(df)
        except:
            df =[]
            print("Error: File Not found\t", sys.exc_info() [0])
            exit

        udf = df.loc[:, ~df.columns.str.match(".*\.\d")] 
        udf = udf.dropna(how='all')

        try: 
            table_num = int(df.columns.values[-1].split('.')[-1])
            fdf = udf
            for i in range(1,table_num+1):
                udfi = pd.DataFrame()
                udfi = df.loc[:, df.columns.str.endswith(f'.{i}')]
                udfi.rename(columns = lambda x: '.'.join(x.split('.')[:-1]), inplace=True)
                udfi = udfi.dropna(how='all')
                fdf = fdf.append(udfi,ignore_index=True)
            fdf.to_csv(output_file)

        except ValueError:
            print ("File Contains only single Table")
            exit

    def rename_duplicate_columns(df):
        cols=pd.Series(df.columns)
        for dup in df.columns.get_duplicates(): 
        cols[df.columns.get_loc(dup)]=[dup+'.'+str(d_idx) if d_idx!=0 else 
              dup for d_idx in range(df.columns.get_loc(dup).sum())]
        df.columns=cols
        print(df.columns)
        return df

    clean(input_file, output_file)

'''