Repository: pandas-dev/pandas
Pull request: ENH: Infer inner file name of zip archive (GH39465)
relevant for `DataFrame.to_csv` and `Series.to_csv` with `compression='zip'`

- [x] closes #39465
  - also relevant for #26023
- [x] tests added / passed
- [x] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit) for how to run them
- [x] whatsnew entry

This fix is similar-in-spirit to #40387, which has been abandoned.


## Before / After
```py
from pandas import pd

df = pd.DataFrame()
df.to_csv('../test.csv.zip')
```

### Before
```
> unzip -l test.csv.zip

Archive:  test.csv.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
        3  2021-11-14 13:39   ../test.csv.zip
---------                     -------
        3                     1 file
```
Notice the `..` in the path - bad! And, of course, that the file _inside_ the zip file is also called `test.csv.zip`.

### After
```
> unzip -l test.csv.zip

Archive:  test.csv.zip
  Length      Date    Time    Name
---------  ---------- -----   ----
        3  2021-11-14 13:44   test.csv
---------                     -------
        3                     1 file
```

Problem statement:
BUG: DataFrame to_csv compression with 'zip' use zipfilename as archive name
- [x] I have checked that this issue has not already been reported.

- [x] I have confirmed this bug exists on the latest version of pandas.

- [x] (optional) I have confirmed this bug exists on the master branch of pandas.

---


```python
a=pd.DataFrame({'a':[1,2,3,4,5],'b':[4,5,6,7,8]})
a.to_csv('myfile.csv.zip')
```

#### Problem description

when use gzip method, the function works fine, a.to_csv('myfile.csv.gz') will create a gzip file with an archive file named myfile.csv.  
the "zip" method works bad,a.to_csv('myfile.csv.zip') will create a zipfile with an archive file named myfile.csv.zip

Hints from issue discussion:
> the "zip" method works bad,a.to_csv('myfile.csv.zip') will create a zipfile with an archive file named myfile.csv.zip 

Do you want to create a PR to remove the ".zip" suffix? If you need a workaround:
`pd.to_csv("test.csv.zip", compression={"method": "zip", "archive_name": "test.csv"})`

> and an strange case shows that a large dataframe will report dumplicate archive name

are you using 1.2.1? #38714


> > the "zip" method works bad,a.to_csv('myfile.csv.zip') will create a zipfile with an archive file named myfile.csv.zip
> 
> Do you want to create a PR to remove the ".zip" suffix? If you need a workaround:
> `pd.to_csv("test.csv.zip", compression={"method": "zip", "archive_name": "test.csv"})`
> 
> > and an strange case shows that a large dataframe will report dumplicate archive name
> 
> are you using 1.2.1? #38714

@twoertwein 

now i'm using 1.2.1,i add a pr(#39647 ) to do some work with the name problem,but not the large dataframe problem 
do you still have the issue with the large dataframe when using 1.2.1?
Is this different than #26023?
> Is this different than #26023?

same purpose, but my PR#40387 will take "myfile" as archive_name when "myfile.zip" used as zipfile name,just like gzip or xz. It won't add ".csv" to the end of "myfile" automatically.
"myfile.csv.zip"->"myfile.csv"
"myfile.zip"->"myfile"
"myfile.csv.csv.zip"->"myfile.csv.csv"

Commit message: ENH: Infer inner file name of zip archive (GH39465)

relevant for `DataFrame.to_csv` and `Series.to_csv` with `compression='zip'`

Patch to review:
diff --git a/doc/source/whatsnew/v1.4.0.rst b/doc/source/whatsnew/v1.4.0.rst
index a593a03de5c25..dcdd36d4ef8ff 100644
--- a/doc/source/whatsnew/v1.4.0.rst
+++ b/doc/source/whatsnew/v1.4.0.rst
@@ -210,6 +210,7 @@ Other enhancements
 - :meth:`read_excel` now accepts a ``decimal`` argument that allow the user to specify the decimal point when parsing string columns to numeric (:issue:`14403`)
 - :meth:`.GroupBy.mean` now supports `Numba <http://numba.pydata.org/>`_ execution with the ``engine`` keyword (:issue:`43731`)
 - :meth:`Timestamp.isoformat`, now handles the ``timespec`` argument from the base :class:``datetime`` class (:issue:`26131`)
+- :meth:`DataFrame.to_csv` and :meth:`Series.to_csv` with ``compression`` set to ``'zip'`` no longer create a zip file that contains another zip file. Instead, they try to infer the inner file name more smartly. (:issue:`39465`)
 
 .. ---------------------------------------------------------------------------
 
diff --git a/pandas/io/common.py b/pandas/io/common.py
index 12c7afc8ee2e4..baf81f84668df 100644
--- a/pandas/io/common.py
+++ b/pandas/io/common.py
@@ -805,6 +805,19 @@ def __init__(
         # _PathLike[str]], IO[bytes]]"
         super().__init__(file, mode, **kwargs_zip)  # type: ignore[arg-type]
 
+    # GH39465
+    # If an explicit archive_name is not given, we still want the file _inside_ the zip
+    # file _not_ to be named something.zip, because that causes massive confusion.
+    def infer_filename(self):
+        if isinstance(self.filename, (os.PathLike, str)):
+            filename = Path(self.filename)
+            if filename.suffix == ".zip":
+                return filename.with_suffix("").name
+            else:
+                return filename.name
+        else:
+            return None
+
     def write(self, data):
         # buffer multiple write calls, write on flush
         if self.multiple_write_buffer is None:
@@ -819,7 +832,7 @@ def flush(self) -> None:
             return
 
         # ZipFile needs a non-empty string
-        archive_name = self.archive_name or self.filename or "zip"
+        archive_name = self.archive_name or self.infer_filename() or "zip"
         with self.multiple_write_buffer:
             super().writestr(archive_name, self.multiple_write_buffer.getvalue())
 
diff --git a/pandas/tests/io/formats/test_to_csv.py b/pandas/tests/io/formats/test_to_csv.py
index 4c482bafa6c9c..119b8e1f7ccb2 100644
--- a/pandas/tests/io/formats/test_to_csv.py
+++ b/pandas/tests/io/formats/test_to_csv.py
@@ -1,6 +1,8 @@
 import io
 import os
+from pathlib import Path
 import sys
+from zipfile import ZipFile
 
 import numpy as np
 import pytest
@@ -541,23 +543,29 @@ def test_to_csv_compression_dict_no_method_raises(self):
                 df.to_csv(path, compression=compression)
 
     @pytest.mark.parametrize("compression", ["zip", "infer"])
-    @pytest.mark.parametrize(
-        "archive_name", [None, "test_to_csv.csv", "test_to_csv.zip"]
-    )
+    @pytest.mark.parametrize("archive_name", ["test_to_csv.csv", "test_to_csv.zip"])
     def test_to_csv_zip_arguments(self, compression, archive_name):
         # GH 26023
-        from zipfile import ZipFile
-
         df = DataFrame({"ABC": [1]})
         with tm.ensure_clean("to_csv_archive_name.zip") as path:
             df.to_csv(
                 path, compression={"method": compression, "archive_name": archive_name}
             )
             with ZipFile(path) as zp:
-                expected_arcname = path if archive_name is None else archive_name
-                expected_arcname = os.path.basename(expected_arcname)
                 assert len(zp.filelist) == 1
-                archived_file = os.path.basename(zp.filelist[0].filename)
+                archived_file = zp.filelist[0].filename
+                assert archived_file == archive_name
+
+    @pytest.mark.parametrize("compression", ["zip", "infer"])
+    def test_to_csv_zip_infer_name(self, compression):
+        # GH 39465
+        df = DataFrame({"ABC": [1]})
+        with tm.ensure_clean("archive.csv.zip") as path:
+            df.to_csv(path, compression=compression)
+            expected_arcname = Path(path).with_suffix("").name
+            with ZipFile(path) as zp:
+                assert len(zp.filelist) == 1
+                archived_file = zp.filelist[0].filename
                 assert archived_file == expected_arcname
 
     @pytest.mark.parametrize("df_new_type", ["Int64"])


Code review comments:
diff_hunk: @@ -805,6 +805,19 @@ def __init__(
         # _PathLike[str]], IO[bytes]]"
         super().__init__(file, mode, **kwargs_zip)  # type: ignore[arg-type]
 
+    # GH39465
path: pandas/io/common.py
text: @user1:
comments would go in a doc-string not above the function

@author:
I don't know the Python code style super-well, hope what I just pushed fits your needs :)

diff_hunk: @@ -805,6 +805,19 @@ def __init__(
         # _PathLike[str]], IO[bytes]]"
         super().__init__(file, mode, **kwargs_zip)  # type: ignore[arg-type]
 
+    # GH39465
+    # If an explicit archive_name is not given, we still want the file _inside_ the zip
+    # file _not_ to be named something.zip, because that causes massive confusion.
+    def infer_filename(self):
+        if isinstance(self.filename, (os.PathLike, str)):
path: pandas/io/common.py
text: @user1:
where is `.filename` actually defined?

@author:
good question.
In `zipfile.ZipFile.__init__()`:
https://github.com/python/cpython/blob/3.10/Lib/zipfile.py#L1234

~And, looking there, it looks like we need to also care of the case where a `PathLike` is passed, in which case only `self.file` and not `self.filename` are set. Will adapt to that!~

@user1:
ok in the super class