Explode nested JSON to Dataframe

Question

There are loads of answers on this topic, but for the life of me I cannot a solution to my issue.

Say I have a JSON like

json_2_explode = [{'scalar': '43',
  'units': 'm',
  'parameter': [{'no_1': '45',
    'no_2': '1038',
    'no_3': '356'}],
  'name': 'Foo'},
 {'scalar': '54.1',
  'units': 's',
  'parameter': [{'no_1': '78',
    'no_2': '103',
    'no_3': '356'}],
  'name': 'Yoo'},
 {'scalar': '1123.1',
  'units': 'Hz',
  'parameter': [{'no_1': '21',
    'no_2': '43',
    'no_3': '3577'}],
  'name': 'Baz'}]

documenting some readings for attributes Foo, Yoo and Baz. For each I detail a number, that is, the value itself, some parameters, and the name.

Say this JSON is a column in a dataframe,

df = pd.DataFrame(data = {'col1': [11, 9, 23, 1],
                         'col2': [7, 3, 1, 12],
                         'col_json' : [json_2_explode,
                                       json_2_explode,
                                       json_2_explode,
                                       json_2_explode]}, index=[0, 1, 2, 3])

    col1    col2    col_json
0   11      7        [{'scalar': '43', 'units': 'MPa', 'parameter':...
1   9       3        [{'scalar': '43', 'units': 'MPa', 'parameter':...
2   23      1        [{'scalar': '43', 'units': 'MPa', 'parameter':...
3   1       12       [{'scalar': '43', 'units': 'MPa', 'parameter':...

The issue I have is that if I try

df = pd.json_normalize(df['col_json'].explode())

I get

    scalar  units            parameter                          name
0   43      m   [{'no_1': '45', 'no_2': '1038', 'no_3': '356'}] Foo
1   54.1    s   [{'no_1': '78', 'no_2': '103', 'no_3': '356'}]  Yoo
2   1123.1  Hz  [{'no_1': '21', 'no_2': '43', 'no_3': '3577'}]  Baz
3   43      m   [{'no_1': '45', 'no_2': '1038', 'no_3': '356'}] Foo
4   54.1    s   [{'no_1': '78', 'no_2': '103', 'no_3': '356'}]  Yoo
5   1123.1  Hz  [{'no_1': '21', 'no_2': '43', 'no_3': '3577'}]  Baz
6   43      m   [{'no_1': '45', 'no_2': '1038', 'no_3': '356'}] Foo
7   54.1    s   [{'no_1': '78', 'no_2': '103', 'no_3': '356'}]  Yoo
8   1123.1  Hz  [{'no_1': '21', 'no_2': '43', 'no_3': '3577'}]  Baz
9   43      m   [{'no_1': '45', 'no_2': '1038', 'no_3': '356'}] Foo
10  54.1    s   [{'no_1': '78', 'no_2': '103', 'no_3': '356'}]  Yoo
11  1123.1  Hz  [{'no_1': '21', 'no_2': '43', 'no_3': '3577'}]  Baz

So it is exploding each JSON in 3 rows (admitteldy each JSON does contain 3 sub-dicts, so to say).
I actually would like Foo, Yoo and Baz to be documented in the same row, adding columns. Is there maybe solution not involving manually manipulating rows/piercing it as desired? I would love to see one of your fancy one-liners, thanks so much for your help

Answer 1

json_normalize may work after adding prefixes, I didn't try but this puts all your data on one line.

from flatten_json import flatten

prefix_json_2_explode = {}
for d in json_2_explode:
    prefix_json_2_explode.update({d['name'] + '_' + key: value for key, value in d.items()})
print(prefix_json_2_explode)
dict_flattened = (flatten(d, '.') for d in [prefix_json_2_explode])
df = pd.DataFrame(dict_flattened)
df

  Foo_scalar Foo_units Foo_parameter.0.no_1 Foo_parameter.0.no_2  \
0         43         m                   45                 1038   

  Foo_parameter.0.no_3 Foo_name Yoo_scalar Yoo_units Yoo_parameter.0.no_1  \
0                  356      Foo       54.1         s                   78   

  Yoo_parameter.0.no_2 Yoo_parameter.0.no_3 Yoo_name Baz_scalar Baz_units  \
0                  103                  356      Yoo     1123.1        Hz   

  Baz_parameter.0.no_1 Baz_parameter.0.no_2 Baz_parameter.0.no_3 Baz_name  
0                   21                   43                 3577      Baz