convert nested list of dictionaries to pandas dataframe

We first take the list of nested dictionary and extract the rows of data from it. Then we create another for loop to append the rows into the new list which was originally created empty. Finally we apply the DataFrames function in the pandas library to create the Data Frame.,Many times python will receive data from various sources which can be in different formats like csv, JSON etc which can be converted to python list or dictionaries etc. But to apply the calculations or analysis using packages like pandas, we need to convert this data into a dataframes. In this article we will see how we can convert a given python list whose elements are a nested dictionary, into a pandas Datframe.,We can also apply the pivot_table function to re-organize the data the way we want.,We make use of First and third party cookies to improve our user experience. By using this website, you agree with our Cookies Policy. Agree Learn more

Example

import pandas as pd

# Given nested dictionary
list = [{
      "Fruit": [{
            "Price": 15.2,
            "Quality": "A"
         },
         {
            "Price": 19,
            "Quality": "B"
         },
         {
            "Price": 17.8,
            "Quality": "C"
         },
      ],
      "Name": "Orange"
   },
   {
      "Fruit": [{
            "Price": 23.2,
            "Quality": "A"
         },
         {
            "Price": 28,
            "Quality": "B"
         }
      ],
      "Name": "Grapes"
   }
]

rows = []

# Getting rows
for data in list:
   data_row = data['Fruit']
n = data['Name']

for row in data_row:
   row['Name'] = n
rows.append(row)

# Convert to data frame
df = pd.DataFrame(rows)
print(df)

Output

Price Quality Name
0 15.2 A Orange
1 19.0 B Orange
2 17.8 C Orange
3 23.2 A Grapes
4 28.0 B Grapes

Suggestion : 2

You will need to flatten your input using itertools.chain, and you can then call the pd.DataFrame constructor.

from itertools
import chain
pd.DataFrame(list(chain.from_iterable(data)))

gender name roll_no
0 male tom 1234
1 male sam 1212
2 female kavi 1235
3 female maha 1211

Suggestion : 3

Last Updated : 14 May, 2020

Output:

            Name Maths Physics Chemistry
            0 Chunky Pandey 89 80 NaN
            1 Paras Jain 90 99 97

Suggestion : 4

Convert a List of Dictionaries to a Pandas DataFrame,Setting an Index When Converting a List of Dictionaries to a Pandas DataFrame,How to convert a list of dictionaries to a Pandas DataFrame,Reading Only Some Columns When Converting a List of Dictionaries to a Pandas DataFrame

Each dictionary will represent a record in the DataFrame, while the keys become the columns. Let’s take a look at an example where each dictionary contains every key:

# Converting a List of Dictionaries to a DataFrame
import pandas as pd

list_of_dicts = [{
      'Name': 'Nik',
      'Age': 33,
      'Location': 'Toronto'
   },
   {
      'Name': 'Kate',
      'Age': 32,
      'Location': 'London'
   },
   {
      'Name': 'Evan',
      'Age': 36,
      'Location': 'London'
   }
]

df = pd.DataFrame(list_of_dicts)

print(df)

# Returns:
   # Name Age Location
# 0 Nik 33 Toronto
# 1 Kate 32 London
# 2 Evan 36 London

Because each dictionary in the list contains the same keys, we’re able to use a number of different methods to accomplish this. The other following methods would also work:

# These methods all produce the same result
df = pd.DataFrame(list_of_dicts)
df = pd.DataFrame.from_dict(list_of_dicts)
df = pd.DataFrame.from_records(list_of_dicts)

Let’s now take a look at a more complex example. In the example below, we’ll provide dictionaries where one dictionary will be missing a key. Let’s use the .from_dict() method to read the list to see how the data will be read:

# Reading Dictionaries with Missing Keys
import pandas as pd

list_of_dicts = [{
      'Name': 'Nik',
      'Age': 33,
      'Location': 'Toronto'
   },
   {
      'Name': 'Kate',
      'Age': 32,
      'Location': 'London'
   },
   {
      'Name': 'Evan',
      'Age': 36
   }
]

df = pd.DataFrame.from_dict(list_of_dicts)

print(df)

# Returns:
   # Name Age Location
# 0 Nik 33 Toronto
# 1 Kate 32 London
# 2 Evan 36 NaN

Let’s see how this is done in Pandas:

# Setting an index when reading a list of dictionaries
import pandas as pd

list_of_dicts = [{
      'Name': 'Nik',
      'Age': 33,
      'Location': 'Toronto'
   },
   {
      'Name': 'Kate',
      'Age': 32,
      'Location': 'London'
   },
   {
      'Name': 'Evan',
      'Age': 36,
      'Location': 'New York'
   }
]

df = pd.DataFrame.from_records(list_of_dicts, index = ['Employee_001', 'Employee_002', 'Employee_003'])
# Same as: df = pd.DataFrame(list_of_dicts, index = ['Employee_001', 'Employee_002', 'Employee_003'])

print(df)

# Returns:
   # Name Age Location
# Employee_001 Nik 33 Toronto
# Employee_002 Kate 32 London
# Employee_003 Evan 36 New York

Let’s read our data and use the 'Name' column as the index:

# Setting a column as an index
import pandas as pd

list_of_dicts = [{
      'Name': 'Nik',
      'Age': 33,
      'Location': 'Toronto'
   },
   {
      'Name': 'Kate',
      'Age': 32,
      'Location': 'London'
   },
   {
      'Name': 'Evan',
      'Age': 36,
      'Location': 'New York'
   }
]

df = pd.DataFrame(list_of_dicts).set_index('Name')
# Same as: df = pd.DataFrame.from_dict(list_of_dicts).set_index('Name')
# Same as: df = pd.DataFrame.from_records(list_of_dicts).set_index('Name')

print(df)

# Returns:
   # Age Location
# Name
# Nik 33 Toronto
# Kate 32 London
# Evan 36 New York

Suggestion : 5

The pd.DataFrame.from_dict() takes a dictionary as an argument and converts it into dataFrame. It creates a dataframe that has default orientation which is columns that mean keys of the dictionary is used as columns of dataframe and values as an index. We can change the orientation to ‘index ‘ which means index used as columns to dataframe and key as columns.,In pd.DataFrame.from_dict() method we have passed List of dictionaries as an argument and the default orientation is columns that mean dictionary keys will be used as columns and values as an index.,We are converting nested dictionary Student_dict. By using pd.DataFrame.from_dict() method into a data frame by specifying the orientation by orient=’Index’.The default orientation is ‘columns’ which means the key will be used as a column.,The Student_dict has a list of values corresponding to a single key. We are passing a dictionary as an argument to the dataframe constructor and specified the index too. The created data frame will have dictionary keys as columns and values as indexes.

import pandas as pd

Student_dict = {
   'Jack': 100,
   'Rack': 100,
   'Max': 100,
   'David': 100

}

dict_to_list = list(Student_dict.items())
dict_to_df = pd.DataFrame(dict_to_list, columns = ['Name', 'Marks'], index = ['Row_1', 'Row_2', 'Row_3', 'Row_4'])

print(dict_to_df)

        Name Marks
        Row_1 Jack 100
        Row_2 Rack 100
        Row_3 Max 100
        Row_4 David 100

import pandas as pd

Student_dict = {
   'Name': ['Jack', 'Rack', 'Max', 'David'],
   'Marks': [100, 100, 100, 100],
   'Subject': ['Math', 'Math', 'Music', 'Physic']
}

dict_to_df = pd.DataFrame(Student_dict, index = ['Row_1', 'Row_2', 'Row_3', 'Row_4'])

print(dict_to_df)

        Name Marks Subject
        Row_1 Jack 100 Math
        Row_2 Rack 100 Math
        Row_3 Max 100 Music
        Row_4 David 100 Physic

import pandas as pd

Student_dict = {
   'student1': {
      'Name': 'Jack',
      'Mark': 100,
      'Subject': 'Math'
   },
   'student2': {
      'Name': 'Rack',
      'Mark': 100,
      'Subject': 'Math'
   },
   'student3': {
      'Name': 'Max',
      'Mark': 100,
      'Subject': 'Music'
   },
   'student4': {
      'Name': 'David',
      'Mark': 100,
      'Subject': 'Math'
   }

}

dict_to_df = pd.DataFrame(Student_dict)

print(dict_to_df.transpose())

          Name Mark Subject
          student1 Jack 100 Math
          student2 Rack 100 Math
          student3 Max 100 Music
          student4 David 100 Math

Suggestion : 6

You will need to flatten your input using itertools.chain, and you can then call the pd.DataFrame constructor.,Convert nested list of dictionaries to pandas DataFrame,How can I convert a list of dictionaries and nested lists into a pandas Dataframe for use in a markdown table generator?,How to convert a List of lists with nested dictionaries and lists within each list to a pandas dataframe

You will need to flatten your input using itertools.chain, and you can then call the pd.DataFrame constructor.

from itertools
import chain
pd.DataFrame(list(chain.from_iterable(data)))

gender name roll_no
0 male tom 1234
1 male sam 1212
2 female kavi 1235
3 female maha 1211