Replace string/value in entire DataFrame

Question

I have a very large dataset were I want to replace strings with numbers. I would like to operate on the dataset without typing a mapping function for each key (column) in the dataset. (similar to the fillna method, but replace specific string with assosiated value). Is there anyway to do this?

Here is an example of my dataset

data
   resp          A          B          C
0     1       poor       poor       good
1     2       good       poor       good
2     3  very good  very good  very good
3     4       bad        poor       bad 
4     5   very bad   very bad   very bad
5     6       poor       good   very bad
6     7       good       good       good
7     8  very good  very good  very good
8     9       bad        bad    very bad
9    10   very bad   very bad   very bad

The desired result:

 data
   resp  A  B  C
0      1  3  3  4
1     2  4  3  4
2     3  5  5  5
3     4  2  3  2
4     5  1  1  1
5     6  3  4  1
6     7  4  4  4
7     8  5  5  5
8     9  2  2  1
9    10  1  1  1

very bad=1, bad=2, poor=3, good=4, very good=5

//Jonas

In more recent versions of pandas, there are more performant alternatives involving map and pd.Categorical. See this answer. — cs95
– cs95, Commented Jan 23, 2019 at 8:14

waitingkuo · Accepted Answer · 2013-06-17 07:43:01Z

78

Use replace

In [126]: df.replace(['very bad', 'bad', 'poor', 'good', 'very good'], 
                     [1, 2, 3, 4, 5]) 
Out[126]: 
      resp  A  B  C
   0     1  3  3  4
   1     2  4  3  4
   2     3  5  5  5
   3     4  2  3  2
   4     5  1  1  1
   5     6  3  4  1
   6     7  4  4  4
   7     8  5  5  5
   8     9  2  2  1
   9    10  1  1  1

answered Jun 17, 2013 at 7:43

waitingkuo

94.5k28 gold badges119 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alison S Over a year ago

This isn't working in 0.20.1. See pandas.pydata.org/pandas-docs/version/0.20/generated/… for new syntax.

Sandeep Singhal Over a year ago

Above with a minor variation should work df.replace(['very bad', 'bad', 'poor', 'good', 'very good'], [1, 2, 3, 4, 5], inplace=True)

Jash Shah · Accepted Answer · 2017-07-11 10:30:39Z

13

Considering data is your pandas DataFrame you can also use:

data.replace({'very bad': 1, 'bad': 2, 'poor': 3, 'good': 4, 'very good': 5}, inplace=True)

answered Jul 11, 2017 at 10:30

Jash Shah

2,1644 gold badges29 silver badges44 bronze badges

Comments

product_nick · Accepted Answer · 2020-03-11 22:01:14Z

1

data = data.replace(['very bad', 'bad', 'poor', 'good', 'very good'], [1, 2, 3, 4, 5])

You must state where the result should be saved. If you say only data.replace(...) it is only shown as a change in preview, not in the envirable itself.

answered Mar 11, 2020 at 22:01

product_nick

931 silver badge4 bronze badges

Collectives™ on Stack Overflow

Replace string/value in entire DataFrame

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related