0

df1

Pet                            
Dog-Ralph                     
Cat                                  
2016-11-03 00:00:00

I have the dataframe above and I want to turn the '2016-11-03 00:00:00' value into a NaN value. Since it is the only value that contains a colon, how do I turn that value into NaN like below:

Pet                            
Dog-Ralph                     
Cat                                  
NaN

Appreciate the help!

sschade
  • 75
  • 8
  • It should be converted to `NaN` because it contains a colon? Is there only one value that needs to change or all values that contain colons should change to NaN? – Tadhg McDonald-Jensen Dec 22 '16 at 16:25
  • Any value in Column 'Pet'. There could be multiple in my dataset that have this attribute. – sschade Dec 22 '16 at 16:32

2 Answers2

2
import pandas as pd
import numpy as np

data = [{'name': 'Pet'},{'name':'Dog-Ralph'},{'name':'Cat'},{'name':'2016-11-03 00:00:00'}]

df = pd.DataFrame.from_dict(data, orient='columns')

df

output:

    name
0   Pet
1   Dog-Ralph
2   Cat
3   2016-11-03 00:00:00

Find and replace using np.where as its faster : Read more

df['name'] = np.where(df['name'].str.contains(":"), np.nan, df['name'])

df

output:

    name
0   Pet
1   Dog-Ralph
2   Cat
3   NaN

See np.where performance details : Why is np.where faster than pd.apply

Community
  • 1
  • 1
Vikash Singh
  • 13,213
  • 8
  • 40
  • 70
1

You can try this:

df

#   Pet
#0  Dog-Ralph
#1  Cat
#2  2016-11-03 00:00:00

import numpy as np
df[df["Pet"].notnull() & df['Pet'].str.contains(":")] = np.nan
# replace any non-nan string with `colon` with np.nan    

df
#   Pet
#0  Dog-Ralph
#1  Cat
#2  NaN

Or if you only need to replace the Pet column:

df.loc[df["Pet"].notnull() & df["Pet"].str.contains(":"), "Pet"] = np.nan
Psidom
  • 209,562
  • 33
  • 339
  • 356
  • I get the following error below when I run this: ValueError: cannot index with vector containing NA / NaN values – sschade Dec 22 '16 at 16:36
  • Check the updated version, it's likely that you have some missing values in the column already, use `notnull()` to convert them to `False`. – Psidom Dec 22 '16 at 16:39
  • Interesting, it worked for me for the dummy example, Perhaps, your data contains some special cases I can't think of. – Psidom Dec 22 '16 at 16:51