How to convert Json to array?

Question

I created a system with Django. In this system, I read a .xlsx file with panda and I convert it to JSON. I will make some operations and display them in my template. Because of that I want to convert it an array.

Here are my codes:

views.py,

def test_view(request):
    # Financial Ratios
    fr = pd.read_excel('media/test.xlsx', usecols='A:G', skiprows=lambda x: x < 0 or x > 42, sheet_name='Scoring')
    json_fr = fr.to_json(orient="records")
    context = {
        'json_fr': json_fr
    }

    return render(request, 'json_test.html', context)

my json data

{"Financial Ratios":{"0":"Ratios","1":"Activity Ratios","2":"Total Asset Turnover Ratio","3":"Working Capital Turnover","4":"RiskCalc. v4. Activity Ratio 2","5":"Days of Inventory on H
and (DOH)","6":"Days of Sales Outstanding (DSO)","7":"Number of Days of Payables","8":"Cash Conversion Cycle","9":null,"10":"Liquidity Ratios","11":"liquidity","12":"Current Ratio","13
":"Quick Ratio","14":"Cash Ratio","15":null,"16":"Solvency Ratios","17":"Solvency","18":"Total Debt Ratio","19":"Financial Liabilities Percentage","20":"Current Liabilities to Net Wort
h","21":"Financial Leverage Ratio","22":"Financial Expense Coverage","23":null,"24":"Profitability Ratios","25":"Cumulative Profitability","26":"Gross Profit Margin","27":"Operating Pr
ofit Margin","28":"Net Profit Margin","29":"Return on Assets-Productivity","30":"Return on Equity","31":null,"32":"Growth","33":"Sales Growth","34":"Operating Profit Growth","35":"Net
Profit Growth","36":"Tangible Net Worth Growth","37":"Cash and cash equivalents Growth","38":null,"39":null,"40":"General Financial Score","41":null},"Unnamed: 1":{"0":"Formula ","1":n
ull,"2":" Sales\/Total Assets (S\/TA) (X5)","3":"Sales\/Working Capital","4":"Account Payable\/Sales","5":"365\/(Cost of Goods Sold\/Inventory)","6":"365\/(Sales\/Trade Receivables)","
7":"365\/(Cost of Goods Sold\/Trade Payables)","8":"DOH + DSO \u2013 Number of Days of Payables","9":null,"10":null,"11":"X1, Working Capital\/Total Assets (WC\/TA)","12":"Current Asse
ts\/Current Liabilities","13":"(Current Assets-Inventory)\/Current Liabilities","14":"Cash and Marketable Securities\/Current Liabilities","15":null,"16":null,"17":"X4, Equity\/ Total
Liabilities (E\/TL)","18":"Total Debt\/Total Assets","19":"short-term Financial Liabilities\/Current Liabilities","20":"(Current Liabilities\/Net Worth)*100","21":"Total Assets\/Total
Equity","22":"Operating Profit\/Total Financial Expense","23":null,"24":null,"25":"X2, Retained Earnings\/Total Assets (RE\/TA)","26":"Gross Profit\/Sales","27":"Operating Profit\/Sale
s","28":"Net Profit\/Sales","29":"X3, Operating Profit\/Total Assets","30":"Net Income\/Equity","31":null,"32":null,"33":"Annual Sales Growth","34":"Annual Operating Profit Growth","35
":"Annual Net Profit Growth","36":"Annual Net Worth Growth","37":"Annual Cash and cash equivalents Growth","38":null,"39":null,"40":3,"41":3},"Unnamed: 2":{"0":"2013","1":null,"2":0.36
88805385,"3":1.5217292178,"4":0.3654207703,"5":126.7687160825,"6":201.1519182976,"7":156.6392091208,"8":171.2814252594,"9":null,"10":null,"11":0.24240879,"12":1.4011830177,"13":1.22063
83114,"14":0.1198051849,"15":null,"16":null,"17":0.2542669835,"18":0.7972784209,"19":0.1830886471,"20":298.0614724129,"21":4.9328739664,"22":0.6270806042,"23":null,"24":null,"25":0.023
6571651,"26":0.1484981192,"27":0.0492337241,"28":0.0334269397,"29":0.0181613627,"30":0.0608250368,"31":null,"32":"Year over year trends 2012 vs 2013","33":0.121184383,"34":0.0492337241
,"35":-0.6389706106,"36":0.0647643295,"37":-0.503449854,"38":null,"39":null,"40":16.8,"41":9},"Unnamed: 3":{"0":2012.0,"1":null,"2":0.46659987,"3":2.3187325549,"4":0.7809577867,"5":89.
3996349298,"6":136.5175896751,"7":343.2088569019,"8":-117.291632297,"9":null,"10":null,"11":0.2012305684,"12":1.3122086769,"13":1.1649433701,"14":0.3207784969,"15":null,"16":null,"17":
0.3698847524,"18":0.7299884156,"19":0.1413598101,"20":238.7077877629,"21":3.7035448029,"22":5.1835849804,"23":null,"24":null,"25":0.1355888945,"26":0.169457354,"27":0.0634190537,"28":0
.1038080661,"29":0.0295913222,"30":0.1793879706,"31":null,"32":null,"33":null,"34":null,"35":null,"36":null,"37":null,"38":null,"39":null,"40":5.0,"41":null},"Unnamed: 4":{"0":"Point",
"1":null,"2":1,"3":0,"4":1,"5":0,"6":0,"7":0,"8":0,"9":"Efficiency Score","10":null,"11":2,"12":3,"13":4,"14":0,"15":"liquidity Score","16":null,"17":0,"18":2,"19":0,"20":2,"21":0,"22"
:1,"23":"Solvency Score","24":null,"25":0,"26":0,"27":0,"28":0,"29":1,"30":2,"31":"Profitability Score","32":null,"33":1,"34":1,"35":0,"36":1,"37":0,"38":null,"39":"Growth Score","40":
"General Financial Score","41":null},"Unnamed: 5":{"0":"Max Score for the Ratio","1":null,"2":50,"3":15,"4":10,"5":5,"6":5,"7":5,"8":10,"9":"100\/","10":null,"11":20,"12":40,"13":30,"1
4":10,"15":"100\/","16":null,"17":10,"18":25,"19":10,"20":25,"21":5,"22":25,"23":"100\/","24":null,"25":10,"26":10,"27":15,"28":15,"29":25,"30":25,"31":"100\/","32":null,"33":20,"34":2
0,"35":20,"36":20,"37":20,"38":null,"39":"100\/","40":"100\/","41":null},"Unnamed: 6":{"0":"Weighted Score","1":null,"2":10,"3":0,"4":10,"5":0,"6":0,"7":0,"8":0,"9":20,"10":null,"11":8
,"12":24,"13":24,"14":0,"15":56,"16":null,"17":0,"18":10,"19":0,"20":10,"21":0,"22":5,"23":25,"24":null,"25":0,"26":0,"27":0,"28":0,"29":5,"30":10,"31":15,"32":null,"33":20,"34":20,"35
":0,"36":20,"37":0,"38":null,"39":60,"40":36.8,"41":null}}

How can I convert json_fr to an array?

template

{% for test in json_fr %}
    <li>{{ test }}</li>
{% endfor %}

Answer 1

I think you need data into this format

[{'Financial Ratios': 'Ratios',
  'Unnamed: 1': 'Formula ',
  'Unnamed: 2': '2013',
  'Unnamed: 3': 2012.0,
  'Unnamed: 4': 'Point',
  'Unnamed: 5': 'Max Score for the Ratio',
  'Unnamed: 6': 'Weighted Score'},
 {'Financial Ratios': 'Activity Ratios',
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None},
 {'Financial Ratios': 'Total Asset Turnover Ratio',
  'Unnamed: 1': ' Sales/Total Assets (S/TA) (X5)',
  'Unnamed: 2': 0.3688805385,
  'Unnamed: 3': 0.46659987,
  'Unnamed: 4': 1,
  'Unnamed: 5': 50,
  'Unnamed: 6': 10},
 {'Financial Ratios': 'Working Capital Turnover',
  'Unnamed: 1': 'Sales/Working Capital',
  'Unnamed: 2': 1.5217292178,
  'Unnamed: 3': 2.3187325549,
  'Unnamed: 4': 0,
  'Unnamed: 5': 15,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'RiskCalc. v4. Activity Ratio 2',
  'Unnamed: 1': 'Account Payable/Sales',
  'Unnamed: 2': 0.3654207703,
  'Unnamed: 3': 0.7809577867,
  'Unnamed: 4': 1,
  'Unnamed: 5': 10,
  'Unnamed: 6': 10},
 {'Financial Ratios': 'Days of Inventory on Hand (DOH)',
  'Unnamed: 1': '365/(Cost of Goods Sold/Inventory)',
  'Unnamed: 2': 126.7687160825,
  'Unnamed: 3': 89.3996349298,
  'Unnamed: 4': 0,
  'Unnamed: 5': 5,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Days of Sales Outstanding (DSO)',
  'Unnamed: 1': '365/(Sales/Trade Receivables)',
  'Unnamed: 2': 201.1519182976,
  'Unnamed: 3': 136.5175896751,
  'Unnamed: 4': 0,
  'Unnamed: 5': 5,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Number of Days of Payables',
  'Unnamed: 1': '365/(Cost of Goods Sold/Trade Payables)',
  'Unnamed: 2': 156.6392091208,
  'Unnamed: 3': 343.2088569019,
  'Unnamed: 4': 0,
  'Unnamed: 5': 5,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Cash Conversion Cycle',
  'Unnamed: 1': 'DOH + DSO – Number of Days of Payables',
  'Unnamed: 2': 171.2814252594,
  'Unnamed: 3': -117.291632297,
  'Unnamed: 4': 0,
  'Unnamed: 5': 10,
  'Unnamed: 6': 0},
 {'Financial Ratios': None,
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': 'Efficiency Score',
  'Unnamed: 5': '100/',
  'Unnamed: 6': 20},
 {'Financial Ratios': 'Liquidity Ratios',
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None},
 {'Financial Ratios': 'liquidity',
  'Unnamed: 1': 'X1, Working Capital/Total Assets (WC/TA)',
  'Unnamed: 2': 0.24240879,
  'Unnamed: 3': 0.2012305684,
  'Unnamed: 4': 2,
  'Unnamed: 5': 20,
  'Unnamed: 6': 8},
 {'Financial Ratios': 'Current Ratio',
  'Unnamed: 1': 'Current Assets/Current Liabilities',
  'Unnamed: 2': 1.4011830177,
  'Unnamed: 3': 1.3122086769,
  'Unnamed: 4': 3,
  'Unnamed: 5': 40,
  'Unnamed: 6': 24},
 {'Financial Ratios': 'Quick Ratio',
  'Unnamed: 1': '(Current Assets-Inventory)/Current Liabilities',
  'Unnamed: 2': 1.2206383114,
  'Unnamed: 3': 1.1649433701,
  'Unnamed: 4': 4,
  'Unnamed: 5': 30,
  'Unnamed: 6': 24},
 {'Financial Ratios': 'Cash Ratio',
  'Unnamed: 1': 'Cash and Marketable Securities/Current Liabilities',
  'Unnamed: 2': 0.1198051849,
  'Unnamed: 3': 0.3207784969,
  'Unnamed: 4': 0,
  'Unnamed: 5': 10,
  'Unnamed: 6': 0},
 {'Financial Ratios': None,
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': 'liquidity Score',
  'Unnamed: 5': '100/',
  'Unnamed: 6': 56},
 {'Financial Ratios': 'Solvency Ratios',
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None},
 {'Financial Ratios': 'Solvency',
  'Unnamed: 1': 'X4, Equity/ TotalLiabilities (E/TL)',
  'Unnamed: 2': 0.2542669835,
  'Unnamed: 3': 0.3698847524,
  'Unnamed: 4': 0,
  'Unnamed: 5': 10,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Total Debt Ratio',
  'Unnamed: 1': 'Total Debt/Total Assets',
  'Unnamed: 2': 0.7972784209,
  'Unnamed: 3': 0.7299884156,
  'Unnamed: 4': 2,
  'Unnamed: 5': 25,
  'Unnamed: 6': 10},
 {'Financial Ratios': 'Financial Liabilities Percentage',
  'Unnamed: 1': 'short-term Financial Liabilities/Current Liabilities',
  'Unnamed: 2': 0.1830886471,
  'Unnamed: 3': 0.1413598101,
  'Unnamed: 4': 0,
  'Unnamed: 5': 10,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Current Liabilities to Net Worth',
  'Unnamed: 1': '(Current Liabilities/Net Worth)*100',
  'Unnamed: 2': 298.0614724129,
  'Unnamed: 3': 238.7077877629,
  'Unnamed: 4': 2,
  'Unnamed: 5': 25,
  'Unnamed: 6': 10},
 {'Financial Ratios': 'Financial Leverage Ratio',
  'Unnamed: 1': 'Total Assets/TotalEquity',
  'Unnamed: 2': 4.9328739664,
  'Unnamed: 3': 3.7035448029,
  'Unnamed: 4': 0,
  'Unnamed: 5': 5,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Financial Expense Coverage',
  'Unnamed: 1': 'Operating Profit/Total Financial Expense',
  'Unnamed: 2': 0.6270806042,
  'Unnamed: 3': 5.1835849804,
  'Unnamed: 4': 1,
  'Unnamed: 5': 25,
  'Unnamed: 6': 5},
 {'Financial Ratios': None,
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': 'Solvency Score',
  'Unnamed: 5': '100/',
  'Unnamed: 6': 25},
 {'Financial Ratios': 'Profitability Ratios',
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None},
 {'Financial Ratios': 'Cumulative Profitability',
  'Unnamed: 1': 'X2, Retained Earnings/Total Assets (RE/TA)',
  'Unnamed: 2': 0.0236571651,
  'Unnamed: 3': 0.1355888945,
  'Unnamed: 4': 0,
  'Unnamed: 5': 10,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Gross Profit Margin',
  'Unnamed: 1': 'Gross Profit/Sales',
  'Unnamed: 2': 0.1484981192,
  'Unnamed: 3': 0.169457354,
  'Unnamed: 4': 0,
  'Unnamed: 5': 10,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Operating Profit Margin',
  'Unnamed: 1': 'Operating Profit/Sales',
  'Unnamed: 2': 0.0492337241,
  'Unnamed: 3': 0.0634190537,
  'Unnamed: 4': 0,
  'Unnamed: 5': 15,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Net Profit Margin',
  'Unnamed: 1': 'Net Profit/Sales',
  'Unnamed: 2': 0.0334269397,
  'Unnamed: 3': 0.1038080661,
  'Unnamed: 4': 0,
  'Unnamed: 5': 15,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Return on Assets-Productivity',
  'Unnamed: 1': 'X3, Operating Profit/Total Assets',
  'Unnamed: 2': 0.0181613627,
  'Unnamed: 3': 0.0295913222,
  'Unnamed: 4': 1,
  'Unnamed: 5': 25,
  'Unnamed: 6': 5},
 {'Financial Ratios': 'Return on Equity',
  'Unnamed: 1': 'Net Income/Equity',
  'Unnamed: 2': 0.0608250368,
  'Unnamed: 3': 0.1793879706,
  'Unnamed: 4': 2,
  'Unnamed: 5': 25,
  'Unnamed: 6': 10},
 {'Financial Ratios': None,
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': 'Profitability Score',
  'Unnamed: 5': '100/',
  'Unnamed: 6': 15},
 {'Financial Ratios': 'Growth',
  'Unnamed: 1': None,
  'Unnamed: 2': 'Year over year trends 2012 vs 2013',
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None},
 {'Financial Ratios': 'Sales Growth',
  'Unnamed: 1': 'Annual Sales Growth',
  'Unnamed: 2': 0.121184383,
  'Unnamed: 3': None,
  'Unnamed: 4': 1,
  'Unnamed: 5': 20,
  'Unnamed: 6': 20},
 {'Financial Ratios': 'Operating Profit Growth',
  'Unnamed: 1': 'Annual Operating Profit Growth',
  'Unnamed: 2': 0.0492337241,
  'Unnamed: 3': None,
  'Unnamed: 4': 1,
  'Unnamed: 5': 20,
  'Unnamed: 6': 20},
 {'Financial Ratios': 'NetProfit Growth',
  'Unnamed: 1': 'Annual Net Profit Growth',
  'Unnamed: 2': -0.6389706106,
  'Unnamed: 3': None,
  'Unnamed: 4': 0,
  'Unnamed: 5': 20,
  'Unnamed: 6': 0},
 {'Financial Ratios': 'Tangible Net Worth Growth',
  'Unnamed: 1': 'Annual Net Worth Growth',
  'Unnamed: 2': 0.0647643295,
  'Unnamed: 3': None,
  'Unnamed: 4': 1,
  'Unnamed: 5': 20,
  'Unnamed: 6': 20},
 {'Financial Ratios': 'Cash and cash equivalents Growth',
  'Unnamed: 1': 'Annual Cash and cash equivalents Growth',
  'Unnamed: 2': -0.503449854,
  'Unnamed: 3': None,
  'Unnamed: 4': 0,
  'Unnamed: 5': 20,
  'Unnamed: 6': 0},
 {'Financial Ratios': None,
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None},
 {'Financial Ratios': None,
  'Unnamed: 1': None,
  'Unnamed: 2': None,
  'Unnamed: 3': None,
  'Unnamed: 4': 'Growth Score',
  'Unnamed: 5': '100/',
  'Unnamed: 6': 60},
 {'Financial Ratios': 'General Financial Score',
  'Unnamed: 1': 3,
  'Unnamed: 2': 16.8,
  'Unnamed: 3': 5.0,
  'Unnamed: 4': 'General Financial Score',
  'Unnamed: 5': '100/',
  'Unnamed: 6': 36.8},
 {'Financial Ratios': None,
  'Unnamed: 1': 3,
  'Unnamed: 2': 9,
  'Unnamed: 3': None,
  'Unnamed: 4': None,
  'Unnamed: 5': None,
  'Unnamed: 6': None}]

You can use this snippet of code :-

data = json.loads(<stringify_json>)

index_key_mapping = {index: key for index, key in enumerate(data.keys())}
formated_data = [{
    index_key_mapping[index]: value for index, value in enumerate(ord_pair)
} for ord_pair in zip(*[
    dictionary.values() for key, dictionary in data.items()
])]

Answer 2

You can use list and .items():

arr_fr = list(json_fr.items())

print('Child title: ', arr_fr[0][0])
print('Child value: ', arr_fr[0][1])
print('Child of child: ', arr_fr[0][1]['3'])

And I get result:

Answer 3

Array of what? You need to detail the format you seek. However, I suspect you may be looking for:

json_fr = fr.to_json(orient="records")

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_json.html

This will give you an array of "rows" in the dataframe essentially.