Excel and python: finding first empty cell in order to get relevant column size
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!
Posted Aug 26, 2020, 7:08 PM UTC
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
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?
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
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!