从Mongo加载JSON到Python Dataframe及扁平化时NaN处理与报错解决

Hey, let's tackle your two questions one by one—they're super common when working with MongoDB and pandas, so I've got you covered!

MongoDB stores missing values as null, which pandas automatically converts to NaN when you load data into a DataFrame. But if you want to customize how these are handled, here are a few practical approaches:

• Post-load replacement with pandas: Once you've converted your MongoDB cursor to a DataFrame, use fillna() to replace NaNs with values that make sense for your use case:

  import pandas as pd
  from pymongo import MongoClient
  
  # Connect to MongoDB and fetch data
  client = MongoClient()
  data = list(client.your_db.your_collection.find())
  df = pd.DataFrame(data)
  
  # Replace all NaNs with 0 (or use a string like "N/A" for text fields)
  df = df.fillna(0)
  
  # Or target specific columns only
  df["user_email"] = df["user_email"].fillna("no_email_provided")
  

• Pre-load replacement with MongoDB aggregation: If you want to handle nulls before fetching data, use the $replaceNull stage in an aggregation pipeline to swap out nulls for a default value:

  pipeline = [
      {
          "$replaceNull": {"with": "N/A", "path": "$your_target_field"}
      }
  ]
  data = list(client.your_db.your_collection.aggregate(pipeline))
  df = pd.DataFrame(data)
  

This error happens because some cells in your nested field are NaN (float type) instead of dictionaries—so when your code tries to access .keys() on a NaN, it crashes. Here are the best fixes, ordered by simplicity:

Option 1: Use pandas' json_normalize with errors='ignore' #

Pandas built-in json_normalize has a parameter that handles exactly this scenario. It will skip flattening NaN values instead of throwing an error:

from pandas import json_normalize

# data is the list of documents from MongoDB
df = json_normalize(data, errors='ignore')

This is the quickest solution for most standard nested structures.

Option 2: Custom Flattening Function with NaN Checks #

If your nested data has a weird or non-standard structure, write a recursive function that checks for NaNs before trying to access dictionary keys:

import pandas as pd

def flatten_nested_dict(d, parent_key='', sep='_'):
    items = []
    # Exit early if we hit a NaN
    if pd.isna(d):
        return {parent_key: d}
    for k, v in d.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            items.extend(flatten_nested_dict(v, new_key, sep=sep).items())
        else:
            items.append((new_key, v))
    return dict(items)

# Apply the function to each document
flattened_data = [flatten_nested_dict(doc) for doc in data]
df = pd.DataFrame(flattened_data)

Option 3: Replace NaNs with Empty Dictionaries First #

Before flattening, swap out any NaN values in your nested fields with empty dictionaries. This way, your flattening code will have a valid dictionary to work with:

for doc in data:
    # Replace NaN in 'nested_field' with empty dict
    if pd.isna(doc.get('nested_field')):
        doc['nested_field'] = {}

# Now run your original flattening code without errors

内容的提问来源于stack exchange，提问作者Phil409