how do you find the last column or row in an excel

Question

Hi am looking to import part of a spreadsheet as a data frame using pandas but the problem is the spreadsheet changes weekly and the number of rows and columns varies each week.

In Excel VBA I can programmatically determine the number of columns and rows in an excel spreadsheet, but how do I determine that in python??

col-1 | col -2
1.     blue
2.     green
3.     blue
4.     blank
5.     blank

I need some python code preferably a parameter of the python - pandas method read_excel which tells me that the number of columns in the document I pass to it is 2, and the number of rows is 5 or 6 if you include header.

However I need this to be programmable (called each time) because I want to feed it spreadsheets of different sizes with different numbers of columns.

Answer 1

Use fillna with method 'ffill'

In [71]:

df1 = pd.DataFrame({'a':[1,2,3,NaN,NaN,4,5,6], 'b':[1,2,NaN,NaN, 3,NaN,4,5]})
df1
Out[71]:
    a   b
0   1   1
1   2   2
2   3 NaN
3 NaN NaN
4 NaN   3
5   4 NaN
6   5   4
7   6   5

[8 rows x 2 columns]

In [85]:

df1.fillna(method='ffill',inplace=True)
df1
Out[85]:
   a  b
0  1  1
1  2  2
2  3  2
3  3  2
4  3  3
5  4  3
6  5  4
7  6  5

[8 rows x 2 columns]