pythonamazon-s3aws-lambdaaws-api-gateway

formatting json output from lambda that reads csv from S3


I have a lambda that reads a csv from a bucket:

import boto3
import csv
import json

BUCKET_NAME = 'olaptrader-products'
OBJECT_NAME = 'bats.csv'
LAMBDA_LOCAL_TMP_FILE = '/tmp/bats.csv'

def lambda_handler(event, context):
    s3 = boto3.client('s3')
    s3.download_file(BUCKET_NAME, OBJECT_NAME, LAMBDA_LOCAL_TMP_FILE)
    
    with open(LAMBDA_LOCAL_TMP_FILE,'r') as infile:
        reader = list(csv.reader(infile))
    
    return {
        'body': json.dumps(reader)
    }

The csv in my bucket looks like this:

Ticker  Exchange    Date        Open    High    Low    Close    Volume
6A      BATS        12/2/2021   0.9     0.95    0.83    0.95    1200
6B      BATS        12/3/2021   1       1.3     0.9     1.2     1500
6C      BATS        12/4/2021   1.2     1.3     1.1     1.1     1300

My output from the api gateway that calls this lambda looks like this:

[["Ticker", "Exchange", "Date", "Open", "High", "Low", "Close", "Volume"], ["6A", "BATS", "12/2/2021", "0.9", "0.95", "0.83", "0.95", "1200"], ["6B", "BATS", "12/3/2021", "1", "1.3", "0.9", "1.2", "1500"], ["6C", "BATS", "12/4/2021", "1.2", "1.3", "1.1", "1.1", "1300"]]

However, I want the output to look like this:

[{"Ticker":"6A","Exchange":"BATS","Date":"12/2/2021","Open":0.9,"High":0.95,"Low":0.83,"Close":0.95,"volume":1200},{"Ticker":"6B","Exchange":"BATS","Date":"12/3/2021","Open":1,"High":1.3,"Low":0.9,"Close":1.2,"volume":1500},{"Ticker":"6C","Exchange":"BATS","Date":"12/4/2021","Open":1.2,"High":1.3,"Low":1.1,"Close":1.1,"volume":1300}]

Can anyone advise me how to change the lambda around to achieve this?

Also, I dont know why this 'LAMBDA_LOCAL_TMP_FILE" is there. I dont need this. (got from some other site). Can you tell me how to remove that and still have the lambda work? Thanks alot.


Solution

  • you can try with csv.DictReader function

    import boto3
    import csv
    import json
    
    BUCKET_NAME = 'olaptrader-products'
    OBJECT_NAME = 'bats.csv'
    LAMBDA_LOCAL_TMP_FILE = '/tmp/bats.csv'
    
    def lambda_handler(event, context):
        s3 = boto3.client('s3')
        s3.download_file(BUCKET_NAME, OBJECT_NAME, LAMBDA_LOCAL_TMP_FILE)
        
        with open(LAMBDA_LOCAL_TMP_FILE,'r') as infile:
            reader = csv.DictReader(infile)
            rows = list(reader)
        
        return {
            'body': json.dumps(rows)
        }