CODEWITHSUNDEEP
python-3.xpandas
yangyangliu
yangyangliu

Reputation: 33

how to replace a string with a value in pandas?

There are data from csv file using read_csv(). Now they are pandas data frames. They may be like the following.

1     2 11    
inf   2  1
1   inf  3

I used the code:

df = df.replace('inf', 1000000)

or

df.replace('inf', 1000000, inplace=True)

So they can not replace inf string with a scalar 1000000.

How to replace inf with 1000000?

Upvotes: 2

Views: 71

Answers (2)

cs95
cs95

Reputation: 402553

I would suggest using df.clip_upper if establishing an upper bound:

df.clip_upper(1000000)

           a          b     c
0        1.0        2.0  11.0
1  1000000.0        2.0   1.0
2        1.0  1000000.0   3.0

Otherwise, you can use np.isfinite and set values:

df.where(np.isfinite(df), 1000000)
# df.mask(~np.isfinite(df), 1000000)

           a          b   c
0        1.0        2.0  11
1  1000000.0        2.0   1
2        1.0  1000000.0   3

If NaNs should not be affected, use

df.where(np.isfinite(df) | np.isnan(df), 1000000)

           a          b   c
0        1.0        2.0  11
1  1000000.0        2.0   1
2        1.0  1000000.0   3

You can also do this with isin:

df.where(~df.isin([np.inf]), 1000000)
# df.where(~np.isin(df, np.inf), 1000000)
# df.mask(df.isin([np.inf]), 1000000)

           a          b   c
0        1.0        2.0  11
1  1000000.0        2.0   1
2        1.0  1000000.0   3

There is an in-place version of the above using np.where:

df[:] = np.where(np.isin(df, np.inf), 10000000, df)

Or,

pd.DataFrame(np.where(np.isin(df, np.inf), 10000000, df), 
             index=df.index, 
             columns=df.columns)

           a          b   c
0        1.0        2.0  11
1  1000000.0        2.0   1
2        1.0  1000000.0   3

Performance 

df_ = df.copy()
df = pd.concat([df_] * 10000, ignore_index=True)

%timeit df.replace(np.inf, 1000000)
%timeit df.where(np.isfinite(df) | np.isnan(df), 1000000)
%timeit df.where(np.isfinite(df), 1000000)
%timeit df.clip_upper(1000000)

9.44 ms ± 157 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
4.26 ms ± 38.9 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
3.37 ms ± 114 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
605 µs ± 17.7 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

Upvotes: 1

jezrael
jezrael

Reputation: 862761

Use np.inf, because 'inf' is string representation of inf:

print (df)
          a         b   c
0  1.000000  2.000000  11
1       inf  2.000000   1
2  1.000000       inf   3

df = df.replace(np.inf, 1000000)

print (df)
           a          b   c
0        1.0        2.0  11
1  1000000.0        2.0   1
2        1.0  1000000.0   3

Upvotes: 2

Related Questions