I have an abnormal setup where I have multiple tables on the same excel sheet. I'm trying to make each table (on the same sheet) a separate pandas dataframe. For example, on one excel sheet I might have:
+--------+--------+--------+--------+--------+--------+-----+
| Col | Col | Col | Col | Col | Col | Col |
+--------+--------+--------+--------+--------+--------+-----+
| Table1 | Table1 | | | | | |
| | | Table2 | Table2 | Table2 | Table2 | |
| | Table3 | Table3 | Table3 | | | |
+--------+--------+--------+--------+--------+--------+-----+
And what I want is tables broken out by table type (example below is one of the multiple tables in a pandas df). The table header beginning column is unique for each table, so table1 might have the column header corner column named: "Leads", table2 has the column header corner column named: "Sales", and table3 has the column header named: "Products".
+--------+--------+--+
| Leads | Table1 | |
+--------+--------+--+
| pd.Data| pd.Data| |
| | | |
| | | |
+--------+--------+--+
+--------+--------+--------+--------+--+
| Sales | Table2 | Table2 | Table2 | |
+--------+--------+--------+--------+--+
| pd.Data| pd.Data| pd.Data| pd.Data| |
| | | | | |
| | | | | |
+--------+--------+--------+--------+--+
+---------+---------+---------+--+
| Products| Table3 | Table3 | |
+---------+---------+---------+--+
| pd.Data | pd.Data | pd.Data | |
| | | | |
| | | | |
+---------+---------+---------+--+
Because I know that pandas will do well just assuming that the excel sheet is one big table, but with multiple tables I'm stumped on the best way to partition the data into separate df's, especially because I can't index on row or column due to variable length of the tables over time.
This is how far I got before I realized this only works for one table, not three:
import pandas as pd
import string
df = pd.read_excel("file.xlsx", usecols="B:I", index_col=3)
print(df)
letter = list(string.ascii_uppercase)
df1 = pd.read_excel("file.xlsx")
def get_start_column(df):
for i, column in enumerate(df.columns):
if df[column].first_valid_index():
return letter[i]
def get_last_column(df):
columns = df.columns
len_column = len(columns)
for i, column in enumerate(columns):
if df[column].first_valid_index():
return letter[len_column - i]
def get_first_row(df):
for index, row in df.iterrows():
if not row.isnull().values.all():
return index + 1
def usecols(df):
start = get_start_column(df)
end = get_last_column(df)
return f"{start}:{end}"
df = pd.read_excel("file.xlsx", usecols=usecols(df1), header=get_first_row(df1))
print(df)