使用Python统计Pandas数据框行内重复值的累计重复次数

Alright, let's tackle this problem where we need to calculate the total number of duplicate occurrences (excluding the first instance of each value) for every row in a Pandas DataFrame, then add that total as a new column. Here's a step-by-step solution that matches your example requirements:

First, let's set up a sample DataFrame that aligns with your example scenario:

import pandas as pd

# Sample data matching your description
df = pd.DataFrame({
    'Col1': ['K1', 'K4', 'A3'],
    'Col2': ['K2', 'R2', 'B1'],
    'Col3': ['K1', 'R2', 'C2'],
    'Col4': ['K3', 'R2', 'D3']
})

Step 1: Define a Row-Wise Duplicate Count Function #

We need a function that takes a single row, counts how many times each value repeats (ignoring the first occurrence), and sums those repetitions. Using Pandas' value_counts() makes this straightforward:

def calculate_total_duplicates(row):
    # Get count of each value in the row (keep empty/NaN values if present)
    value_counts = row.value_counts(dropna=False)
    # For each value, subtract 1 to exclude the first occurrence, then sum all repetitions
    total_dupes = sum(count - 1 for count in value_counts)
    return total_dupes

Step 2: Apply the Function to All Rows #

Use df.apply() with axis=1 to run the function on every row, then assign the result to a new column:

df['total_duplicates'] = df.apply(calculate_total_duplicates, axis=1)

Step 3: Verify the Result #

Printing the DataFrame will show the expected output matching your example:

Col1 Col2 Col3 Col4  total_duplicates
0   K1   K2   K1   K3                  1
1   K4   R2   R2   R2                  3
2   A3   B1   C2   D3                  0

Optimized Version for Large Datasets #

If you're working with a massive DataFrame, using numpy.unique() can deliver better performance since it's optimized for array operations:

import numpy as np

def calculate_total_duplicates_fast(row):
    # Extract unique values and their occurrence counts from the row
    _, counts = np.unique(row.values, return_counts=True)
    # Sum the repetitions (count - 1) for all values
    return sum(counts - 1)

df['total_duplicates'] = df.apply(calculate_total_duplicates_fast, axis=1)

Key Notes #

• Use dropna=False in value_counts() if you want to count empty strings or NaN values as duplicates (remove it if you need to ignore these).
• The core logic works because count - 1 gives the number of times a value repeats after its first appearance—summing these values gives the total duplicate instances in the row.

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