Python for Data course menu
Add, Rename and Drop Columns in pandas
Create new pandas columns from calculations, conditions with np.where and np.select, and bins with cut; rename and reorder columns, drop columns and rows, and build several columns at once with assign.
On this page
Most analyses create new columns: revenue from price and quantity, a category from a number, a flag from a condition. pandas makes these vectorised one-liners.
New columns from calculations#
Assign to a new column name, as with a dictionary:
import pandas as pd
orders = pd.DataFrame({
"item": ["pen", "notebook", "bag", "bottle"],
"price": [20, 60, 900, 350],
"quantity": [10, 5, 1, 2],
})
orders["total"] = orders["price"] * orders["quantity"]
orders["total_with_gst"] = (orders["total"] * 1.18).round(2)
orders["share_pct"] = (orders["total"] / orders["total"].sum() * 100).round(1)
print(orders) item price quantity total total_with_gst share_pct
0 pen 20 10 200 236.0 9.5
1 notebook 60 5 300 354.0 14.3
2 bag 900 1 900 1062.0 42.9
3 bottle 350 2 700 826.0 33.3The calculation runs on every row at once; there is no loop.
Columns from conditions#
For two outcomes, use np.where; for several, np.select with a list of conditions checked in order:
import numpy as np
import pandas as pd
orders = pd.DataFrame({
"item": ["pen", "notebook", "bag", "bottle"],
"total": [200, 300, 900, 700],
})
orders["size"] = np.where(orders["total"] >= 500, "large", "small")
conditions = [orders["total"] >= 800, orders["total"] >= 400]
choices = ["premium", "standard"]
orders["tier"] = np.select(conditions, choices, default="basic")
print(orders) item total size tier
0 pen 200 small basic
1 notebook 300 small basic
2 bag 900 large premium
3 bottle 700 large standardnp.select uses the first condition that is true, so put the strictest one first.
Binning numbers with cut#
pd.cut sorts numbers into labelled ranges, like age groups or score bands:
import pandas as pd
students = pd.DataFrame({"name": ["Asha", "Ravi", "Meera", "John", "Priya"],
"score": [92, 67, 45, 78, 88]})
students["grade"] = pd.cut(students["score"], bins=[0, 50, 70, 85, 100],
labels=["D", "C", "B", "A"])
print(students)
print(students["grade"].value_counts().sort_index()) name score grade
0 Asha 92 A
1 Ravi 67 C
2 Meera 45 D
3 John 78 B
4 Priya 88 A
grade
D 1
C 1
B 1
A 2
Name: count, dtype: int64By default each bin includes its right edge: 50 would be a D, 51 a C. pd.qcut instead makes bins with (roughly) equal numbers of rows, such as quartiles.
Several columns at once: assign#
assign returns a new DataFrame with extra columns, which suits a chain of steps:
import pandas as pd
sales = pd.DataFrame({"units": [3, 8, 5], "unit_price": [250, 120, 400]})
report = (
sales
.assign(revenue=lambda d: d["units"] * d["unit_price"])
.assign(big=lambda d: d["revenue"] > 1000)
.sort_values("revenue", ascending=False)
)
print(report) units unit_price revenue big
2 5 400 2000 True
1 8 120 960 False
0 3 250 750 FalseThe lambda d: receives the DataFrame as it is at that step, so the second assign can use revenue.
Renaming, reordering and dropping#
import pandas as pd
df = pd.DataFrame({"Emp Name": ["Asha", "Ravi"], "SAL": [52000, 78000], "tmp": [1, 2]})
df = df.rename(columns={"Emp Name": "name", "SAL": "salary"})
df = df.drop(columns=["tmp"])
df["dept"] = ["Sales", "Engineering"]
df = df[["name", "dept", "salary"]] # choose the column order
print(df)
df.columns = df.columns.str.upper() # change every name at once
print(df.columns.tolist())
print(df.drop(index=0)) # drop a row by its label name dept salary
0 Asha Sales 52000
1 Ravi Engineering 78000
['NAME', 'DEPT', 'SALARY']
NAME DEPT SALARY
1 Ravi Engineering 78000Cleaning column names to lowercase with underscores (df.columns.str.lower().str.replace(" ", "_")) makes them easy to type and lets you use them in query.
Common mistakes#
Practice#
Practice exercise
For the employees name = ["Asha", "Ravi", "Meera", "John"], salary = [52000, 78000, 81000, 46000], years = [3, 6, 9, 1]: add a bonus of 10% of salary for people with 5 or more years and 5% otherwise, a band column using pd.cut with bins [0, 50000, 75000, 100000] labelled "low", "mid", "high", and print the table.
Show hint
np.where(df["years"] >= 5, 0.10, 0.05) * df["salary"] gives the bonus.
Show solution
import numpy as np
import pandas as pd
df = pd.DataFrame({
"name": ["Asha", "Ravi", "Meera", "John"],
"salary": [52000, 78000, 81000, 46000],
"years": [3, 6, 9, 1],
})
df["bonus"] = (np.where(df["years"] >= 5, 0.10, 0.05) * df["salary"]).round().astype(int)
df["band"] = pd.cut(df["salary"], bins=[0, 50000, 75000, 100000], labels=["low", "mid", "high"])
print(df) name salary years bonus band
0 Asha 52000 3 2600 mid
1 Ravi 78000 6 7800 high
2 Meera 81000 9 8100 high
3 John 46000 1 2300 lowKey takeaway#
Next, real data is messy: Missing Values.
Finished this lesson?