replace unique values in a pandas dataframe with random numbers

Question

I have a pandas dataframe where I would like to replace some unique values by a random normal number. In the example below, the value to replace is 0.

import numpy as np
import pandas as pd

dates = pd.date_range('20160101', periods=10)
x = [1.0,2.0,10.0,9.0,0,7.0,6.0,0,3.0,9.0]
df = pd.DataFrame(x,index=dates,columns=['A'])

             A
2016-01-01   1.000000
2016-01-02   2.000000
2016-01-03  10.000000
2016-01-04   9.000000
2016-01-05   0.000000
2016-01-06   7.000000
2016-01-07   6.000000
2016-01-08   0.000000
2016-01-09   3.000000
2016-01-10   9.000000

This is what I have:

df['A'] = df.A.replace(to_replace =0, value =  np.random.normal(0,1))

Which replaces the zeros with the same value.

A
2016-01-01   1.000000
2016-01-02   2.000000
2016-01-03  10.000000
2016-01-04   9.000000
2016-01-05   6.993988
2016-01-06   7.000000
2016-01-07   6.000000
2016-01-08   6.993988
2016-01-09   3.000000
2016-01-10   9.000000

I would like different values. How can I do that?

Answer 1

Try this:

In [51]:
dates = pd.date_range('20160101', periods=10)    ​
x = [1.0,2.0,10.0,9.0,0,7.0,6.0,0,3.0,9.0]
df = pd.DataFrame(x,index=dates,columns=['A'])    ​
df

Out[51]:
             A
2016-01-01   1
2016-01-02   2
2016-01-03  10
2016-01-04   9
2016-01-05   0
2016-01-06   7
2016-01-07   6
2016-01-08   0
2016-01-09   3
2016-01-10   9

In [56]:
df.loc[df['A'] == 0,'A'] = np.random.normal(0,1, len(df.loc[df['A'] == 0]))
df

Out[56]:
                    A
2016-01-01   1.000000
2016-01-02   2.000000
2016-01-03  10.000000
2016-01-04   9.000000
2016-01-05   0.259048
2016-01-06   7.000000
2016-01-07   6.000000
2016-01-08   0.623833
2016-01-09   3.000000
2016-01-10   9.000000

Basically you need to pass the number of random samples to generate, as you don't pass a size, it returns a scalar value so all replaced values are the same.

See the docs: http://docs.scipy.org/doc/numpy-1.10.1/reference/generated/numpy.random.normal.html

Also possible to use apply here:

In [94]:
df.loc[df['A'] == 0,'A'] = df['A'].apply(lambda x: np.random.normal(0,1))
df

Out[94]:
                    A
2016-01-01   1.000000
2016-01-02   2.000000
2016-01-03  10.000000
2016-01-04   9.000000
2016-01-05   2.794664
2016-01-06   7.000000
2016-01-07   6.000000
2016-01-08  -0.524947
2016-01-09   3.000000
2016-01-10   9.000000

Answer 2

I had a similar problem recently and created a function. Try this modified function:

def replace_zeros_w_random_normal(DF,label, mu, sigma):
    truth_1 = DF[label] == 0
    random = np.random.normal(mu, sigma, DF.shape[0])
    filt = DF[DF[label] > 0]
    vector_1 = truth_1 * random
    truth_2 = vector_1 == 0
    vector_2 = truth_2 * DF[label]
    DF[label] = np.maximum(vector_1,vector_2)
    return DF

Then run:

replace_zeros_w_random_normal(df,'A ,1,0.1)