基于Pandas Apply/Loc实现客户统一new_status的技术问询

Hey there! Let's work through how to get your desired new_status field set up correctly. The core rule here is super clear: only mark new_status as 'canceled' if every single subscription for that customer is in 'canceled' status—otherwise, leave it blank.

Step 1: Identify customers with all canceled subscriptions #

First, we need a way to check for each customer whether all their Status entries are 'canceled'. We can do this with a groupby operation:

# Create a lookup: key = Customer, value = True if all Status are 'canceled'
all_canceled_customers = df.groupby('Customer')['Status'].apply(lambda x: all(x == 'canceled'))

This gives us a Series where each customer maps to a boolean value indicating if all their subscriptions are canceled.

Step 2: Update new_status using .loc (cleanest approach) #

Now we can use .loc to target rows where the customer falls into the "all canceled" group, and set their new_status to 'canceled':

# Initialize new_status as empty strings first (optional but clean)
df['new_status'] = ''
# Update only the rows where the customer has all canceled subscriptions
df.loc[df['Customer'].isin(all_canceled_customers[all_canceled_customers].index), 'new_status'] = 'canceled'

Alternative: Using .apply #

If you prefer using .apply, you can define a helper function and apply it row-wise:

def determine_new_status(row, customer_check):
    # Return 'canceled' if all of the customer's subscriptions are canceled, else empty string
    return 'canceled' if customer_check[row['Customer']] else ''

# Apply the function to each row
df['new_status'] = df.apply(lambda row: determine_new_status(row, all_canceled_customers), axis=1)

Result Verification #

After running either of these approaches, your dataframe will match exactly what you're looking for:

Quick note: Your existing duplicated column doesn't affect this logic—we don't need it for the status check, so you can keep it as-is without any issues.

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