How to convert list of dict of list of dict with same key as pandas dataframe-CodePudding

I have a json response like this -

{
  "returnedRowCount":2,
  "columns":[
    {"name":"type","type":"TEXT","index":0},
    {"name":"type","type":"TEXT","index":1},
    {"name":"dept","type":"TEXT","index":2}],
  "rows":[
    {"row":[{"v":"type1"},{"v":"name1"},{"v":"dept1"}]},
    {"row":[{"v":"type2"},{"v":"name2"},{"v":"dept2"}]}
    ]
}

Need a way to convert to dataframe like below -

    type name dept
 0  type1 name1  dept1 
 1  type2 name2  dept1

I believe this requires comprehension to convert to dict first but not able to figure it out

CodePudding user response：

Yes, you can extract column names and row data using list comprehensions:

import pandas as pd

j = {
  "returnedRowCount":2,
  "columns":[
    {"name":"type","type":"TEXT","index":0},
    {"name":"name","type":"TEXT","index":1},
    {"name":"dept","type":"TEXT","index":2}],
  "rows":[
    {"row":[{"v":"type1"},{"v":"name1"},{"v":"dept1"}]},
    {"row":[{"v":"type2"},{"v":"name2"},{"v":"dept2"}]}
    ]
}

data = [[c['v'] for c in r] for r in [row['row'] for row in j['rows']]]
columns = [c['name'] for c in j['columns']]
df = pd.DataFrame(data, columns=columns)

Result:

    type   name   dept
0  type1  name1  dept1
1  type2  name2  dept2

CodePudding user response：

You could also do:

data = [[vals['v'] for vals in dat['row']] for dat in j['rows']]
pd.DataFrame(data, columns = pd.DataFrame(j['columns'])['name'].tolist())

    type   name   dept
0  type1  name1  dept1
1  type2  name2  dept2