Iterate in a dictionary inside a list of json and get a dataframe-CodePudding

I have a file with this structure (it comes from a json):

lista = [{'document': {'amount': 20,
   'name': 'Peter',
   'city': 'Madrid'},
  'highlights': [],
  'text_match': 100},
 {'document': {'amount': 15,
   'name': 'Angel',
   'city': 'Barcelona'},
  'highlights': [],
  'text_match': 100},
 {'document': {'amount': 10,
   'name': 'Louis',
   'city': 'London'},
   'highlights': [],
   'text_match': 100}]

I need to iterate through it for getting a dataframe like this:

     amount   name     city
0    20       Peter    Madrid
1    15       Angel    Barcelona
2    10       Louis    London

I'm trying to do it with this code:

res = []
for p in lista:
    res.append(p)
df = pd.DataFrame(res)

But I'm getting a dataframe with 3 columns: document, highlights and text_match like this:

    document                                            highlights  text_match
0   {'amount': 20, 'name': 'Peter', 'city': 'Madrid'}   []          100
1   {'amount': 15, 'name': 'Angel', 'city': 'Barce...   []          100
2   {'amount': 10, 'name': 'Louis', 'city': 'London'}   []          100

. I guess the solution is not very difficult but I'm having problems about manage the iteration in lists/dictionarys

CodePudding user response：

Just edit the for - loop to append not the whole p but p['document']:

res = []
for p in lista:
    res.append(p['document'])
df = pd.DataFrame(res)

Hope that helped

CodePudding user response：

Try:

df = pd.DataFrame([d["document"] for d in lista])
print(df)

Prints:

   amount   name       city
0      20  Peter     Madrid
1      15  Angel  Barcelona
2      10  Louis     London