CODEWITHSUNDEEP
pandasdataframe
Lymacro
Lymacro

Reputation: 35

Python: How replace non-zero values in a Pandas dataframe with values from a series

I have a dataframe 'A' with 3 columns and 4 rows (X1..X4). Some of the elements in 'A' are non-zero. I have another dataframe 'B' with 1 column and 4 rows (X1..X4). I would like to create a dataframe 'C' so that where 'A' has a nonzero value, it takes the value from the equivalent row in 'B'

I've tried a.where(a!=0,c)..obviously wrong as c is not a scalar

A = pd.DataFrame({'A':[1,6,0,0],'B':[0,0,1,0],'C':[1,0,3,0]},index=['X1','X2','X3','X4'])

B =  pd.DataFrame({'A':{'X1':1.5,'X2':0.4,'X3':-1.1,'X4':5.2}})

These are the expected results:

C = pd.DataFrame({'A':[1.5,0.4,0,0],'B':[0,0,-1.1,0],'C':[1.5,0,-1.1,0]},index=['X1','X2','X3','X4'])

Upvotes: 1

Views: 288

Answers (3)

ansev
ansev

Reputation: 30920

Use:

A.mask(A!=0,B['A'],axis=0,inplace=True)
print(A)

       A      B     C
X1   1.5    0.0   1.5
X2   0.4    0.0   0.0
X3   0.0   -1.1  -1.1
X4   0.0    0.0   0.0

Upvotes: 1

anky
anky

Reputation: 75080

np.where():

If you want to assign back to A:

A[:]=np.where(A.ne(0),B,A)

For a new df:

final=pd.DataFrame(np.where(A.ne(0),B,A),columns=A.columns)

     A    B    C
0  1.5  0.0  1.5
1  0.4  0.0  0.0
2  0.0 -1.1 -1.1
3  0.0  0.0  0.0

Upvotes: 2

BENY
BENY

Reputation: 323236

Usage of fillna 

A=A.mask(A.ne(0)).T.fillna(B.A).T
A
Out[105]: 
      A    B    C
X1  1.5  0.0  1.5
X2  0.4  0.0  0.0
X3  0.0 -1.1 -1.1
X4  0.0  0.0  0.0

Or 

A=A.mask(A!=0,B.A,axis=0)
Out[111]: 
      A    B    C
X1  1.5  0.0  1.5
X2  0.4  0.0  0.0
X3  0.0 -1.1 -1.1
X4  0.0  0.0  0.0

Upvotes: 2

Related Questions