Python Pandas代码优化求助：ES数据转DataFrame性能优化

Hey there! Let's spruce up that Elasticsearch-to-Pandas code of yours. Your current approach gets the job done, but we can make it cleaner, more Pythonic, and less error-prone. First off, I noticed a potential bug in your original code: you're accessing unmtchd_ESdata['avg'] directly in the loop, but those values should be coming from each individual bucket in your aggregations. Let's fix that and optimize at the same time.

Option 1: Replace manual index loops with list comprehensions #

Using range(len(...)) to iterate is a bit dated. Instead, we can loop directly over the buckets list and use list comprehensions to build our data lists quickly:

# Extract the buckets first to clean up repeated code
buckets = unmtchd_ESdata['aggregations']['filtered']['POSCode']['buckets']

# Build lists with list comprehensions (way cleaner than manual appends!)
avg_values = [bucket['avg'] for bucket in buckets]
key_values = [bucket['key'] for bucket in buckets]

# Create the DataFrame in one go
mkt_df = pd.DataFrame({
    "market_avg_total_sales_count": avg_values,
    "POS_Code": key_values  # Replace with your actual column name for the 'key' field
})

Option 2: Directly convert buckets to DataFrame (most efficient) #

We don't even need to create intermediate lists! We can turn the buckets list straight into a DataFrame, then just rename and select the columns we need:

buckets = unmtchd_ESdata['aggregations']['filtered']['POSCode']['buckets']

# Convert buckets to DataFrame, then clean up columns
mkt_df = pd.DataFrame(buckets)[['key', 'avg']].rename(columns={
    'key': 'POS_Code',
    'avg': 'market_avg_total_sales_count'
})

Bonus: Add robustness with safe access #

If there's any chance your Elasticsearch response might be missing fields (e.g., empty aggregations), use get() to avoid KeyErrors and handle missing data gracefully:

# Safely extract buckets with fallbacks for missing fields
buckets = unmtchd_ESdata.get('aggregations', {}).get('filtered', {}).get('POSCode', {}).get('buckets', [])

# Use get() again to handle buckets missing 'avg' or 'key'
avg_values = [bucket.get('avg', None) for bucket in buckets]
key_values = [bucket.get('key', None) for bucket in buckets]

mkt_df = pd.DataFrame({
    "market_avg_total_sales_count": avg_values,
    "POS_Code": key_values
})

All these changes make your code shorter, easier to read, and less likely to break if your ES response structure has minor variations.

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