BUG/REG: file-handle object handled incorrectly in to_csv
State: closed

- [x] closes #https://github.com/pandas-dev/pandas/issues/21471
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

This error related to PR #21249 and https://github.com/pandas-dev/pandas/issues/21227. This is never supported use case and to use file-handle in to_csv with compression, the file-object itself should be a compression archive such as:
```
with gzip.open('test.txt.gz', 'wt') as f:
    pd.DataFrame([0,1],index=['a','b'], columns=['c']).to_csv(f, sep='\t')
```

Regressed to 0.22 to_csv with support for zipfile. zipfile doesn't support writing csv strings to a zip archive using a file-handle. So buffer is used to catch the writing and dump into zip archive in one go. The other scenarios remain unchanged.

Labels:
name: Regression
description: Functionality that used to work in a prior pandas version
name: IO CSV
description: read_csv, to_csv

Comments:
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21478?src=pr&el=h1) Report
> Merging [#21478](https://codecov.io/gh/pandas-dev/pandas/pull/21478?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/bf1c3dc253c08e70c543ae09c146b72544e85df5?src=pr&el=desc) will **increase** coverage by `0.03%`.
> The diff coverage is `95.65%`.

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

```diff
@@            Coverage Diff             @@
##           master   #21478      +/-   ##
==========================================
+ Coverage   91.89%   91.92%   +0.03%     
==========================================
  Files         153      153              
  Lines       49604    49599       -5     
==========================================
+ Hits        45584    45595      +11     
+ Misses       4020     4004      -16
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.32% <95.65%> (+0.03%)` | :arrow_up: |
| #single | `41.88% <43.47%> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/21478?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.23% <ø> (ø)` | :arrow_up: |
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `94.17% <ø> (ø)` | :arrow_up: |
| [pandas/io/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2NvbW1vbi5weQ==) | `70.28% <100%> (+0.24%)` | :arrow_up: |
| [pandas/io/formats/csvs.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvY3N2cy5weQ==) | `97.67% <95.23%> (-0.48%)` | :arrow_down: |
| [pandas/core/indexes/base.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXhlcy9iYXNlLnB5) | `96.62% <0%> (ø)` | :arrow_up: |
| [pandas/core/algorithms.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvYWxnb3JpdGhtcy5weQ==) | `94.83% <0%> (+0.01%)` | :arrow_up: |
| [pandas/io/json/json.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2pzb24vanNvbi5weQ==) | `92.47% <0%> (+0.23%)` | :arrow_up: |
| [pandas/util/testing.py](https://codecov.io/gh/pandas-dev/pandas/pull/21478/diff?src=pr&el=tree#diff-cGFuZGFzL3V0aWwvdGVzdGluZy5weQ==) | `85.96% <0%> (+1.35%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/21478?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/21478?src=pr&el=footer). Last update [bf1c3dc...cf3afac](https://codecov.io/gh/pandas-dev/pandas/pull/21478?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

I am not a proper reviewer, but I am suspicious about the whole "ZipFile magic" part.

It looks really ugly to me, and this code may potentially cause spike of 2x memory usage due to StringIO accumulation and "getvalue()" call.

I do not fully understand what was the problem with 0.22 implementation. Yes, "read_csv" and "to_csv" were inconsistent, but it contained less "magic", and users could always wrap anything they need with few lines of code.

Also, compression algos usually have additional parameters. Users might want to change defaults later on (e.g. compression level), and they'll need manual wrapping for that anyway.

Just thoughts.
agree that for extremely large zip output, memory usage will increase when writing csv to StringIO first and then call `getvalue()`. With 8G RAM MacOS, enough to write a [150m, 3] dataframe of 3.4g memory. the memory increase from getvalue call is only when zip compression, for other use cases, there should be no difference as 0.22. When path_or_buf is None, to_csv uses StringIO and call `getvalue()` already.
https://github.com/pandas-dev/pandas/blob/bf1c3dc253c08e70c543ae09c146b72544e85df5/pandas/core/frame.py#L1747

the custom class just provides a file-like that accepts string into zip archive when ZipFile class doesn't really offer that. 

The ability to write zip archive is added https://github.com/pandas-dev/pandas/issues/17778 where only read zip was supported, whereas we have round-trip ability for others. the motivation is consistency but also zip compression format is quite common and strange not to be supported.

the zip class is not only for to_csv but provides utility for to_json and to_pickle case too.
Hello @minggli! Thanks for updating the PR.















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

##### Comment last updated on June 16, 2018 at 13:40 Hours UTC
@jreback comments carried out. 
thanks @minggli nice followup patch!
@TomAugspurger this might be tricky to backport 
@jreback @gfyoung happy to help. thanks for reviewing.