ENH: Add 'infer' option to compression in _get_handle()
xref #15008 
xref #17262 

Added 'infer' option to compression in `_get_handle()`.
This way for example `.to_csv()` is made more similar to `pandas.read_csv()`.
The default value remains `None `to keep backward compatibility.

- [ ] closes #xxxx
- [ ] tests passed ( 14046 passed, 1637 skipped, 11 xfailed, 1 xpassed, 7 warnings)
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

my first pull request. if there is something wrong, i'd be happy to change that.
I am not sure about the xfailed entries, I ran `pytest --skip-slow --skip-network pandas`
is there a related issue? This is related https://github.com/pandas-dev/pandas/issues/15008, which is a much larger project. @Dobatymo The general idea looks good to me, but can you add some new test to confirm the behaviour? (for those read/write functions that now gained the ability to infer the compression type) # [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=h1) Report
> Merging [#17900](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/5bf7f9a4ff6968fd444fa7098f8dc95586591994?src=pr&el=desc) will **decrease** coverage by `0.02%`.
> The diff coverage is `100%`.

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

```diff
@@            Coverage Diff             @@
##           master   #17900      +/-   ##
==========================================
- Coverage   91.23%   91.21%   -0.03%     
==========================================
  Files         163      163              
  Lines       50105    50105              
==========================================
- Hits        45715    45704      -11     
- Misses       4390     4401      +11
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.02% <100%> (-0.01%)` | :arrow_down: |
| #single | `40.31% <0%> (-0.06%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/pickle.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=tree#diff-cGFuZGFzL2lvL3BpY2tsZS5weQ==) | `80.43% <ø> (-0.82%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.75% <ø> (-0.1%)` | :arrow_down: |
| [pandas/io/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=tree#diff-cGFuZGFzL2lvL2NvbW1vbi5weQ==) | `68.9% <100%> (-0.59%)` | :arrow_down: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/17900?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/17900?src=pr&el=footer). Last update [5bf7f9a...6c0873f](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
 # [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=h1) Report
> Merging [#17900](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/5cb5880d8f1647e303ba52487dc867db838fd6e0?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `100%`.

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

```diff
@@            Coverage Diff             @@
##           master   #17900      +/-   ##
==========================================
- Coverage   91.95%   91.94%   -0.01%     
==========================================
  Files         160      160              
  Lines       49858    49860       +2     
==========================================
  Hits        45845    45845              
- Misses       4013     4015       +2
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.32% <100%> (-0.01%)` | :arrow_down: |
| #single | `42.08% <50%> (ø)` | :arrow_up: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/generic.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZ2VuZXJpYy5weQ==) | `96.45% <ø> (ø)` | :arrow_up: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.19% <ø> (ø)` | :arrow_up: |
| [pandas/io/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/17900/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2NvbW1vbi5weQ==) | `70.65% <100%> (-0.55%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/17900?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/17900?src=pr&el=footer). Last update [5cb5880...651e43f](https://codecov.io/gh/pandas-dev/pandas/pull/17900?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).
 I didn't find any tests for compression in `to_csv()` so I added a simple test for that and 'infer'.
 can you rebase and update Sorry for the late reply.

I rebased, moved the imports and added a reference to https://github.com/pandas-dev/pandas/issues/15008

I checked the tests for `read_csv` compression in pandas/tests/io/parser/compression.py, but and I don't see any parameterization...

EDIT: Ok, I parameterized the tests. But I kept the lzma test separate as I didn't know how to do the skipping stuff with the parameterization included. This is related as well https://github.com/pandas-dev/pandas/issues/17262 can you rebase can you rebase rebased, squashed commits and fixed lzma compat issues which arose from rebase can you rebase once again, having some CI issues @Dobatymo can you rebase @Dobatymo tip: if you rebase and push, we are not notified of that. So if you do that, it is best to put a short comment that you did it (ideally after the CI passed), so we can merge if possible, and don't forget it until it needs to be rebased again .. 
(or, you can also merge master instead of rebasing, and then we actually get notified)

@jreback I fixed the merge conflicts. Did you have any further comments?

 let me have a look @Dobatymo can you rebase hmm, isn't infer de-facto equivalent to None? (e.g. we always infer)? why do we need an extra option here? > hmm, isn't infer de-facto equivalent to None? (e.g. we always infer)? why do we need an extra option here?

@jreback :

* `None` means the file isn't compressed
* `infer` means there's compression, but we're asking `pandas` to figure it out thanks @Dobatymo and @gfyoung for the fixup! Nice inferring compression when writing dataframes will be really useful.

> The default value remains `None` to keep backward compatibility.

Unfortunately, much of the convenience of `compression='infer'` is lost if you have to explicitly specify it. Defaulting to infer would only affect users who are currently using paths with compression extensions but not actually compressing. That's pretty bad practice IMO. Hence, I'm in favor of breaking backwards compatibility and changing the default for compression to infer. It looks like this is going into a major release 0.24?

**Update:** I've opened an issue at https://github.com/pandas-dev/pandas/issues/22004