如何在Pandas中筛选高频出发国与目的地生成热力图？

Got it, let's walk through how to add that filtering step to your workflow. You already have the core data transformation down with melt() and pivot_table()—we just need to narrow down to the top X most frequent origins and destinations before (or after) building your heatmap. Let's use your X=2 example to make it concrete.

Step 1: Define your target X and get top origins/destinations #

First, let's calculate which origins and destinations are the most common using the melted dataframe (df1 you already created):

X = 2  # Your desired number of top entries

# Get the top X most frequent origins
top_origins = df1['origin'].value_counts().head(X).index.tolist()

# Get the top X most frequent destinations (from the merged 'value' column)
top_destinations = df1['value'].value_counts().head(X).index.tolist()

For your sample data, this would give:

• top_origins = ['UK', 'Germany'] (since UK appears twice, others once)
• top_destinations = ['UK', 'China'] (both appear 3 times across all destination columns)

Step 2: Filter your data and rebuild the pivot table #

You can either filter the melted data first (cleaner, especially for large datasets) or filter the existing pivot table:

Option 1: Filter melted data first (recommended)

# Keep only rows where origin is in top_origins AND destination is in top_destinations
df_filtered = df1[(df1['origin'].isin(top_origins)) & (df1['value'].isin(top_destinations))]

# Rebuild the pivot table with filtered data (fill 0 for missing combinations)
df_heatmap_filtered = df_filtered.pivot_table(
    index='origin',
    columns='value',
    aggfunc='count',
    fill_value=0
)

Option 2: Filter existing pivot table

If you already have df_heatmap built, you can directly slice it using the top lists:

# Slice rows (origins) and columns (destinations), fill NaNs with 0
df_heatmap_filtered = df_heatmap.loc[top_origins, top_destinations].fillna(0)

Step 3: Visualize the filtered heatmap #

Now you can plot your focused heatmap as usual. For example, with seaborn:

import seaborn as sns
import matplotlib.pyplot as plt

sns.heatmap(df_heatmap_filtered, annot=True, cmap='YlGnBu')
plt.title(f'Top {X} Origins vs Top {X} Destinations')
plt.show()

Bonus: Handling ties #

If you want to include all origins/destinations that tie for the X-th spot (instead of just the first X), adjust the top lists like this:

# For origins: include all with count >= the X-th highest count
origin_counts = df1['origin'].value_counts()
top_origins = origin_counts[origin_counts >= origin_counts.iloc[X-1]].index.tolist()

# Same for destinations
dest_counts = df1['value'].value_counts()
top_destinations = dest_counts[dest_counts >= dest_counts.iloc[X-1]].index.tolist()

This ensures you don't exclude entries that have the same frequency as the X-th top entry.

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