Replacing values greater than a number in pandas dataframe

Question

I have a large dataframe which looks as:

df1['A'].ix[1:3]
2017-01-01 02:00:00    [33, 34, 39]
2017-01-01 03:00:00    [3, 43, 9]

I want to replace each element greater than 9 with 11.

So, the desired output for above example is:

df1['A'].ix[1:3]
2017-01-01 02:00:00    [11, 11, 11]
2017-01-01 03:00:00    [3, 11, 9]

Edit:

My actual dataframe has about 20,000 rows and each row has list of size 2000.

Is there a way to use numpy.minimum function for each row? I assume that it will be faster than list comprehension method?

So values are not in list? Ithink `df[df > 9] = 11` solution is wrong. Or something missing? — jezrael, Jan 08 '19 at 14:58

score 45 · Answer 1 · answered Oct 02 '18 at 09:10

45

Very simply : df[df > 9] = 11

answered Oct 02 '18 at 09:10

Edouard Cuny

1,051
1
10
10

11

Solution is wrong, because not working with input data. – jezrael Jan 08 '19 at 14:58
This returns a 'SettingWithCopyWarning'. – M.G.Poirot Apr 25 '22 at 16:02

jezrael · Accepted Answer · 2017-05-03T11:05:57.037

38

You can use apply with list comprehension:

df1['A'] = df1['A'].apply(lambda x: [y if y <= 9 else 11 for y in x])
print (df1)
                                A
2017-01-01 02:00:00  [11, 11, 11]
2017-01-01 03:00:00    [3, 11, 9]

Faster solution is first convert to numpy array and then use numpy.where:

a = np.array(df1['A'].values.tolist())
print (a)
[[33 34 39]
 [ 3 43  9]]

df1['A'] = np.where(a > 9, 11, a).tolist()
print (df1)
                                A
2017-01-01 02:00:00  [11, 11, 11]
2017-01-01 03:00:00    [3, 11, 9]

edited May 03 '17 at 11:05

answered May 03 '17 at 10:55

jezrael

729,927
78
1,141
1,090

Since, there dataframe is very large I was hoping to use numpy.minimum function for each row or will the speed be the same ? – Zanam May 03 '17 at 10:57
There are always `3` values in list? – jezrael May 03 '17 at 10:58

score 23 · Answer 3 · answered Jan 29 '19 at 17:06

23

You can use numpy indexing, accessed through the .values function.

df['col'].values[df['col'].values > x] = y

where you are replacing any value greater than x with the value of y.

So for the example in the question:

df1['A'].values[df1['A'] > 9] = 11

answered Jan 29 '19 at 17:06

D.Griffiths

1,993
3
12
29

1

This was the best solution I could find that worked as expected. – f.thorpe Jul 01 '21 at 20:34

score 23 · Answer 4 · edited Mar 28 '21 at 00:07

I know this is an old post, but pandas now supports DataFrame.where directly. In your example:

df.where(df <= 9, 11, inplace=True)

Please note that pandas' where is different than numpy.where. In pandas, when the condition == True, the current value in the dataframe is used. When condition == False, the other value is taken.

EDIT:

You can achieve the same for just a column with Series.where:

df['A'].where(df['A'] <= 9, 11, inplace=True)

score 5 · Answer 5 · answered Sep 18 '19 at 08:07

5

I came for a solution to replacing each element larger than h by 1 else 0, which has the simple solution:

df = (df > h) * 1

(This does not solve the OP's question as all df <= h are replaced by 0.)

answered Sep 18 '19 at 08:07

CFW

166
3
9

Why do you write it as an answer, if it doesn't answer the OP's question? – Geshode Sep 18 '19 at 08:29
6

Because the title (which led me and potentially others to come here) is imprecise and could imply this answer. – CFW Sep 19 '19 at 16:01

Replacing values greater than a number in pandas dataframe

5 Answers5

Linked