Reading in Empty Cells with `read_excel` in Python pandas

Question

I am using Python pandas to read_excel. This is the column I am reading in.

My problem is that read_excel isn't counting the empty cells as cells. When I use df2=df1.iloc[0:30], I want it to include those empty cells so those last two data items are not included in my dataframe (this is because these cells are populated daily throughout the month, so those empty cells will exist until the very last day of the month). How do I ensure pandas read_excel includes those empty cells in its dataframe?

try adding parameter skip_blank_lines=False in your read function — Uvar
– Uvar, Commented Nov 30, 2017 at 15:42
This worked! Is there additional documentation other than pandas.pydata.org/pandas-docs/stable/generated/…? I don't see that in the list of parameters. — JMP0629
– JMP0629, Commented Nov 30, 2017 at 15:51
It is undocumented as far as I know, but a hidden gem you get to know after dwelling on the internet for a while. :$ — Uvar
– Uvar, Commented Nov 30, 2017 at 15:54
In pandas 1.1.4: read_excel() got an unexpected keyword argument 'skip_blank_lines' — craq
– craq, Commented Feb 4, 2021 at 22:08

Scott Boston · Accepted Answer · 2020-11-19 19:35:44Z

5

df = pd.read_excel('book1.xlsx',header=None, skip_blank_lines=False)

       0
0     17
1      0
2      0
3      0
4      0
5      T
6   0.13
7   0.33
8   0.02
9   0.04
10     T
11     0
12     0
13  0.57
14     0
15     0
16     T
17     0
18     0
19  0.07
20     0
21     0
22  0.11
23     0
24     0
25   NaN
26   NaN
27   NaN
28   NaN
29   NaN
30   NaN
31  1.27
32     7

#Note: Count doesn't count NaN values.

df.count()

returns

0    27
dtype: int64

and

df.size

returns

edited Nov 19, 2020 at 19:35

answered Nov 30, 2017 at 15:49

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pyd Over a year ago

what about len(df) @Scott

Scott Boston Over a year ago

@pyd len(df) returns 33 it includes NaN as well as df.shape returns (33,1).

craq Over a year ago

In pandas 1.1.4: read_excel() got an unexpected keyword argument 'skip_blank_lines'

Lijo Abraham · Accepted Answer · 2021-02-17 08:08:43Z

3

skip_blank_lines parameter is not valid in newer pandas version. Use code like be get the exact df as in the excel.

df = pd.read_excel('book1.xlsx',dtype="str").fillna('')

answered Feb 17, 2021 at 8:08

Lijo Abraham

8819 silver badges32 bronze badges

Collectives™ on Stack Overflow

Reading in Empty Cells with `read_excel` in Python pandas

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related