How to change all values of a dataframe according to values of another dataframe in Pandas?-CodePudding

IDs needs to be replaced by Canonical Names. First data frame (please use code):

    df = pd.DataFrame({'LopCityCriteriaId': {0: 1007788.0,
      1: 1000002.0,
      2: 1000003.0,
      3: 1000003.0,
      4: 1000004.0},
     'LopCountryCriteriaId': {0: 2356.0,
      1: 1000003.0,
      2: 1000002.0,
      3: 1000009.0,
      4: 1000004.0},
     'LopMostSpecificTargetId': {0: 1007788.0,
      1: 1000003.0,
      2: 9062093.0,
      3: 1000002.0,
      4: 1000002.0},
     'LopRegionCriteriaId': {0: 20462.0,
      1: 1000009.0,
      2: 1000003.0,
      3: 1000008.0,
      4: 1000004.0}})

df

Now I need all those IDs throughout the data frame replaced with these (please use code):

df_IDs = pd.DataFrame({'Criteria ID': {0: 1000002, 1: 1000003, 2: 1000004, 3: 1000008, 4: 1000009},
 'Canonical Name': {0: 'Kabul,Kabul,Afghanistan',
  1: 'Luanda,Luanda Province,Angola',
  2: 'The Valley,Anguilla',
  3: 'Philipsburg,Sint Maarten',
  4: 'Willemstad,Curacao'}})

df_IDs

Please point me in the right direction.

CodePudding user response：

Use DataFrame.replace:

df1 = df.replace(df_IDs.set_index('Criteria ID')['Canonical Name'])
print (df1)
               LopCityCriteriaId           LopCountryCriteriaId  \
0                      1007788.0                         2356.0   
1        Kabul,Kabul,Afghanistan  Luanda,Luanda Province,Angola   
2  Luanda,Luanda Province,Angola        Kabul,Kabul,Afghanistan   
3  Luanda,Luanda Province,Angola             Willemstad,Curacao   
4            The Valley,Anguilla            The Valley,Anguilla   

         LopMostSpecificTargetId            LopRegionCriteriaId  
0                      1007788.0                        20462.0  
1  Luanda,Luanda Province,Angola             Willemstad,Curacao  
2                      9062093.0  Luanda,Luanda Province,Angola  
3        Kabul,Kabul,Afghanistan       Philipsburg,Sint Maarten  
4        Kabul,Kabul,Afghanistan            The Valley,Anguilla

Or Series.map:

df2 = df.apply(lambda x: x.map(df_IDs.set_index('Criteria ID')['Canonical Name']))
print (df2)
               LopCityCriteriaId           LopCountryCriteriaId  \
0                            NaN                            NaN   
1        Kabul,Kabul,Afghanistan  Luanda,Luanda Province,Angola   
2  Luanda,Luanda Province,Angola        Kabul,Kabul,Afghanistan   
3  Luanda,Luanda Province,Angola             Willemstad,Curacao   
4            The Valley,Anguilla            The Valley,Anguilla   

         LopMostSpecificTargetId            LopRegionCriteriaId  
0                            NaN                            NaN  
1  Luanda,Luanda Province,Angola             Willemstad,Curacao  
2                            NaN  Luanda,Luanda Province,Angola  
3        Kabul,Kabul,Afghanistan       Philipsburg,Sint Maarten  
4        Kabul,Kabul,Afghanistan            The Valley,Anguilla