如何用Python Pandas补全产品功能状态缺失记录？

Got it, let's tackle this problem step by step using Pandas. The core idea is to first build a full set of all possible (id, feature) combinations, then merge in your existing status records, and finally fill any missing status values with no_contact. Here's a complete, reproducible implementation:

Step 1: Simulate Your Input Data #

First, let's recreate sample data matching your scenario (you can replace this with your actual imported DataFrames):

import pandas as pd

# all_ids corresponds to table2: full list of IDs with some feature entries
all_ids = pd.DataFrame({
    'id': ['id1', 'id2', 'id3', 'id3'],
    'feature': ['login', 'pay', 'login', 'profile']
})

# has_status corresponds to table1: existing feature status records
has_status = pd.DataFrame({
    'xml_id': ['id1', 'id1', 'id2'],
    'feature': ['login', 'pay', 'login'],
    'status': ['completed', 'in_progress', 'completed']
})

# Optional: table3 with full feature list (use this if available)
table3 = pd.DataFrame({'feature': ['login', 'pay', 'profile', 'notification']})

Step 2: Generate Full Feature List #

First, we need the complete set of features. Prioritize table3 if you have it; otherwise, use the unique features from all_ids:

# Get full feature list
if 'table3' in locals():
    full_features = table3['feature'].unique()
else:
    full_features = all_ids['feature'].unique()

# Get all unique IDs from all_ids
unique_ids = all_ids['id'].unique()

Step 3: Create Full (ID, Feature) Combinations #

We need every ID paired with every feature—this is a Cartesian product to ensure no pair is missed:

# Generate all possible (id, feature) pairs
full_combinations = pd.MultiIndex.from_product(
    [unique_ids, full_features],
    names=['id', 'feature']
).to_frame(index=False)

Step 4: Merge Existing Status & Fill Missing Values #

Now merge in your existing status data, then fill any missing status entries with no_contact:

# Rename xml_id to id for matching, then merge with full combinations
result = full_combinations.merge(
    has_status.rename(columns={'xml_id': 'id'}),
    on=['id', 'feature'],
    how='left'
)

# Fill missing status values with 'no_contact'
result['status'] = result['status'].fillna('no_contact')

Step 5: Verify the Result #

Let's check the output, which should match your expected full tracking table:

print(result)

Sample Output: #

id       feature        status
0   id1         login      completed
1   id1           pay    in_progress
2   id1       profile    no_contact
3   id1  notification    no_contact
4   id2         login      completed
5   id2           pay    no_contact
6   id2       profile    no_contact
7   id2  notification    no_contact
8   id3         login    no_contact
9   id3           pay    no_contact
10  id3       profile    no_contact
11  id3  notification    no_contact

Key Explanations #

• Cartesian Product: Ensures we don't miss any ID-feature pair, which fixes the "missing unimplemented feature records" issue you were facing.
• Left Merge: Preserves all rows from the full combinations, pulling in only matching existing statuses from table1.
• Fillna: Explicitly sets no_contact for any pair that doesn't have an existing status entry, making your final table fully complete.

This approach gives you full visibility into every feature's status for every ID, and it's easy to validate counts since you can directly cross-check the length of full_combinations against your final result.

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