How to read a .json file in pandas to export it as a readable .csv file-CodePudding

I have created a .json file by appending a number of json strings using a get request. My aim is to convert the appended .json file into a readable .csv file. The .json file has the following format:

[{
  "trades":[
    {
      "id": 20995465,
      "unique_identifier": null,
      "transaction_type": "BUY",
      "transaction_date": "2016-11-08",
      "symbol": "RDSA",
      "market": "LSE",
      "quantity": 10,
      "price": 20.84,
      "exchange_rate": 0.5525,
      "brokerage": 3.619909502,
      "brokerage_currency_code": "GBP",
      "value": 380.81,
      "comments": null,
      "portfolio_id": 293304,
      "holding_id": 6258682,
      "instrument_id": 32021,
      "confirmed": true,
      "links": {
        "portfolio": "https://api.sharesight.com/api/v3/portfolios/293304"
      }
    }
  ],
  "links":{
   "self":"https://api.sharesight.com/api/v3/portfolios/2/trades"
  }
},
{
  "trades":[
    {
      "id": 20995425,
      "unique_identifier": null,
      "transaction_type": "BUY",
      "transaction_date": "2018-11-08",
      "symbol": "PDSA",
      "market": "LSE",
      "quantity": 1,
      "price": 2.84,
      "exchange_rate": 0.25,
      "brokerage": 7.619909502,
      "brokerage_currency_code": "GBP",
      "value": 80.81,
      "comments": null,
      "portfolio_id": 293604,
      "holding_id": 6258635,
      "instrument_id": 32023,
      "confirmed": true,
      "links": {
        "portfolio": "https://api.sharesight.com/api/v3/portfolios/293604"
      }
    }
 ],
  "links":{
   "self":"https://api.sharesight.com/api/v3/portfolios/2/trades"
   }
  }
]

My attempt

client_id = 'ClientID'
client_secret = 'ClientSecret'

access_token_url='https://api.sharesight.com/oauth2/token'

client = BackendApplicationClient(client_id=client_id)
oauth = OAuth2Session(client=client)
token = oauth.fetch_token(token_url=access_token_url, client_id=client_id, client_secret=client_secret)
access_token = token['access_token']
head = {'Authorization': f'Bearer {access_token}'}

# Get the portfolios
r = requests.get('https://api.sharesight.com/api/v2/portfolios.json', headers=head)
# print(r)
j = r.json()
# print(j)

rjs = []
for p in j['portfolios']:
    # print(p)
    name = p['name']
    pid = p['id']
    print(f'Retrieving {name} - {pid}')
    vurl = f'https://api.sharesight.com/api/v2/portfolios/{pid}/trades.json'
    r = requests.get(vurl, headers=head)
    rj = r.json()
    rjs.append(rj)

with open('/Users/Filename.json', 'w') as json_file:
        json.dump(rjs, json_file)

# Opening JSON file and loading the data
# into the variable data
with open('/Users/Filename.json') as json_file:
    data = json.load(json_file)
 
trades_data = data['trades']
 
# now we will open a file for writing - create a blank .csv file
data_file = open('/Users/Filename.csv', 'w')
 
# create the csv writer object
csv_writer = csv.writer(data_file)
 
# Counter variable used for writing
# headers to the CSV file
count = 0
 
for emp in trades_data:
    if count == 0:
 
        # Writing headers of CSV file
        header = emp.keys()
        csv_writer.writerow(header)
        count  = 1
 
    # Writing data of CSV file
    csv_writer.writerow(emp.values())
 
data_file.close()

Error Code

trades_data = data['trades']
TypeError: list indices must be integers or slices, not str

I think I get this error because 'trades' is replicated twice in my .json string and thus might be viewed as a string. Is there a workaround around this issue? I'm new to python so would greatly appreciate your help!

Desired Output

A .csv file with the following structure:

CodePudding user response：

Answer by @dsillman2000 for entry in data: trades_data = entry['trades'] ... etc