Import latest file from S3 bucket to Pandas dataframe

Question

SITUATION

I have written some code in Python 3 in which I use the OS and Glob packages to find the latest csv file in a directory and convert it to a Panda dataframe.

Code as follows:

import pandas as pd
from pathlib import Path
import glob
import os

# LOOK FOR ALL CSVs IN FOLDER AND GET LATEST
fld = '.'
latest_csv_file = glob.glob('/path/to/file/filename.csv')

imported_file = max(latest_csv_file, key=os.path.getctime)


# IMPORT LATEST CV USING PANDAS
imported_file = pd.read_csv(latest_csv_file, dtype={2:'str'})


# REMOVE SPACES FROM COL NAMES AND CONVERT COL NAMES TO LOWER CASE
imported_file.columns = imported_file.columns.str.replace(' ', '')
imported_file.columns = imported_file.columns.str.lower()

This seems to work well, however I need to be able to perform the same operation in my Lambda fuction, which saves a csv file attachment from an incoming email.

WHAT I HAVE TRIED

 bucketname = 'my_bucket' 
    
    
 s3_client = boto3.client('s3')
 response = s3_client.list_objects_v2(Bucket = bucketname2, Prefix = 'attachments/')
 all = response['Contents']      
 latest_file = max(all, key=lambda x: x['LastModified'])
    
 print(latest_file)

This will give me the name of the file in the following format

attachments/nolu34lqipv1cl14i0qjebcc1rnqb2ngbnf4ss01-filename.csv

[folder]/[original_msg_key]-[filename.csv]

However if I try and read the file into a Pandas df I get the follwoing

imported_file = pd.read_csv(latest_file, dtype={2:'str'})

module initialization error Invalid file path or buffer object type:

I understand this to be because Pandas is expecting a specific file path and not an object of type 'dict', but can't see how to achieve my aim.

Any help appreciated.

user_248823 · Answer

To get the key of your object, you have to use latest_file['Key'] and for pandas you should include s3:// as a prefix:

imported_file = pd.read_csv('s3://' + latest_file['Key'], dtype={2:'str'})

This will require s3fs correctly setup for your python along with AWS credentials for S3 access.

You also have bucketname2 where bucketname should be used.

user_12133434 · Answer

You should be able to use this in lambda. Tack it on to your existing code

bucketname = 'my_bucket'  
s3_client = boto3.client('s3')
key = latest_file #validate this is the full key path

obj = s3_client.get_object(Bucket=bucketname, Key=key)
data = io.StringIO(obj['Body'].read().decode('utf-8'))
pd.read_csv(data) # use additional arguments as needed

Import latest file from S3 bucket to Pandas dataframe

2 Answers