留学生 DS 求职工具栈完全指南 2026：Pandas/SQL/Scikit-learn/Tableau 实战

1import pandas as pd
2import numpy as np
3
4# 处理缺失值
5df.isnull().sum()  # 统计每列缺失值数量
6df.fillna(df.mean(), inplace=True)  # 用均值填充数值列
7df.dropna(subset=['critical_column'], inplace=True)  # 删除关键列为空的行
8
9# 处理重复值
10df.duplicated().sum()  # 统计重复行数量
11df.drop_duplicates(subset=['user_id', 'date'], keep='last', inplace=True)
12
13# 处理异常值（IQR 方法）
14Q1 = df['amount'].quantile(0.25)
15Q3 = df['amount'].quantile(0.75)
16IQR = Q3 - Q1
17df = df[(df['amount'] >= Q1 - 1.5 * IQR) & (df['amount'] <= Q3 + 1.5 * IQR)]

1# 多级分组聚合
2result = df.groupby(['country', 'product_category']).agg({
3    'revenue': ['sum', 'mean', 'count'],
4    'user_id': 'nunique'
5}).reset_index()
6result.columns = ['country', 'category', 'total_revenue', 'avg_revenue', 'transactions', 'unique_users']
7
8# 滚动窗口计算（时间序列）
9df['7day_rolling_avg'] = df.groupby('user_id')['daily_spend'].transform(
10    lambda x: x.rolling(7, min_periods=1).mean()
11)

1# 计算每个月的用户留存率（Cohort Analysis）
2df['cohort_month'] = df.groupby('user_id')['signup_date'].transform('min').dt.to_period('M')
3df['activity_month'] = df['activity_date'].dt.to_period('M')
4df['months_since_signup'] = (df['activity_month'] - df['cohort_month']).apply(lambda x: x.n)
5
6cohort_data = df.groupby(['cohort_month', 'months_since_signup'])['user_id'].nunique().reset_index()
7cohort_pivot = cohort_data.pivot(index='cohort_month', columns='months_since_signup', values='user_id')
8cohort_size = cohort_pivot[0]
9retention = cohort_pivot.divide(cohort_size, axis=0)

1-- 计算每个用户的累计消费金额（Running Total）
2SELECT 
3    user_id,
4    transaction_date,
5    amount,
6    SUM(amount) OVER (PARTITION BY user_id ORDER BY transaction_date) AS running_total
7FROM transactions;
8
9-- 计算每个产品在同类别中的销售排名
10SELECT 
11    product_name,
12    category,
13    sales,
14    RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rank_in_category,
15    sales - LAG(sales) OVER (PARTITION BY category ORDER BY sales DESC) AS diff_from_prev
16FROM products;
17
18-- 计算用户连续登录天数（经典难题）
19WITH login_groups AS (
20    SELECT 
21        user_id,
22        login_date,
23        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn,
24        DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp_date
25    FROM (SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM logins) t
26)
27SELECT user_id, MIN(login_date) AS start_date, MAX(login_date) AS end_date, COUNT(*) AS streak
28FROM login_groups
29GROUP BY user_id, grp_date
30ORDER BY streak DESC;

1-- 找出从未购买过的用户（LEFT JOIN + NULL 过滤）
2SELECT u.user_id, u.email
3FROM users u
4LEFT JOIN orders o ON u.user_id = o.user_id
5WHERE o.user_id IS NULL;
6
7-- 找出每个部门薪资最高的员工（相关子查询）
8SELECT e.name, e.department, e.salary
9FROM employees e
10WHERE e.salary = (
11    SELECT MAX(salary) 
12    FROM employees 
13    WHERE department = e.department
14);

1from sklearn.pipeline import Pipeline
2from sklearn.preprocessing import StandardScaler, OneHotEncoder
3from sklearn.compose import ColumnTransformer
4from sklearn.ensemble import GradientBoostingClassifier
5from sklearn.model_selection import cross_val_score, GridSearchCV
6from sklearn.metrics import classification_report, roc_auc_score
7
8# 定义特征预处理 Pipeline
9numeric_features = ['age', 'income', 'tenure']
10categorical_features = ['country', 'product_type']
11
12preprocessor = ColumnTransformer([
13    ('num', StandardScaler(), numeric_features),
14    ('cat', OneHotEncoder(handle_unknown='ignore'), categorical_features)
15])
16
17# 完整 Pipeline（预处理 + 模型）
18pipeline = Pipeline([
19    ('preprocessor', preprocessor),
20    ('classifier', GradientBoostingClassifier(random_state=42))
21])
22
23# 交叉验证
24cv_scores = cross_val_score(pipeline, X_train, y_train, cv=5, scoring='roc_auc')
25print(f"CV AUC: {cv_scores.mean():.3f} ± {cv_scores.std():.3f}")
26
27# 超参数调优
28param_grid = {
29    'classifier__n_estimators': [100, 200],
30    'classifier__max_depth': [3, 5],
31    'classifier__learning_rate': [0.05, 0.1]
32}
33grid_search = GridSearchCV(pipeline, param_grid, cv=3, scoring='roc_auc', n_jobs=-1)
34grid_search.fit(X_train, y_train)

1# 对于不平衡数据集（如欺诈检测），使用 AUC-ROC 和 Precision-Recall
2from sklearn.metrics import roc_auc_score, average_precision_score, confusion_matrix
3
4y_pred_proba = pipeline.predict_proba(X_test)[:, 1]
5print(f"AUC-ROC: {roc_auc_score(y_test, y_pred_proba):.3f}")
6print(f"Average Precision: {average_precision_score(y_test, y_pred_proba):.3f}")
7print(classification_report(y_test, pipeline.predict(X_test)))

1import matplotlib.pyplot as plt
2import seaborn as sns
3import plotly.express as px
4
5# 用 Seaborn 绘制相关性热力图
6plt.figure(figsize=(10, 8))
7sns.heatmap(df.corr(), annot=True, fmt='.2f', cmap='coolwarm', center=0)
8plt.title('Feature Correlation Matrix')
9plt.tight_layout()
10
11# 用 Plotly 绘制交互式时间序列图
12fig = px.line(df, x='date', y='revenue', color='product_category',
13              title='Monthly Revenue by Product Category',
14              labels={'revenue': 'Revenue (£)', 'date': 'Month'})
15fig.show()