Creating a multi-header table from JSON

Question

I'm new to pandas. I'm trying to create the following table:

        |          C         |          Perl      |
        |   line   |   func  |    line   |   func |
version |  covered |  total  |  covered  |  total |

There is the following structure of JSON that I have created:

{
   "version1":{
      "perl":{
         "line_covered":207,
         "line_total":312,
         "func_covered":15,
         "func_total":18
      },
      "C":{
         "line_covered":321,
         "line_total":512,
         "func_covered":10,
         "func_total":10
      }
   }
}

I want to iterate over this JSON and create the table. My first problem is that I can't seem to understand how should I create the header of the table. From previous topics, I found that I should use MultiIndex but for some reason, any combination I tried, does not create the wanted header. Is it possible to show me how to create this table?

ansev · Accepted Answer · 2020-03-17T21:15:41.153

2

My approach

import numpy as np
from collections import defaultdict
d = defaultdict(list)

for k,v in my_dict.items():
    d['index'].append(k)
    for k1,v1 in v.items():
        for k2,v2 in v1.items():
            d['columns'].append((k1,k2))
            d['data'].append(v2)
d = dict(d)
d['data'] = np.array(d['data']).reshape(1, len(d['data']))
d['columns'] = pd.MultiIndex.from_tuples(columns)

Build DataFrame

pd.DataFrame(**d)

Output

                 perl                                               C                       
         line_covered line_total func_covered func_total func_covered           func_total   
version1          207        312           15         18           10  version1         10

use defaultdict but could just start three lists before starting the loop

EDIT

for expected output use

import numpy as np
from collections import defaultdict
d = defaultdict(list)
for k,v in my_dict.items():
    d['index'].append(k)
    for k1,v1 in v.items():
        for k2,v2 in v1.items():
            split = k2.split('_')
            d['columns'].append((k1, split[0]))
            d['data'].append(split[1])
d = dict(d)
d['data'] = np.array(d['data']).reshape(1,len(d['data']))
d['columns'] = pd.MultiIndex.from_tuples(d['columns']).copy()
pd.DataFrame(**d)

Output

             perl                               C       
             line   line     func   func     func   func
version1  covered  total  covered  total  covered  total

Details

print(d)
#{'index': ['version1'], 'columns': MultiIndex([('perl', 'line_covered'),
#            ('perl',   'line_total'),
#            ('perl', 'func_covered'),
#            ('perl',   'func_total'),
#            (   'C', 'func_covered'),
#            (   'C',   'func_total')],
#           ), 'data': array([[207, 312,  15,  18,  10,  10]])}

you can see what** do

edited Mar 17 '20 at 21:15

answered Mar 17 '20 at 20:38

ansev

28,746
5
11
29

numpy library, you need import numpy as np, – ansev Mar 17 '20 at 20:49
I see. For some reason I get `ValueError: cannot reshape array of size 8 into shape (1,1)` on line `d['data'] = np.array(d['data']).reshape(1,len(data))` – vesii Mar 17 '20 at 20:58
upps sorry you need `.reshape(1,len(d['data'])` I use `data` and I forgot remove it. Solution update – ansev Mar 17 '20 at 21:01
Thanks! I tried to print `d` but I get `{'index': ['version1'], 'columns': MultiIndex(levels=[['C', 'perl'], ['func', 'line']], labels=[[1, 1, 1, 1, 0, 0, 0, 0], [0, 0, 1, 1, 0, 0, 1, 1]]), 'data': array([['covered', 'total', 'covered', 'total', 'covered', 'total', 'covered', 'total']], dtype=' – vesii Mar 17 '20 at 21:09
Your are welcome :), We use `**` in order to pass arguments to `pd.DataFrame` object, you can see : https://stackoverflow.com/questions/36901/what-does-double-star-asterisk-and-star-asterisk-do-for-parameters , you can check : `dict(**d) == d` is True – ansev Mar 17 '20 at 21:11
Yes, I understand that. But the result, does not show numbers at all. On the third line you get: `version covered total covered total covered total covered total` – vesii Mar 17 '20 at 21:13
but if you want to show the values you must use the first code – ansev Mar 17 '20 at 21:15
Oh sorry! I though it was the same code. what is `columns` in the first code? – vesii Mar 17 '20 at 21:18
check `df.columns` where df is your dataframe – ansev Mar 17 '20 at 21:23

Creating a multi-header table from JSON

1 Answers1

Linked