How to replace a word in a column, which is present in the pandas dataframe by another word in the same column?

Question

I have placed this in the df1 data frame in my code.

I have modified this row by converting every letter to lowercase.

df1 = df1.applymap(lambda s:s.lower() if type(s) == str else s)

And then I have replaced every hyphen '-'or ' - ' and spaces ' ' with underscores '_' using

  list=[]
for x in df1['route']:
    list.append(x.replace(" - ", "_").replace(" ","_").replace("-","_").replace("  ","_"))
df1['route'] = list

Now, I have different abbreviations which I don't want in my output. I want to replace them with full-forms, like _del to _delhi, _blr to _banglore, _vara to _varanasi, _hyd to _hyderabad.

And also _del_ to _delhi, _blr_ to _banglore_, _vara_ to _varanasi_, _hyd_ to _hyderabad_.

So, I have written these lines of code for this, as I have renamed Route Name column to route

   for idx in range(len(df1)):
   df1.loc[idx,'route']  =  df1.loc[idx,'route'].replace('del_','delhi_').replace('blr_','banglore_').replace('vara_','varanasi_').replace('hyd_','hyderabad_').replace('_blr_','_banglore_').replace('_vara_','_varanasi_').replace('_hyd_','_hyderabad_').replace('_del_','_delhi_')

The problem I am facing right now is that sometimes the data comes like delhi_to_vara or _chennai_blr or luckhnow_to_vara and so on.

I am not getting the clue about how to convert the string to their full form when they are ending with,

_vara, _blr, _hyd, _del to _varanasi, _banglore, _hyderabad, _delhi

Right now the data looks like this [ See 2nd row, the del remains the same. ]and other abbreviations too which just end with _name [2]

The code I have written for all this process to convert it in the desired form is this

 df1 = df1.applymap(lambda s:s.lower() if type(s) == str else s)            

        list=[]
        for x in df1['route']:
            list.append(x.replace(" - ", "_").replace(" ","_").replace("-","_").replace("  ","_"))
        df1['route'] = list     

        for idx in range(len(df1)):
           df1.loc[idx,'route']  =  df1.loc[idx,'route'].replace('del_','delhi_').replace('blr_','banglore_').replace('vara_','varanasi_').replace('hyd_','hyderabad_').replace('_blr_','_banglore_').replace('_vara_','_varanasi_').replace('_hyd_','_hyderabad_').replace('_del_','_delhi_')         .
           if df1['route'][idx].endswith('_del'):
                 df1['route'][idx].replace('_del','_delhi')
           if df1['route'][idx].endswith('_vara'):
                 df1['route'][idx].replace('_vara','_varanasi')
           if df1['route'][idx].endswith('_blr'):
                 df1['route'][idx].replace('_blr','_banglore')
           if df1['route'][idx].endswith('_hyd'):
                 df1['route'][idx].replace('_hyd','_hyderabad')

Route name
Del-Manali
Manali-Del
Del to Katra - 46 Sleeper
Delhi to Manali 8:30 PM
Manali to Delhi 8:00
Delhi to Manali 5:00 PM
DELHI TO KATRA 46 SEATER 8:00 PM
Lucknow to Vara
DELHI_MANALI_05:00PM
Delhi To Manali 10:30pm
MANALI TO DEL 5:00 PM
10:15 Lucknow To Vara
MANALI_DELHI_6:00PM
MANALI_DELhi_4.00PM
Delhi_Manali_6:00PM
BLR_CHENNAI_1:30PM
CHENNAI_BLR_11:00PM
BANGALORE_CHENNAI_11:05PM
CHENNAI_BANGALORE_5:30AM
Hyd_Blr
BANGALORE_HYDERABAD_ 7:45PM
CHENNAI_BANGALORE_5:45AM
DELHI TO DEHRADUN
DEHRADUN TO DELHI
Delhi to Katra UP22T8671
BANGALORE_CHENNAI_10:45PM
Delhi-Lucknow 9:00PM 30 sleeper
CHENNAI_BANGALORE_10:30PM
Lucknow_Vara_10:30PM
VARANASI_LUCKNOW_9:30PM
Delhi to Jalandhar 10:00 PM
JALANDHAR TO DELHI 11:00 AM
BANGALORE_CHENNAI_5:05AM
DELHI_AMRITSAR_23:00PM
AMRITSAR_DELHI_11:00PM
BANGALORE_COIMBATORE_11:30PM
Coimbatore_Bangalore_11:00PM

user_9375102 · Answer

First we create a dictionary for your string replacements and placing a word boundry \b before and after them. you could build this up easier but i've hard coded it.

we then pass it as an argument in replace

repl_dict = {r'\bdel\b' : 'delhi',
            r'\bvara\b' : 'varanasi',
            r'\bblr\b' : 'banglore',
            r'\bhyd\b' : 'hyerabad'}

df['R'] = df['Route name'].str.replace('-|_',' ').str.lower().replace(repl_dict,regex=True)

print(df)

                          Route name                                 R
0                         Del-Manali                      delhi manali
1                         Manali-Del                      manali delhi
2          Del to Katra - 46 Sleeper       delhi to katra   46 sleeper
3            Delhi to Manali 8:30 PM           delhi to manali 8:30 pm
4               Manali to Delhi 8:00              manali to delhi 8:00
5            Delhi to Manali 5:00 PM           delhi to manali 5:00 pm
6   DELHI TO KATRA 46 SEATER 8:00 PM  delhi to katra 46 seater 8:00 pm
7                    Lucknow to Vara               lucknow to varanasi
8               DELHI_MANALI_05:00PM              delhi manali 05:00pm
9            Delhi To Manali 10:30pm           delhi to manali 10:30pm
10             MANALI TO DEL 5:00 PM           manali to delhi 5:00 pm
11             10:15 Lucknow To Vara         10:15 lucknow to varanasi
12               MANALI_DELHI_6:00PM               manali delhi 6:00pm
13               MANALI_DELhi_4.00PM               manali delhi 4.00pm
14               Delhi_Manali_6:00PM               delhi manali 6:00pm
15                BLR_CHENNAI_1:30PM           banglore chennai 1:30pm
16               CHENNAI_BLR_11:00PM          chennai banglore 11:00pm
17         BANGALORE_CHENNAI_11:05PM         bangalore chennai 11:05pm
18          CHENNAI_BANGALORE_5:30AM          chennai bangalore 5:30am
19                           Hyd_Blr                 hyerabad banglore
20       BANGALORE_HYDERABAD_ 7:45PM       bangalore hyderabad  7:45pm
21          CHENNAI_BANGALORE_5:45AM          chennai bangalore 5:45am
22                 DELHI TO DEHRADUN                 delhi to dehradun
23                 DEHRADUN TO DELHI                 dehradun to delhi
24          Delhi to Katra UP22T8671          delhi to katra up22t8671
25         BANGALORE_CHENNAI_10:45PM         bangalore chennai 10:45pm
26   Delhi-Lucknow 9:00PM 30 sleeper   delhi lucknow 9:00pm 30 sleeper
27         CHENNAI_BANGALORE_10:30PM         chennai bangalore 10:30pm
28              Lucknow_Vara_10:30PM          lucknow varanasi 10:30pm
29           VARANASI_LUCKNOW_9:30PM           varanasi lucknow 9:30pm
30       Delhi to Jalandhar 10:00 PM       delhi to jalandhar 10:00 pm
31       JALANDHAR TO DELHI 11:00 AM       jalandhar to delhi 11:00 am
32          BANGALORE_CHENNAI_5:05AM          bangalore chennai 5:05am
33            DELHI_AMRITSAR_23:00PM            delhi amritsar 23:00pm
34            AMRITSAR_DELHI_11:00PM            amritsar delhi 11:00pm
35      BANGALORE_COIMBATORE_11:30PM      bangalore coimbatore 11:30pm
36      Coimbatore_Bangalore_11:00PM      coimbatore bangalore 11:00pm

How to replace a word in a column, which is present in the pandas dataframe by another word in the same column?

1 Answers