# From Zero to Pandas (Part 4): Sorting Through Data

# 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:

```plaintext
- 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.

```python
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:

```text
     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:

```python
print(students["Name"])
```

Output:

```text
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:

```python
print(students["Score"])
```

Output:

```text
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:

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

Output:

```text
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.

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

Output:

```text
     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:

```python
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:

```python
print(students.iloc[0])
```

Output:

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

Remember that Python uses zero-based indexing:

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

To select the third row:

```python
print(students.iloc[2])
```

Output:

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

You can also select several rows using slicing.

For example:

```python
print(students.iloc[0:3])
```

Output:

```text
    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:

```python
dataframe.iloc[row_selection, column_selection]
```

For example:

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

Output:

```text
    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:

```python
print(students.iloc[0, 3])
```

Output:

```text
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:

```python
print(students.loc[0])
```

Output:

```text
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:

```python
students_custom = students.set_index("Name")

print(students_custom)
```

Output:

```text
        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:

```python
print(students_custom.loc["Grace"])
```

Output:

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

But you can still select the third row by position:

```python
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:

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

print(high_scores)
```

Output:

```text
     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:

```python
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:

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

Output:

```text
    Name Class  Age  Score
3  David   SS2   16     45
```

Or find students who are exactly 17 years old:

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

Output:

```text
     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:

```plaintext
- Are in SS1.
- Scored more than 80.
```

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

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

print(result)
```

Output:

```text
    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:

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

print(result)
```

Output:

```text
     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()`.

```python
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.

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

print(result)
```

Output:

```text
     Name  Score
0   Amina     85
2   Grace     91
4   Lydia     88
```

This example uses `loc` to specify:

```plaintext
- 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.

```python
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:

```text
    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.

```python
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:

```text
    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()`:

```python
print(customers.isnull())
```

Output:

```text
    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:

```python
print(customers.isnull().sum())
```

Output:

```text
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:

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

Output:

```text
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.

```python
cleaned_customers = customers.dropna()

print(cleaned_customers)
```

Output:

```text
    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:

```python
print(customers.shape)
```

Output:

```text
(6, 4)
```

And the cleaned version:

```python
print(cleaned_customers.shape)
```

Output:

```text
(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:

```python
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:

```python
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:

```text
    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:

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

print(customers_filled["Amount"])
```

Output:

```text
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()`:

```python
print(customers.duplicated())
```

Output:

```text
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:

```python
print(customers.duplicated().sum())
```

Output:

```text
1
```

This tells us there is one duplicate record.

## 10.7 Remove Duplicate Records Using `drop_duplicates()`

To remove duplicate rows, use `drop_duplicates()`:

```python
customers_unique = customers.drop_duplicates()

print(customers_unique)
```

Output:

```text
    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:

```python
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:

```text
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:

```python
customers_clean = customers.copy()

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

print(customers_clean["City"])
```

Output:

```text
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:

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

This converts text to lowercase.

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

This converts text to uppercase.

```python
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()`:

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

print(customers_renamed.columns)
```

Output:

```text
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:

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

print(data.dtypes)
```

Output:

```text
Name    object
Age     object
dtype: object
```

The `Age` column is stored as text.

You can convert it to integers:

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

print(data.dtypes)
```

Output:

```text
Name    object
Age      int64
dtype: object
```

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

For example:

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

Output:

```text
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:

```python
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:

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

print(unusual_ages)
```

Output:

```text
    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.

```python
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:

```text
    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.

```python
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:

```text
     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()`:

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

print(students_sorted)
```

Output:

```text
     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`.

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

print(students_sorted)
```

Output:

```text
     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`:

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

print(students_sorted)
```

Output:

```text
     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:

```text
     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:

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

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

Output:

```text
     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()`:

```python
students_index_sorted = students.sort_index(ascending=False)

print(students_index_sorted)
```

Output:

```text
     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()`:

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

print(top_students)
```

Output:

```text
    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:

```python
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:

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

print(lowest_students)
```

Output:

```text
     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()`:

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

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

Output:

```text
     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:

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

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

print(scores)
```

Output:

```text
    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"`:

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

print(scores)
```

Output:

```text
    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.

```python
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:

```text
     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.

```python
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:

```text
       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:

```python
grouped = sales.groupby("Category")

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

Output:

```text
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.

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

print(branch_revenue)
```

Output:

```text
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:

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

print(branch_quantity)
```

Output:

```text
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?

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

print(average_revenue)
```

Output:

```text
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()`:

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

print(transaction_count)
```

Output:

```text
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()`:

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

Output:

```text
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:

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

print(highest_revenue)
```

Output:

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

To find the lowest transaction revenue:

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

print(lowest_revenue)
```

Output:

```text
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()`.

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

print(branch_summary)
```

Output:

```text
        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`.

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

print(product_branch_sales)
```

Output:

```text
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:

```python
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()`:

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

print(branch_revenue)
```

Output:

```text
     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:

```python
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:

```text
     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.

```python
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:

```text
     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.