Skip to main content

Python Pandas: How to Fix ValueError: Mixing dicts with non-Series may lead to ambiguous ordering

When attempting to load JSON data or construct a Pandas DataFrame from a Python dictionary, you might encounter the ValueError: Mixing dicts with non-Series may lead to ambiguous ordering. This error typically arises when the structure of your input dictionary (often derived from a nested JSON object) is not directly translatable into a 2D tabular DataFrame because it contains a mix of list-like data (which could form columns) alongside other dictionaries or scalar values at the same level. Pandas struggles to determine a clear order or structure for the columns in such cases.

This guide will thoroughly explain why this ValueError occurs, demonstrate scenarios with problematic JSON structures, and provide clear solutions, focusing on how to correctly target the list of records within your nested data for DataFrame creation or leverage the powerful pandas.json_normalize() function for flattening more complex JSON.

Understanding the Error: Ambiguity in DataFrame Construction​

The pandas.DataFrame() constructor expects data in a format it can readily interpret as a 2D table. Common valid inputs include:

  • A dictionary of lists, arrays, or Series (where keys are column names).
  • A list of dictionaries (where each dictionary is a row).

The "Mixing dicts with non-Series" error occurs when you pass a dictionary where some values are list-like (and could form DataFrame columns or rows), but other values at the same level are plain dictionaries or scalar values that are not Pandas Series. Pandas then faces ambiguity:

  • How should these "non-Series" dicts or scalars be incorporated?
  • What should the column order be if some potential columns are defined by list-like data and others are implied by keys of nested dicts?

This ambiguity in determining column structure and order triggers the ValueError.

Reproducing the Error: A Common Nested JSON Structure​

Consider the following example.json file, which is a common pattern for API responses or structured data: example.json:

example.json
{
  "request_info": {
    "status_code": 200,
    "message": "Data retrieved successfully"
  },
  "data_records": [
    {"id": "A101", "product_name": "Laptop", "quantity": 10},
    {"id": "B202", "product_name": "Mouse", "quantity": 50},
    {"id": "C303", "product_name": "Keyboard", "quantity": 25}
  ]
}

If you load this JSON and try to create a DataFrame from the entire loaded dictionary directly:

import pandas as pd
import json

file_path = 'example.json' # Assume the JSON content above is in this file

with open(file_path, 'r') as f:
    raw_data_dict = json.load(f)

print("Raw loaded dictionary:")
print(raw_data_dict)
print()

try:
    # ⛔️ Incorrect: Passing the entire nested dictionary to pd.DataFrame()
    df_error = pd.DataFrame(raw_data_dict)
    print(df_error)
except ValueError as e:
    print(f"Error: {e}")

Output:

Raw loaded dictionary:
{'request_info': {'status_code': 200, 'message': 'Data retrieved successfully'}, 'data_records': [{'id': 'A101', 'product_name': 'Laptop', 'quantity': 10}, {'id': 'B202', 'product_name': 'Mouse', 'quantity': 50}, {'id': 'C303', 'product_name': 'Keyboard', 'quantity': 25}]}

Error: Mixing dicts with non-Series may lead to ambiguous ordering.
note

The top-level dictionary raw_data_dict contains request_info (a dict) and data_records (a list of dicts). Pandas cannot directly resolve this mixed structure into a single DataFrame without guidance.

Solution 1: Access the Key Containing the List of Records​

Often, the actual tabular data you want in your DataFrame is nested under a specific key within the JSON/dictionary.

Identifying the Tabular Data​

By inspecting raw_data_dict (printed above), we can see that the list of records suitable for a DataFrame is under the key 'data_records'.

Creating the DataFrame from the Specific Key​

Access this key before passing it to pd.DataFrame():

import pandas as pd
import json

file_path = 'example.json' # Assume the JSON content above is in this file

with open(file_path, 'r') as f:
    raw_data_dict = json.load(f)

# ✅ Correct: Access the 'data_records' key which holds the list of dictionaries
df_correct_key = pd.DataFrame(raw_data_dict['data_records'])

print("DataFrame created from 'data_records' key:")
print(df_correct_key)

Output:

DataFrame created from 'data_records' key:
     id product_name  quantity
0  A101       Laptop        10
1  B202        Mouse        50
2  C303     Keyboard        25

This works because raw_data_dict['data_records'] is a list of dictionaries, a format that pd.DataFrame() understands perfectly (each dictionary becomes a row).

Best Practice: Using with open() for File Handling​

The with open(...) as f: syntax is recommended for reading files because it ensures the file is automatically closed, even if errors occur.

Solution 2: Using pandas.json_normalize() for Flattening JSON​

For more complex nested JSON, or when you want to flatten parts of the structure, pandas.json_normalize() is a very powerful tool.

Basic json_normalize() with record_path​

The record_path argument tells json_normalize where to find the list of records that should form the main rows of the DataFrame.

import pandas as pd
import json

file_path = 'example.json' # Assume the JSON content above is in this file

with open(file_path, 'r') as f:
    raw_data_dict = json.load(f)

# ✅ Using json_normalize with record_path
# 'data_records' is the path to the list of records we want to tabulate
df_normalized = pd.json_normalize(raw_data_dict, record_path='data_records')

print("DataFrame created using json_normalize(record_path='data_records'):")
print(df_normalized)

Output:

DataFrame created using json_normalize(record_path='data_records'):
     id product_name  quantity
0  A101       Laptop        10
1  B202        Mouse        50
2  C303     Keyboard        25

json_normalize() by Pre-selecting the Record List​

You can also pass the already-extracted list of records to json_normalize(), though using record_path is often cleaner if you also need to extract meta-data (see next section).

import pandas as pd
import json

file_path = 'example.json' # Assume the JSON content above is in this file

with open(file_path, 'r') as f:
    raw_data_dict = json.load(f)

# ✅ Using json_normalize on the pre-selected list of records
df_normalized_preselected = pd.json_normalize(raw_data_dict['data_records'])

print("DataFrame created using json_normalize(data['data_records']):")
print(df_normalized_preselected)

Output:

DataFrame created using json_normalize(data['data_records']):
     id product_name  quantity
0  A101       Laptop        10
1  B202        Mouse        50
2  C303     Keyboard        25

Bonus: Incorporating Meta-Data from Other JSON Properties​

Often, the JSON contains meta-data (like the request_info in our example) that you might want to include in your DataFrame, perhaps repeated for each record. json_normalize() can help with this using the meta argument, or you can use DataFrame.assign().

Using assign() after normalizing the main records:​

This is often simpler if the meta-data is a flat dictionary whose keys you want as new columns.

import pandas as pd
import json

file_path = 'example.json' # Assume the JSON content above is in this file

with open(file_path, 'r') as f:
    raw_data_dict = json.load(f)

# Normalize the main records
df_records_part = pd.json_normalize(raw_data_dict, record_path='data_records')

# Add columns from the 'request_info' dictionary
# The ** operator unpacks the dictionary into keyword arguments for assign
df_with_meta = df_records_part.assign(**raw_data_dict['request_info'])

print("DataFrame with records and added meta-data using assign():")
print(df_with_meta)

Output:

DataFrame with records and added meta-data using assign():
     id product_name  quantity  status_code                      message
0  A101       Laptop        10          200  Data retrieved successfully
1  B202        Mouse        50          200  Data retrieved successfully
2  C303     Keyboard        25          200  Data retrieved successfully

Using meta in json_normalize():​

The meta argument allows you to specify paths to other scalar or list values in the JSON that should be included. If the meta item is a dictionary itself, you'd need to specify paths to its individual elements.

import pandas as pd
import json

file_path = 'example.json' # Assume the JSON content above is in this file

with open(file_path, 'r') as f:
    raw_data_dict = json.load(f)


# Specifying paths to individual items within 'request_info'
df_normalized_with_meta = pd.json_normalize(
    raw_data_dict,
    record_path='data_records',
    meta=[
        ['request_info', 'status_code'], # Path to status_code
        ['request_info', 'message']      # Path to message
    ]
    # You can rename them too:
    # meta=[
    #     ['request_info', 'status_code'],
    #     ['request_info', 'message'],
    # ],
    # meta_prefix='req_' # or use record_prefix, or rename columns later
)
# To rename columns created from meta:
df_normalized_with_meta.rename(columns={
    'request_info.status_code': 'status_code', # Default name from meta path
    'request_info.message': 'message'
    }, inplace=True)


print("DataFrame with records and meta-data using json_normalize(meta=...):")
print(df_normalized_with_meta)

Output:

DataFrame with records and meta-data using json_normalize(meta=...):
     id product_name  quantity status_code                      message
0  A101       Laptop        10         200  Data retrieved successfully
1  B202        Mouse        50         200  Data retrieved successfully
2  C303     Keyboard        25         200  Data retrieved successfully
note

For simple cases like adding all keys from a flat dictionary as new columns, the .assign(**dict) method is often more concise.

Conclusion​

The ValueError: Mixing dicts with non-Series may lead to ambiguous ordering in Pandas is a clear sign that the input dictionary's top-level structure is not directly suitable for pd.DataFrame() construction due to a mix of list-like data and other non-Series dictionary values. The primary solutions are:

  1. Identify and select the specific key within your loaded dictionary that holds the list of records (or column-oriented data) and pass that part to pd.DataFrame().
  2. Utilize pd.json_normalize(), especially with the record_path argument, to intelligently flatten your nested JSON/dictionary structure into a tabular DataFrame. This is particularly powerful for complex JSON.

By inspecting your JSON structure and choosing the appropriate method, you can effectively parse even complex nested data into a clean Pandas DataFrame.

Table of Contents