9. Selecting Rows and Columns in Pandas

In the previous section, you learned how to inspect a DataFrame and understand the information it contains.

Now imagine you have a dataset with hundreds or thousands of records. You probably do not want to work with every row and every column at the same time.

You may want to answer questions such as:

- What are the names of all the students?
- What scores did students in SS1 receive?
- Which students scored above 80?
- What information is recorded for the student in the third row?
- Which students scored below 50?   

Pandas allows you to select specific columns and rows without manually searching through the entire dataset.

In this section, you will learn how to select columns, select rows using their positions or labels, filter records using conditions, and combine multiple conditions.

9.1 Create a Dataset to Work With

Let's create a dataset containing student examination results from a school in Abuja.

import pandas as pd

students = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David", "Lydia", "Samuel"],
    "Class": ["SS1", "SS1", "SS2", "SS2", "SS1", "SS2"],
    "Age": [15, 16, 17, 16, 15, 17],
    "Score": [85, 72, 91, 45, 88, 67]
})

print(students)

Output:

     Name Class  Age  Score
0   Amina   SS1   15     85
1   Brian   SS1   16     72
2   Grace   SS2   17     91
3   David   SS2   16     45
4   Lydia   SS1   15     88
5  Samuel   SS2   17     67

We will use this DataFrame for the examples in this section.

9.2 Select One Column

To select one column, use its name inside square brackets.

For example, to display only the students' names:

print(students["Name"])

Output:

0     Amina
1     Brian
2     Grace
3     David
4     Lydia
5    Samuel
Name: Name, dtype: object

This returns a pandas Series.

A Series is a one-dimensional structure containing a single column of data.

You can also select the scores:

print(students["Score"])

Output:

0    85
1    72
2    91
3    45
4    88
5    67
Name: Score, dtype: int64

This is useful when you want to calculate statistics or work with one particular column.

For example:

print(students["Score"].mean())

Output:

74.66666666666667

9.3 Select Multiple Columns

What if you want to display both the students' names and their scores?

You can select multiple columns by passing a list of column names inside the square brackets.

print(students[["Name", "Score"]])

Output:

     Name  Score
0   Amina     85
1   Brian     72
2   Grace     91
3   David     45
4   Lydia     88
5  Samuel     67

Notice the double square brackets:

• The outer brackets tell pandas that you are selecting from the DataFrame.
• The inner brackets contain the list of column names.

You can select as many columns as you need:

print(students[["Name", "Class", "Score"]])

This returns a DataFrame containing only those three columns.

The order of the column names determines their order in the result.

9.4 Select Rows Using iloc

Sometimes you want to select rows based on their position.

Pandas provides iloc for position-based selection.

For example, to select the first row:

print(students.iloc[0])

Output:

Name     Amina
Class      SS1
Age         15
Score       85
Name: 0, dtype: object

Remember that Python uses zero-based indexing:

- Position 0 is the first row.
- Position 1 is the second row.
- Position 2 is the third row.

To select the third row:

print(students.iloc[2])

Output:

Name     Grace
Class      SS2
Age         17
Score       91
Name: 2, dtype: object

You can also select several rows using slicing.

For example:

print(students.iloc[0:3])

Output:

    Name Class  Age  Score
0  Amina   SS1   15     85
1  Brian   SS1   16     72
2  Grace   SS2   17     91

This selects rows at positions 0, 1, and 2.

The ending position, 3, is not included.

9.5 Select Specific Rows and Columns with iloc

You can use iloc to select both rows and columns.

The general structure is:

dataframe.iloc[row_selection, column_selection]

For example:

print(students.iloc[0:3, 0:2])

Output:

    Name Class
0  Amina   SS1
1  Brian   SS1
2  Grace   SS2

Let's understand the code:

• 0:3 selects the first three rows.
• 0:2 selects the first two columns.

You can also select one particular value:

print(students.iloc[0, 3])

Output:

85

This selects the value in the first row and fourth column.

9.6 Select Rows Using loc

While iloc selects rows by their position, loc selects rows using their index labels.

For example:

print(students.loc[0])

Output:

Name     Amina
Class      SS1
Age         15
Score       85
Name: 0, dtype: object

In this DataFrame, the index labels are 0, 1, 2, 3, 4, and 5.

Because the default index labels match the row positions, loc[0] and iloc[0] return the same row.

However, they are not always interchangeable.

Consider a DataFrame with custom index labels:

students_custom = students.set_index("Name")

print(students_custom)

Output:

        Class  Age  Score
Name
Amina     SS1   15     85
Brian     SS1   16     72
Grace     SS2   17     91
David     SS2   16     45
Lydia     SS1   15     88
Samuel    SS2   17     67

Now you can select Grace's record using the index label:

print(students_custom.loc["Grace"])

Output:

Class    SS2
Age       17
Score     91
Name: Grace, dtype: object

But you can still select the third row by position:

print(students_custom.iloc[2])

Both return Grace's record in this example, but they use different selection methods.

Remember:

• loc uses index labels.
• iloc uses integer positions.

9.7 Select Rows Using a Condition

One of the most useful pandas operations is filtering rows based on a condition.

Suppose you want to find all students who scored more than 80.

You can write:

high_scores = students[students["Score"] > 80]

print(high_scores)

Output:

     Name Class  Age  Score
0   Amina   SS1   15     85
2   Grace   SS2   17     91
4   Lydia   SS1   15     88

Let's understand what happened.

First:

students["Score"] > 80

This checks whether each student's score is greater than 80.

Pandas produces a series of True and False values.

It then uses those values to select only the rows where the condition is True.

You can use different comparison operators:

Operator	Meaning
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to
==	Equal to
!=	Not equal to

For example, find students who scored below 50:

print(students[students["Score"] < 50])

Output:

    Name Class  Age  Score
3  David   SS2   16     45

Or find students who are exactly 17 years old:

print(students[students["Age"] == 17])

Output:

     Name Class  Age  Score
2   Grace   SS2   17     91
5  Samuel   SS2   17     67

9.8 Filter Using Multiple Conditions

Sometimes one condition is not enough.

Imagine you want to find students who:

- Are in SS1.
- Scored more than 80.

You can combine the conditions using &, which means AND.

result = students[
    (students["Class"] == "SS1") &
    (students["Score"] > 80)
]

print(result)

Output:

    Name Class  Age  Score
0  Amina   SS1   15     85
4  Lydia   SS1   15     88

Both conditions must be True for a row to be selected.

You can also use |, which means OR.

For example, find students who are in SS1 or scored above 80:

result = students[
    (students["Class"] == "SS1") |
    (students["Score"] > 80)
]

print(result)

Output:

     Name Class  Age  Score
0   Amina   SS1   15     85
1   Brian   SS1   16     72
2   Grace   SS2   17     91
4   Lydia   SS1   15     88

A row is included if at least one of the conditions is True.

Important: Put each condition inside parentheses when combining conditions with & or |. Also, use & and | rather than Python's and and or for element-by-element pandas filtering.

9.9 Filter Using isin()

Suppose you want to select students from SS1 and SS2.

You could write two conditions, but pandas provides a convenient method called isin().

result = students[
    students["Class"].isin(["SS1", "SS2"])
]

print(result)

Since every student in our dataset belongs to either SS1 or SS2, all six rows will appear.

isin() checks whether each value belongs to a list of allowed values.

It becomes more useful when you have many categories.

For example, if a larger dataset contains SS1, SS2, SS3, and other classes, you can select only SS1 and SS2 without writing several conditions.

9.10 Select Columns and Filter Rows Together

You can combine column selection and row filtering.

Suppose you want to see only the names and scores of students who scored above 80.

result = students.loc[
    students["Score"] > 80,
    ["Name", "Score"]
]

print(result)

Output:

     Name  Score
0   Amina     85
2   Grace     91
4   Lydia     88

This example uses loc to specify:

- The rows that meet the condition.
- The columns you want to display.

It is a useful way to create a smaller, more focused view of a large dataset.

9.11 A Complete Practical Example

Let's put the ideas together.

Imagine a school administrator wants to identify students who need academic support.

The administrator wants to find students who scored below 50 and display only their names, classes, and scores.

import pandas as pd

students = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David", "Lydia", "Samuel"],
    "Class": ["SS1", "SS1", "SS2", "SS2", "SS1", "SS2"],
    "Age": [15, 16, 17, 16, 15, 17],
    "Score": [85, 72, 91, 45, 88, 67]
})

# Find students who scored below 50
students_needing_support = students.loc[
    students["Score"] < 50,
    ["Name", "Class", "Score"]
]

print(students_needing_support)

Output:

    Name Class  Score
3  David   SS2     45

The administrator can now see that David scored below 50 and may need additional academic support.

Notice how a few lines of pandas code can answer a practical question quickly.

9.12 What You Should Remember

Here are the main selection techniques covered in this section:

Technique	Purpose
df["Column"]	Select one column
df[["A", "B"]]	Select multiple columns
df.iloc[0]	Select a row by position
df.loc[0]	Select a row by index label
df.iloc[0:3]	Select rows by position range
df[df["Score"] > 80]	Filter rows using a condition
&	Combine conditions using AND
|	Combine conditions using OR
isin()	Select values from a list
df.loc[condition, columns]	Filter rows and select columns together

The main lesson is that you do not always need to work with an entire DataFrame. You can select exactly the information needed to answer a particular question.

In the next section, we will learn how to clean data in pandas, including how to handle missing values, remove duplicates, and correct common data problems.

10. Cleaning Data in Pandas

In the previous section, you learned how to select rows and columns and filter data based on conditions.

But real-world data is not always clean.

Imagine receiving a spreadsheet containing customer records for a shop in Lagos. Some customers have missing phone numbers, a few records appear more than once, and some product prices have been entered incorrectly.

If you analyse this data without checking it, your results may be misleading.

This is why data cleaning is an important part of data analysis.

Data cleaning is the process of identifying and correcting problems in a dataset so that the information is more accurate, consistent, and useful.

In this section, you will learn how to:

• Identify missing values.
• Remove or fill missing values.
• Find and remove duplicate records.
• Correct inconsistent text.
• Rename columns.
• Convert data types.
• Identify unusual values.

10.1 Create a Dataset with Data Problems

Let's create a small customer dataset containing some common problems.

import pandas as pd

customers = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David", "Amina", None],
    "City": ["Lagos", "abuja", "LAGOS", "Kano", "Lagos", "Abuja"],
    "Age": [25, 30, None, 28, 25, 35],
    "Amount": [50000, 75000, 60000, None, 50000, 90000]
})

print(customers)

Output:

    Name   City   Age   Amount
0  Amina  Lagos  25.0  50000.0
1  Brian  abuja  30.0  75000.0
2  Grace  LAGOS   NaN  60000.0
3  David   Kano  28.0      NaN
4  Amina  Lagos  25.0  50000.0
5   None  Abuja  35.0  90000.0

Can you spot the problems?

• One customer has no name.
• One customer has no age.
• One customer has no purchase amount.
• Some city names use inconsistent capitalization.
• Amina's record appears twice.

We will correct these problems step by step.

10.2 Find Missing Values

Missing values occur when information is not available in a particular cell.

Pandas commonly represents missing numerical values as NaN.

To identify missing values, use isnull():

print(customers.isnull())

Output:

    Name   City    Age  Amount
0  False  False  False   False
1  False  False  False   False
2  False  False   True   False
3  False  False  False    True
4  False  False  False   False
5   True  False  False   False

True means the value is missing, while False means it is present.

You can count missing values in each column:

print(customers.isnull().sum())

Output:

Name      1
City      0
Age       1
Amount    1
dtype: int64

This tells us:

• The Name column has one missing value.
• The Age column has one missing value.
• The Amount column has one missing value.
• The City column has no missing values.

You can also check whether the entire DataFrame contains any missing values:

print(customers.isnull().values.any())

Output:

True

This is a quick way to check whether missing values exist anywhere in the dataset.

10.3 Remove Rows with Missing Values Using dropna()

Sometimes a record is too incomplete to be useful.

Pandas provides dropna() to remove rows containing missing values.

cleaned_customers = customers.dropna()

print(cleaned_customers)

Output:

    Name   City   Age   Amount
0  Amina  Lagos  25.0  50000.0
1  Brian  abuja  30.0  75000.0
4  Amina  Lagos  25.0  50000.0

Rows 2, 3, and 5 were removed because each contained at least one missing value.

Notice that dropna() returns a new DataFrame by default. It does not automatically change the original customers DataFrame.

You can check the original:

print(customers.shape)

Output:

(6, 4)

And the cleaned version:

print(cleaned_customers.shape)

Output:

(3, 4)

Important: Removing rows is not always the best solution. If a dataset contains many records with missing information, deleting them could mean losing valuable data.

You should understand why values are missing before deciding what to do.

10.4 Remove Rows Based on a Particular Column

You may only want to remove records where a specific column is missing.

For example, a customer record without a name may be difficult to identify.

You can remove rows with missing names:

customers_without_missing_names = customers.dropna(
    subset=["Name"]
)

print(customers_without_missing_names)

This removes only the row where Name is missing.

The other rows remain, even if their age or amount is missing.

This is useful when one column is particularly important to your analysis.

10.5 Fill Missing Values Using fillna()

Instead of removing incomplete records, you may decide to fill missing values.

Pandas provides fillna() for this purpose.

For example, you can replace missing ages with the average age:

customers_filled = customers.copy()

average_age = customers_filled["Age"].mean()

customers_filled["Age"] = customers_filled["Age"].fillna(
    average_age
)

print(customers_filled[["Name", "Age"]])

Output:

    Name        Age
0  Amina  25.000000
1  Brian  30.000000
2  Grace  28.600000
3  David  28.000000
4  Amina  25.000000
5   None  35.000000

The average age of the available customers is 28.6, so pandas uses that value to fill Grace's missing age.

Notice that we used copy() before making changes. This allows us to work on a separate DataFrame without modifying the original.

You can also fill missing values with a fixed value.

For example, if you want missing purchase amounts to be marked as zero:

customers_filled["Amount"] = customers_filled["Amount"].fillna(0)

print(customers_filled["Amount"])

Output:

0    50000.0
1    75000.0
2    60000.0
3        0.0
4    50000.0
5    90000.0
Name: Amount, dtype: float64

However, be careful with this decision.

A missing purchase amount does not necessarily mean the customer spent nothing. It may mean the amount was never recorded.

Choose a replacement value based on what the data represents.

10.6 Find Duplicate Records

Duplicate records occur when the same information appears more than once.

In our dataset, Amina's record appears twice.

You can identify duplicates using duplicated():

print(customers.duplicated())

Output:

0    False
1    False
2    False
3    False
4     True
5    False
dtype: bool

The fifth row is marked True because it duplicates the first row.

You can count the duplicates:

print(customers.duplicated().sum())

Output:

1

This tells us there is one duplicate record.

10.7 Remove Duplicate Records Using drop_duplicates()

To remove duplicate rows, use drop_duplicates():

customers_unique = customers.drop_duplicates()

print(customers_unique)

Output:

    Name   City   Age   Amount
0  Amina  Lagos  25.0  50000.0
1  Brian  abuja  30.0  75000.0
2  Grace  LAGOS   NaN  60000.0
3  David   Kano  28.0      NaN
5   None  Abuja  35.0  90000.0

Only one copy of Amina's record remains.

By default, pandas keeps the first occurrence and removes later duplicates.

Just like dropna(), drop_duplicates() returns a new DataFrame by default.

You can also remove duplicates based on selected columns.

For example:

customers_unique_names = customers.drop_duplicates(
    subset=["Name"]
)

This keeps only the first occurrence of each name.

Be careful with this approach: two different customers can have the same name. Removing records based only on names may accidentally delete valid customers.

10.8 Correct Inconsistent Text

Look at the City column:

Lagos
abuja
LAGOS
Kano
Lagos
Abuja

These values refer to the same cities, but their capitalization is inconsistent.

This can create problems when grouping or filtering data.

Pandas provides string methods that help standardise text.

To convert city names to title case:

customers_clean = customers.copy()

customers_clean["City"] = customers_clean["City"].str.title()

print(customers_clean["City"])

Output:

0    Lagos
1    Abuja
2    Lagos
3     Kano
4    Lagos
5    Abuja
Name: City, dtype: object

Now the city names use consistent capitalization.

Other useful string methods include:

customers_clean["City"].str.lower()

This converts text to lowercase.

customers_clean["City"].str.upper()

This converts text to uppercase.

customers_clean["City"].str.strip()

This removes unnecessary spaces from the beginning and end of text values.

These methods are useful when working with names, addresses, categories, and other text columns.

10.9 Rename Columns Using rename()

Sometimes column names are unclear or difficult to work with.

For example, a dataset might contain a column named Amount.

You may prefer a more descriptive name such as Purchase_Amount.

You can rename it using rename():

customers_renamed = customers.rename(
    columns={
        "Amount": "Purchase_Amount",
        "Name": "Customer_Name"
    }
)

print(customers_renamed.columns)

Output:

Index(['Customer_Name', 'City', 'Age', 'Purchase_Amount'], dtype='object')

You can rename one column or several columns at the same time.

Clear column names make your code easier to understand.

10.10 Convert Data Types Using astype()

Sometimes data is stored in the wrong format.

For example, an age column might be stored as text instead of numbers.

Let's create a small example:

data = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace"],
    "Age": ["25", "30", "28"]
})

print(data.dtypes)

Output:

Name    object
Age     object
dtype: object

The Age column is stored as text.

You can convert it to integers:

data["Age"] = data["Age"].astype(int)

print(data.dtypes)

Output:

Name    object
Age      int64
dtype: object

Now the age values are stored as numbers, so you can perform numerical calculations.

For example:

print(data["Age"].mean())

Output:

27.666666666666668

Important: astype(int) works when the values can be converted into whole numbers. If the column contains text such as "unknown", the conversion will fail.

You need to inspect and handle such values before converting the column.

10.11 Identify Unusual Values

Not every data problem involves missing values or duplicates.

Sometimes a value exists but does not make sense.

Imagine a customer dataset containing these ages:

ages = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David"],
    "Age": [25, 30, -5, 150]
})

The values -5 and 150 may indicate data-entry errors, depending on the context.

You can filter for suspicious ages:

unusual_ages = ages[
    (ages["Age"] < 0) |
    (ages["Age"] > 100)
]

print(unusual_ages)

Output:

    Name  Age
2  Grace   -5
3  David  150

Pandas has identified the values that fall outside the range we chose to investigate.

This does not automatically prove that the records are wrong. You should check the original source or confirm the correct values before changing them.

10.12 A Complete Practical Example

Let's bring several cleaning techniques together.

Imagine you have received a customer dataset and want to prepare it for analysis.

import pandas as pd

customers = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David", "Amina", None],
    "City": ["Lagos", "abuja", "LAGOS", "Kano", "Lagos", "Abuja"],
    "Age": [25, 30, None, 28, 25, 35],
    "Amount": [50000, 75000, 60000, None, 50000, 90000]
})

# Make a copy so the original data remains unchanged
cleaned = customers.copy()

# Remove duplicate records
cleaned = cleaned.drop_duplicates()

# Standardise city names
cleaned["City"] = cleaned["City"].str.title()

# Fill missing ages with the average age
average_age = cleaned["Age"].mean()
cleaned["Age"] = cleaned["Age"].fillna(average_age)

# Fill missing purchase amounts with zero
cleaned["Amount"] = cleaned["Amount"].fillna(0)

# Rename the Amount column
cleaned = cleaned.rename(
    columns={"Amount": "Purchase_Amount"}
)

print(cleaned)

Output:

    Name   City        Age  Purchase_Amount
0  Amina  Lagos  25.000000          50000.0
1  Brian  Abuja  30.000000          75000.0
2  Grace  Lagos  29.666667          60000.0
3  David   Kano  28.000000              0.0
5   None  Abuja  35.000000          90000.0

Notice that we removed the duplicate Amina record, standardised the city names, filled missing values, and renamed a column.

The average age is now approximately 29.67 because the duplicate record was removed before calculating the average.

This example shows how different cleaning techniques can work together.

However, the choices we made are assumptions for this example. In a real project, you should decide how to handle missing amounts and missing customer names based on the meaning of the data.

10.13 What You Should Remember

Here are the important data-cleaning methods covered in this section:

Method	Purpose
isnull()	Identifies missing values
isnull().sum()	Counts missing values in each column
dropna()	Removes rows with missing values
fillna()	Replaces missing values
duplicated()	Identifies duplicate rows
drop_duplicates()	Removes duplicate rows
.str.title()	Converts text to title case
.str.lower()	Converts text to lowercase
.str.strip()	Removes extra spaces around text
rename()	Renames columns
astype()	Converts data types

The most important lesson is that cleaning data is not just about making a dataset look neat. It is about making sure the data is suitable for the questions you want to answer.

Do not automatically delete or replace information without understanding what it means. A cleaning decision that is wrong for the data can be just as harmful as leaving the original problem untouched.

In the next section, we will learn how to sort data and rank records, allowing us to identify the highest values, lowest values, and top-performing records in a dataset.

11. Sorting and Ranking Data in Pandas

In the previous section, you learned how to clean a dataset by handling missing values, removing duplicates, and correcting inconsistent information.

Now that you know how to prepare data, let's learn how to organise it.

Imagine you are analysing examination results at a school in Accra, Ghana. You may want to:

• Arrange students from the highest score to the lowest.
• Find the student with the highest score.
• Identify the three best-performing students.
• Arrange students alphabetically.
• Sort students by class and then by score.

Pandas makes these tasks straightforward using sorting and ranking methods.

In this section, you will learn how to use sort_values(), sort_index(), nlargest(), nsmallest(), and rank().

11.1 Create a Dataset to Work With

Let's create a student performance dataset.

import pandas as pd

students = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David", "Lydia", "Samuel"],
    "Class": ["SHS1", "SHS1", "SHS2", "SHS2", "SHS1", "SHS2"],
    "Score": [85, 72, 91, 45, 88, 67],
    "Attendance": [95, 88, 98, 70, 92, 85]
})

print(students)

Output:

     Name Class  Score  Attendance
0   Amina  SHS1     85          95
1   Brian  SHS1     72          88
2   Grace  SHS2     91          98
3   David  SHS2     45          70
4   Lydia  SHS1     88          92
5  Samuel  SHS2     67          85

We will use this DataFrame throughout the section.

11.2 Sort Data in Ascending Order

Ascending order means arranging values from the smallest to the largest.

Suppose you want to arrange students according to their scores, starting with the lowest score.

Use sort_values():

students_sorted = students.sort_values(by="Score")

print(students_sorted)

Output:

     Name Class  Score  Attendance
3   David  SHS2     45          70
5  Samuel  SHS2     67          85
1   Brian  SHS1     72          88
0   Amina  SHS1     85          95
4   Lydia  SHS1     88          92
2   Grace  SHS2     91          98

Notice that the scores now appear in ascending order:

45, 67, 72, 85, 88, 91.

The row indexes remain attached to their original records. Sorting changes the order in which rows appear, not the identity of the records.

By default, sort_values() uses ascending order.

11.3 Sort Data in Descending Order

Descending order means arranging values from the largest to the smallest.

If you want to see the highest-performing students first, use ascending=False.

students_sorted = students.sort_values(
    by="Score",
    ascending=False
)

print(students_sorted)

Output:

     Name Class  Score  Attendance
2   Grace  SHS2     91          98
4   Lydia  SHS1     88          92
0   Amina  SHS1     85          95
1   Brian  SHS1     72          88
5  Samuel  SHS2     67          85
3   David  SHS2     45          70

Now the highest score appears first.

This is useful when creating leaderboards, identifying top-performing students, or organising sales from highest to lowest.

11.4 Sort Using Multiple Columns

Sometimes you need to sort data using more than one column.

For example, imagine you want to organise students by class and then arrange their scores from highest to lowest within each class.

You can pass a list of column names to by:

students_sorted = students.sort_values(
    by=["Class", "Score"],
    ascending=[True, False]
)

print(students_sorted)

Output:

     Name Class  Score  Attendance
0   Amina  SHS1     85          95
4   Lydia  SHS1     88          92
1   Brian  SHS1     72          88
2   Grace  SHS2     91          98
5  Samuel  SHS2     67          85
3   David  SHS2     45          70

The first column, Class, is sorted in ascending order.

The second column, Score, is sorted in descending order.

A note about the output: Within SHS1, the scores should be 88, 85, and 72. The correctly sorted output is:

     Name Class  Score  Attendance
4   Lydia  SHS1     88          92
0   Amina  SHS1     85          95
1   Brian  SHS1     72          88
2   Grace  SHS2     91          98
5  Samuel  SHS2     67          85
3   David  SHS2     45          70

This illustrates why it is important to check the output rather than assume that code has produced the expected order.

11.5 Sort Alphabetically

You can also sort text columns.

For example, to arrange students alphabetically by name:

students_sorted = students.sort_values(by="Name")

print(students_sorted[["Name", "Score"]])

Output:

     Name  Score
0   Amina     85
1   Brian     72
3   David     45
2   Grace     91
4   Lydia     88
5  Samuel     67

This is useful when preparing lists, organising customer records, or making datasets easier to read.

11.6 Sort Using the Index with sort_index()

So far, we have sorted rows using column values.

Pandas also allows you to sort rows using their index labels.

Use sort_index():

students_index_sorted = students.sort_index(ascending=False)

print(students_index_sorted)

Output:

     Name Class  Score  Attendance
5  Samuel  SHS2     67          85
4   Lydia  SHS1     88          92
3   David  SHS2     45          70
2   Grace  SHS2     91          98
1   Brian  SHS1     72          88
0   Amina  SHS1     85          95

The rows are now arranged according to their index labels, from 5 down to 0.

The difference is:

• sort_values() sorts using values in one or more columns.
• sort_index() sorts using index labels.

11.7 Find the Highest Values with nlargest()

Sometimes you do not need to sort the entire dataset.

You may only want to find the three students with the highest scores.

Pandas provides nlargest():

top_students = students.nlargest(3, "Score")

print(top_students)

Output:

    Name Class  Score  Attendance
2  Grace  SHS2     91          98
4  Lydia  SHS1     88          92
0  Amina  SHS1     85          95

The number 3 tells pandas to return the three largest values in the Score column.

This is a convenient way to identify top performers.

You could also use sorting and select the first three rows:

top_students = students.sort_values(
    by="Score",
    ascending=False
).head(3)

Both approaches can answer the same question.

11.8 Find the Lowest Values with nsmallest()

The opposite of nlargest() is nsmallest().

It returns the records with the smallest values in a column.

For example, find the three students with the lowest scores:

lowest_students = students.nsmallest(3, "Score")

print(lowest_students)

Output:

     Name Class  Score  Attendance
3   David  SHS2     45          70
5  Samuel  SHS2     67          85
1   Brian  SHS1     72          88

This can help identify students who may need additional academic support.

It is also useful in business analysis when you want to find the lowest-selling products or smallest transactions.

11.9 Rank Values Using rank()

Sorting changes the order of rows.

Ranking, on the other hand, assigns a position or rank to each value.

Suppose you want to assign each student a position based on their examination score.

Use rank():

students["Rank"] = students["Score"].rank(
    ascending=False
)

print(students[["Name", "Score", "Rank"]])

Output:

     Name  Score  Rank
0   Amina     85   3.0
1   Brian     72   4.0
2   Grace     91   1.0
3   David     45   6.0
4   Lydia     88   2.0
5  Samuel     67   5.0

Because ascending=False, the highest score receives rank 1.

The ranks are stored as numbers in a new column called Rank.

Notice that the original row order has not changed.

This is the key difference:

• Sorting rearranges the rows.
• Ranking adds a value that describes each row's position.

11.10 Understand Tied Ranks

What happens when two students receive the same score?

Let's create a small example:

scores = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David"],
    "Score": [90, 80, 80, 60]
})

scores["Rank"] = scores["Score"].rank(
    ascending=False
)

print(scores)

Output:

    Name  Score  Rank
0  Amina     90   1.0
1  Brian     80   2.5
2  Grace     80   2.5
3  David     60   4.0

Brian and Grace have the same score, so pandas gives them the average of the positions they occupy: 2 and 3.

The average is 2.5.

You can change how pandas handles tied ranks using the method argument.

For example, use method="min":

scores["Rank"] = scores["Score"].rank(
    ascending=False,
    method="min"
)

print(scores)

Output:

    Name  Score  Rank
0  Amina     90   1.0
1  Brian     80   2.0
2  Grace     80   2.0
3  David     60   4.0

Both Brian and Grace receive rank 2. The next rank is 4.

Other useful ranking methods include:

Method	How it handles ties
average	Gives tied values the average rank (default)
min	Gives tied values the lowest rank
max	Gives tied values the highest rank
first	Ranks tied values according to their order in the dataset
dense	Gives tied values the same rank without skipping the next rank

For example, with method="dense", the ranks would be 1, 2, 2, and 3.

Choose the ranking method that matches the rules of your analysis.

11.11 Sort and Rank in a Practical Example

Let's combine what you have learned.

Imagine a school administrator wants to prepare a performance report showing students from highest to lowest, with their ranks.

import pandas as pd

students = pd.DataFrame({
    "Name": ["Amina", "Brian", "Grace", "David", "Lydia", "Samuel"],
    "Class": ["SHS1", "SHS1", "SHS2", "SHS2", "SHS1", "SHS2"],
    "Score": [85, 72, 91, 45, 88, 67]
})

# Assign ranks based on scores
students["Rank"] = students["Score"].rank(
    ascending=False,
    method="min"
)

# Arrange students from highest to lowest score
report = students.sort_values(
    by="Score",
    ascending=False
)

print(report)

Output:

     Name Class  Score  Rank
2   Grace  SHS2     91   1.0
4   Lydia  SHS1     88   2.0
0   Amina  SHS1     85   3.0
1   Brian  SHS1     72   4.0
5  Samuel  SHS2     67   5.0
3   David  SHS2     45   6.0

The administrator now has an organised performance report with each student's rank.

11.12 What You Should Remember

Here are the main methods covered in this section:

Method	Purpose
sort_values()	Sorts rows using column values
sort_index()	Sorts rows using index labels
nlargest()	Returns the largest values
nsmallest()	Returns the smallest values
rank()	Assigns ranks to values

The important lesson is that sorting and ranking help you organise data and identify meaningful patterns.

When someone asks you to find the best-performing students, highest-selling products, or lowest expenses, pandas gives you simple tools to answer those questions.

In the next section, we will learn how to group data and calculate summaries using groupby(). This will help you answer questions such as which class has the highest average score or which product category generates the most revenue.

12. Grouping and Aggregating Data in Pandas

In the previous section, you learned how to sort and rank records in a DataFrame.

Now imagine you are analysing sales records from a supermarket in Nairobi, Kenya.

You have hundreds of transactions, but you want to answer questions such as:

• How much revenue did each product generate?
• Which branch made the most sales?
• What was the average transaction value in each city?
• How many transactions were recorded for each product?
• Which product category performed best?

Looking at individual rows will not answer these questions easily.

You need a way to organise records into groups and calculate summaries for each group.

Pandas provides a powerful method called groupby() for this purpose.

In this section, you will learn how to group data, calculate totals and averages, count records, use multiple aggregation functions, and group data using more than one column.

12.1 Create a Dataset to Work With

Let's create a sales dataset for a business operating in Nairobi.

Each row represents a sales transaction.

import pandas as pd

sales = pd.DataFrame({
    "Product": [
        "Rice", "Beans", "Rice", "Maize Flour",
        "Beans", "Rice", "Maize Flour", "Beans"
    ],
    "Category": [
        "Grains", "Legumes", "Grains", "Flour",
        "Legumes", "Grains", "Flour", "Legumes"
    ],
    "Branch": [
        "Nairobi", "Mombasa", "Nairobi", "Kisumu",
        "Mombasa", "Kisumu", "Nairobi", "Nairobi"
    ],
    "Quantity": [10, 5, 8, 12, 7, 6, 9, 4],
    "Revenue_KES": [
        15000, 9000, 12000, 18000,
        12600, 9000, 13500, 7200
    ]
})

print(sales)

Output:

       Product  Category    Branch  Quantity  Revenue_KES
0         Rice    Grains   Nairobi        10        15000
1        Beans   Legumes   Mombasa         5         9000
2         Rice    Grains   Nairobi         8        12000
3  Maize Flour     Flour    Kisumu        12        18000
4        Beans   Legumes   Mombasa         7        12600
5         Rice    Grains    Kisumu         6         9000
6  Maize Flour     Flour   Nairobi         9        13500
7        Beans   Legumes   Nairobi         4         7200

The dataset contains information about products, categories, branches, quantities sold, and revenue.

Let's use it to answer practical business questions.

12.2 What Does groupby() Do?

The groupby() method allows you to divide a DataFrame into groups based on shared values in a column.

For example, the Category column contains three categories:

• Grains
• Legumes
• Flour

You can group the sales records by category:

grouped = sales.groupby("Category")

print(grouped["Revenue_KES"].sum())

Output:

Category
Flour      31500
Grains     36000
Legumes    28800
Name: Revenue_KES, dtype: int64

What happened?

Pandas:

1. Looked at the Category column.
2. Placed records with the same category into groups.
3. Added together the revenue for each group.
4. Returned the total revenue for each category.

The result tells us that:

• Flour generated KES 31,500.
• Grains generated KES 36,000.
• Legumes generated KES 28,800.

This is much easier than manually adding the values in each category.

12.3 Calculate Total Revenue with sum()

Suppose you want to know how much revenue each branch generated.

You can group the data by Branch and calculate the total revenue.

branch_revenue = sales.groupby("Branch")["Revenue_KES"].sum()

print(branch_revenue)

Output:

Branch
Kisumu     27000
Mombasa    21600
Nairobi    47700
Name: Revenue_KES, dtype: int64

This tells us that Nairobi generated the highest revenue.

You can also calculate the total quantity sold by each branch:

branch_quantity = sales.groupby("Branch")["Quantity"].sum()

print(branch_quantity)

Output:

Branch
Kisumu     18
Mombasa    12
Nairobi    31
Name: Quantity, dtype: int64

Now you can compare the branches based on the number of units sold rather than revenue.

12.4 Calculate Average Values with mean()

Totals are useful, but sometimes you want to understand average performance.

For example, what was the average revenue per transaction for each branch?

average_revenue = sales.groupby("Branch")["Revenue_KES"].mean()

print(average_revenue)

Output:

Branch
Kisumu     13500.0
Mombasa    10800.0
Nairobi    11925.0
Name: Revenue_KES, dtype: float64

This shows the average revenue recorded in each transaction for each branch.

Notice that Nairobi has the highest total revenue, but Kisumu has the highest average revenue per transaction.

This demonstrates why different measurements can tell different stories about the same business.

12.5 Count Records with count() and size()

Sometimes you want to know how many transactions belong to each group.

You can use size():

transaction_count = sales.groupby("Branch").size()

print(transaction_count)

Output:

Branch
Kisumu     2
Mombasa    2
Nairobi    4
dtype: int64

This tells us that:

• Kisumu recorded 2 transactions.
• Mombasa recorded 2 transactions.
• Nairobi recorded 4 transactions.

You can also use count():

print(sales.groupby("Branch")["Revenue_KES"].count())

Output:

Branch
Kisumu     2
Mombasa    2
Nairobi    4
Name: Revenue_KES, dtype: int64

What is the difference?

• size() counts all rows in each group.
• count() counts non-missing values in the selected column.

If a group contains missing revenue values, count() will exclude those missing values, while size() will still count the rows.

12.6 Find the Highest and Lowest Values in Each Group

Pandas also allows you to calculate the maximum and minimum values within each group.

For example, find the highest transaction revenue recorded at each branch:

highest_revenue = sales.groupby("Branch")["Revenue_KES"].max()

print(highest_revenue)

Output:

Branch
Kisumu     18000
Mombasa    12600
Nairobi    15000
Name: Revenue_KES, dtype: int64

To find the lowest transaction revenue:

lowest_revenue = sales.groupby("Branch")["Revenue_KES"].min()

print(lowest_revenue)

Output:

Branch
Kisumu     9000
Mombasa    9000
Nairobi    7200
Name: Revenue_KES, dtype: int64

These calculations help you understand the range of transaction values in each branch.

12.7 Use Multiple Aggregation Functions with agg()

What if you want to calculate several statistics at the same time?

For example, you want to see the total revenue, average revenue, highest transaction, and number of transactions for each branch.

Instead of writing separate groupby statements, you can use agg().

branch_summary = sales.groupby("Branch").agg({
    "Revenue_KES": ["sum", "mean", "max", "count"],
    "Quantity": "sum"
})

print(branch_summary)

Output:

        Revenue_KES                       Quantity
                 sum     mean    max count     sum
Branch
Kisumu         27000  13500.0  18000     2      18
Mombasa        21600  10800.0  12600     2      12
Nairobi        47700  11925.0  15000     4      31

This produces a summary table containing several statistics.

For each branch, you can see:

• Total revenue.
• Average transaction revenue.
• Highest transaction revenue.
• Number of transactions.
• Total quantity sold.

This is especially useful when preparing reports.

12.8 Group by Multiple Columns

Sometimes you need to group data using more than one column.

For example, you may want to know how much revenue each product generated at each branch.

You can group by both Branch and Product.

product_branch_sales = sales.groupby(
    ["Branch", "Product"]
)["Revenue_KES"].sum()

print(product_branch_sales)

Output:

Branch   Product
Kisumu   Maize Flour    18000
         Rice            9000
Mombasa  Beans          21600
Nairobi  Beans           7200
         Maize Flour    13500
         Rice           27000
Name: Revenue_KES, dtype: int64

Pandas first groups the records by branch and then groups them by product within each branch.

This helps you understand which products are performing well in different locations.

For example, Rice generated KES 27,000 in Nairobi but KES 9,000 in Kisumu.

12.9 Use reset_index() to Make Results Easier to Work With

When you use groupby(), pandas often places the grouping column in the result's index.

For example:

branch_revenue = sales.groupby("Branch")["Revenue_KES"].sum()

print(branch_revenue)

The branch names appear as index labels.

If you want the branch names to become a normal column again, use reset_index():

branch_revenue = sales.groupby("Branch")["Revenue_KES"].sum().reset_index()

print(branch_revenue)

Output:

     Branch  Revenue_KES
0    Kisumu        27000
1   Mombasa        21600
2   Nairobi        47700

This is useful when you want to save the results, create charts, or perform further analysis.

12.10 Give Aggregated Columns Clear Names

When you use several aggregation functions, pandas may create multi-level column names.

You can make the result easier to understand by using named aggregation.

For example:

branch_summary = sales.groupby("Branch").agg(
    Total_Revenue=("Revenue_KES", "sum"),
    Average_Revenue=("Revenue_KES", "mean"),
    Transactions=("Revenue_KES", "count"),
    Total_Quantity=("Quantity", "sum")
).reset_index()

print(branch_summary)

Output:

     Branch  Total_Revenue  Average_Revenue  Transactions  Total_Quantity
0    Kisumu          27000          13500.0             2              18
1   Mombasa          21600          10800.0             2              12
2   Nairobi          47700          11925.0             4              31

This approach lets you choose meaningful names for the resulting columns.

It is especially useful when you are preparing a clean summary for someone else to read.

12.11 A Complete Practical Example

Let's imagine a business manager wants a report showing the total and average revenue generated by each branch.

We can solve this with a few lines of pandas code.

import pandas as pd

sales = pd.DataFrame({
    "Product": [
        "Rice", "Beans", "Rice", "Maize Flour",
        "Beans", "Rice", "Maize Flour", "Beans"
    ],
    "Branch": [
        "Nairobi", "Mombasa", "Nairobi", "Kisumu",
        "Mombasa", "Kisumu", "Nairobi", "Nairobi"
    ],
    "Quantity": [10, 5, 8, 12, 7, 6, 9, 4],
    "Revenue_KES": [
        15000, 9000, 12000, 18000,
        12600, 9000, 13500, 7200
    ]
})

# Group transactions by branch and calculate summary statistics
report = sales.groupby("Branch").agg(
    Total_Revenue=("Revenue_KES", "sum"),
    Average_Revenue=("Revenue_KES", "mean"),
    Total_Quantity=("Quantity", "sum"),
    Number_of_Transactions=("Revenue_KES", "count")
).reset_index()

# Display the report
print(report)

Output:

     Branch  Total_Revenue  Average_Revenue  Total_Quantity  Number_of_Transactions
0    Kisumu          27000          13500.0              18                       2
1   Mombasa          21600          10800.0              12                       2
2   Nairobi          47700          11925.0              31                       4

The manager can now compare the branches without manually adding individual transactions.

Nairobi generated the highest total revenue, while Kisumu recorded the highest average revenue per transaction.

That is the value of grouping and aggregation: they help turn many individual records into useful business information.

12.12 What You Should Remember

Here are the main methods covered in this section:

Method	Purpose
groupby()	Divides records into groups
sum()	Calculates totals
mean()	Calculates averages
count()	Counts non-missing values
size()	Counts rows in each group
max()	Finds the highest value
min()	Finds the lowest value
agg()	Applies multiple aggregation functions
reset_index()	Converts index labels back into columns

The most important lesson is that grouping helps you answer questions about categories, locations, products, departments, and other shared characteristics in your data.

Instead of looking at hundreds of individual transactions, you can summarise them into a smaller report that makes patterns easier to understand.

In the next section, we will learn how to combine DataFrames using merge(), join(), and concat(). These techniques are useful when information is stored in separate tables and you need to bring it together for analysis.