CODEWITHSUNDEEP
pythonexcelpandasdataframe
JMP0629
JMP0629

Reputation: 95

Reading in Empty Cells with `read_excel` in Python pandas

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

Excel Snippet

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?

Upvotes: 1

Views: 9716

Answers (2)

Lijo Abraham
Lijo Abraham

Reputation: 881

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('')

Upvotes: 3

Scott Boston
Scott Boston

Reputation: 153460

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

33

Upvotes: 5

Related Questions