Python Pandas read_excel doesn't recognize null cell

Question

My excel sheet:

   A   B  
1 first second
2
3 
4  x   y  
5  z   j

Python code:

df = pd.read_excel (filename, parse_cols=1)

return a correct output:

  first second
0 NaN   NaN
1 NaN   NaN
2 x     y
3 z     j

If i want work only with second column

df = pd.read_excel (filename, parse_cols=[1])

return:

 second
0  y
1  j

I'd have information about empty excel rows (NaN in my df) even if I work only with a specific column. If output loose NaN information it's not ok, for example, for skiprows paramater, etc

Thanks

It works. Why I cannot see this parameter here: pandas.pydata.org/pandas-docs/stable/generated/… — franco_b
– franco_b, Commented Sep 5, 2016 at 16:31

jezrael · Accepted Answer · 2016-09-05 16:38:33Z

9

For me works parameter skip_blank_lines=False:

df = pd.read_excel ('test.xlsx', 
                     parse_cols=1, 
                     skip_blank_lines=False)
print (df)

       A       B
0  first  second
1    NaN     NaN
2    NaN     NaN
3      x       y
4      z       j

Or if need omit first row:

df = pd.read_excel ('test.xlsx', 
                     parse_cols=1, 
                     skiprows=1,
                     skip_blank_lines=False)
print (df)

  first second
0   NaN    NaN
1   NaN    NaN
2     x      y
3     z      j

edited Sep 5, 2016 at 16:38

answered Sep 5, 2016 at 16:33

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

franco_b Over a year ago

Yes it works. But why I don't see this parameter in the official documentation? pandas.pydata.org/pandas-docs/stable/generated/…

jezrael Over a year ago

See General Parsing Configuration in docs, or this parameter is in read_csv.

jezrael Over a year ago

In official docs - read_excel it is behind **kwds ;(

jezrael Over a year ago

I try omit it df = pd.read_excel ('test.xlsx', parse_cols=1, skiprows=1) and with my test data it works too - I use pandas 0.18.1

My Work Over a year ago

In pandas 1.1.4: TypeError: read_excel() got an unexpected keyword argument 'skip_blank_lines' and no mention in the documentation posted above.

Collectives™ on Stack Overflow

Python Pandas read_excel doesn't recognize null cell

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related