CODEWITHSUNDEEP
pythonpandasdataframenull
ds_student
ds_student

Reputation: 183

Filling nan if row in another column meets condition

I have the following dataframe: 

df=pd.DataFrame({'state':['AL','WI','FL','NJ','BM'],'country':['USA','USA','','','']})

I am trying to fill in my country column as 'USA' if the corresponding state row is following state list: 

states = ['AL', 'WI', 'AZ', 'FL', 'NJ', 'CO', 'CT', 'NY']

I reviewed the following related SO post: Python Dataframe filling NaN values using information from other columns

Thought the question is similar, I am unable to use the apply function to my case since I don't know how to check if another column value is in a list of values. I tried the following (unsuccessful) code: 

 df['country'] = values.where(df['country'] == np.nan and df['state'] in states, others=df['country'])
    ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Upvotes: 1

Views: 3202

Answers (1)

anky
anky

Reputation: 75080

Assumung the spaces are np.nan , if not, you can replace then by df=df.replace('',np.nan) you can use numpy.where() for faster results:

df.country=np.where(df.state.isin(states),df.country.fillna('USA'),df.country)
print(df)

  state country
0    AL     USA
1    WI     USA
2    FL     USA
3    NJ     USA
4    BM     NaN

Upvotes: 2

Related Questions