read_excel with dtype=str converts empty cells to np.nan
State: closed

Checklist for other PRs (remove this part if you are doing a PR for the pandas documentation sprint):

- [x] closes #20377
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Dtype Conversions
description: Unexpected or buggy dtype conversions
name: IO Excel
description: read_excel, to_excel

Comments:
No idea what's going on with this conflict. Any help?
Try rebasing and then resolving the conflict?
> @gfyoung do we handle this correctly in read_csv itself?

@jreback : unfortunately, no.  Too bad I didn't see this PR earlier.  I would have actually suggested to fix it in `read_csv` and then check if the bug still persists in `read_excel` (since they share parsing engines).
`read_csv` only works correctly (empty cells turn to `np.nan`) if it uses its default engine `CParserWrapper`.

In `read_excel` empty cells turn to the string `'nan'`, because it uses `PythonParser`. This parser has a function called `self._cast_types`, which uses `astype_nansafe`, which uses `astype_unicode` and `astype_str` (https://github.com/pandas-dev/pandas/blob/master/pandas/_libs/lib.pyx#L460).

The later functions are only used by `PythonParser`, so I think that we should fix them by using `checknull` as `astype_intsafe` does (https://github.com/pandas-dev/pandas/blob/master/pandas/_libs/lib.pyx#L437).
this is my point - the fix needs to be in read_csv not here
I think it's not a matter of `read_csv` or `read_excel`.
The fix needs to be in `PythonParser` which can be used in both `read_csv` and `read_excel`.
@arnau126 and @gfyoung have hit the nail on the head. The issue is at least in `PythonParser`, if not deeper.  Using the python parser with `read_csv` produces the same result as @jreback has pointed out. Question is: which is the expected behavior? `np.nan` or 'nan'?
I would go with empty string as per the issue.
In the ticket I mentioned this https://github.com/pandas-dev/pandas/issues/20377#issuecomment-374693647.
@cbertinato gave an answer there but, since we're discussing it, I'd like some more opinions.
> I would go with empty string as per the issue.

Should the C parser also be brought in line?

@nikoskaragiannakis: @gfyoung suggests an empty string. That sounds like a good idea as it is consistent with the originating DataFrame.
> Should the C parser also be brought in line?

Absolutely!  Both parsers should be patched.
Any comments for the new changes?
I'm good with this PR because after using `read_csv` I always do `.fillna('')` for all `str` columns.
However, other operations like `.fillna('-')` or `.dropna()` won't work anymore because an empty string `''` is not `np.nan`.
I think that this PR is a big change in the `read_csv` behaviour.
so this needs a test for csv reading. The intent here is NOT to use '' as the missing value, simply to remove the case where an existing ``np.nan`` is converted to the string ``nan``. You would need a test to prove this as its not obvious from the implementation (which actually looks quite strange).
> However, other operations like .fillna('-') or .dropna() won't work anymore because an empty string '' is not np.nan.

I think what @arnau126 says here makes total sense.

> The intent here is NOT to use '' as the missing value, simply to remove the case where an existing np.nan is converted to the string nan.

@jreback what you're saying here agrees with the comment above, but that contradicts something @cbertinato said some days ago:
> I would go with empty string as per the issue.

Sorry if I misunderstood something, but I want to be sure there is a clear decision here.


Sorry to have added confusion. I misinterpreted an earlier comment. I believe @jreback’s suggested changes are the best way to go. 
So, what needs to be done here is to make sure that an existing `np.nan` is **not** converted to a string `'nan'`, right? Does this mean that it should remain as a `np.nan`?
Hello @nikoskaragiannakis! Thanks for updating the PR.











Cheers ! There are no PEP8 issues in this Pull Request. :beers: 

##### Comment last updated on April 08, 2018 at 11:56 Hours UTC
I made the changes so now both `read_csv` and `read_excel`
* turn empty values to `np.nan` when `dtype=str` and `na_filter=True`
* turn empty values to empty string when `dtype=str` and `na_filter=False`

I also ran the performance tests for csv, excel, and series:
```
io.csv
======
[  0.00%] · For pandas commit hash 7d5f6b20:
[  0.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[  3.33%] ··· Running io.csv.ReadCSVCategorical.time_convert_direct                                                                                                                                      44.4±0.2ms
[  6.67%] ··· Running io.csv.ReadCSVCategorical.time_convert_post                                                                                                                                        70.3±0.1ms
[ 10.00%] ··· Running io.csv.ReadCSVComment.time_comment                                                                                                                                                2.01±0.01ms
[ 13.33%] ··· Running io.csv.ReadCSVDInferDatetimeFormat.time_read_csv                                                                                                                              1.15±0.01ms;...
[ 16.67%] ··· Running io.csv.ReadCSVFloatPrecision.time_read_csv                                                                                                                                    1.64±0.01ms;...
[ 20.00%] ··· Running io.csv.ReadCSVFloatPrecision.time_read_csv_python_engine                                                                                                                      1.81±0.01ms;...
[ 23.33%] ··· Running io.csv.ReadCSVParseDates.time_baseline                                                                                                                                               3.15±0ms
[ 26.67%] ··· Running io.csv.ReadCSVParseDates.time_multiple_date                                                                                                                                          3.10±0ms
[ 30.00%] ··· Running io.csv.ReadCSVSkipRows.time_skipprows                                                                                                                                         21.5±0.04ms;...
[ 33.33%] ··· Running io.csv.ReadCSVThousands.time_thousands                                                                                                                                        18.0±0.02ms;...
[ 36.67%] ··· Running io.csv.ReadUint64Integers.time_read_uint64                                                                                                                                        1.13±0.01ms
[ 40.00%] ··· Running io.csv.ReadUint64Integers.time_read_uint64_na_values                                                                                                                              1.22±0.01ms
[ 43.33%] ··· Running io.csv.ReadUint64Integers.time_read_uint64_neg_values                                                                                                                                1.15±0ms
[ 46.67%] ··· Running io.csv.ToCSV.time_frame                                                                                                                                                         156±0.7ms;...
[ 50.00%] ··· Running io.csv.ToCSVDatetime.time_frame_date_formatting                                                                                                                                   11.6±0.05ms
[ 50.00%] · For pandas commit hash bd8a3cff:
[ 50.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt..............................................................................
[ 50.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 53.33%] ··· Running io.csv.ReadCSVCategorical.time_convert_direct                                                                                                                                      43.0±0.2ms
[ 56.67%] ··· Running io.csv.ReadCSVCategorical.time_convert_post                                                                                                                                        66.9±0.2ms
[ 60.00%] ··· Running io.csv.ReadCSVComment.time_comment                                                                                                                                                2.04±0.02ms
[ 63.33%] ··· Running io.csv.ReadCSVDInferDatetimeFormat.time_read_csv                                                                                                                              1.17±0.01ms;...
[ 66.67%] ··· Running io.csv.ReadCSVFloatPrecision.time_read_csv                                                                                                                                    1.69±0.01ms;...
[ 70.00%] ··· Running io.csv.ReadCSVFloatPrecision.time_read_csv_python_engine                                                                                                                      1.84±0.01ms;...
[ 73.33%] ··· Running io.csv.ReadCSVParseDates.time_baseline                                                                                                                                            3.07±0.02ms
[ 76.67%] ··· Running io.csv.ReadCSVParseDates.time_multiple_date                                                                                                                                       3.10±0.02ms
[ 80.00%] ··· Running io.csv.ReadCSVSkipRows.time_skipprows                                                                                                                                         21.4±0.06ms;...
[ 83.33%] ··· Running io.csv.ReadCSVThousands.time_thousands                                                                                                                                        17.9±0.03ms;...
[ 86.67%] ··· Running io.csv.ReadUint64Integers.time_read_uint64                                                                                                                                        1.13±0.07ms
[ 90.00%] ··· Running io.csv.ReadUint64Integers.time_read_uint64_na_values                                                                                                                              1.25±0.02ms
[ 93.33%] ··· Running io.csv.ReadUint64Integers.time_read_uint64_neg_values                                                                                                                             1.17±0.01ms
[ 96.67%] ··· Running io.csv.ToCSV.time_frame                                                                                                                                                         156±0.3ms;...
[100.00%] ··· Running io.csv.ToCSVDatetime.time_frame_date_formatting                                                                                                                                   11.4±0.03ms
BENCHMARKS NOT SIGNIFICANTLY CHANGED.



io.excel
========
[  0.00%] · For pandas commit hash 7d5f6b20:
[  0.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 25.00%] ··· Running io.excel.Excel.time_read_excel                                                                                                                                                  154±0.2ms;...
[ 50.00%] ··· Running io.excel.Excel.time_write_excel                                                                                                                                                     641ms;...
[ 50.00%] · For pandas commit hash bd8a3cff:
[ 50.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 50.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 75.00%] ··· Running io.excel.Excel.time_read_excel                                                                                                                                                  154±0.1ms;...
[100.00%] ··· Running io.excel.Excel.time_write_excel                                                                                                                                                     641ms;...
BENCHMARKS NOT SIGNIFICANTLY CHANGED.



series_methods
==============
[  0.00%] · For pandas commit hash 7d5f6b20:
[  0.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[  0.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[  5.56%] ··· Running series_methods.Clip.time_clip                                                                                                                                                       125±0.5μs
[ 11.11%] ··· Running series_methods.Dir.time_dir_strings                                                                                                                                               1.74±0.01ms
[ 16.67%] ··· Running series_methods.Dropna.time_dropna                                                                                                                                                882±20μs;...
[ 22.22%] ··· Running series_methods.IsIn.time_isin                                                                                                                                                 1.63±0.02ms;...
[ 27.78%] ··· Running series_methods.Map.time_map                                                                                                                                                       948±6μs;...
[ 33.33%] ··· Running series_methods.NSort.time_nlargest                                                                                                                                             2.78±0.1ms;...
[ 38.89%] ··· Running series_methods.NSort.time_nsmallest                                                                                                                                            1.96±0.3ms;...
[ 44.44%] ··· Running series_methods.SeriesConstructor.time_constructor                                                                                                                                 314±1ms;...
[ 50.00%] ··· Running series_methods.ValueCounts.time_value_counts                                                                                                                                  2.14±0.03ms;...
[ 50.00%] · For pandas commit hash bd8a3cff:
[ 50.00%] ·· Building for conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt...
[ 50.00%] ·· Benchmarking conda-py3.6-Cython-matplotlib-numexpr-numpy-openpyxl-pytables-pytest-scipy-sqlalchemy-xlrd-xlsxwriter-xlwt
[ 55.56%] ··· Running series_methods.Clip.time_clip                                                                                                                                                      121±0.05μs
[ 61.11%] ··· Running series_methods.Dir.time_dir_strings                                                                                                                                               1.75±0.01ms
[ 66.67%] ··· Running series_methods.Dropna.time_dropna                                                                                                                                                874±10μs;...
[ 72.22%] ··· Running series_methods.IsIn.time_isin                                                                                                                                                 1.63±0.01ms;...
[ 77.78%] ··· Running series_methods.Map.time_map                                                                                                                                                       950±8μs;...
[ 83.33%] ··· Running series_methods.NSort.time_nlargest                                                                                                                                             2.80±0.1ms;...
[ 88.89%] ··· Running series_methods.NSort.time_nsmallest                                                                                                                                            1.96±0.3ms;...
[ 94.44%] ··· Running series_methods.SeriesConstructor.time_constructor                                                                                                                                 320±4ms;...
[100.00%] ··· Running series_methods.ValueCounts.time_value_counts                                                                                                                                  2.08±0.08ms;...
BENCHMARKS NOT SIGNIFICANTLY CHANGED.
```
Looks like performance is not significantly affected.

I look forward for your feedback.
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=h1) Report
> Merging [#20429](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/73cb32e71469cf1615907544728bb9a751089de4?src=pr&el=desc) will **increase** coverage by `0.02%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/20429/graphs/tree.svg?height=150&width=650&token=eZ4WkYLtcO&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #20429      +/-   ##
==========================================
+ Coverage   91.82%   91.84%   +0.02%     
==========================================
  Files         153      153              
  Lines       49256    49257       +1     
==========================================
+ Hits        45229    45242      +13     
+ Misses       4027     4015      -12
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.23% <100%> (+0.02%)` | :arrow_up: |
| #single | `41.91% <100%> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/20429/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `93.9% <100%> (ø)` | :arrow_up: |
| [pandas/plotting/\_converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/20429/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19jb252ZXJ0ZXIucHk=) | `66.81% <0%> (+1.73%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=footer). Last update [73cb32e...7341cd1](https://codecov.io/gh/pandas-dev/pandas/pull/20429?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

Fixed a merge conflict.

Any concerns with this @gfyoung?
don’t merge tom - need to look
Back to you @jreback 
closing as stale, if you want to continue working, pls ping and we can re-open. you will need to merge master.