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

Question

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'])

anky · Accepted Answer · 2019-09-19 16:02:07Z

2

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

answered Sep 19, 2019 at 16:02

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

Comments

BENY · Accepted Answer · 2019-09-19 16:01:42Z

2

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

answered Sep 19, 2019 at 16:01

BENY

324k22 gold badges176 silver badges250 bronze badges

Comments

ansev · Accepted Answer · 2019-09-19 16:08:37Z

1

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

answered Sep 19, 2019 at 16:08

ansev

31k5 gold badges21 silver badges33 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related