Pandas Sorting and New Columns
This lesson shows how to put rows in order and how to create new columns from existing ones.
The Example Data
Example
python
import pandas as pd
df = pd.DataFrame({
"student": ["Ali", "Ayesha", "Hamza", "Sana", "Bilal"],
"maths": [78, 92, 65, 88, 92],
"english": [70, 85, 80, 91, 60]
})
print(df)Output
student maths english 0 Ali 78 70 1 Ayesha 92 85 2 Hamza 65 80 3 Sana 88 91 4 Bilal 92 60
Sorting by a Column
sort_values() sorts the rows. Add ascending=False for largest first.
Example
python
print(df.sort_values("maths", ascending=False))Output
student maths english 1 Ayesha 92 85 4 Bilal 92 60 3 Sana 88 91 0 Ali 78 70 2 Hamza 65 80
Sorting by Several Columns
When two students have the same maths marks, sort them by English next:
Example
python
print(df.sort_values(["maths", "english"], ascending=[False, False]).head(3))Output
student maths english 1 Ayesha 92 85 4 Bilal 92 60 3 Sana 88 91
Use sort_index() to sort by the index labels instead.
Adding a Calculated Column
Create a column by assigning to a new name. The calculation runs on every row at once:
Example
python
df["total"] = df["maths"] + df["english"]
df["average"] = df["total"] / 2
print(df)Output
student maths english total average 0 Ali 78 70 148 74.0 1 Ayesha 92 85 177 88.5 2 Hamza 65 80 145 72.5 3 Sana 88 91 179 89.5 4 Bilal 92 60 152 76.0
Columns From Conditions
Use NumPy's where() for a simple yes/no choice:
Example
python
import numpy as np
df["result"] = np.where(df["average"] >= 75, "Pass", "Retry")
print(df[["student", "average", "result"]])Output
student average result 0 Ali 74.0 Retry 1 Ayesha 88.5 Pass 2 Hamza 72.5 Retry 3 Sana 89.5 Pass 4 Bilal 76.0 Pass
apply(): Your Own Function
apply() runs a function on every value of a column:
Example
python
def grade(avg):
if avg >= 85:
return "A"
elif avg >= 75:
return "B"
return "C"
df["grade"] = df["average"].apply(grade)
print(df[["student", "grade"]])Output
student grade 0 Ali C 1 Ayesha A 2 Hamza C 3 Sana A 4 Bilal B
map(): Replace Values With a Dictionary
Example
python
remarks = {"A": "Excellent", "B": "Good", "C": "Needs work"}
df["remark"] = df["grade"].map(remarks)
print(df[["student", "remark"]])Output
student remark 0 Ali Needs work 1 Ayesha Excellent 2 Hamza Needs work 3 Sana Excellent 4 Bilal Good
Prefer plain column maths like df["a"] + df["b"] when you can. It is much faster than apply(), which works one value at a time.
Top and Bottom Rows
| Method | What it gives |
|---|---|
nlargest(n, col) | The n rows with the biggest values |
nsmallest(n, col) | The n rows with the smallest values |
rank() | The rank position of each value |
Example
python
print(df.nlargest(2, "total")[["student", "total"]])Output
student total 3 Sana 179 1 Ayesha 177
Exercise
Exercise
Which code sorts df by the column "price" from highest to lowest?