Reading a file from a private S3 bucket to a pandas dataframe

Update for pandas 0.22 and up:

If you have already installed s3fs (pip install s3fs) then you can read the file directly from s3 path, without any imports:

data = pd.read_csv('s3://bucket....csv')

stable docs

Based on this answer, I found smart_open to be much simpler to use:

import pandas as pd
from smart_open import smart_open

initial_df = pd.read_csv(smart_open('s3://bucket/file.csv'))

import pandas as pd
import boto3
from io import StringIO

# Read CSV
s3 = boto3.client('s3',endpoint_url,aws_access_key_id=,aws_secret_access_key)
read_file = s3.get_object(Bucket, Key)
df = pd.read_csv(read_file['Body'],sep=',')

# Write CSV
csv_buffer = StringIO()
df.to_csv(csv_buffer)
s3.put_object(Bucket, Key,Body=csv_buffer.getvalue())

Update for pandas 0.20.3 without using s3fs:

import boto3
import pandas as pd
import sys

if sys.version_info[0] < 3: 
    from StringIO import StringIO # Python 2.x
else:
    from io import StringIO # Python 3.x

s3 = boto3.client('s3')
obj = s3.get_object(Bucket='bucket', Key='key')
body = obj['Body']
csv_string = body.read().decode('utf-8')

df = pd.read_csv(StringIO(csv_string))

In addition to other awesome answers, if a custom endpoint is required, it is possible to use pd.read_csv('s3://...') syntax by monkey patching the s3fs init method.

import s3fs
s3fsinit = s3fs.S3FileSystem.__init__
def s3fsinit_patched(self, *k, *kw):
    s3fsinit(self, *k, client_kwargs={'endpoint_url': 'https://yourcustomendpoint'}, **kw)
s3fs.S3FileSystem.__init__ = s3fsinit_patched

Or, a more elegant way:

import s3fs
class S3FileSystemPatched(s3fs.S3FileSystem):
    def __init__(self, *k, **kw):
        super(S3FileSystemPatched, self).__init__( *k,
                                                  key = os.environ['aws_access_key_id'],
                                                  secret = os.environ['aws_secret_access_key'],
                                                  client_kwargs={'endpoint_url': 'https://yourcustomendpoint'},
                                                  **kw)
        print('S3FileSystem is patched')
s3fs.S3FileSystem = S3FileSystemPatched

Also see: s3fs custom endpoint url

import s3fs
import pandas as pd
s3 = s3fs.S3FileSystem(profile='<profile_name>')
pd.read_csv(s3.open(<s3_path>))

You can also manually use credentials.

Note that if your bucket is private AND on an aws-like provider, you will meet errors as s3fs does not load the profile config file at ~/.aws/config like awscli.

One solution is to define the current environment variable :

export AWS_S3_ENDPOINT="myEndpoint"
export AWS_DEFAULT_REGION="MyRegion"

The previous answers are a good basic start but I wanted to achieve advanced objectives stated below. Overall I feel awswrangler is the way to go.

1. read .gzip
2. read only the first 5 lines without downloading the full file
3. explicitly pass credentials (make sure you don't commit them to code!!)
4. use full s3 paths

Here are a couple of things that worked

import boto3
import pandas as pd
import awswrangler as wr

boto3_creds = dict(region_name="us-east-1", aws_access_key_id='', aws_secret_access_key='')
boto3.setup_default_session(**boto3_creds)

s3 = boto3.client('s3')

# read first 5 lines from file path
obj = s3.get_object(Bucket='bucket', Key='path.csv.gz')
df = pd.read_csv(obj['Body'], nrows=5, compression='gzip')

# read first 5 lines from directory
dft_xp = pd.concat(list(wr.s3.read_csv(wr.s3.list_objects('s3://bucket/path/')[0], chunksize=5, nrows=5, compression='gzip')))

# read all files into pandas
df_xp = wr.s3.read_csv(wr.s3.list_objects('s3://bucket/path/'), compression='gzip')

Did not use s3fs wasn't sure if it uses boto3.

For distributed compute with dask, this worked but it uses s3fs afaik and apparently gzip can't be parallized.

import dask.dataframe as dd

dd.read_csv('s3://bucket/path/*', storage_options={'key':'', 'secret':''}, compression='gzip').head(5)

dd.read_csv('s3://bucket/path/*', storage_options={'key':'', 'secret':''}, compression='gzip') 
# Warning gzip compression does not support breaking apart files Please ensure that each individual file can fit in memory
 

To add on to the other solutions here with more recent updates: pandas, fsspec, and s3fs have all been updated such that you can reading directly from custom endpoints using pandas and no other imports. You must make sure you have both fsspec and s3fs installed, as they are optional dependencies for pandas.

Then you can use

import pandas as pd

pd.read_csv(
    's3://mybucket/file.csv',
    storage_options = {
        client_kwargs = {
            'endpoint_url': <MY_S3_ENDPOINT_URL>
        }
    }

Its clunky, but its required as maintainers of boto3 for some reason have refused to update the library to allow for custom endpoint configuration outside of client construction (i.e. in config files or environment variables) for years now. But if you don't need to be reading/writing through pandas, awswrangler is perhaps better as others have mentioned.