[Python] How can I extract specific objects, transpose, and combine multiple, complex-nested...

I'm aware of the several posts that cover this topic, I apologise in advance. I've been reading and trying several times.

Here are three example json files that I save into fildir:

https://data.sec.gov/api/xbrl/companyfacts/CIK0000320193.json
https://data.sec.gov/api/xbrl/companyfacts/CIK0001722010.json
https://data.sec.gov/api/xbrl/companyfacts/CIK0001722606.json


primary keys are cik, entityName, facts. within facts: "dei", "us-gaap". within either of the two facts, you have a "Concept", in quotes, that isn't assigned a key...sigh.

Here is a sample of one of these json files:

{
"cik": 320193,
"entityName": "Apple Inc.",
"facts": {
"dei": {
"EntityCommonStockSharesOutstanding": {
"label": "Entity Common Stock, Shares Outstanding",
"description": "Indicate number of shares or other units outstanding of each of registrant'.......etc....",
"units": {
"shares": [
{
"end": "2009-06-27",
"val": 895816758,
"accn": "0001193125-09-153165",
"fy": 2009,
"fp": "Q3",
"form": "10-Q",
"filed": "2009-07-22",
"frame": "CY2009Q2I"
},
}
]
}
}
},
"us-gaap": {
"AccountsPayable": {
"label": "Accounts Payable (Deprecated 2009-01-31)",
"description": "Carrying value as of the balance sheet date of liabilities incurred (and for which invoices have typically been received) and payable to vendors for goods and services received that are used in an entity's business. For classified balance sheets, used to reflect the current portion of the liabilities (due within one year or within the normal operating cycle if longer); for unclassified balance sheets, used to reflect the total liabilities (regardless of due date).",
"units": {
"USD": [
{
"end": "2008-09-27",
"val": 5520000000,
"accn": "0001193125-09-153165",
"fy": 2009,
"fp": "Q3",
"form": "10-Q",
"filed": "2009-07-22",
"frame": "CY2008Q3I"
},



here is one of the several attempts i've tried:

import pandas as pd
import os
import glob
import json

OUTPUT_PATH = "../output/concepts/csv/"
fildir = '../resources/companyfacts/'
fils = os.path.join(fildir, '*.json')
filist = glob.glob(fils)

for fils in filist:
i = open(fils, "r")
sec_data = json.loads(i.read())
try:
for item in sec_data["facts"]["dei"]:
i.close()
except:
continue
try:
for item in sec_data["facts"]["us-gaap"]:
i.close()
except:
continue
dfs = [pd.read_json(fils) for sec_data in filist]
data = {"concepts": {}}
for item in sec_data["facts"]["dei"]:
if f"{item}" not in data:
data[f"{item}"] = {}
data[f"{item}"] = item
for item in sec_data["facts"]["us-gaap"]:
if f"{item}" not in data:
data[f"{item}"] = {}
data[f"{item}"] = item
df = pd.concat(dfs, ignore_index=True)
df = pd.DataFrame(data).transpose()
df.to_csv(OUTPUT_PATH + "CONCEPTS.csv")


output results:

concepts
EntityCommonStockSharesOutstanding
EntityPublicFloat
AccruedLiabilitiesCurrent
AdditionalPaidInCapital
CommonStockParOrStatedValuePerShare
CommonStockSharesAuthorized
......
......
......etc


what I want: first, I want the script to skip and process the next file in the list, for any file that doesn't match the JSON keys it searches for. (any issues that come up processing the file). Some files are empty, and some files don't have "us-gaap" or "dei" objects in the facts key. There are a few thousand files. One error that comes up:

for item in sec_data["facts"]["us-gaap"]:
~~~~~~~~~~~~~~~~~^^^^^^^^^^^
KeyError: 'us-gaap'



Then, I want my csv file to look like this:

CIK0000320193-concepts: CIK0001722010-concepts: CIK0001722606-concepts:
concpet list #1 here concept list #2 here concept list #3 here


Please forgive me, I'm not the best with code, but Im good with patterns, details, and solving problems. The closest I've gotten with checking guides and other stack questions, is an output that processes only one of the several files in my folder.

Continue reading...