- \n
- Introduction<\/a><\/li>\n
- Syntax<\/a><\/li>\n
- Examples<\/a><\/li>\n
- Frequently Asked Questions<\/a><\/li>\n<\/ul>\n
Having said that, this is a somewhat complicated technique to use, so it might be best to read the whole tutorial.<\/p>\n
<\/a><\/p>\n
A Quick Introduction to Pandas Replace<\/h2>\n
The Pandas replace method replaces values inside of a Pandas dataframe or other Pandas object. We often use this technique for data cleaning<\/a> and data manipulation in Python<\/a>.<\/p>\n
![\"An](\"https:\/\/www.sharpsightlabs.com\/wp-content\/uploads\/2021\/09\/pandas-replace_simple-dataframe-example.png\")
<\/p>\nHaving said that, in practice, the technique can actually be quite complicated to use.<\/p>\n
We can use this technique to replace:<\/p>\n
- \n
- specific values in any column of the dataframe<\/li>\n
- multiple values in any column of the dataframe<\/li>\n
- specific values in a specific column of the dataframe<\/li>\n
- … and more<\/li>\n<\/ul>\n
For better or worse, this flexibility can make the
replace()<\/code> technique somewhat difficult to understand and difficult to use.<\/p>\nHaving said that, I’m going to try to simplify things as much as possible.<\/p>\n
To help you understand though, we really need to look at the syntax.<\/p>\n
<\/a><\/p>\n
The Syntax of Pandas Replace<\/h2>\n
Here, we’ll look at the syntax of the
replace()<\/code> technique.<\/p>\nSince there are multiple ways to use this technique, we’ll actually look at three different variations on the syntax:<\/p>\n
- \n
- replace a single value throughout the dataframe<\/a><\/li>\n
- replace a multiple values throughout the dataframe<\/a><\/li>\n
- and, replace a specific value in a specific column<\/a><\/li>\n<\/ul>\n
A quick note<\/h4>\n
Before we look at the syntax, I should mention that we make some assumptions.<\/p>\n
First, we assume that you’ve already imported Pandas. You can do that with the following code:<\/p>\n
\r\nimport pandas as pd\r\n<\/pre>\n
Second, we assume that you already have a Pandas dataframe or similar Pandas object like a Series.<\/p>\n
You can learn more about how to create dataframes in our blog post on Python dataframes<\/a>.<\/p>\n
<\/a><\/p>\n
syntax: replace a single value throughout the dataframe<\/h3>\n
First, let’s start with the simplest case. Here, I’ll show you how to use the syntax to replace a specific value in every column of a dataframe.<\/p>\n
To do this, we use two paramters:<\/p>\n
- \n
to_replace<\/code><\/li>\nvalue<\/code><\/li>\n<\/ul>\nThe
to_replace<\/code> parameter specifies the value you want to replace.<\/p>\nThe
value<\/code> parameter specifies the new replacement value.<\/p>\n![\"An](\"https:\/\/www.sharpsightlabs.com\/wp-content\/uploads\/2021\/09\/pandas-replace-single-value-syntax.png\")
<\/p>\nWhen you use this, the
replace()<\/code> method will replace every instance of theto_replace<\/code> value in any column of the dataframe.<\/p>\nWe’ll look at an example of this in example 1<\/a>.<\/p>\n
<\/a><\/p>\n
syntax: replace a multiple values throughout the dataframe<\/h3>\n
The syntax for variation 2 is actually similar to the first syntax variation.<\/p>\n
Here, we’ll replace multiple specific values with a new value.<\/p>\n
To do this, we’ll still use the
to_replace<\/code> parameter and thevalue<\/code> parameter. <\/p>\nBut instead of providing a single value as the argument to the
to_replace<\/code> parameter, we’ll provide a list<\/em> of values.<\/p>\n![\"An](\"https:\/\/www.sharpsightlabs.com\/wp-content\/uploads\/2021\/09\/pandas-replace-list-of-values-syntax.png\")
<\/p>\nWhen you use this syntax, the
replace()<\/code> method will replace any of the specified values with the new replacement value.<\/p>\nWe’ll look at an example of this in example 2<\/a>.<\/p>\n
<\/a><\/p>\n
syntax: replace a specific value in a specific column<\/h3>\n
Finally, I’ll show you a third variation on the syntax.<\/p>\n
This syntax will replace a specific value in a specific column.<\/p>\n
To do this we actually need to use dictionaries.<\/p>\n
When we call the
replace()<\/code> method, the argument will be a dictionary. The “key” of the dictionary will be the name of the new column. The “value” of the dictionary will be a second dictionary<\/em>. <\/p>\n![\"An](\"https:\/\/www.sharpsightlabs.com\/wp-content\/uploads\/2021\/09\/pandas-replace-value-in-specific-column.png\")
<\/p>\nInside this second nested dictionary is the value you want to search for and the value you want to replace it with.<\/p>\n
This syntax is somewhat complicated, but it’s the best way to replace a specific value in a specific column.<\/p>\n
I’ll show you an example of this in example 4<\/a>.<\/p>\n
<\/a><\/p>\n
The parameters of Pandas replace<\/h3>\n
We already talked about two parameters in the previous section, but there are some other optional parameters that you can use.<\/p>\n
Let’s look at the parameters that are available.<\/p>\n
- \n
to_replace<\/code><\/li>\nvalue<\/code><\/li>\ninplace<\/code><\/li>\n<\/ul>\nto_replace<\/code> (required)<\/h6>\nThe
to_replace<\/code> parameter controls the item to replace, or the column to operate on. To be clear, this can be somewhat difficult to understand, so let me simplify:<\/p>\n- \n
- if the argument is a number or character, replace will search for and replace that number<\/li>\n
- if the argument is a list, replace will search for and replace the values in the list<\/li>\n
- and if the argument is nested dictionary of the form
{'column':{'old_value':'new_value'}}<\/code>, it will search forold_value<\/code> incolumn<\/code>, and replace it withnew_value<\/code><\/li>\n<\/ul>\nThere are also other options for using this parameter, but I discourage you from using them because they just make the syntax more complicated. <\/p>\n
value<\/code><\/h6>\nThe
value<\/code> parameter specifies the replacement value.<\/p>\nIf you use the “nested dictionary” syntax described above, then
value<\/code> should be set tovalue = None<\/code>.<\/p>\ninplace<\/code><\/h6>\nThe
inplace<\/code> parameter enable you to modify the dataframe directly.<\/p>\nRemember that by default,
replace()<\/code> produces a new dataframe or new Python object and leaves the original object unchanged.<\/p>\nThe
replace()<\/code> technique also haslimit<\/code>,regex<\/code>, andmethod<\/code> parameters, but those are more complex and less commonly used, so I won’t address them here.<\/p>\nThe output of Pandas replace<\/h3>\n
By default, the Pandas replace method returns a new dataframe. (This is the default behavior because by default, the
inplace<\/code> parameter is set toinplace = False<\/code>.)<\/p>\nIf you set
inplace = True<\/code>, the method will return nothing, and will instead directly modify the dataframe that’s being operated on.<\/p>\n<\/a><\/p>\n
Examples: How replace values in a Pandas dataframe<\/h2>\n
Ok. Now that we’ve looked at the syntax for the Pandas replace method, let’s look at some examples of how to use it.<\/p>\n
Examples:<\/strong><\/p>\n
- \n
- Replace one specific value across all columns of a dataframe<\/a><\/li>\n
- Replace several possible values across all columns of a dataframe<\/a><\/li>\n
- Modify a dataframe inplace (i.e., replace and modify the original dataframe)<\/a><\/li>\n
- Replace a specific value in a specific dataframe column<\/a><\/li>\n
- Replace different values in multiple different columns<\/a><\/li>\n<\/ul>\n
Run this code first<\/h4>\n
Before you run any of these examples, you’ll need to run some preliminary code in order to:<\/p>\n
- \n
- import necessary packages<\/li>\n
- create a dataframe<\/li>\n<\/ul>\n
Let’s do each of those.<\/p>\n
Import necessary packages<\/h5>\n
First let’s just import the packages that we’ll need to run our examples.<\/p>\n
You can do that with the following code:<\/p>\n
\r\nimport pandas as pd\r\nimport numpy as np\r\n<\/pre>\n
We’ll obviously need the Pandas package to use the Pandas replace method. <\/p>\n
We’ll also use both Pandas and Numpy in order to create our dataframe.<\/p>\n
Let’s do that next.<\/p>\n
Create example dataframe<\/h5>\n
Here, we’ll create a simple dataframe that we can use for our examples.<\/p>\n
\r\nsales_data = pd.DataFrame({\"name\":[\"William\",\"Emma\",\"Sofia\",\"Markus\",\"Edward\",\"Thomas\",\"Ethan\",\"Olivia\",\"Arun\",\"Anika\",\"Paulo\"]\r\n,\"region\":[\"East\",'N',\"East\",\"South\",\"West\",\"West\",\"S\",\"West\",\"West\",\"East\",\"South\"]\r\n,\"sales\":[50000,52000,90000,-1,42000,72000,49000,-1,67000,65000,67000]\r\n,\"expenses\":[42000,43000,-999,44000,38000,39000,42000,-1,39000,44000,45000]})\r\n<\/pre>\nAnd let’s print it out so we can see the contents:<\/p>\n
\r\nprint(sales_data)\r\n<\/pre>\n
OUT:<\/p>\n
\r\n name region sales expenses\r\n0 William East 50000 42000\r\n1 Emma N 52000 43000\r\n2 Sofia East 90000 -999\r\n3 Markus South -1 44000\r\n4 Edward West 42000 38000\r\n5 Thomas West 72000 39000\r\n6 Ethan S 49000 42000\r\n7 Olivia West -1 -1\r\n8 Arun West 67000 39000\r\n9 Anika East 65000 44000\r\n10 Paulo South 67000 45000\r\n<\/pre>\n
As you can see, this dataframe contains mock “sales” data about several salespeople. The dataframe has four variables, and if you look carefully, you can see some unusual values. For example, the values ‘
N<\/code>‘, ‘S<\/code>‘,-1<\/code>, and-99<\/code> are all values that we might want to replace with something different.<\/p>\n<\/a><\/p>\n
EXAMPLE 1: Replace one specific value across all columns of a dataframe<\/h3>\n
First, let’s start very simple.<\/p>\n
Here, we’ll replace the value
-1<\/code> with the valueNaN<\/code>.<\/p>\nLet’s take a look:<\/p>\n
\r\nsales_data.replace(to_replace = -1, value = np.NaN)\r\n<\/pre>\n
OUT:<\/p>\n
\r\n name region sales expenses\r\n0 William East 50000.0 42000.0\r\n1 Emma N 52000.0 43000.0\r\n2 Sofia East 90000.0 -999.0\r\n3 Markus South NaN 44000.0\r\n4 Edward West 42000.0 38000.0\r\n5 Thomas West 72000.0 39000.0\r\n6 Ethan S 49000.0 42000.0\r\n7 Olivia West NaN NaN\r\n8 Arun West 67000.0 39000.0\r\n9 Anika East 65000.0 44000.0\r\n10 Paulo South 67000.0 45000.0\r\n<\/pre>\n
Explanation<\/h5>\n
Here, we only replaced the value
-1<\/code> in the dataframe. Wherever it was found, this value was replaced with the valuenp.NaN<\/code>.<\/p>\nNotice that by using
replace()<\/code> like this on the whole dataframe, by default, it operated on all of the columns. In particular, since the value-1<\/code> existed in both thesales<\/code> column and theexpenses<\/code> column,replace()<\/code> ended up replacing the-1<\/code> values found in both of those columns.<\/p>\nThere is a way to target only a single column as well (instead of all of the columns). I’ll show you that in example 4<\/a>.<\/p>\n
<\/a><\/p>\n
EXAMPLE 2: Replace several possible values across all columns of a dataframe<\/h3>\n
Next, let’s replace several possible values.<\/p>\n
Specifically, we’ll replace any occurrence of
-1<\/code> or-999<\/code> with the valuenp.NaN<\/code>.<\/p>\nLet’s take a look:<\/p>\n
\r\nsales_data.replace(to_replace = [-1,-999], value = np.NaN)\r\n<\/pre>\n
OUT:<\/p>\n
\r\n name region sales expenses\r\n0 William East 50000.0 42000.0\r\n1 Emma N 52000.0 43000.0\r\n2 Sofia East 90000.0 NaN\r\n3 Markus South NaN 44000.0\r\n4 Edward West 42000.0 38000.0\r\n5 Thomas West 72000.0 39000.0\r\n6 Ethan S 49000.0 42000.0\r\n7 Olivia West NaN NaN\r\n8 Arun West 67000.0 39000.0\r\n9 Anika East 65000.0 44000.0\r\n10 Paulo South 67000.0 45000.0\r\n<\/pre>\n
Explanation<\/h5>\n
Notice in the output that all instances of
-1<\/code> or-999<\/code> throughout the dataframe have been replaced bynp.NaN<\/code>.<\/p>\nTo do this, we simply typed the name of the dataframe, and then
.replace()<\/code> to call the method.<\/p>\nWe specified the values that we wanted to replace<\/em> with the code
to_replace = [-1,-999]<\/code>. The argument here to theto_replace<\/code> parameter is a list of values that we want to replace.<\/p>\nThe
value<\/code> parameter specifies the value we want to use as the replacement value. By settingvalue = np.NaN<\/code>, we’re specifying that the replacement value will benp.NaN<\/code>.<\/p>\nAgain: this syntax allows us to specify several possible values that we want to replace, using a Python list.<\/p>\n
<\/a><\/p>\n
EXAMPLE 3: Modify a dataframe inplace (i.e., replace and modify the original dataframe)<\/h3>\n
Now, let’s modify our dataframe “in place.” <\/p>\n
Essentially, we’re going to modify our dataframe directly.<\/p>\n
Remember: when we use the
replace()<\/code> method, by default, it keeps the original dataframe unchanged and outputs a new dataframe. <\/p>\nBy setting
inplace = True<\/code>, however,replace()<\/code> will directly operate on the original dataframe.<\/p>\nCreate copy of dataframe<\/h4>\n
Since directly modifying a dataframe can sometimes be risky, we’re going to make a copy of our dataframe first, and we’ll work with the copy.<\/p>\n
\r\nsales_data_copy = sales_data.copy()\r\n<\/pre>\n
Use the replace method<\/h4>\n
Now, let’s use the replace method.<\/p>\n
Here, we’re going to replace the values
-1<\/code> or-999<\/code> withnp.NaN<\/code>. So this code is very similar to example 2<\/a>.<\/p>\nBut we’re also going to set
inplace = True<\/code>. This will directly modify our dataframesales_data_copy<\/code>.<\/p>\nLet’s take a look:<\/p>\n
\r\nsales_data_copy.replace(to_replace = [-1,-999], value = np.NaN, inplace = True)\r\n<\/pre>\n
And now let’s print out the data:<\/p>\n
\r\nprint(sales_data_copy)\r\n<\/pre>\n
OUT:<\/p>\n
\r\n name region sales expenses\r\n0 William East 50000.0 42000.0\r\n1 Emma N 52000.0 43000.0\r\n2 Sofia East 90000.0 NaN\r\n3 Markus South NaN 44000.0\r\n4 Edward West 42000.0 38000.0\r\n5 Thomas West 72000.0 39000.0\r\n6 Ethan S 49000.0 42000.0\r\n7 Olivia West NaN NaN\r\n8 Arun West 67000.0 39000.0\r\n9 Anika East 65000.0 44000.0\r\n10 Paulo South 67000.0 45000.0\r\n<\/pre>\n
Explanation<\/h5>\n
Here, by setting
inplace = True<\/code>, we have permanently changedsales_data_copy<\/code>.<\/p>\nBe careful when you do this! You’ll effectively overwrite your data.<\/p>\n
Before you use
inplace = True<\/code>, you should probably test your code to make sure that it works properly.<\/p>\n<\/a><\/p>\n
EXAMPLE 4: Replace a specific value in a specific dataframe column<\/h3>\n
Next, we’ll replace a specific value in a specific column.<\/p>\n
The syntax for doing this is a little different. To accomplish this, we need to use the “dictionary” style syntax for the
replace()<\/code> method.<\/p>\nLet’s run the code, and then I’ll explain:<\/p>\n
\r\nsales_data.replace({'region':{'N':'North'}})\r\n<\/pre>\nOUT:<\/p>\n
\r\n name region sales expenses\r\n0 William East 50000 42000\r\n1 Emma North 52000 43000\r\n2 Sofia East 90000 -999\r\n3 Markus South -1 44000\r\n4 Edward West 42000 38000\r\n5 Thomas West 72000 39000\r\n6 Ethan S 49000 42000\r\n7 Olivia West -1 -1\r\n8 Arun West 67000 39000\r\n9 Anika East 65000 44000\r\n10 Paulo South 67000 45000\r\n<\/pre>\n
Explanation<\/h5>\n
Here, we’re looking for the value ‘
N<\/code>‘ only in theregion<\/code> variable.<\/p>\nTo do this we typed the name of the dataframe and called the
.replace()<\/code> method as we typically do. <\/p>\nBut inside the parenthesis, we provided a nested dictionary; a dictionary within a dictionary.<\/p>\n
Here, the “key” of the dictionary is the column we want to operate on. The “value” of the dictionary is another dictionary. This second dictionary contains the value we’re looking for, and the replacement value.<\/p>\n
So inside the parenthesis, the expression
{'region':{'N':'North'}}<\/code> means:<\/p>\n- \n
- look inside the
region<\/code> variable<\/li>\n- replace all instances of
'N'<\/code> with the value'North'<\/code><\/li>\n<\/ul>\nThis is a somewhat more complicated syntax, but it enables you to target specific values in specific columns.<\/p>\n
<\/a><\/p>\n
EXAMPLE 5: Replace different values in multiple different columns<\/h3>\n
Finally, let’s replace several different values in different columns.<\/p>\n
Pandas has a special syntax for doing this with dictionaries, but I think that the typical way of doing it in Pandas is just too d*mn complicated.<\/p>\n
So here, I’ll show you a different way. (I think this is a better and easier way.)<\/p>\n
Here, we’re going to use replace multiple times in a “chain”. Each time we use the method, we’ll replace a different value in a different column.<\/p>\n
By doing it this way, the code is easier to read, easier to debug, and easier to understand overall.<\/p>\n
Let’s take a look and then I’ll explain:<\/p>\n
\r\n(sales_data\r\n .replace({'region':{'N':'North'}})\r\n .replace({'region':{'S':'South'}})\r\n .replace({'sales':{-1:np.nan}})\r\n .replace({'expenses':{-1:np.nan}})\r\n .replace({'expenses':{-999:np.nan}})\r\n)\r\n<\/pre>\nOUT:<\/p>\n
\r\n name region sales expenses\r\n0 William East 50000.0 42000.0\r\n1 Emma North 52000.0 43000.0\r\n2 Sofia East 90000.0 NaN\r\n3 Markus South NaN 44000.0\r\n4 Edward West 42000.0 38000.0\r\n5 Thomas West 72000.0 39000.0\r\n6 Ethan South 49000.0 42000.0\r\n7 Olivia West NaN NaN\r\n8 Arun West 67000.0 39000.0\r\n9 Anika East 65000.0 44000.0\r\n10 Paulo South 67000.0 45000.0\r\n<\/pre>\n
Explanation<\/h5>\n
Here, we effectively used
replace()<\/code> five times in a row. <\/p>\nSpecifically, we used it to:<\/p>\n
- \n
- replace
'N'<\/code> with'North'<\/code> in theregion<\/code> variable<\/li>\n- replace
'S'<\/code> with'South'<\/code> in theregion<\/code> variable<\/li>\n- replace
-1<\/code> withnp.NaN<\/code> in thesales<\/code> variable<\/li>\n- replace
-1<\/code> withnp.NaN<\/code> in theexpenses<\/code> variable<\/li>\n- replace
-999<\/code> withnp.NaN<\/code> in theexpenses<\/code> variable<\/li>\n<\/ul>\nSo we’re doing multiple different replacements in a “chain” to clean up all of the inappropriate values.<\/p>\n
Notice also that to do this, we needed to enclose the whole expression inside of parenthesis. This is somewhat unorthodox, but it is a very powerful technique in Pandas. This “chaining” syntax enables you to perform many data wrangling operations in series<\/a> to do complex manipulations in a single block of code.<\/p>\n
If you want to learn more about how to use this “chaining” syntax for data manipulation in Pandas, then sign up for our email newsletter. You probably won’t see this coding style almost anywhere else.<\/p>\n
- replace
- replace all instances of
- look inside the
- Replace several possible values across all columns of a dataframe<\/a><\/li>\n
- Replace one specific value across all columns of a dataframe<\/a><\/li>\n
- replace a multiple values throughout the dataframe<\/a><\/li>\n
- replace a single value throughout the dataframe<\/a><\/li>\n
- Syntax<\/a><\/li>\n