코호트 유지율 표

import pandas as pd

df = pd.read_csv('user_events.csv', parse_dates=['event_date'])
print(df.dtypes)
print(df.head())

cohort_week = df.groupby('user_id')['event_date'].min().dt.to_period('W').rename('cohort_week')
df = df.join(cohort_week, on='user_id')

df['event_week'] = df['event_date'].dt.to_period('W')

print(df[['user_id', 'event_date', 'cohort_week', 'event_week']].head())

df['week_number'] = (df['event_week'] - df['cohort_week']).apply(lambda x: x.n)

print(df[['user_id', 'cohort_week', 'event_week', 'week_number']].head(10))

retention_counts = (
    df.groupby(['cohort_week', 'week_number'])['user_id']
    .nunique()
    .reset_index(name='active_users')
)

print(retention_counts.head(10))

retention_matrix = retention_counts.pivot_table(
    index='cohort_week',
    columns='week_number',
    values='active_users',
    aggfunc='sum'
).fillna(0)

print(retention_matrix.iloc[:5, :8])

cohort_sizes = retention_matrix[0]
retention_pct = retention_matrix.divide(cohort_sizes, axis=0) * 100
retention_pct = retention_pct.round(1)

print(retention_pct.iloc[:5, :8])

# Average retention rate per week number across all cohorts
avg_retention = retention_pct.mean(axis=0)
print('Average retention by week:')
print(avg_retention.round(1))

if 4 in retention_pct.columns:
    week4 = retention_pct[4].sort_values(ascending=False)
    print('Cohorts ranked by week-4 retention:')
    print(week4)

import seaborn as sns
import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(14, 6))
sns.heatmap(retention_pct.iloc[:, :13],
            annot=True, fmt='.0f',
            cmap='RdYlGn', vmin=0, vmax=100,
            ax=ax, linewidths=0.5)
ax.set_title('Weekly Cohort Retention (%)')
ax.set_xlabel('Week Number')
ax.set_ylabel('Cohort Week')
plt.tight_layout()
plt.show()

avg_ret = retention_pct.mean(axis=0)
std_ret = retention_pct.std(axis=0)

fig, ax = plt.subplots(figsize=(10, 5))
ax.plot(avg_ret.index, avg_ret.values, color='steelblue', linewidth=2, marker='o')
ax.fill_between(avg_ret.index, avg_ret - std_ret, avg_ret + std_ret, alpha=0.2)
ax.set_xlabel('Week Number')
ax.set_ylabel('Retention %')
ax.set_title('Average Cohort Retention Curve')
plt.tight_layout()
plt.show()

styled = retention_pct.style.background_gradient(cmap='RdYlGn', vmin=0, vmax=100)
styled.to_excel('retention_table.xlsx', engine='openpyxl')
print('Retention table saved to retention_table.xlsx')