Feature Engineering and Modifying Tables
Create business measures whose formulas, units, and handling of missing values you can explain.
An existing column can be a feature. We can also derive a new variable from existing columns. Revenue, gross profit, and order-size labels answer different business questions.
Columns are Series, so arithmetic acts element by element and aligns row labels. An explicit loop is unnecessary for this calculation.
Change O101’s Quantity from 2 to 3. Predict its new Revenue before running.
ProductCost = Quantity × UnitCost. GrossProfit = Revenue − ProductCost. This excludes rent, salaries, and other operating expenses.
Gross margin is GrossProfit / Revenue. A zero-revenue record has an undefined margin. Copy Revenue, then use .loc to replace non-positive denominators with None before dividing. Pandas displays these missing numerical values as NaN.
Use .loc in two ways:
orders.loc[positive, "Revenue"] returns only the positive-revenue rows A and C.denominator.loc[denominator <= 0] = None changes the zero value to missing in a copied Series. All three index values remain.Selection returns 2 rows; assignment keeps 3: B is absent from selected but remains as NaN in denominator. The original Revenue column still contains zero for B.
Revisit Chapter 1: label observed orders as Large when Revenue >= 200, and Standard otherwise. This example has complete revenue values.
A two-branch label is only appropriate when its branches cover all intended cases.
Initialise a clear label, then use .loc for observed positive, zero, and negative values. An unknown return should remain Unknown.
Use the .columns attribute to assign a list of column names in their current order. Use .copy() first to preserve the original table. .drop(columns=[...]) returns a table without the specified columns.
by="Price" chooses the sorting column. axis=0 reorders whole rows. ascending=True means smallest first; ascending=False means largest first. There is no descending= argument.
Predict the descending labels: A, B, D, C. Store and Price move together; the original row labels stay attached.
| Argument | Default when omitted | What it controls |
|---|---|---|
axis |
axis=0 |
Reorder whole rows using values in a named column. Use axis=1 to reorder columns using a named row. |
ascending |
ascending=True |
Put smaller values first. Use ascending=False to put larger values first. |
With neither argument supplied, .sort_values(by="Monday") sorts rows by Monday from smallest to largest.
Default row order: Central, Kowloon. Descending row order: Kowloon, Central. Descending column order using Central: Tuesday, Monday.
A selected column is a Series: use .sort_values() without by. Its labels stay attached. Missing values go last by default, even for descending order; na_position="first" moves them first.
Descending observed values are A = 120, D = 60, C = 35. Moving B does not fill its missing value.
A chain passes each returned object to the next method. First sort the table, then take its first two rows. Read left to right; in a multiline chain, read top to bottom.
df → sorted DataFrame → first two rows. Both versions return A and B. The dot calls the next method on the previous result.
Task: find the two largest observed revenues. Step 1 removes unknown revenues (4 → 3 rows). Step 2 sorts those 3 rows. Step 3 keeps the first 2. Write the three calls as one expression and read from left to right.
Expected: C = 300, D = 200. All three methods return DataFrames. Change .head(2) to .head(1) and predict the result.
Sort → head: find the largest two orders in the whole table. Head → sort: rank only the first two recorded orders. The same method names can answer different questions.
First result: B, C. Second result: B, A. Predict both before running; C is already excluded when .head(2) runs first.
df["Revenue"] returns a Series; .dropna() returns another Series; .sum() returns one number. Stop when you have the answer. A next method must be supported by the returned object.
Both totals are 600. Keep chains short; split them into named steps when the intermediate result needs checking.
Let x be one Sales value. Calculate the reference values from the same Sales Series.
| Comparison | Min-max scaling | Z-score standardisation |
|---|---|---|
| Formula | (x - min) / (max - min) |
(x - mean) / std |
| What it measures | Position within the observed range | Distance from the mean in standard-deviation units |
| A result of 0 means | The observed minimum | The observed mean |
| A result of 1 means | The observed maximum | One standard deviation above the mean |
| Range for these input values | 0 to 1 | No fixed bounds; values can be negative |
| Required denominator | max - min > 0 |
std > 0 |
Both methods rescale values; neither makes a distribution normal. Here std is the sample standard deviation returned by .std().
Calculate revenue and gross profit for each record. Then print the label of the order with the largest gross profit. Explain the units.
Expected gross profits: A = 100, B = 54, C = 40 HKD. A ranks first.
Prof. Xuhu Wan · HKUST ISOM · Introduction to Business Analytics