How to create a table from json data?

Question

I have a json format data and I want to create a table from that. I can take values and display them correctly. But when I create a table it is not working as what I want. My data returns as rows but I want to every array should be column.

It is my table:

And this is like what I want:

Here are my codes:

views.py

def test_fin(request):
 data_test = json.loads(json_bss_test)
    test_data = []
    test_1 = data_test['Assets']
    test_2 = data_test['Unnamed: 1']
    test_3 = data_test['Unnamed: 2']
    test_4 = data_test['Unnamed: 3']
    test_5 = data_test['Unnamed: 4']
    test_6 = data_test['Unnamed: 5']

    test_data.append(test_1)
    test_data.append(test_2)
    test_data.append(test_3)
    test_data.append(test_4)
    test_data.append(test_5)
    test_data.append(test_6)
    table_headers = []
    column_1_bss_header = data_bss_header["Unnamed: 1"]
    column_2_bss_header = data_bss_header["Unnamed: 2"]
    column_3_bss_header = data_bss_header["Unnamed: 3"]
    column_4_bss_header = data_bss_header["Unnamed: 4"]
    column_5_bss_header = data_bss_header["Unnamed: 5"]

    for i in range(0, 1):
        table_headers = [column_1_bss_header, column_2_bss_header, column_3_bss_header,
                         column_4_bss_header, column_5_bss_header]
context = {
        'test_data': test_data,
        'table_headers': table_headers,

    }
return render(request, 'fin_test.html', context)

fin_test.html

<table id="basic-datatables" class="display table table-bordered">
   <thead style="background-color: #d9f6ef">
      <th>in Millions USD</th>
      {% for data in table_headers %}
      {% for x,y in data.items %}
      <th>{{ y }}</th>
      {% endfor %}
      {% endfor %}
   </thead>
   <tbody>
      {%  for data in test_data %}
      <tr>
         {% for k,v in data.items %}
         <td>{{ v }}</td>
         {% endfor %}
      </tr>
      {% endfor %}
   </tbody>
</table>

my json:

{'Assets': {'0': 'Current Assests', '1': 'Cash and cash equivalents', '2': 'Inventories', '3': 'Trade receivables (Notes and accounts receivable)', '4': 'Total Current Assets', '5': 'N
on-Current Assets', '6': 'Property, Plant, and Equipment (Net)', '7': 'Intangible Assets and Goodwill', '8': 'Total Non-Current Assets', '9': 'Total Assets', '10': 'Liabilities and Sto
ckholders ’ Equity', '11': 'Current Liabilities', '12': 'Short-term financial debt (Short-term borrowings/Bank loans/Overdraft)', '13': 'Current portion of long - term debt', '14': 'Tr
ade Payables (Notes and accounts payable)', '15': 'Total Curent Liabilities', '16': 'Non-Current Liabilities', '17': 'Long - term financial debt', '18': 'Total Liabilities', '19': "Sto
ckholder's Equity", '20': 'Equity/Net Worth (Stockholders ’ Equity)', '21': 'Total liabilities and stockholders ’ equity', '22': None, '23': 'Retained Profits/Reserves/Provisions', '24
': 'Depreciation and Amortization', '25': 'Asset = Total Liabilities + Equity', '26': 'Tangible Net Worth'}, 'Unnamed: 1': {'0': None, '1': 23879229.26, '2': 35985658.17, '3': 67058822
.84, '4': 279279820.23, '5': None, '6': 28179061.26, '7': 19565189.57, '8': 50587177.82, '9': 329866998.05, '10': None, '11': None, '12': 36492709.23, '13': None, '14': 44464953.26, '1
5': 199317160.36, '16': None, '17': 49154190.77, '18': 262995839.31, '19': None, '20': 66871158.74, '21': 329866998.05, '22': None, '23': 7803718.05, '24': 21973498.18, '25': 1.0, '26'
: 47305969.17}, 'Unnamed: 2': {'0': None, '1': 48090267.32, '2': 22077626.89, '3': 40592268.16, '4': 196722868.47, '5': None, '6': 14578271.51, '7': 18660848.52, '8': 35873515.12, '9':
 232596383.59, '10': None, '11': None, '12': 21192290.38, '13': None, '14': 84756913.09, '15': 149917365.99, '16': None, '17': 19875299.55, '18': 169792665.54000002, '19': None, '20':
62803718.05, '21': 232596383.59, '22': None, '23': 31537486.52, '24': 18154261.78, '25': 1.0, '26': 44142869.53}, 'Unnamed: 3': {'0': None, '1': -0.503449854, '2': 0.6299604278, '3': 0
.6520097516, '4': 0.4196611833, '5': None, '6': 0.9329494063, '7': 0.0484619469, '8': 0.4101539158, '9': 0.4181948703, '10': None, '11': None, '12': 0.7219804266, '13': 0.0, '14': -0.4
753825778, '15': 0.3295134893, '16': None, '17': 1.4731295569, '18': 0.5489234383, '19': None, '20': 0.0647643295, '21': 0.4181948703, '22': None, '23': -0.7525573877, '24': 0.21037684
96, '25': None, '26': 0.0716559588}, 'Unnamed: 4': {'0': None, '1': 0.0723904768, '2': 0.1090914168, '3': 0.2032904875, '4': 0.8466437136, '5': None, '6': 0.0854255243, '7': 0.05931235
83, '8': 0.1533562864, '9': 1.0, '10': None, '11': None, '12': 0.1106285547, '13': 0.0, '14': 0.1347966105, '15': 0.6042349236, '16': None, '17': 0.1490121505, '18': 0.7972784209, '19'
: None, '20': 0.2027215791, '21': 1.0, '22': None, '23': 0.0236571651, '24': 0.0666132057, '25': None, '26': None}, 'Unnamed: 5': {'0': None, '1': 0.2067541489, '2': 0.0949181864, '3':
 0.1745180537, '4': 0.8457692481, '5': None, '6': 0.0626762604, '7': 0.0802284551, '8': 0.1542307519, '9': 1.0, '10': None, '11': None, '12': 0.0911118653, '13': 0.0, '14': 0.364394801
8, '15': 0.6445386797, '16': None, '17': 0.0854497359, '18': 0.7299884156, '19': None, '20': 0.2700115844, '21': 1.0, '22': None, '23': 0.1355888945, '24': 0.0780504903, '25': None, '2
6': None}}

aaron · Accepted Answer · 2021-04-25 15:34:53Z

The two functions that you need:

def _values_list(d):
    return list(d.values())

def _transpose(l):
    return list(zip(*l))

_values_list reduces a dictionary to a list of its values, e.g.:

{'0': 'Current Assests', '1': 'Cash and cash equivalents', ...}

to:

['Current Assests', 'Cash and cash equivalents', ...]

_transpose converts rows to columns, e.g.:

[['Current Assests', 'Cash and cash equivalents', ...], [None, 23879229.26, ...], ...]

to:

[('Current Assests', None, ...), ('Cash and cash equivalents', 23879229.26, ...), ...]

views.py:

def test_fin(request):
    data_test = json.loads(json_bss_test)
    test_data = []
    test_1 = _values_list(data_test['Assets'])      # Modified
    test_2 = _values_list(data_test['Unnamed: 1'])  # Modified
    test_3 = _values_list(data_test['Unnamed: 2'])  # Modified
    test_4 = _values_list(data_test['Unnamed: 3'])  # Modified
    test_5 = _values_list(data_test['Unnamed: 4'])  # Modified
    test_6 = _values_list(data_test['Unnamed: 5'])  # Modified

    test_data.append(test_1)
    test_data.append(test_2)
    test_data.append(test_3)
    test_data.append(test_4)
    test_data.append(test_5)
    test_data.append(test_6)
    test_data = _transpose(test_data)  # Added

    table_headers = []
    column_1_bss_header = data_bss_header["Unnamed: 1"]
    column_2_bss_header = data_bss_header["Unnamed: 2"]
    column_3_bss_header = data_bss_header["Unnamed: 3"]
    column_4_bss_header = data_bss_header["Unnamed: 4"]
    column_5_bss_header = data_bss_header["Unnamed: 5"]

    for i in range(0, 1):
        table_headers = [column_1_bss_header, column_2_bss_header, column_3_bss_header,
                         column_4_bss_header, column_5_bss_header]
    context = {
        'test_data': test_data,
        'table_headers': table_headers,

    }
    return render(request, 'fin_test.html', context)

templates/fin_test.html:

{% load mathfilters %}

<table id="basic-datatables" class="display table table-bordered">
  <thead style="background-color: #d9f6ef">
    <tr>
      <th>in Millions USD</th>
      {% for data in table_headers %}
      {% for x,y in data.items %}
      <th>{{ y }}</th>
      {% endfor %}
      {% endfor %}
    </tr>
  </thead>
  <tbody>
    <tr>
      <td colspan="{{ test_data.0|length }}" style="text-align:center">Assets</td>
    </tr>
    {% for data in test_data %}
    <tr>
      {% for v in data %}
      {% if forloop.first and data.1 is None %}
      <td colspan="{{ data|length }}" style="text-align:center">{{ v }}</td>
      {% elif forloop.first %}
      <td>{{ v }}</td>
      {% elif forloop.counter0 < 3 and v %}
      <td style="text-align:center">{{ v|div:1000|floatformat:"3g" }}</td>
      {% elif v %}
      <td style="text-align:center">{{ v|mul:100|floatformat:0 }}%</td>
      {% endif %}
      {% endfor %}
    </tr>
    {% endfor %}
  </tbody>
</table>

templatetags/mathfilters.py:

from django.template import Library

register = Library()

@register.filter
def div(value, arg):
    return value / arg

@register.filter
def mul(value, arg):
    return value * arg

Collectives™ on Stack Overflow

How to create a table from json data?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related