7 Advanced DataFrame Opterations

There are many more operations that we can perform on DataFrames. In this chapter we will go over some of the more advanced operations that Pandas provides.

Tip

Find the Pandas Cheat Sheet here.

Operations on Columns

When working with DataFrames it’s easy to generate new columns from existing column data. We call these types of operations column operations. We can use all type of operations from mathematical operations to comparative operations. Let’s say we have a DataFrame with class grades. We can construct a new column with the average grade for each student.

import pandas as pd

grades = {
    "student": ["Alice", "Bob", "Charlie"],
    "math": [85, 90, 78],
    "science": [92, 88, 84],
    "english": [88, 76, 95]
}
grades_df = pd.DataFrame(grades)

Now we can create a new column average by writing it into existence, using the existing columns.

grades_df["average"] = (grades_df["math"] + grades_df["science"] + grades_df["english"]) / 3
grades_df

	student	math	science	english	average
0	Alice	85	92	88	88.333333
1	Bob	90	88	76	84.666667
2	Charlie	78	84	95	85.666667

We could similarly create a column that uses a conditional operation to determine if a student passed or failed based on their average grade.

grades_df["status"] = grades_df["average"] >= 60
grades_df

	student	math	science	english	average	status
0	Alice	85	92	88	88.333333	True
1	Bob	90	88	76	84.666667	True
2	Charlie	78	84	95	85.666667	True

This example returns a boolean column indicating whether each student passed (True) or failed (False). This is an undesirable solution because it is not idiomatic. A better solution would be to either (1) use Python’s built-in map operator and pass in a dictionary where the key is the boolean value and the value is the corresponding string; (2) use Numpy’s where operator; or (3) rename the column to a more appropriate name like is_passing.

Solution 1: Python map operator

grades_df["status"] = (grades_df["average"] >= 60).map({True: "Pass", False: "Fail"})
grades_df

	student	math	science	english	average	status
0	Alice	85	92	88	88.333333	Pass
1	Bob	90	88	76	84.666667	Pass
2	Charlie	78	84	95	85.666667	Pass

Solution 2: Numpy where operator

import numpy as np
grades_df["status"] = np.where(grades_df["average"] >= 60, "Pass", "Fail")
grades_df

	student	math	science	english	average	status
0	Alice	85	92	88	88.333333	Pass
1	Bob	90	88	76	84.666667	Pass
2	Charlie	78	84	95	85.666667	Pass

This solution works by using the np.where function, which takes three arguments: a condition, a value to return if the condition is true, and a value to return if the condition is false. Numpy is a powerful library for numerical computing in Python. We do not have the time to get into its details here, but it is worth exploring when working with large datasets. In this case, if the average grade is greater than or equal to 60, it returns “Pass.” Otherwise, it returns “Fail”.

Solution 3: Rename the column

grades_df["is_passing"] = grades_df["average"] >= 60
grades_df

	student	math	science	english	average	status	is_passing
0	Alice	85	92	88	88.333333	Pass	True
1	Bob	90	88	76	84.666667	Pass	True
2	Charlie	78	84	95	85.666667	Pass	True

This is the simplest solution and is often the best choice when the column is only going to be used for filtering or other boolean operations.

I think these examples indicate the variety of use column operations to transform data in a DataFrame.

Iterating over DataFrames

We can also iterate over DatFrames using three different techniques: iterrows(), itertuples(), and iteritems(). Each of these methods has its own advantages and disadvantages. But they are necessary by the basic design of DataFrames. DataFrames are not designed for iteration, but rather for vectorized operations. However, there are times when we need to iterate over the rows or columns of a DataFrame.

We use all of them by appending them to the end of the DataFrame instance in a for loop. The basic differences between the three methods is the way they return the data. iterrows() returns each row as a Series, itertuples() returns each row as a named tuple, and iteritems() returns each column as a Series. Let’s look at each of these methods in turn using the same DataFrame as above.

`df.iterrows()`

Let’s start to understand the differences by first looking at the basic output of each method. Here is an example of using iterrows() to iterate over the rows of a DataFrame.

for row in grades_df.iterrows():
    print(row)

(0, student           Alice
math                 85
science              92
english              88
average       88.333333
status             Pass
is_passing         True
Name: 0, dtype: object)
(1, student             Bob
math                 90
science              88
english              76
average       84.666667
status             Pass
is_passing         True
Name: 1, dtype: object)
(2, student         Charlie
math                 78
science              84
english              95
average       85.666667
status             Pass
is_passing         True
Name: 2, dtype: object)

As you can see, df.iterrows() return each row as a tuple where the first element is the index and the second element is a Series object containing the row data. We can unpack (or destructure) the tuple into two variables: index and row. This is often more convenient when working with the data.

for index, row in grades_df.iterrows():
    print(index, row)

0 student           Alice
math                 85
science              92
english              88
average       88.333333
status             Pass
is_passing         True
Name: 0, dtype: object
1 student             Bob
math                 90
science              88
english              76
average       84.666667
status             Pass
is_passing         True
Name: 1, dtype: object
2 student         Charlie
math                 78
science              84
english              95
average       85.666667
status             Pass
is_passing         True
Name: 2, dtype: object

As you can see the row variable is a Series object. We can access the values in the Series using the column names as keys. That means that we can use either dot notation or bracket notation to access the values. Here is an example of accessing the values using bracket notation (because I prefer bracket notation)

for index, row in grades_df.iterrows():
    print(index, row["student"], row["average"])

0 Alice 88.33333333333333
1 Bob 84.66666666666667
2 Charlie 85.66666666666667

`df.itertuples()`

Let’s look at the output of itertuples() to see how it differs from iterrows().

for row in grades_df.itertuples():
    print(row)

Pandas(Index=0, student='Alice', math=85, science=92, english=88, average=88.33333333333333, status='Pass', is_passing=True)
Pandas(Index=1, student='Bob', math=90, science=88, english=76, average=84.66666666666667, status='Pass', is_passing=True)
Pandas(Index=2, student='Charlie', math=78, science=84, english=95, average=85.66666666666667, status='Pass', is_passing=True)

As you can see, df.itertuples() returns each row as a named tuple. This means that we can access the values using dot notation, which can be more convenient. Here is an example of accessing the values using dot notation:

for row in grades_df.itertuples():
    print(row.Index, row.student, row.average)

0 Alice 88.33333333333333
1 Bob 84.66666666666667
2 Charlie 85.66666666666667

`df.items()`

Finally, df.items() works differently than the previous two. Where as df.iterrows() and df.itertuples() iterate over the rows of data, df.items() allows us to iterate over the columns of a DataFrame. Here is an example of using items() to iterate over the columns of a DataFrame.

for column, series in grades_df.items():
    print(f"column: {column}, series: {series}")

column: student, series: 0      Alice
1        Bob
2    Charlie
Name: student, dtype: object
column: math, series: 0    85
1    90
2    78
Name: math, dtype: int64
column: science, series: 0    92
1    88
2    84
Name: science, dtype: int64
column: english, series: 0    88
1    76
2    95
Name: english, dtype: int64
column: average, series: 0    88.333333
1    84.666667
2    85.666667
Name: average, dtype: float64
column: status, series: 0    Pass
1    Pass
2    Pass
Name: status, dtype: object
column: is_passing, series: 0    True
1    True
2    True
Name: is_passing, dtype: bool

df.items() returns each column as a Series. This means that we can access the values in the Series using the column names as keys, just like with iterrows(). Here is an example of accessing the values using bracket notation:

for column, series in grades_df.items():
    print(f"column: {column}, values: {series.values}")

column: student, values: ['Alice' 'Bob' 'Charlie']
column: math, values: [85 90 78]
column: science, values: [92 88 84]
column: english, values: [88 76 95]
column: average, values: [88.33333333 84.66666667 85.66666667]
column: status, values: ['Pass' 'Pass' 'Pass']
column: is_passing, values: [ True  True  True]