Lesson 2.1: Building a credit modelling dataset
Learn how to turn loan tables into one modelling dataset: choosing the grain, the observation date, features, the target, exclusions and a sample size.
By the end of this lesson you will be able to design a modelling dataset for a credit model from the raw tables of a lender: decide what one row stands for, fix the observation date, build features from only what was known by then, attach the target from Lesson 1.4, apply sensible exclusions, split the data by time, and judge whether the sample has enough bad loans to model. You should have read Module 1, especially the loan lifecycle in Lesson 1.2 and the definition of default in Lesson 1.4. The Python code uses pandas, and each step is also explained in plain words with numbers.
Models do not learn from loans. They learn from a table. Every credit model, whether a simple scorecard or a boosted tree, is only as good as the table it is trained on, and that table is not handed over by anyone. It is built, by joining the application, bureau, loan and repayment tables, creating features, attaching the outcome and filtering the rows. Mistakes made here, such as using data from after the decision or counting young loans as good, cannot be repaired later by a clever algorithm. This lesson is the construction manual.
What one row stands for
The first decision is the grain, the idea from Lesson 1.1 of the Data Science track. For a credit model the usual grain is one row per loan, or per application, at its observation date. Everything in the row describes that loan as it looked on that date, and the last column says what happened next.
There are two common designs, and the choice follows the question the model answers.
Application model. One row per application that was approved and disbursed. The observation date is the application date. Features are the application form, the bureau pull on that day and policy variables. The target is default within 12 months of disbursal. It supports the approve-or-decline decision.
Behaviour model. One row per loan per month-end snapshot. The observation date is the snapshot. Features are the repayment history up to that month, such as maximum days past due in the last six months and the share of the limit used. The target is default in the next 12 months. It supports limit management, early warning and collections.
A behaviour dataset has many more rows, because each loan appears once per month. That is a feature, since it gives many observations of how loans move, and a hazard, because rows from the same loan are not independent. When you split the data for testing, split by loan or by date and not by random row, or the same loan will appear in both training and test sets.
The time structure
Every row needs three dates, and they must be kept apart.
1. The observation date, when the decision is made or the snapshot is taken.
2. The look-back period, the history before that date that features may use, such as the last 6 or 12 months.
3. The performance window, the 12 months after the observation date in which the outcome is recorded.
A loan with an observation date of 1 March 2025 uses data up to 1 March 2025 for features, and watches until 28 February 2026 for the outcome. The rule from Lesson 1.2 follows: features come from before the observation date, the outcome from after it.
Predict first: a modelling table is being built in October 2026 with a 12-month performance window. What is the latest observation date a loan can have and still be included with a full outcome?
The answer is about October 2025. A loan observed in November 2025 would have only 11 months of performance by October 2026, so its outcome is incomplete. The newest cohort in the table is therefore always about a year old, which is one reason credit models lag behind the market: they are trained on lending from a year ago and applied to today's customers.
Joining the tables without leaking the future
The raw material sits in different tables with different grains, as the table in Lesson 1.2 showed. To build the dataset, start from the spine, the list of rows with their observation dates, and attach features to it. Every join must respect the observation date.
python
import pandas as pd
spine = pd.readcsv("loans.csv", parsedates=["appdate", "disbursaldate"]) # one row per loan
bureau = pd.readcsv("bureaupulls.csv", parsedates=["pulldate"]) # many pulls per customer
ledger = pd.readcsv("ledger.csv", parsedates=["duedate", "paiddate"])
take, for each loan, the latest bureau pull ON OR BEFORE the application date
spine = spine.sortvalues("appdate")
bureau = bureau.sortvalues("pulldate")
feat = pd.mergeasof(spine, bureau, lefton="appdate", righton="pulldate",
by="customerid", direction="backward")
assert len(feat) == len(spine) # the join must not change the row count
Two lines carry the weight. The mergeasof with direction="backward" takes the most recent bureau record on or before the application date, never a later one. And the assert checks that the join did not multiply rows, which would double-count any customer with more than one pull. Always check the row count after every join.
The same idea applies to every table: take the value as of the observation date. A customer's "current employer type" in the master table may have been updated since; use the application's own value. A "current bureau score" may reflect events after the loan. Use the pull stored with its date. If a table holds only the latest value, you cannot rebuild the past with it, and any model trained on it will quietly use the future.
Choosing features
Features are the inputs to the model. For a retail credit model they fall into four families.
Family · Examples · Source · CautionsApplication · Age, income, employment type, loan amount, tenure, city · Application form · Self-declared, can be wrong or inflated
Bureau · Number of active loans, worst delinquency in 12 months, enquiries in 6 months, oldest account age, bureau score · Credit bureau · Must be stored as of the pull date; thin files have blanks
Behaviour · Maximum DPD in the last 6 months, average payment ratio, utilisation, months since last delinquency · Ledger and snapshots · Only for existing customers, and only up to the observation date
Derived · Ratios and flags built from the above · Computed · Easy to build with errors; document each formula
Derived features often carry more signal than raw ones. A few that appear in almost every retail model:
Fixed obligations to income ratio (FOIR): all monthly loan payments, existing and new, divided by monthly income. It measures how much of a borrower's income is already committed.
Loan to income: the loan amount divided by annual income.
Utilisation: the balance on a revolving line divided by its limit.
Payment ratio: the amount paid in a month divided by the amount due.
Trend features: the change in a quantity over time, such as utilisation now minus utilisation three months ago.
Take a borrower with a monthly income of ₹42,000, existing loan payments of ₹8,000 and a new loan with an EMI of ₹9,415. The FOIR is (8,000 + 9,415) ÷ 42,000 = 17,415 ÷ 42,000 = 41.5 per cent. Try other figures.
For each feature you build, write down four things in a data dictionary: its name, its definition and formula, the source table and the date it is taken as of, and how blanks are coded. A model cannot be validated, or even rerun, without it.
Attaching the target
With the spine and features in place, attach the target from the definition in Lesson 1.4. For an application model, find each loan's maximum days past due in months 1 to 12 after disbursal. A loan is bad if that maximum reached 90, good if it never exceeded 30, and indeterminate otherwise.
python
snap = pd.readcsv("snapshots.csv", parsedates=["disbursaldate", "snapshotdate"])
snap["mob"] = ((snap["snapshotdate"].dt.year - snap["disbursaldate"].dt.year) 12
+ snap["snapshotdate"].dt.month - snap["disbursaldate"].dt.month)
w = snap[(snap["mob"] >= 1) & (snap["mob"] <= 12)]
tgt = w.groupby("loanid").agg(maxdpd=("dpd", "max"), monthsseen=("mob", "max")).resetindex()
tgt["target"] = (tgt["maxdpd"] >= 90).astype(int)
tgt["label"] = pd.cut(tgt["maxdpd"], [-1, 30, 89, 10000], labels=["good", "indeterminate", "bad"])
data = feat.merge(tgt, on="loanid", how="inner", validate="onetoone")
The validate="onetoone" argument makes pandas fail loudly if either side has repeated loan ids. That one word has saved many analysts from a silently doubled dataset.
Exclusions: who stays out of the sample
Not every loan belongs in the table. A written list of exclusions, with counts, is part of the build. Typical ones:
Loans without a full performance window. Seasoning of fewer than 12 months, from Lesson 1.4.
Test, duplicate and staff records. Records created for system testing, repeated ids, and loans to employees on special terms.
Fraud cases. Confirmed fraud and first-instalment defaults behave differently from credit risk and are usually modelled apart.
Product or channel outside scope. A personal loan model should not include gold loans.
Indeterminates. Many teams drop them from training, as Lesson 1.4 explained.
Follow a worked example. A lender disbursed 10,000 loans in the study period. 600 have not had 12 months, 80 are confirmed fraud or first-instalment defaults, and 20 are test records and duplicates. That leaves 10,000 − 600 − 80 − 20 = 9,300 mature, clean loans. Within those, with a 4.2 per cent bad rate and 5.3 per cent indeterminate, there are 391 bad loans, 493 indeterminate and 8,416 good (391 + 493 + 8,416 = 9,300). Dropping the indeterminates leaves 8,807 loans for modelling, with a modelling bad rate of 391 ÷ 8,807 = 4.4 per cent.
Keep the exclusion table in the build log, with counts at each step. When someone asks why the model was trained on 8,807 loans and not 10,000, the answer should be a table and not a memory.
Splitting the data
A model must be judged on data it has not seen, so the dataset is split. For credit models, the preferred split is by time.
Development sample. The older cohorts, used to fit the model.
Validation or hold-out sample. A later part of the same period, used to tune and compare models.
Out-of-time sample. The newest mature cohort, kept untouched until the end to check that the model holds up on lending it never saw.
Time-based splits mimic reality, because a model built today scores tomorrow's customers. A random split mixes cohorts and tends to flatter the model, particularly if the economy changed. With our 8,807 loans, an 80/20 split gives about 7,046 for development and 1,761 for validation, each keeping a bad rate near 4.4 per cent. Check that the bad rate in each part is similar. If not, the split has a problem, or the portfolio changed over time, which is itself a finding.
Is there enough data?
A model needs enough bad loans to learn from, not just enough rows. Because defaults are rare, a table of 100,000 loans with a 1 per cent bad rate has only 1,000 bads, and that count decides the quality of the model. A common rule of thumb in scorecard work is to aim for at least several hundred bad loans in the development sample, and many teams prefer 1,000 or more, though the number depends on the number of features and the method. Treat any such figure as a guide.
How certain is the bad rate itself? With a bad rate p estimated from n loans, a rough 95 per cent range is plus or minus 1.96 × √(p × (1 − p) ÷ n). Use the simulator to see how size and rate interact.
With 8,807 loans and a 4.4 per cent bad rate there are about 387 bad loans, and the bad rate is known to within about 0.43 percentage points. To collect 500 bad loans at that rate, a lender needs about 11,364 loans. A lender with a 1 per cent bad rate would need 50,000. This is why new products, small lenders and low-default portfolios are hard to model, and why teams sometimes pool several years or use expert judgement beside the data.
A worked example: one row of the finished table
It helps to see what all this produces. Here is one row of an application model's dataset, shown as field and value pairs.
Field · Value · Where it comes fromloanid · L5001 · Spine
appdate · 2025-03-01 · Spine, the observation date
age · 34 · Application
monthlyincome · 42,000 · Application
employmenttype · Salaried · Application
loanamount · 2,00,000 · Application
bureauscore · 712 · Bureau pull dated 2025-02-27
enquiries6m · 3 · Bureau pull dated 2025-02-27
worstdpd12mbureau · 0 · Bureau pull dated 2025-02-27
foir · 41.5% · Derived from income, bureau obligations and the new EMI
maxdpd12m · 28 · Ledger, months 1 to 12 after disbursal
label · good · Derived: never more than 30 days past due
Check the dates. Every feature comes from on or before 1 March 2025, the observation date, and the bureau pull is from two days earlier, which is the latest available. The two outcome fields, maxdpd12m and label, come from the period after disbursal and are never used as inputs. Mixing up these two groups of columns is the most common way to build a model that looks wonderful in testing and fails in use.
Imbalance and sampling choices
Credit data is imbalanced: bad loans are a small minority, 4.4 per cent in our sample and often less. Some teams respond by throwing away most of the good loans so the classes are balanced, or by repeating the bad ones. These tricks can help certain algorithms, but they change the sample's bad rate, and a model trained on a balanced sample outputs probabilities that are far too high for the real portfolio. If you resample, you must correct the model's scores back to the true bad rate, or use weights that restore it.
For a classical scorecard built with logistic regression, the usual advice is simpler: keep the natural sample, make sure there are enough bad loans, and let the method handle the imbalance. Resampling becomes more attractive with very rare events or with algorithms that struggle on skewed data, and Module 6 returns to it. Whatever you choose, record the bad rate of the sample used for fitting next to the bad rate of the real portfolio, because they will not be the same, and the difference is something validators will ask about.
Common mistakes
Using data from after the observation date. Check every feature's date. A "latest" value is a leak.
Joining without checking the row count. Assert that the join kept the number of loans, and validate one-to-one.
Splitting by random row for a behaviour model. The same loan lands in training and test. Split by loan or date.
Forgetting the exclusions log. Without counts at each step, nobody can reproduce or defend the sample.
Including young loans. They have no complete outcome and push the bad rate down.
Too few bad loans for too many features. The model will fit noise. Reduce features or gather more data.
No data dictionary. A feature without a definition and an as-of date cannot be validated.
Key takeaways
A credit modelling dataset has one row per loan or per loan-month, with features from before the observation date and a target from the 12 months after.
Build it from a spine of rows and join tables as of the observation date, checking the row count after every join.
Features come in four families: application, bureau, behaviour and derived. Ratios such as FOIR often carry the most signal.
Write down exclusions with counts: young loans, fraud, test records, out-of-scope products and, often, indeterminates.
Split by time into development, validation and out-of-time samples, and check that bad rates are similar.
Count bad loans, not rows. A few hundred is a minimum for a simple scorecard, and a margin of error on the bad rate shows how precise the sample is.
Check yourself
1. A modelling table is built in March 2027 with a 12-month window. About what is the latest observation date a loan can have?
About March 2026, because later loans lack 12 months of performance.
2. A borrower earns ₹60,000 a month, has existing payments of ₹12,000 and a new EMI of ₹9,000. What is the FOIR?
(12,000 + 9,000) ÷ 60,000 = 35 per cent.
3. Why is mergeasof with a backward direction used for the bureau join?
It picks the latest bureau record on or before the application date, so later information does not leak into the features.
4. A table of 50,000 loans has a 1.2 per cent bad rate. How many bad loans, and is that plenty?
600 bad loans. That is workable for a simple scorecard, but a model with many features would be thin.
5. Why do credit teams prefer a time-based split to a random one?
It mimics real use, where a model scores future customers, and it exposes changes in the portfolio that a random split hides.
Try this
Design a modelling dataset on paper or in a spreadsheet for a loan product you know. Set aside forty-five minutes.
1. State the product, the question the model answers and whether it is an application or a behaviour model. Write the grain: one row per what.
2. Choose an observation date rule, a look-back period and a performance window. State the latest observation date for a table built today.
3. List at least 12 features across the four families, and write a data dictionary line for each, with its formula, its source and its as-of date.
4. Write the target definition from Lesson 1.4 and say how indeterminates are handled.
5. Write an exclusion list. For an invented lender of 20,000 loans, give a plausible count removed at each step and the sample that remains.
6. Describe a time-based split, with the dates for development, validation and out-of-time.
7. Use the sample-size simulator with your own numbers and say whether you have enough bad loans, and what you would do if not.
A good result is a two-page design note that another analyst could use to build the table without asking you anything. The next lesson takes that table and studies each variable on its own.
Tags: Credit Risk Analytics Modelling dataset Feature building Sampling Beginner