China Universities:
ETL, EDA, Visualize
& ML Model.
Dive into the China Universities dataset on Kaggle. We’ll walk through the full data-science pipeline — ETL (extract, transform, load), EDA with seaborn, visualization, and ML modeling with four scikit-learn regressors — to predict the score of 582 Chinese universities. The pipeline auto-detects the problem type and trains RandomForestRegressor, DecisionTreeRegressor, KNeighborsRegressor, and LinearRegression side-by-side. Every line of code is explained for beginners — with copy buttons on every block.
import pandas as pd
from sklearn.ensemble import RandomForestRegressor
from sklearn.pipeline import Pipeline
# Auto-detect problem type, train 4 models,
# pick the best by R-squared.
pipe = Pipeline([
('preprocessor', preprocessor),
('model', RandomForestRegressor(random_state=42))
])
pipe.fit(X_train, y_train)Six steps from raw CSV
to a trained ML model.
The complete pipeline for the China Universities ETL, EDA, Visualize & Model project. Click any step to jump straight to its code and beginner-friendly explanation.
ETL — Load Data
Walk the Kaggle input directory, locate universities.csv, and load it with UTF-8 / latin1 fallback.
Audit & Clean
Inspect dtypes, missing values, duplicates. Parse dates, drop duplicate rows, build a clean copy.
EDA & Visualization
Describe the dataset, plot numeric histograms, correlation heatmap, and top categorical bars.
Feature Engineering
Extract year/month/dayofweek from dates, compute text length and word counts from object columns.
Preprocessor & Pipeline
Build a ColumnTransformer (median + scaler for numeric, mode + onehot for categorical) wrapped in a Pipeline.
Train & Evaluate Models
Train 4 regressors, sort by R², pick the best, plot actual-vs-predicted and residuals.
Walk the Kaggle input dir with os.walk.
Kaggle mounts every dataset attachment under /kaggle/input. We walk that tree, find universities.csv, and load it with a UTF-8 → latin1 fallback so non-ASCII bytes never crash the pipeline.
What is ETL? ETL stands for Extract, Transform, Load. In this first step we only extract: we locate the raw CSV file on disk and pull it into a pandas DataFrame. Steps 2 and 3 handle transform; the DataFrame itself acts as our in-memory load target. ETL is the standard name for any data-ingestion pipeline — even when the destination is just RAM.
Why os.walk? Kaggle notebooks mount datasets under unpredictable nested paths like /kaggle/input/datasets/imadcharradi/china-universities/universities.csv. Walking the tree and matching the filename is far more robust than hard-coding the full path — your code keeps working if Kaggle changes its folder layout.
Why the encoding fallback? The universities dataset contains Chinese characters in the name_zh and tags columns. Most files are UTF-8, but some Kaggle re-uploads use latin1. The try/except UnicodeDecodeError pattern gives you a one-line safety net so the notebook never crashes mid-run.
import os main_csv_local_path = 'universities.csv' DATA_DIR = '' for dirname, _, filenames in os.walk('/kaggle/input'): for filename in filenames: if filename == main_csv_local_path: DATA_DIR = os.path.join(dirname, filename) print(DATA_DIR) # /kaggle/input/datasets/imadcharradi/china-universities/universities.csv import pandas as pd # Load with encoding fallback (UTF-8 → latin1) try: df = pd.read_csv(DATA_DIR, encoding='utf-8') except UnicodeDecodeError: df = pd.read_csv(DATA_DIR, encoding='latin1') print("--- DATA LOADED ---") print(f"Dataset Shape: {df.shape}")Audit dtypes, missing values,
then clean a copy.
Before touching the data, we audit it: print dtypes, count missing values per column, count duplicate rows, and split columns into numeric vs categorical. Then we build a clean copy and auto-parse any column that looks like a date.
Why audit first? If you start transforming a DataFrame without knowing which columns have missing values or weird dtypes, you’ll hit cryptic errors later. The audit step prints three things: dtypes (are columns numeric, text, or datetime?), missing-value counts (which columns need imputation?), and duplicate rows (do we need to deduplicate?).
The China Universities audit results: only 2 numeric columns (national_rank and score), 14 categorical columns, and 0 duplicate rows. uni_type, languages_of_instruction, and website each have 570 missing values (out of 582) — these columns are sparse and will need imputation in the preprocessor.
Why df.copy()? pandas DataFrame operations can be in-place or return new objects. Always copy before transforming so the original df stays available for reference. drop_duplicates().reset_index(drop=True) removes duplicate rows and rebuilds a clean integer index.
Date auto-parse: the for loop tries pd.to_datetime on every object column. If more than 50% of values successfully convert, we replace the column with its datetime version. This catches dates hidden inside text columns without manual inspection.
import numpy as np # --- AUDIT --- print("\n--- DATA AUDIT ---") print("Data Types:") print(df.dtypes) print("\nMissing Values:") print(df.isnull().sum()) print(f"\nDuplicates: {df.duplicated().sum()}") num_cols_init = df.select_dtypes(include=[np.number]).columns.tolist() cat_cols_init = df.select_dtypes(include=['object', 'category', 'bool']).columns.tolist() print(f"Numeric Columns ({len(num_cols_init)}): {num_cols_init}") print(f"Categorical Columns ({len(cat_cols_init)}): {cat_cols_init}") # --- CLEAN --- df_clean = df.copy() df_clean = df_clean.drop_duplicates().reset_index(drop=True) # Auto-parse potential date columns for col in df_clean.columns: if df_clean[col].dtype == 'object': try: converted = pd.to_datetime(df_clean[col], errors='coerce') if converted.notnull().sum() > 0.5 * len(df_clean): df_clean[col] = converted except Exception: passSummarize, then plot histograms,
heatmaps & bar charts.
EDA (Exploratory Data Analysis) is where you actually look at the data. We print a transposed describe summary, then plot three things: numeric histograms, a correlation heatmap, and bar charts of the top-10 values in the first categorical columns.
Why visualize? Numbers in a table don’t reveal patterns; pictures do. A histogram shows whether score is skewed (it is — most universities cluster between 113 and 235, with a long tail up to 1087). A correlation heatmap shows whether two numeric columns move together. A bar chart shows which categories dominate.
describe(include='all').T: the include='all' argument forces pandas to describe both numeric and categorical columns in the same table. The .T transposes the result so each row is a column and each column is a statistic — much easier to read on a wide screen.
Three guarded plots: each plot is wrapped in if len(num_cols_clean) > 0: so the notebook never crashes if the dataset has zero numeric columns. We plot only the first 4 numeric columns and first 2 categorical columns to keep the output compact on a 582-row dataset.
Key insight from this dataset: national_rank and score are the only numeric columns. They’re strongly correlated (high-rank universities have high scores), which is exactly what our regression model will learn in Step 6.
import matplotlib.pyplot as plt import seaborn as sns sns.set_theme(style="whitegrid") plt.rcParams['figure.figsize'] = (10, 6) # Compact EDA summary print("--- EDA SUMMARY ---") print(df_clean.describe(include='all').T) num_cols_clean = df_clean.select_dtypes(include=[np.number]).columns.tolist() # 1) Numeric histograms (first 4 columns) if len(num_cols_clean) > 0: plt.figure(figsize=(8, 5)) df_clean[num_cols_clean[:4]].hist(bins=20, layout=(2, 2)) plt.tight_layout() plt.suptitle("Numeric Feature Distributions", y=1.02) plt.show() # 2) Correlation heatmap if len(num_cols_clean) > 1: plt.figure(figsize=(8, 6)) corr = df_clean[num_cols_clean].corr() sns.heatmap(corr, annot=True, cmap='coolwarm', fmt='.2f', square=True) plt.title("Correlation Matrix") plt.show() # 3) Top values per categorical column (first 2) cat_cols_clean = df_clean.select_dtypes(include=['object', 'category']).columns.tolist() if len(cat_cols_clean) > 0: for c in cat_cols_clean[:2]: plt.figure(figsize=(8, 4)) top_cats = df_clean[c].value_counts().head(10).reset_index() top_cats.columns = [c, 'count'] sns.barplot(data=top_cats, x='count', y=c, hue=c, palette='viridis', legend=False) plt.title(f"Top Values in {c}") plt.tight_layout() plt.show()Derive new numeric features
from dates & text.
Raw text and datetime columns can’t feed a regression model directly. We extract numeric features — length, word count, year, month, day-of-week — that capture useful signal without exploding the column count.
Why engineer features? Scikit-learn models only understand numbers. A column like website = "https://www.tsinghua.edu.cn/en/" is meaningless to a regressor — but its length (31 characters) and word count (1) are numeric signals the model can use.
Datetime features: we use df_clean[dcol].dt.year, .dt.month, and .dt.dayofweek to break any datetime column into three integer columns. These are widely used in time-series forecasting and seasonality analysis.
Text features: for every object column with more than 1 unique value and fewer unique values than rows (i.e., not an identifier like slug), we add __len (string length) and __words (whitespace-split count). The cardinality guard skips ID-like columns such as slug_aliases if every value is unique.
Result: the cleaned DataFrame grows from 16 to 36 columns. The 20 new engineered features give the regression model many more numeric signals to learn from.
# Set the regression target TARGET_COL = 'score' if 'score' in df_clean.columns else None # --- Feature Engineering --- # A) Datetime → year / month / dayofweek datetime_cols = df_clean.select_dtypes(include=['datetime', 'datetime64']).columns.tolist() for dcol in datetime_cols: df_clean[f"{dcol}__year"] = df_clean[dcol].dt.year df_clean[f"{dcol}__month"] = df_clean[dcol].dt.month df_clean[f"{dcol}__dayofweek"] = df_clean[dcol].dt.dayofweek # B) Text → length + word count (skip id-like high-cardinality cols) text_cols = df_clean.select_dtypes(include=['object']).columns.tolist() for tcol in text_cols: if df_clean[tcol].nunique() > 1 and df_clean[tcol].nunique() < len(df_clean): df_clean[f"{tcol}__len"] = df_clean[tcol].astype(str).str.len() df_clean[f"{tcol}__words"] = df_clean[tcol].astype(str).apply(lambda x: len(x.split()))Build a ColumnTransformer
wrapped in a Pipeline.
scikit-learn's ColumnTransformer applies different preprocessing to numeric vs categorical columns. Wrapping it in a Pipeline together with the model means we can fit and predict in one call — no manual transform steps, no leakage from test set into training.
Why two transformers? Numeric and categorical columns need opposite treatment. Numeric columns want median imputation (fill missing values with the median) followed by standardization (subtract mean, divide by std). Categorical columns want most-frequent imputation followed by one-hot encoding (turn each category into a 0/1 column). ColumnTransformer applies each transformer to the right subset of columns and joins the results.
Why median, not mean? Median is robust to outliers. If one university has a score of 1087 while most are around 170, the mean is pulled up but the median stays representative. Median imputation is the safe default for numeric columns.
Why handle_unknown='ignore'? At prediction time you may encounter a category that didn't exist in training (e.g., a new university in a city the model has never seen). Without handle_unknown='ignore', the OneHotEncoder would crash. With it, unknown categories get all-zero one-hot vectors.
Why wrap in a Pipeline? A Pipeline bundles preprocessing + model into a single object. You call pipe.fit(X_train, y_train) once — the transformer learns medians and one-hot categories from training data only, then passes the transformed matrix to the model. At pipe.predict(X_test) time, the same learned transformations apply. No data leakage, no boilerplate.
from sklearn.compose import ColumnTransformer from sklearn.pipeline import Pipeline from sklearn.impute import SimpleImputer from sklearn.preprocessing import OneHotEncoder, StandardScaler def build_preprocessor(X): num_cols = X.select_dtypes(include=[np.number]).columns.tolist() cat_cols = X.select_dtypes(include=['object', 'category', 'bool']).columns.tolist() transformers = [] if num_cols: num_transformer = Pipeline(steps=[ ('imputer', SimpleImputer(strategy='median')), ('scaler', StandardScaler()) ]) transformers.append(('num', num_transformer, num_cols)) if cat_cols: cat_transformer = Pipeline(steps=[ ('imputer', SimpleImputer(strategy='most_frequent')), ('onehot', OneHotEncoder(handle_unknown='ignore', sparse_output=False)) ]) transformers.append(('cat', cat_transformer, cat_cols)) return ColumnTransformer(transformers=transformers, remainder='drop')Train 4 regressors, sort by R²,
plot the best one.
We train four scikit-learn regressors on the same train/test split, evaluate each on MAE, MSE, RMSE, and R², then sort by R² to pick the winner. Finally, we plot actual-vs-predicted and residuals for the best model.
Why four models? Different regressors make different assumptions. LinearRegression assumes a linear relationship between features and target. KNeighborsRegressor predicts by averaging the k nearest training points. DecisionTreeRegressor splits the data with if-then rules. RandomForestRegressor averages many decision trees. Training all four lets us see which assumption best matches the China Universities data.
The four metrics: MAE (Mean Absolute Error) is the average absolute difference between predicted and actual score. MSE (Mean Squared Error) squares the errors before averaging — large errors get penalized harder. RMSE is the square root of MSE, putting the error back in the same units as the target. R² (R-squared) measures how much variance the model explains, from 0 (no better than the mean) to 1 (perfect predictions).
Results on this dataset: RandomForestRegressor wins with R² = 0.9798 — it explains 97.98% of the variance in university scores. Its MAE of 4.09 means predictions are off by ~4 score points on average, compared to a target range of 113–1087. Decision Tree is close behind (R² = 0.96), while Linear Regression (R² = 0.88) and KNN (R² = 0.83) lag — they can't capture the non-linear relationship between features and score.
Two diagnostic plots: the Actual vs Predicted scatter should hug the 45° red line if predictions are perfect. The Residual Plot shows errors (actual − predicted) against predicted values — residuals should look like random noise around zero, with no curve or funnel shape. If you see a curve, the model is missing a non-linear pattern; if you see a funnel, the variance grows with the prediction (heteroscedasticity).
from sklearn.model_selection import train_test_split from sklearn.metrics import mean_absolute_error, mean_squared_error, r2_score from sklearn.linear_model import LinearRegression from sklearn.neighbors import KNeighborsRegressor from sklearn.tree import DecisionTreeRegressor from sklearn.ensemble import RandomForestRegressor # Drop rows where target is NaN, drop raw datetime cols, split X / y df_model = df_clean.dropna(subset=[TARGET_COL]).copy() drop_candidates = [c for c in df_model.columns if df_model[c].dtype == 'datetime64[ns]'] drop_candidates.append(TARGET_COL) X = df_model.drop(columns=drop_candidates) y = df_model[TARGET_COL] X_train, X_test, y_train, y_test = train_test_split( X, y, test_size=0.2, random_state=42 ) preprocessor = build_preprocessor(X_train) candidates = { 'LinearRegression': LinearRegression(), 'KNeighborsRegressor': KNeighborsRegressor(), 'DecisionTreeRegressor': DecisionTreeRegressor(random_state=42), 'RandomForestRegressor': RandomForestRegressor(random_state=42), } results_list, trained_pipelines, preds_dict = [], {}, {} for name, clf in candidates.items(): pipe = Pipeline(steps=[('preprocessor', preprocessor), ('model', clf)]) pipe.fit(X_train, y_train) preds = pipe.predict(X_test) mae = mean_absolute_error(y_test, preds) mse = mean_squared_error(y_test, preds) rmse = np.sqrt(mse) r2 = r2_score(y_test, preds) results_list.append({'MAE':mae, 'MSE':mse, 'RMSE':rmse, 'R2':r2, 'model':name}) trained_pipelines[name] = pipe preds_dict[name] = preds model_results = (pd.DataFrame(results_list) .sort_values(by='R2', ascending=False) .reset_index(drop=True)) print(model_results) # Plot actual vs predicted for the best model best_name = model_results.iloc[0]['model'] best_preds = preds_dict[best_name] plt.figure(figsize=(12, 5)) plt.subplot(1, 2, 1) plt.scatter(y_test, best_preds, alpha=0.6) plt.plot([y_test.min(), y_test.max()], [y_test.min(), y_test.max()], 'r--') plt.title(f"Actual vs Predicted — {best_name}") plt.xlabel("Actual"); plt.ylabel("Predicted") plt.subplot(1, 2, 2) residuals = y_test - best_preds plt.scatter(best_preds, residuals, alpha=0.6) plt.axhline(0, color='r', linestyle='--') plt.title("Residual Plot") plt.xlabel("Predicted"); plt.ylabel("Residuals") plt.tight_layout(); plt.show()By completing this project, you've built a complete data-science pipeline — the same shape used in real production ML systems:
- ETL with
os.walkand pandas — locating and loading a Kaggle CSV with an encoding fallback. - Audit & Clean with pandas — printing dtypes, missing values, and duplicates; building a clean copy with auto-parsed dates.
- EDA & Visualization with seaborn and matplotlib — describe summary, histograms, correlation heatmap, and top-categorical bar charts.
- Feature Engineering — extracting year/month/dayofweek from dates and length/word-count from text columns.
- Preprocessing with
ColumnTransformerandPipeline— median+scaler for numeric, mode+onehot for categorical. - Modeling — training 4 regressors, sorting by R², plotting actual-vs-predicted and residuals for the best model.
Happy coding, Love you, my lovely developers! 💙
🚀 Ideas to extend the project
- Add
GridSearchCVto tunen_estimators,max_depth, andmin_samples_splitfor the Random Forest. - Try
GradientBoostingRegressororXGBoost— they often beat Random Forest on tabular data. - Switch
TARGET_COLtonational_rankand run the same pipeline to compare R². - Run the unsupervised branch (KMeans) by setting
TARGET_COL = Noneand clustering universities by features. - Build a Streamlit app where users pick a province and category, then get a predicted score.
Frequently Asked Questions
What is ETL in a machine learning project? +
ETL stands for Extract, Transform, Load. In this China Universities project, we extract the universities.csv file from the Kaggle input directory, transform it by parsing dates, dropping duplicates, and engineering text-length features, and load it into a clean pandas DataFrame ready for EDA and modeling.
What ML models are used in this China Universities project? +
This project trains and compares four scikit-learn regression models on the score target: LinearRegression, KNeighborsRegressor, DecisionTreeRegressor, and RandomForestRegressor. The best model is RandomForestRegressor with an R-squared of 0.9798.
How does the pipeline detect whether to use classification or regression? +
The detect_problem_type function inspects the target column dtype and unique value count. If the target is numeric with more than 15 unique values and the unique-to-total ratio exceeds 5%, the pipeline runs regression. Otherwise, it runs classification or unsupervised KMeans clustering.
Why does RandomForestRegressor beat LinearRegression here? +
The relationship between the engineered features (text lengths, word counts, one-hot categories) and the score target is non-linear. Random Forest builds many decision trees that can capture non-linear splits, while Linear Regression can only fit a straight hyperplane. The 12-point gap in R² (0.98 vs 0.88) is the price Linear Regression pays for its linearity assumption.
Run the project
yourself.
Open a Kaggle notebook, attach the China Universities dataset, and run the file end-to-end. Or copy each step's code into your own notebook.
Share this Project
If you found this walkthrough helpful, share it with your friends and fellow developers!