filtering a column that has str and float numbers

user_14241181 0
Viewed 37

Assume we have a dataframe and we are interested in 1 column in order to create a new one. So let's deal with that column only. That column has the following format

df
A
n.a
0.74
0.3
1
n.a
...

I want to create a new column that:

  • whenever the value in the column A is n.a. OR smaller than 1 then set my new column equal to a variable that I have (which is str)
  • If the value is 1 then it should fill n.a.
    So in our previous example the dataframe should look something like this
A         New col
n.a       15/2/2022
0.74      15/2/2022
0.3       15/2/2022
1         n.a.
n.a       15/2/2022
...

I tried the following

   df['New col'] = 'n.a.'
   if df['A']!='n.a.':
       if df['A']< 1:
           df['New col']=date

I am sure this is not the optimal or even a good way to do it. However, I get the following error

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

which I do not understand why

pandas filter python
1 Answers

If need set to n.a. only if greater or equal 1 use numpy.where with to_numeric and errors='coerce':

date = '15/2/2022'
df['new'] = np.where(pd.to_numeric(df['A'], errors='coerce') >= 1, 'n.a.', date)

print (df)
      A        new
0   n.a  15/2/2022
1  0.74  15/2/2022
2   0.3  15/2/2022
3     1       n.a.
4   n.a  15/2/2022
user_29010020
Related