Combine List Into Dataframe

Question

Bear with me as I'm new to python. I'm iterating through data and getting multiple lists that I need to combine into one pandas dataframe.

I then need to add headers so I can perform calcs.

Problem is each item is being treated as a separate entity. Please help

for r in rows:
  person_info = list()
  person_info.append(r['metadata']['name'])
  person_info.append(r['metadata']['CountryId'])
  person_info.append(r['metadata']['StateId'])
  person_info.append(r['metadata']['Income'])
  print(person_info)

Here's the output:

['mike' , 1, 4, 20000]
['mary', 2, 5, 30000]
['jane', 3, 6, 40000]

here's the desired output as a dataframe with the headers "name", "id_a", "id_b", and "income":

name    id_a    id_b    income
mike    1       4       20000
mary    2       5       30000
jane    3       6       40000

Answer 1

Pandas accepts a list of dictionaries directly. Don't fight this, you can simply extract i['metadata'] for each item in your list.

Your only task thereafter is to rename and sort columns.

r = [{'metadata': {'name': 'mike', 'CountryId': 1, 'StateId': 4, 'Income': 20000}},
     {'metadata': {'name': 'mary', 'CountryId': 2, 'StateId': 5, 'Income': 30000}},
     {'metadata': {'name': 'jane', 'CountryId': 3, 'StateId': 6, 'Income': 40000}}]

df = pd.DataFrame([i['metadata'] for i in r])\
       .rename(columns={'CountryId': 'id_a', 'StateId': 'id_b', 'Income': 'income'})\
       .reindex(['name', 'id_a', 'id_b', 'income'], axis=1)

print(df)

   name  id_a  id_b  income
0  mike     1     4   20000
1  mary     2     5   30000
2  jane     3     6   40000