Question 242669 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

format json response properly of csv pulled from S3 bucket

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

Tags:

2

Answers


  1. 0

    It looks like you have serialized the JSON response in the body using json.dumps()

    You can use the function json.loads() to deserialize JSON.

    Try:

    json.loads(pretty_json);
    Login or Signup to reply.
  2. 0 
    import pprint
s=''' 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'''

z=s.split('n')
res=[]
d={}
zz=z[0].split()
for st in range(1,len(z)):
    for t in range(len(z[st].split())):
         d[zz[t]]=z[st].split()[t]
    res.append(d)
pprint.pprint(res,sort_dicts=False)

    [{'Ticker': '6C',
  'Exchange': 'BATS',
  'Date': '12/4/2021',
  'Open': '1.2',
  'High': '1.3',
  'Low': '1.1',
  'Close': '1.1',
  'Volume': '1300'},
 {'Ticker': '6C',
  'Exchange': 'BATS',
  'Date': '12/4/2021',
  'Open': '1.2',
  'High': '1.3',
  'Low': '1.1',
  'Close': '1.1',
  'Volume': '1300'},
 {'Ticker': '6C',
  'Exchange': 'BATS',
  'Date': '12/4/2021',
  'Open': '1.2',
  'High': '1.3',
  'Low': '1.1',
  'Close': '1.1',
  'Volume': '1300'}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search