read_excel with dtype=str converts empty cells to the string 'nan'

[arnau126]

Code Sample, a copy-pastable example if possible

In [10]: df = pd.DataFrame({'a': ['x', 'y', '', 'z']})

In [11]: df
Out[11]: 
   a
0  x
1  y
2   
3  z

In [12]: df.to_excel('temp.xlsx')

In [13]: df = pd.read_excel('temp.xlsx', dtype=str)

In [14]: df
Out[14]: 
     a
0    x
1    y
2  nan
3    z

In [15]: df.loc[2, 'a']
Out[15]: 'nan'

In [16]: type(df.loc[2, 'a'])
Out[16]: str

Problem description

The empty string of the original dataframe becomes the string 'nan', instead of numpy.nan.

Expected Output

In [14]: df
Out[14]: 
     a
0    x
1    y
2  NaN
3    z

In [15]: df.loc[2, 'a']
Out[15]: nan

In [16]: type(df.loc[2, 'a'])
Out[16]: float

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.5.2.final.0
python-bits: 64
OS: Linux
OS-release: 4.13.0-36-generic
machine: x86_64
processor: x86_64
byteorder: little
LC_ALL: None
LANG: ca_ES.UTF-8
LOCALE: ca_ES.UTF-8

pandas: 0.22.0
pytest: None
pip: 9.0.1
setuptools: 38.5.2
Cython: 0.27.3
numpy: 1.14.1
scipy: 1.0.0
pyarrow: None
xarray: None
IPython: 6.2.1
sphinx: None
patsy: 0.5.0
dateutil: 2.7.0
pytz: 2018.3
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 2.2.0
openpyxl: 2.5.1
xlrd: 1.1.0
xlwt: None
xlsxwriter: 0.7.3
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.2.5
pymysql: 0.8.0
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: None
pandas_gbq: None
pandas_datareader: None

[TomAugspurger]

Thanks for the report. Mind taking a look to see what's going on?

[arnau126]

I've found the function that converts the np.nan to 'nan': pandas.core.dtypes.astype_nansafe.

read_excel uses PythonParser. This parser has a function called self._cast_types, which uses astype_nansafe.

https://github.com/pandas-dev/pandas/blob/master/pandas/io/parsers.py#L1570

An example of astype_nansafe:

In [4]: from pandas.core.dtypes.cast import astype_nansafe

In [5]: arr = np.array([np.nan, 'a', 'b'], object)

In [6]: arr
Out[6]: array([nan, 'a', 'b'], dtype=object)

In [7]: astype_nansafe(arr, str)
Out[7]: array(['nan', 'a', 'b'], dtype=object)

I don't know if it's the expected behavior or not.
Should we modify astype_nansafe? or _cast_types?.

I'm not sure how to fix the issue.

[cbertinato]

Is that what one should expect when using dtype=str? Not specifying dtype produces the expected result. The default value is None and dtype conversion is done by row.

In [16]: df = pd.read_excel('temp.xlsx')

In [17]: df
Out[17]:
     a
0    x
1    y
2  NaN
3    z

In [18]: df.loc[2, 'a']
Out[18]: nan

In [19]: type(df.loc[2, 'a'])
Out[19]: float

In [20]: type(df.loc[1, 'a'])
Out[20]: str

I think that the output for dtype=str is expected behavior.

[nikoskaragiannakis]

read_csv returns what you would expect

from io import StringIO
import pandas as pd

arr = pd.read_csv(StringIO('col1, col2, col3\n,a,b'), dtype=str)

it will give you

  col1  col2  col3
0  NaN     a     b

so, I guess, what you expect from read_excel seems to be consistent with this.

I guess, if this needs to be changed, the best would be to change astype_unicode in pandas/_libs/lib.pyx.
What do you think?

[nikoskaragiannakis]

@cbertinato just saw your comment! Good point.

[arnau126]

@cbertinato, if I have a column with numbers, but I want to read them as strings I need to specify dtype=str, otherwise they will be read as float.

In [1]: df = pd.DataFrame({'a': ['1', '2', '', '3']})

In [2]: df.to_excel('temp.xlsx')

In [3]: pd.read_excel('temp.xlsx', dtype=str)
Out[3]: 
     a
0   1
1   2
2  nan
3   3

In [5]: pd.read_excel('temp.xlsx')
Out[5]: 
     a
0  1.0
1  2.0
2  NaN
3  3.0

Moreover, as @nikoskaragiannakis says, it should be consistent with read_csv.

[cbertinato]

I missed the use case for numbers, and @nikoskaragiannakis's comment. It should probably be consistent with read_csv.

[nikoskaragiannakis]

@cbertinato if the case is as @arnau126 says, then I can make a fix for this.

[nikoskaragiannakis]

Question: When we use dtype=str do we want np.nan to turn to a string "nan" or to stay as it is?
I made a fix for this issue, but the above functionality is affected.

[arnau126]

The purpose of this issue is just change this functionality: currently np.nan turns to a string "nan", and I think that it should stay as it.