Wrong way to display JSON to dataframe

Question

I have a JSON with this shape:

{
  "txs": [
    {
      "time": 1593748984,
      "size": 668,
      "input": [
        {
          "value": 75000000000,
          "address": "************************"
        },
        {
          "value": 6530483896,
          "address": "***********************"
        }
      ]
    },
    {
      "time": 1593748374,
      "size": 72470,
      "input": [
        {
          "value": 714488220,
          "address": "************************"
        },
        {
          "value": 700000000,
          "address": "******************************"
        },
        {
          "value": 571794323,
          "address": "*******************************"
        },
        {
          "value": 554727196,
          "address": "*********************************"
  ...
  }

I would like a dataframe of this shape with multiindex time,size:

time           size     value            address  
1593748984     668      75000000000      *********************
                        6530483896       ***********************
1593748374     72470    714488220        ****************************
                        700000000        *******************************
                        571794323        *****************************
                        554727196        ***************************
...

I tried some code to flatten the JSON and put it into a dataframe but could not do it. I tried the following code:

def flatten_json_iterative_solution(dictionary):
    def unpack(parent_key, parent_value): 
        if isinstance(parent_value, dict):
            for key, value in parent_value.items():
                temp1 = parent_key + '_' + key
                yield temp1, value
        elif isinstance(parent_value, list):
            i = 0 
            for value in parent_value:
                temp2 = parent_key + '_'+str(i) 
                i += 1
                yield temp2, value
        else:
            yield parent_key, parent_value    

        dictionary = dict(chain.from_iterable(starmap(unpack, dictionary.items())))
        if not any(isinstance(value, dict) for value in dictionary.values()) and \
           not any(isinstance(value, list) for value in dictionary.values()):
            break

    return dictionary

frame = p.to_json(indent=2, sort_keys=False)
df = pd.Series(flatten_json_iterative_solution(dict(frame))).to_frame().reset_index()

Error:

 df = pd.Series(flatten_json_iterative_solution(dict(frame))).to_frame().reset_index()

ValueError: dictionary update sequence element #0 has length 1; 2 is required

user_12833166 · Answer

You can use pd.read_json to read a json string then initialize a new dataframe from the txs list in this json, then using DataFrame.explode explode the dataframe on column input, finally using Series.str.get extract the values associated with the keys value and address from input column:

df1 = pd.DataFrame(pd.read_json(json_str)['txs'].tolist()).explode('input')
df1 = (
    df1.assign(
        value=df1['input'].str.get('value'),
        address=df1['input'].str.get('address'))
        .drop('input', 1).set_index(['time', 'size'])
)

Result:

# print(df1)
                        value                            address
time       size                                                 
1593748984 668    75000000000           ************************
           668     6530483896            ***********************
1593748374 72470    714488220           ************************
           72470    700000000     ******************************
           72470    571794323    *******************************
           72470    554727196  *********************************

Wrong way to display JSON to dataframe

1 Answers