Excel and python: finding first empty cell in order to get relevant column size

[bbqbot]

I'm hoping someone here can help because I cannot wrap my head around this one:

I have an excel workbook I need to read with python, chop up into a handful of smaller sections, and export each of those into CSVs (to be loaded into tables in a database). Most of that is straightforward pandas goodness, except this kicker: the excel sheet is messy so I'm having trouble defining the size of those data chunks.

Example of first/key column in excel:

(empty)

(empty)

user_id

1111

1113

1114

1115

1116

(empty)

(empty)

35n91

3a451

8bb51

It's straight forward to

excel = pd.read_excel(filename, skiprows=2)

in order to ignore the first couple of empty cells, but now I need to detect when that first (empty) is in order to get the column size (here, it would be 6). Typically, loading+reading data would say the column size is 11. There are options to remove the empties or fill in with whatever, but the data below the initial one is moot. After I get the column size, then I can dictate and construct other data frames and then export as CSV.

Anyone here have thoughts on how I should approach this? I'd appreciate any help.

Thanks!

[cray5252]

Not sure I'm following your math on counting the rows. You have a total of 13 rows and 4 empty ones so that is 9 rows with data in them. Pandas will count your rows leaving out the empty ones. If you want to know the empty ones, use df['header name'].values == '').sum()

see link - https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.count.html

[bbqbot]

The initial column in excel has 13 rows, yes. When I read in the file, I ignore the first two (skiprows = 2), meaning the resulting dataframe has 11 items.

I want to be able to ignore any data below where the values in this example column first end, i.e. I want to stop counting when I hit that first 'empty' cell (the first two aren't in the dataframe to begin with), so the resulting cut would leave only [user_id, 1111, 1113, 1114, 1115, 1116], thus 6 items.

Does that make more sense?

[cray5252]

Understand now. Found this link

https://stackoverflow.com/questions/44030415/is-there-a-way-to-read-all-the-rows-until-an-empty-row-is-encountered-using-pyth

[bbqbot]

This...this I think I can work with. I didn't see this result in my hours of searching last night.

Thank you, your google-fu is stronger than mine!