use values in dictionary to replace values in column

Question

import pandas as pd
df= pd.DataFrame({'Data':['Hey this is 123456 Jonny B Good',
                              'This is Jonny B Good at 511-233-1137',
                                  'Wow that is Alice N Wonderland A999b',
                                  'Yes hi: Mick E Mouse 1A25629Q88 or ',
                            'Bye Mick E Mouse A13B ok was seen on '], 
                          'E_ID': ['E11','E11', 'E22', 'E33', 'E33'],
                           'N_ID' : ['111', '112', '211', '311', '312'],
                           'Name' : ['JONNY B GOOD', 'JONNY B GOOD', 
                                      'ALICE N WONDERLAND',
                                      'MICK E MOUSE', 'MICK E MOUSE'],        
                          })

df
                      Data                 E_ID N_ID    Name
0   Hey this is 123456 Jonny B Good         E11 111 JONNY B GOOD
1   This is Jonny B Good at 511-233-1137    E11 112 JONNY B GOOD
2   Wow that is Alice N Wonderland A999b    E22 211 ALICE N WONDERLAND
3   Yes hi: Mick E Mouse 1A25629Q88 or      E33 311 MICK E MOUSE
4   Bye Mick E Mouse A13B ok was seen on    E33 312 MICK E MOUSE

I have a sample df as seen above. I also have sample dictionary d as seen below

d = {'E11': ['Jonny',
  'B',
  'Good',
   'Jonny',
   'B',
  'Good',
   '123456',
    '511-233-1137'],

'E22': ['Alice',
  'N',
  'Wonderland',
  'A999b'],

'E33': ['Mick', 
        'E' , 
        'Mouse',
        'Mick', 
        'E' , 
        'Mouse',
        '1A25629Q88',
  'A13B',]}

I would like use the values from d e.g. Jonny to change the corresponding value in Data. So e.g. Jonny in row 0 will become @@@.

To do so, I have looked Remap values in pandas column with a dict and how to replace column values with dictionary keys in pandas but they arent much help. I think I need to use something like this

 df['New'] = df['Data'].str.replace(d[value], '@@@')

I would like my output to look like this

     Data   E_ID N_ID Name  New
0                           Hey this is @@@ @@@ @@@ @@@             
1                           This is @@@  @@@  @@@  at @@@   
2                           Wow that is @@@  @@@  @@@  @@@  
3                           Yes hi: @@@  @@@  @@@  @@@  or      
4                           Bye @@@  @@@  @@@  @@@  ok was seen on

What do I need to do to get this output?

score 0 · Accepted Answer · answered Sep 03 '19 at 23:07

You could generate and use regular expressions, like this:

df['New']= df['Data']
for key, value in d.items():   
    regex='({alternatives})'.format(alternatives='|'.join(value))
    df.loc[df['E_ID']==key, 'New']= df.loc[df['E_ID']==key, 'New'].str.replace(regex, '@@@')

The result looks like this:

Out[115]: 
                                    Data E_ID N_ID                Name                                  New
0        Hey this is 123456 Jonny B Good  E11  111        JONNY B GOOD          Hey this is @@@ @@@ @@@ @@@
1   This is Jonny B Good at 511-233-1137  E11  112        JONNY B GOOD           This is @@@ @@@ @@@ at @@@
2   Wow that is Alice N Wonderland A999b  E22  211  ALICE N WONDERLAND          Wow that is @@@ @@@ @@@ @@@
3    Yes hi: Mick E Mouse 1A25629Q88 or   E33  311        MICK E MOUSE          Yes hi: @@@ @@@ @@@ @@@ or 
4  Bye Mick E Mouse A13B ok was seen on   E33  312        MICK E MOUSE  Bye @@@ @@@ @@@ @@@ ok was seen on

would you please explain what `regex='({alternatives})'.format(alternatives='|'.join(value))` is doing? — , Sep 04 '19 at 19:36

Andy L. · Answer 2 · 2019-09-04T20:47:26.770

Convert d to dict of dicts d1. pivot to make E_ID to columns and replace using d1, and bfill and select 1st columns. Finally, assign back to df.Data

d1 = {k: {x: '@@@' for x in v} for k, v in d.items()}
df['Data'] = (df.pivot(columns='E_ID', values='Data')
                .replace(d1, regex=True).bfill(1).iloc[:,0])

Out[619]:
                                  Data E_ID N_ID                Name
0  Hey this is @@@ @@@ @@@ @@@          E11  111  JONNY B GOOD
1  This is @@@ @@@ @@@ at @@@           E11  112  JONNY B GOOD
2  Wow that is @@@ @@@ @@@ @@@          E22  211  ALICE N WONDERLAND
3  Yes hi: @@@ @@@ @@@ @@@ or           E33  311  MICK E MOUSE
4  Bye @@@ @@@ @@@ @@@ ok was seen on   E33  312  MICK E MOUSE

Extra: as you asked equivalent of dictcomp above in for-loop format:

d1 = {}
for k, v in d.items():
    y = {}
    for x in v:
        y[x] = '@@@'
    d1[k] = y


In [805]: d1
Out[805]:
{'E11': {'Jonny': '@@@',
  'B': '@@@',
  'Good': '@@@',
  '123456': '@@@',
  '511-233-1137': '@@@'},
 'E22': {'Alice': '@@@', 'N': '@@@', 'Wonderland': '@@@', 'A999b': '@@@'},
 'E33': {'Mick': '@@@',
  'E': '@@@',
  'Mouse': '@@@',
  '1A25629Q88': '@@@',
  'A13B': '@@@'}}

@Andy_L what would `{k: {x: '@@@' for x in v} for k, v in d.items()}` look like in a non comprehension way? — , Sep 04 '19 at 19:38
I have something like this but I am missing something `d1 = {} for k,v in d.items(): for x in v: d1[x] = '@@@'` — , Sep 04 '19 at 19:39
@510: for-loop version requires properly tabs so I edited the answer to add it at the end. Please check my edited. — Andy L., Sep 04 '19 at 20:35

use values in dictionary to replace values in column

2 Answers2

Linked