Pandas无迭代筛选存在永久地点变更的人员数据

Great question—handling pattern-based filtering on a 60M-row dataset with 700k unique users demands vectorized, non-iterative operations to avoid crippling performance bottlenecks. Let's break down a fast, scalable solution that avoids row-wise iteration.

Core Logic Recap #

We need to retain rows for people who:

1. Switched places at least once (their place column has exactly 2 unique values: home and office), AND
2. Never switched back after their final place change (i.e., their place sequence is strictly monotonic—either all home → all office, or all office → all home, no back-and-forth).

Scalable Solution (No Row-wise Iteration) #

The key insight here is that a "permanent switch" sequence is monotonic when mapped to numerical values. We can leverage pandas' optimized groupby operations to check this without iterating over each person's rows.

Step-by-Step Implementation #

import pandas as pd

# Sample data (matches your example)
d = pd.DataFrame({
    'place': ['home', 'home', 'home', 'home', 'office', 'office', 'office', 'home', 'office', 'home', 'office', 'home', 'office', 'home'],
    'person': ['a', 'a', 'a', 'a', 'a', 'a', 'a', 'b', 'b', 'c', 'c', 'c', 'c', 'c'],
    'other_stuff': ['f', 'g', 'd', 'q', 'w', 'r', 's', 't', 'u', 'v', 'w', 'l', 'm', 'n']
})

# 1. Map place to numerical values for monotonicity checks
d['place_num'] = d['place'].map({'home': 0, 'office': 1})

# 2. Aggregate per person to validate our two conditions:
#    - Has exactly 2 unique places (switched at least once)
#    - Place sequence is monotonic (no back-and-forth after switch)
valid_persons = d.groupby('person').agg(
    has_two_unique_places=('place', lambda g: g.nunique() == 2),
    is_monotonic_sequence=('place_num', lambda g: g.is_monotonic_increasing or g.is_monotonic_decreasing)
).query('has_two_unique_places & is_monotonic_sequence').index

# 3. Filter the original DataFrame to keep only valid persons
result = d[d['person'].isin(valid_persons)].drop(columns='place_num')

# Show the result
print(result)

Output #

place other_stuff person
0     home           f      a
1     home           g      a
2     home           d      a
3     home           q      a
4   office           w      a
5   office           r      a
6   office           s      a
7     home           t      b
8   office           u      b

Why This Works for Large Datasets #

• No row-wise iteration: All operations use pandas' optimized groupby aggregation, which runs in C-backed code rather than Python loops.
• Minimal memory overhead: We only create one temporary column (place_num) and a small Series of valid persons (700k rows max).
• Fast checks: is_monotonic_increasing/is_monotonic_decreasing are optimized pandas methods that check sequence order in O(n) time per group, with minimal Python overhead.

Alternative Optimized Approach (No Numerical Mapping) #

If you prefer not to map places to numbers, you can check for the absence of reverse switches directly:

# Calculate first/last place per person
d['first_place'] = d.groupby('person')['place'].transform('first')
d['last_place'] = d.groupby('person')['place'].transform('last')

# Condition 1: Switched at least once (first != last)
mask_switch = d['first_place'] != d['last_place']

# Find first occurrence of the final place per person
first_final_place_idx = d.groupby('person').apply(lambda g: g[g['place'] == g['last_place'].iloc[0]].index[0])
d['first_final_idx'] = d['person'].map(first_final_place_idx.to_dict())

# Condition 2: No return to first place after the final switch
has_return = d.groupby('person').apply(lambda g: ((g.index > g['first_final_idx'].iloc[0]) & (g['place'] == g['first_place'].iloc[0])).any())
mask_no_return = ~d['person'].map(has_return.to_dict())

# Combine masks and filter
result = d[mask_switch & mask_no_return].drop(columns=['first_place', 'last_place', 'first_final_idx'])

This approach avoids numerical mapping but uses two groupby apply calls—still far faster than row-wise iteration for large datasets.

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