How to count excel row number for certain column with pandas

Question

I want to count row number for certain column with pandas. I have DataFrame like this:

   A    B    C
0  1    a    a
1  2    b    b
2  3    c    c
3  4  NaN  NaN
4  5  NaN  NaN
5  6  NaN  NaN
6  7  NaN  NaN
7  8  NaN  NaN
8  9  NaN  NaN

I want to count number of rows in column B, my code looks like this:

df = pd.read_excel(excel_file_path, sheetname="test", index_col="1")
print(len(df))
print(df)

But regardless of what index_col i will put it always return the row number of longest column.

What should i change in the code to get the number o rows for column B but without empty rows?

See https://stackoverflow.com/questions/17468878/pandas-python-how-to-count-the-number-of-records-or-rows-in-a-dataframe — Zero, Oct 02 '17 at 15:35

score 0 · Answer 1 · answered Oct 02 '17 at 15:39

0

By using last_valid_index

df.apply(lambda x : x.last_valid_index()).add(1)
Out[440]: 
A    9
B    3
C    3
dtype: int64

answered Oct 02 '17 at 15:39

BENY

2

But `df.count()` is the best ;) – jezrael Oct 02 '17 at 15:40
@jezrael yes. you are right ~ :) – BENY Oct 02 '17 at 15:42
@Bharathshetty which answer? Sry... – BENY Oct 02 '17 at 15:52
@Bharathshetty nope, I have been use this for a well ... – BENY Oct 02 '17 at 15:57

1 Answers1