如何为Pandas中行数大于1的分组分配组ID

Got it, let's work through this problem to get exactly the output you want! We need to filter our DataFrame to keep only groups (based on columns a, b, c) that have more than 1 row, then assign unique sequential IDs to these groups. If the filtered DataFrame ends up empty, we'll set all id values to -1.

Step-by-Step Implementation #

Here's a complete, working solution that matches your expected output:

import pandas as pd

# Your sample input DataFrame
df = pd.DataFrame({
    'a': [10, 10, 20, 30, 30, 30],
    'b': [2017, 2017, 2018, 2017, 2017, 2017],
    'c': [20.0, 20.0, 10.0, 11.0, 11.0, 11.0],
    'd': [231, 223, 113, 134, 112, 111]
})

# 1. Group by target columns and filter groups with >1 row
grouped = df.groupby(['a', 'b', 'c'])
df_filtered = grouped.filter(lambda x: len(x) > 1)

# 2. Assign group IDs (handle empty filtered DataFrame case)
if df_filtered.empty:
    df_filtered['id'] = -1
else:
    # Use ngroup() to get sequential IDs (starts at 0, so add 1 to match your example)
    df_filtered['id'] = df_filtered.groupby(['a', 'b', 'c']).ngroup() + 1

print(df_filtered)

Output #

Running this code will produce exactly the result you're expecting:

a     b     c    d  id
0  10  2017  20.0  231   1
1  10  2017  20.0  223   1
3  30  2017  11.0  134   2
4  30  2017  11.0  112   2
5  30  2017  11.0  111   2

Alternative Approach (Using factorize) #

If you prefer another method, you can use factorize on tuples of the group columns to generate IDs:

else:
    # Convert group columns to tuples, then factorize to get unique IDs
    df_filtered['id'] = df_filtered[['a', 'b', 'c']].apply(tuple, axis=1).factorize()[0] + 1

This works because factorize() maps each unique tuple of (a,b,c) to a unique integer, which we then increment by 1 to start counting from 1 instead of 0.

Key Notes #

• The filter() method keeps all rows from groups that meet the condition (len(x) > 1), which excludes single-row groups like the (20,2018,10.0) group in your sample.
• Checking df_filtered.empty ensures we handle edge cases where no groups have more than 1 row, setting all id values to -1 as required.

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