I have a file with this structure (it comes from a json): I need to iterate through it for getting a dataframe like this: I'm trying to do it with this code: But I'm getting a dataframe with 3 columns: document, highlights and text_match like this: . I guess the solution is not very difficult but I'm having problems about

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

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

Answer

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

Advertisement

Answer