0

I am trying to replace the value in my whole dataframe if part of the string match. I tried to use df.replace df.str.contains etc but none of them are working. Here are similar questions but they are not answering my question [sample][1]:

I have datframe like this: df

Brand                        2Brand                         3Brand
Audi                          BMW                           None of the above
None from list                BMW                           Audi
None                          None below or above           BMW
NaN                           Audi                          NaN

I just want to replace everywhere None appear.

The desired otput would be like this:

Brand                        2Brand                         3Brand
Audi                          BMW                           None
None                          BMW                           Audi
None                          None                          BMW
NaN                           Audi                          NaN
s_khan92
  • 969
  • 8
  • 21

2 Answers2

2

Use DataFrame.mask with Series.str.contains per columns in DataFrame.apply, for avoid convert possible None and NaN values is used na=False parameter:

df = df.mask(df.apply(lambda x: x.str.contains('None', na=False)), 'None')
print (df)
  Brand 2Brand 3Brand
0  Audi    BMW   None
1  None    BMW   Audi
2  None   None    BMW

EDIT: If possible some numeric columns use DataFrame.select_dtypes for get only strings columns (obviously objects ate strings) and then add to mask numeric column filled by False by DataFrame.reindex:

print (df)
            Brand               2Brand             3Brand  col
0            Audi                  BMW  None of the above    4
1  None from list                  BMW               Audi    7
2            None  None below or above                BMW    9
3             NaN                 Audi                NaN    5

mask = (df.select_dtypes(object)
          .apply(lambda x: x.str.contains('None', na=False))
          .reindex(df.columns, fill_value=False, axis=1))
df = df.mask(mask, None)
print (df)
  Brand 2Brand 3Brand  col
0  Audi    BMW   None    4
1  None    BMW   Audi    7
2  None   None    BMW    9
3   NaN   Audi    NaN    5
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks but i have alot of columns and dont want to change datatype for all and as i need to apply to whole df. There are some columns which have no str data type... so i am getting this error `AttributeError: Can only use .str accessor with string values!` – s_khan92 Jun 04 '20 at 10:56
  • I dont want to change NaN values. They should remain NaN. I want to change only where there is Keyword 'None'. Its working but it changed all the NaN values to 'None" too. – s_khan92 Jun 04 '20 at 11:04
  • @s_khan92 - Do you need replace to `None` like [Nonetype](https://stackoverflow.com/questions/21095654/what-is-a-nonetype-object) or like `None` like string `'None'` ? – jezrael Jun 04 '20 at 11:06
  • 1
    Only a string "None" – s_khan92 Jun 04 '20 at 11:07
  • @s_khan92 - Ok, so need `('None', na=True)` change to `('None', na=False)` and `df.mask(mask, None)` to `df.mask(mask, 'None')` – jezrael Jun 04 '20 at 11:08
  • It changed all the values with "Obj" datatype to None whether there is string "None" or not. I just want to change the values "None" when the part of the string contains "None" in the values. – s_khan92 Jun 04 '20 at 11:13
  • I made small edit in my question. I add another extra row where there is NaN value that i dont want to change. I just want to change the values where there is a string "None" like` "None of the above"` -> `'None' or 'None of the below"` -> `'None'`. There are `NaN` values but no `NoneType`, i dont want to change NaN values. – s_khan92 Jun 04 '20 at 11:19
  • I don't know why its not working on my dataframe.. There are some values which contains string like 'YouTube' and 'Facebook' and that are also converted to None – s_khan92 Jun 04 '20 at 11:24
  • @s_khan92 - it seems data related problem, there are not strings like `YouTube some long string None` ? – jezrael Jun 04 '20 at 11:25
  • I added the original dataframe screeshot. – s_khan92 Jun 04 '20 at 11:57
  • @s_khan92 - Unfortuantely I am cannot check type or data from picture, I need real data for find problem. – jezrael Jun 04 '20 at 12:03
1

Use applymap and mask:

In [1732]: df.mask(df.applymap(lambda x: 'None' in x), None)
Out[1732]: 
  Brand 2Brand 3Brand
0  Audi    BMW   None
1  None    BMW   Audi
2  None   None    BMW
Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58