7.1 Replacing Values in a DataFrame

Advanced DataFrames

Sometimes we have to replace values for various reasons. The two primary reasons is that the data is entered as text (and many models do not handle text data well) or the data is not standardized with too many variations. There are many other possibilities, but these are common areas that we need to solve.

For this example, let’s begin with a simple dataset of books from a library that have various characteristics.

import random
import pandas as pd

titles = ["The Great Gatsby", "To Kill a Mockingbird", "1984", "Pride and Prejudice"]
backings = ["hardcover", "paperback", "ebook", "pdf", "boardback"]

data = {
    "title": titles,
    "titles_sold": [random.random() * 100 for _ in titles],
    "backing": [random.choice(backings) for _ in titles],
    "year": [1925, 1960, 1949, 1813],
}
df = pd.DataFrame(data)
df

	title	titles_sold	backing	year
0	The Great Gatsby	74.750471	boardback	1925
1	To Kill a Mockingbird	20.770111	hardcover	1960
2	1984	29.549971	paperback	1949
3	Pride and Prejudice	2.421604	hardcover	1813

Using a Loop

We learned earlier how to iterate over a DataFrame using iterrows(), itertuples(), and iteritems(). These methods are the most straightforward way to access and modify individual elements based on typical programming patterns, but they are not the most efficient.

Using `iterrows()`

df.iterrows() returns an iterator that produces index and row pairs. We can use this to loop through each row and replace values as needed.

First let’s look at the output from iterrows():

for index, row in df.iterrows():
    if index < 4:
        print(f"\tIndex: {index}\n{row}")

    Index: 0
title          The Great Gatsby
titles_sold           74.750471
backing               boardback
year                       1925
Name: 0, dtype: object
    Index: 1
title          To Kill a Mockingbird
titles_sold                20.770111
backing                    hardcover
year                            1960
Name: 1, dtype: object
    Index: 2
title               1984
titles_sold    29.549971
backing        paperback
year                1949
Name: 2, dtype: object
    Index: 3
title          Pride and Prejudice
titles_sold               2.421604
backing                  hardcover
year                          1813
Name: 3, dtype: object

Here we are using \t and \n to format the output for readability. \t adds a tab space and \n adds a new line.

As you can see, iterrows() gives us both the index of the row and the row contents as tuple pairs. We have deconstructed the tuple into index and row for easier access. We could name them anything, but these names are descriptive. For each column in the row, we can access the value using the column name as a key. This means we can also access items in the row using row['column_name']. For instance, if we want to only see the title and backing columns, we can do this:

for index, row in df.iterrows():
    if index < 4:
        print(f"\tIndex: {index}\nTitle: {row['title']}, Backing: {row['backing']}")

    Index: 0
Title: The Great Gatsby, Backing: boardback
    Index: 1
Title: To Kill a Mockingbird, Backing: hardcover
    Index: 2
Title: 1984, Backing: paperback
    Index: 3
Title: Pride and Prejudice, Backing: hardcover

Now that we know how to access the values, we can replace them. Let’s say we want to replace all instances of “pdf” in the backing column with “ebook”. We can do this by checking the value of row['backing'] and updating it if it matches “pdf”.

for index, row in df.iterrows():
    if row['backing'] == 'pdf':
        df.at[index, 'backing'] = 'ebook'   
df

	title	titles_sold	backing	year
0	The Great Gatsby	74.750471	boardback	1925
1	To Kill a Mockingbird	20.770111	hardcover	1960
2	1984	29.549971	paperback	1949
3	Pride and Prejudice	2.421604	hardcover	1813

Using `itertuples()`

df.itertuples() returns an iterator that produces named tuples for each row in the DataFrame. This method is generally faster than iterrows() and allows for more efficient access to row data.

First, let’s look at the output from itertuples():

for row in df.itertuples():
    if row.Index < 4:
        print(f"\tIndex: {row.Index}\n{row}")

    Index: 0
Pandas(Index=0, title='The Great Gatsby', titles_sold=74.75047050164612, backing='boardback', year=1925)
    Index: 1
Pandas(Index=1, title='To Kill a Mockingbird', titles_sold=20.77011131013049, backing='hardcover', year=1960)
    Index: 2
Pandas(Index=2, title='1984', titles_sold=29.5499714775907, backing='paperback', year=1949)
    Index: 3
Pandas(Index=3, title='Pride and Prejudice', titles_sold=2.421604087994622, backing='hardcover', year=1813)

Here, each row is represented as a named tuple, where the first element is the index and the subsequent elements correspond to the columns in the DataFrame. We can access the values using dot notation, such as row.title or row.backing. Whereas iterrows() returns a Series for each row, itertuples() returns a named tuple, which can be more efficient. Moreover, the index is included as a named entity in the tuple, allowing for easy access. In other words, we do not need to deconstruct the tuple into index and row as we did with iterrows(). Instead, we can access the index directly using row.Index.

We could destructure the tuple into its components, but it is not necessary. For example, we could name all the columns in the tuple like this:

for idx, title, titles_sold, backing, year in df.itertuples():
    if idx < 4:
        print(f"\tIndex: {idx}\nTitle: {title}, Backing: {backing}")

    Index: 0
Title: The Great Gatsby, Backing: boardback
    Index: 1
Title: To Kill a Mockingbird, Backing: hardcover
    Index: 2
Title: 1984, Backing: paperback
    Index: 3
Title: Pride and Prejudice, Backing: hardcover

Notice that when we destructure the tuple we use idx instead of index because index is a reserved word in Python. It also indicates that destructuring is ordinal, the name we provide does not relate to the named entity in the tuple but its order in the tuple (remember tuples are indexed, i.e., ordered).

Now that we know how to access the values, we can replace them. Let’s say we want to replace all instances of “hardcover” in the backing column with “hard back”. We can do this by checking the value of row.backing and updating it if it matches “hardcover”.

for idx, title, titles_sold, backing, year in df.itertuples():
    if backing == "hardcover":
        df.at[idx, "backing"] = "hard back"
df

	title	titles_sold	backing	year
0	The Great Gatsby	74.750471	boardback	1925
1	To Kill a Mockingbird	20.770111	hard back	1960
2	1984	29.549971	paperback	1949
3	Pride and Prejudice	2.421604	hard back	1813

Using `items()`

Remember df.items() iterates over the columns of a DataFrame. But it can also iterate over a single column if we specify the column name. This can be useful when we want to replace values in a specific column. We can do this by iterating over the column and checking the values.

for idx, item in df["backing"].items():
    print(f"Index: {idx}, Value: {item}")

Index: 0, Value: boardback
Index: 1, Value: hard back
Index: 2, Value: paperback
Index: 3, Value: hard back

Notice that df["backing"].items() returns each index and each value for the backing column. This means we can access the values in the column using the index. Here is an example of replacing values in the backing column:

for idx, item in df["backing"].items():
    if item == "boardback":
        df.at[idx, "backing"] = "hardback"
df

	title	titles_sold	backing	year
0	The Great Gatsby	74.750471	hardback	1925
1	To Kill a Mockingbird	20.770111	hard back	1960
2	1984	29.549971	paperback	1949
3	Pride and Prejudice	2.421604	hard back	1813

More Efficient Ways

`df.replace()`

While using loops to replace values is straightforward, it is not the most efficient way to do it. Pandas provides a built-in method called df.replace() that allows us to replace values in a DataFrame without using loops. This method is generally faster and more efficient than using loops.

The df.replace() method can take a dictionary as an argument where the keys are the values to be replaced and the values are the new values. Here is an example of using df.replace() to replace multiple values in the backing column:

df["backing"] = df["backing"].replace({
    "boardback": "hardback",
    "pdf": "ebook"
})
df

	title	titles_sold	backing	year
0	The Great Gatsby	74.750471	hardback	1925
1	To Kill a Mockingbird	20.770111	hard back	1960
2	1984	29.549971	paperback	1949
3	Pride and Prejudice	2.421604	hard back	1813

As you can see, we have replaced all instances of “boardback” with “hardback” and “pdf” with “ebook” in the backing column. This method is much more efficient than using loops, especially for large DataFrames.

`df.loc[]`

Another way to replace values in a DataFrame is to use the df.loc[] method. This method allows us to access a group of rows and columns by labels or a boolean array. We can use this method to replace values in a specific column based on a condition.

Here is an example of using df.loc[] to replace values in the backing column:

df.loc[df["backing"] == "ebook", "backing"] = "e-book"
df

	title	titles_sold	backing	year
0	The Great Gatsby	74.750471	hardback	1925
1	To Kill a Mockingbird	20.770111	hard back	1960
2	1984	29.549971	paperback	1949
3	Pride and Prejudice	2.421604	hard back	1813

This works because df.loc[] allows us to use boolean indexing to select the rows where the condition is met (i.e., where the backing column is equal to “ebook”) and then we specify the column we want to update (i.e., the backing column) and assign the new value (i.e., “e-book”). This method is also more efficient than using loops.

Using a Loop

Using iterrows()

Using itertuples()

Using items()