YahooFinancials: Writing multidimensional dictionary to csv

Question

There's a nice package to load financial data, but it stores it in a multidimensional dictionary and I cant figure it out how to store the data properly in a csv file, column by column.

from yahoofinancials import YahooFinancials

ticker = 'AAPL'
yahoo_financials = YahooFinancials(ticker)

balance_sheet_data_qt = yahoo_financials.get_financial_stmts('quarterly', 'balance')

import csv

with open("Yahooprices.csv", "w") as csv_file:
    csv_app = csv.writer(csv_file, delimiter="\t")
    for row in balance_sheet_data_qt["balanceSheetHistoryQuarterly"]["AAPL"][0].items():
        csv_app.writerow(row)

I know what I wanna do is simple, however, I can't write it to a csv file that it looks like:

What am I doing wrong? The dictionary has just one .keys() and has a size of 1.. nevertheless, there are many .items(), which I'd like to split by columns/store in the csv.

Answer 1

Have you tried running the JSON objects through a JSON flattening function before converting to csv? It is tricky to store JSON objects with non-flat data into csv since csv is two dimensional and JSON can be multi-dimensional. Try running the objects you are trying to save as csv through a function like this first:

# Function to Flatten return JSON data
def flatten_json(b, delim):
    val = {}
    for i in b.keys():
        if isinstance(b[i], dict):
            get = flatten_json(b[i], delim)
            for j in get.keys():
                val[i.replace(':', '_') + delim + j.replace(':', '_')] = get[j]
        elif isinstance(b[i], list):
            c = 1
            for it in b[i]:
                if isinstance(it, dict):
                    get = flatten_json(it, delim)
                    for j in get.keys():
                        val[i.replace(':', '_') + delim + j.replace(':', '_') + delim + str(c)] = get[j]
                else:
                    val[i.replace(':', '_') + delim + str(c)] = it
                c += 1
        else:
            val[i.replace(':', '_')] = b[i]
    return val

Where b is the python dictionary you are trying to flatten and delim is the column delimiter string you want to append to the new field names that will need to be created as a result of the flattening.

For example,

init_dict = {data1: string, data2: [string1, string2], data3: [{obj: val}, {obj: val2}], data4: {obj2: value}}

After going through the function:

flat_dict = flatten_json(init_dict, '__')
print(flat_dict.items())

will now output the dictionary as shown below, which is 2 dimensional and csv friendly:

{data1: string, data2__1: string1, data2__2: string2, data3__obj__2: val, data3__obj__2: val2, data4__obj2: value}

Hope this helps!

As an aside, I am also the author of YahooFinancials, so thank you for using the module!