python pandas处理excel表单整合,如何使用python pandas在excel电子表格中找到最后一列...

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.

解决方案

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]