format json response properly of csv pulled from S3 bucket

Question

I have a small csv with data that 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

I am using the following code to pull latest file from bucket and print as json.

import json
import pandas as pd
import boto3

s3 = boto3.client('s3')
object_list = []
bucket_name="bats-candles"

paginator = s3.get_paginator("list_objects_v2")
page_iterator = paginator.paginate(Bucket=bucket_name)
for result in page_iterator:
    object_list += filter(lambda obj: obj['Key'].endswith('.csv'), result['Contents'])
object_list.sort(key=lambda x: x['LastModified'])

A = (object_list[-1]['Key'])
full_path = f"s3://{bucket_name}/{A}"
print(full_path)
print(A)

raw_df = pd.read_csv(full_path)

print(bucket_name, full_path)

df = raw_df.to_json(orient="records")

response = {
    'body': json.dumps(df, indent=4, separators=(',', ':'))
}

pretty_json = json.dumps(response)
print(pretty_json)

The data that I get back looks like this:

{"body": "\"[{\\\"Ticker\\\":\\\"6A\\\",\\\"Exchange\\\":\\\"BATS\\\",\\\"Date\\\":\\\"12\\\\/2\\\\/2021\\\",\\\"Open\\\":0.9,\\\"High\\\":0.95,\\\"Low\\\":0.83,\\\"Close\\\":0.95,\\\"Volume\\\":1200}

but I would like it 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.0,
            "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 suggest the proper way to achieve this? I have tried all sorts of combinations of json.dumps and json.loads but cant find the proper combination.
Thanks

Leave a Comment Cancel reply