Repository: pandas-dev/pandas
Pull request: BUG: to_json not allowing uploads to S3 (#28375)
- [x] closes #28375
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Problem statement:
unable to write JSON to S3 use to_json
#### Code Sample, a copy-pastable example if possible

S3 paths work for reading and writing CSV. However, I am not able to write json files using the to_json method.   Reading json from an S3 path seems to work just fine.


```python
df.to_json(s3uri, orient='values')
```
#### Problem description
```python
---------------------------------------------------------------------------
FileNotFoundError                         Traceback (most recent call last)
 in 
      1 final_users['user_id'].to_json(s3_path + 'users.json',
----> 2                                orient='values')

~/anaconda3/envs/qa-tool/lib/python3.7/site-packages/pandas/core/generic.py in to_json(self, path_or_buf, orient, date_format, double_precision, force_ascii, date_unit, default_handler, lines, compression, index)
   2271                             default_handler=default_handler,
   2272                             lines=lines, compression=compression,
-> 2273                             index=index)
   2274 
   2275     def to_hdf(self, path_or_buf, key, **kwargs):

~/anaconda3/envs/qa-tool/lib/python3.7/site-packages/pandas/io/json/json.py in to_json(path_or_buf, obj, orient, date_format, double_precision, force_ascii, date_unit, default_handler, lines, compression, index)
     66 
     67     if isinstance(path_or_buf, compat.string_types):
---> 68         fh, handles = _get_handle(path_or_buf, 'w', compression=compression)
     69         try:
     70             fh.write(s)

~/anaconda3/envs/qa-tool/lib/python3.7/site-packages/pandas/io/common.py in _get_handle(path_or_buf, mode, encoding, compression, memory_map, is_text)
    425         elif is_text:
    426             # Python 3 and no explicit encoding
--> 427             f = open(path_or_buf, mode, errors='replace', newline="")
    428         else:
    429             # Python 3 and binary mode

FileNotFoundError: [Errno 2] No such file or directory: 's3://bucket/folder/foo.json'
```
#### Expected Output

None. Expected output is no error and the file is written to the s3 bucket.

#### Output of ``pd.show_versions()``

<details>

[paste the output of ``pd.show_versions()`` here below this line]
INSTALLED VERSIONS
------------------
commit: None

pandas: 0.24.2
pytest: None
pip: 19.2.2
setuptools: 41.0.1
Cython: None
numpy: 1.16.4
scipy: 1.3.1
pyarrow: None
xarray: None
IPython: 7.7.0
sphinx: None
patsy: 0.5.1
dateutil: 2.8.0
pytz: 2019.2
blosc: None
bottleneck: None
tables: None
numexpr: None
feather: None
matplotlib: 3.1.0
openpyxl: 2.6.2
xlrd: 1.2.0
xlwt: None
xlsxwriter: None
lxml.etree: None
bs4: None
html5lib: None
sqlalchemy: None
pymysql: None
psycopg2: None
jinja2: None
s3fs: 0.2.1
fastparquet: None
pandas_gbq: None
pandas_datareader: None
gcsfs: None

</details>

Hints from issue discussion:
Thanks for the report. If you'd like to investigate and push a PR to fix would certainly be welcome
I think you can try use StringIO if you are on python3, python2 should be BytesIO
```
import boto3
import io
import pandas as pd

jsonBuffer = io.StringIO()

Cars = {'Brand': ['Honda Civic','Toyota Corolla','Ford Focus','Audi A4'],
        'Price': [22000,25000,27000,35000]
        }

df = pd.DataFrame(Cars,columns= ['Brand', 'Price'])
session = boto3.Session(aws_access_key_id=aws_access_key_id, aws_secret_access_key=aws_secret_access_key)
s3 = session.resource('s3')
df.to_json(outputBuffer, orient='values');
s3.Object(Bucket, 'PATH/TO/test.json').put(Body= jsonBuffer.getvalue())
```

Commit message: BUG: to_json not allowing uploads to S3 (#28375)

Patch to review:
diff --git a/doc/source/whatsnew/v1.0.1.rst b/doc/source/whatsnew/v1.0.1.rst
index 801d97b777e00..c6d8f839b5bb7 100644
--- a/doc/source/whatsnew/v1.0.1.rst
+++ b/doc/source/whatsnew/v1.0.1.rst
@@ -92,7 +92,6 @@ I/O
 
 - Fixed regression in :meth:`~DataFrame.to_csv` where specifying an ``na_rep`` might truncate the values written (:issue:`31447`)
 -
--
 
 Plotting
 ^^^^^^^^
diff --git a/doc/source/whatsnew/v1.1.0.rst b/doc/source/whatsnew/v1.1.0.rst
index e07a8fa0469f4..3304658a37a1a 100644
--- a/doc/source/whatsnew/v1.1.0.rst
+++ b/doc/source/whatsnew/v1.1.0.rst
@@ -167,7 +167,7 @@ MultiIndex
 I/O
 ^^^
 - Bug in :meth:`read_json` where integer overflow was occuring when json contains big number strings. (:issue:`30320`)
--
+- Bug in :meth:`DataFrame.to_json` was raising ``NotFoundError`` when ``path_or_buf`` was an S3 URI (:issue:`28375`)
 -
 
 Plotting
diff --git a/pandas/core/generic.py b/pandas/core/generic.py
index 3b1d7e4c50be5..bfbe35222b9c9 100644
--- a/pandas/core/generic.py
+++ b/pandas/core/generic.py
@@ -98,6 +98,7 @@
 from pandas.core.missing import find_valid_index
 from pandas.core.ops import _align_method_FRAME
 
+from pandas.io.common import get_filepath_or_buffer
 from pandas.io.formats import format as fmt
 from pandas.io.formats.format import DataFrameFormatter, format_percentiles
 from pandas.io.formats.printing import pprint_thing
@@ -2259,6 +2260,7 @@ def to_json(
         config.is_nonnegative_int(indent)
         indent = indent or 0
 
+
         return json.to_json(
             path_or_buf=path_or_buf,
             obj=self,
diff --git a/pandas/io/json/_json.py b/pandas/io/json/_json.py
index 204807b55c877..c65fb53f88810 100644
--- a/pandas/io/json/_json.py
+++ b/pandas/io/json/_json.py
@@ -56,7 +56,11 @@ def to_json(
             "'index=False' is only valid when 'orient' is 'split' or 'table'"
         )
 
-    path_or_buf = stringify_path(path_or_buf)
+    if path_or_buf is not None:
+        path_or_buf, _, _, _ = get_filepath_or_buffer(
+            path_or_buf, compression=compression, mode="w"
+        )
+
     if lines and orient != "records":
         raise ValueError("'lines' keyword only valid when 'orient' is records")
 
diff --git a/pandas/tests/io/json/test_pandas.py b/pandas/tests/io/json/test_pandas.py
index 94d51589023c4..602022a21c4a6 100644
--- a/pandas/tests/io/json/test_pandas.py
+++ b/pandas/tests/io/json/test_pandas.py
@@ -1662,3 +1662,12 @@ def test_json_multiindex(self, dataframe, expected):
         series = dataframe.stack()
         result = series.to_json(orient="index")
         assert result == expected
+
+    def test_to_s3(self, s3_resource):
+        # GH 28375
+        mock_bucket_name, target_file = "pandas-test", "test.json"
+        df = DataFrame({"x": [1, 2, 3], "y": [2, 4, 6]})
+        df.to_json(f"s3://{mock_bucket_name}/{target_file}")
+        assert target_file in (
+            obj.key for obj in s3_resource.Bucket("pandas-test").objects.all()
+        )


Code review comments:
diff_hunk: @@ -92,7 +92,6 @@ I/O
 
 - Fixed regression in :meth:`~DataFrame.to_csv` where specifying an ``na_rep`` might truncate the values written (:issue:`31447`)
 -
--
path: doc/source/whatsnew/v1.0.1.rst
text: @user1:
Looks like this needs the `-` tick added back to eliminate the diff.

diff_hunk: @@ -98,6 +98,7 @@
 from pandas.core.missing import find_valid_index
 from pandas.core.ops import _align_method_FRAME
 
+from pandas.io.common import get_filepath_or_buffer
path: pandas/core/generic.py
text: @user1:
import can be removed now that this was moved (out linter checks for unused imports so this will cause ci to fail the "checks" builds)