工作中客户流失分析：基于现有DataFrame创建多级索引数据帧

Got it, let's break this down step by step for your customer churn analysis. First, let's clarify the sample data structure you shared (I'll clean up the column names a bit for clarity):

CustomerID | Lifetime | Cohort  | Monthly_Pay | Ltv_Rev | Sub     | Pln_strt   | Plan_Cancel
-----------|----------|---------|-------------|---------|---------|------------|------------
fgvghc     | 10       | 2010-5  | 14.99       | 150     | 2010-5  | 2010-5-3   | 2011-5-3
dhsdjk     | 2        | 2010-5  | 14.99       | 179     | 2010-5  | 2010-5-9   | 2010-7-8
5uk0ez     | 3        | 2010-6  | 5.99        | 18      | 2010-6  | 2010-6-4   | ...

For churn/retention analysis, the most logical multi-index levels are usually Cohort (the month your customer first joined) and another time-based column like Sub (subscription start month) or Plan_Cancel (churn date). Here's how to build it:

1. Create the Multi-Index DataFrame #

Use pandas' set_index() method, passing a list of the two columns you want as index levels. I'll use Cohort and Sub as an example (swap them for Plan_Cancel if you want to index by churn date instead):

# First, clean up column names if needed (fix the typo in "pln Can")
df.rename(columns={'pln Can': 'Plan_Cancel'}, inplace=True)

# Build the multi-index
multi_index_df = df.set_index(['Cohort', 'Sub']).sort_index()

# Check the result
multi_index_df.head()

The sort_index() step is optional but highly recommended—it makes filtering and grouping much smoother later on.

2. Use the Multi-Index for Retention Analysis #

Now you can easily slice and aggregate data to analyze retention:

Example 1: Calculate average lifetime per cohort #

# Group by the top-level index (Cohort) and compute average Lifetime
cohort_avg_lifetime = multi_index_df.groupby(level='Cohort')['Lifetime'].mean()
print(cohort_avg_lifetime)

Example 2: Analyze churn rate for a specific cohort #

# Slice all users in the 2010-5 cohort
cohort_201005 = multi_index_df.loc['2010-5']

# Calculate churn rate (users with a non-null Plan_Cancel date)
churn_rate = cohort_201005[cohort_201005['Plan_Cancel'].notna()].shape[0] / cohort_201005.shape[0]
print(f"Churn rate for 2010-5 cohort: {churn_rate:.2%}")

Example 3: Filter users in a specific cohort and subscription month #

# Get users from 2010-5 cohort who started their subscription in 2010-5
target_users = multi_index_df.loc[('2010-5', '2010-5')]

Key Notes #

• Adjust the index columns based on your specific analysis goal: use Cohort + Plan_Cancel if you want to focus on churn timing, or Cohort + Monthly_Pay if you want to segment by pricing tier.
• Multi-indexes work great with pandas' xs() method too, which lets you cross-section data at a specific index level without dropping the other levels:

  # Get all users across all cohorts who started in 2010-5
  sub_201005_users = multi_index_df.xs('2010-5', level='Sub')
  

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