CODEWITHSUNDEEP
pythonpandasdataframe
Apalnay
Apalnay

Reputation: 25

Replace values by NaN in certain rows of a pandas df given a specific condition in another df

I have this initial df

xpos = pd.DataFrame({'N': [1, 2, 3, 4],'Type': [External, External, Internal, Internal]})

And then I have this other one

L = pd.DataFrame({'N': [1, 2, 3, 4],'Length': [100, 300, 400, 200]})

In dataframe 'L' I need to change the values in column named 'Length' by 'NaN' for every row in first dataframe 'xpos' defined as 'External'. This means substituting 100 and 300 by 'NaN'. This is meant to work with a larger dataframe so I cannot change the values individually and it should detect which value of the 'N' column is external and change its length to 'NaN'

I have tried with a loop for but it is not going well

for i in range (0, len(xpos)):       
    if xpos.loc[i,'Type'] == 'External':
        VLDext=xpos.loc[i, 'N']
        L.loc[VLDext, 0]='NaN'

Upvotes: 1

Views: 493

Answers (2)

Code Different
Code Different

Reputation: 93151

Assuming the two data frames have the same length and are aligned on N:

mask = xpos['Type'] == 'External'
L.loc[mask, 'Length'] = np.nan

Upvotes: 0

Utsav
Utsav

Reputation: 5918

Of what I have understood, we are based on the values of xpos Type as External we need to update Length column in L as nan.

We are merging L and xpos on column N and then based on Type External in xpos we are updating Length of L by nan.

Code

L['Length'] = np.where(L.merge(xpos, on='N', how='inner').Type == 'External',np.nan,L.Length)

Output

    N   Length
0   1   NaN
1   2   NaN
2   3   400.0
3   4   200.0

Upvotes: 1

Related Questions