向量化统计目标字符串在DataFrame两列中的出现次数

Hey there! I get what you're trying to do—you want to efficiently count how many times each ID from df2 shows up in either the ID_a or ID_b columns of df1, using vectorized operations (no slow loops!). Let's break this down step by step.

The Approach #

Instead of iterating through each ID in df2 and checking both columns, we can leverage pandas' built-in vectorized functions to do this in a few clean steps:

• Combine the ID_a and ID_b columns from df1 into a single long series of IDs.
• Count the total occurrences of each ID in this combined series.
• Map these counts back to df2, filling in 0 for any IDs that don't appear in df1.

Full Code Implementation #

Here's the code that does exactly this, using your existing data setup:

import pandas as pd
import numpy as np

# Your original data construction
np.random.seed(42)
names = ['jack', 'jill', 'jane', 'joe', 'ben', 'beatrice']
df1 = pd.DataFrame({'ID_a':np.random.choice(names, 20), 'ID_b':np.random.choice(names,20)})
df2 = pd.DataFrame({'ID':names})

# Step 1: Combine both ID columns and count occurrences
id_counts = pd.concat([df1['ID_a'], df1['ID_b']]).value_counts()

# Step 2: Map counts to df2, fill missing IDs with 0
df2['total_occurrences'] = df2['ID'].map(id_counts).fillna(0).astype(int)

print(df2)

Sample Output #

With the seed set to 42, you'll get a result like this:

ID  total_occurrences
0     jack                  4
1     jill                  5
2     jane                  3
3      joe                  6
4      ben                  8
5  beatrice                  4

Why This Works #

• Vectorized Efficiency: pd.concat and value_counts are optimized functions that run in C under the hood, making them way faster than any Python loop—especially critical if you're working with large datasets.
• Clean Mapping: Using map lets us efficiently link the counts from our combined series back to df2's IDs, and fillna(0) ensures we don't have missing values for IDs that never show up in df1.

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