to_csv 'w' mode doesn't truncate for StringIO

[JacobHayes]

Code Sample, a copy-pastable example if possible

from io import StringIO
import pandas as pd
import tempfile
s = StringIO()
# TemporaryFile fails for other reasons, but seems to be covered in pandas-dev/pandas#21471
with tempfile.NamedTemporaryFile('w+') as f:
    for df in (pd.DataFrame({'a': [1,2]}), pd.DataFrame({'a': [3,4]})):
        # Keeping the default mode='w' for all cases
        df.to_csv(f, index=False, header=False, mode='w')
        df.to_csv(s, index=False, header=False, mode='w')
    f.seek(0)
    s.seek(0)
    print("File:\n{}".format(f.read()))
    print("StringIO:\n{}".format(s.read()))

Output:

File:
3
4

StringIO:
1
2
3
4

Problem description

Pandas to_csv doesn't properly handle truncating a StringIO object when writing with mode='w', which is inconsistent with it's own behavior for normal files and the stdlib meaning of w.

While it's probably more often the case to append when writing multiple files like this, I had tests that operated on StringIO objects and didn't explicitly set mode, so the append behavior passed the test while the actual behavior with files truncated.

Expected Output

File:
3
4

StringIO:
3
4

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.6.5.final.0
python-bits: 64
OS: Darwin
OS-release: 17.6.0
machine: x86_64
processor: i386
byteorder: little
LC_ALL: None
LANG: en_US.UTF-8
LOCALE: en_US.UTF-8

pandas: 0.23.1
pytest: 3.6.3
pip: 10.0.1
setuptools: 39.2.0
Cython: None
numpy: 1.14.5
scipy: None
pyarrow: None
xarray: None
IPython: None
sphinx: None
patsy: None
dateutil: 2.7.3
pytz: 2018.5
blosc: None
bottleneck: 1.2.1
tables: None
numexpr: 2.6.5
feather: None
matplotlib: None
openpyxl: None
xlrd: None
xlwt: None
xlsxwriter: None
lxml: None
bs4: None
html5lib: 1.0.1
sqlalchemy: 1.1.18
pymysql: None
psycopg2: 2.7.5 (dt dec pq3 ext lo64)
jinja2: 2.8.1
s3fs: None
fastparquet: None
pandas_gbq: 0.5.0
pandas_datareader: None

[gfyoung]

https://github.com/pandas-dev/pandas/blob/master/pandas/io/formats/csvs.py#L144-L157

@JacobHayes : So mode actually applies if we are creating the IO stream ourselves. If the IO stream is provided, the mode parameter is in fact ignored. At the very least, we should clarify this in the docs.

I'm hesitant to actually "patch" this because StringIO is a specific (corner) case and this would not work for example on a file handler that was open in a different mode. Perhaps others may disagree (@jreback @jorisvandenbossche thoughts?), but that is my current opinion on this. How does that sound?

[JacobHayes]

@gfyoung The IO stream isn't being created in pandas in either case, both StringIO and NamedTemporaryFile return opened IO streams that have the write methods.

>>> from io import StringIO
>>> import tempfile
>>>
>>> s = StringIO()
>>> f = open("f", "w+")
>>> tf = tempfile.NamedTemporaryFile("w+")
>>> s
<_io.StringIO object at 0x10795f048>
>>> f
<_io.TextIOWrapper name='f' mode='w+' encoding='UTF-8'>
>>> tf
<tempfile._TemporaryFileWrapper object at 0x107e39358>
>>> hasattr(s, "write")
True
>>> hasattr(f, "write")
True
>>> hasattr(tf, "write")
True

I think the actual bug was that the file was being incorrectly truncated (meaning the StringIO had the desired behavior). This was tangentially fixed in v0.23.2 (#21478 I think), so I'll go ahead and close this issue.