Replace Values in a Dataframe Based on Lookup Table

Replace values in a dataframe based on lookup table

You posted an approach in your question which was not bad. Here's a smiliar approach:

new <- df  # create a copy of df
# using lapply, loop over columns and match values to the look up table. store in "new".
new[] <- lapply(df, function(x) look$class[match(x, look$pet)])

An alternative approach which will be faster is:

new <- df
new[] <- look$class[match(unlist(df), look$pet)]

Note that I use empty brackets ([]) in both cases to keep the structure of new as it was (a data.frame).

(I'm using df instead of table and look instead of lookup in my answer)

How do I replace the values in a dataframe based on a lookup table in another dataframe

Use pandas' replace method : it will search for the keys in the dataframe and replace found keys with the associated values. your dataframe has a few missing NaNs, so I edited it to match what you posted

  #create a dictionary from the lookup
  repl = lookup.set_index('value')['description'].to_dict()

  #print(repl)
  
  {653: '30 to 39',
   654: '40 to 49', 
   1056: 'Belgium', 
   1158: 'Taiwan', 
   1203: 'Czech Republic',
   545: 'White', 
   530: 'Other'}

  #pass it using pandas' replace method
  df.replace(repl)

  
      age         cty            eth
0   30 to 39    Belgium          NaN
1   30 to 39    Belgium          White
2   40 to 49    NaN               Other
3   30 to 39    Taiwan           Other
4   30 to 39    Czech Republic  White

Find and Replace Based on A Lookup Table in R and Leave Unmatched Ones As Is

You can use the match output to conditionally change values.

inds <- match(main$V1, lookup$V1)
main$V1[!is.na(inds)] <- lookup$V2[na.omit(inds)]
main

#  V1
#1 aa
#2 bb
#3 cc
#4 cc
#5  D
#6  E
#7 aa

---

You can also use the join approach :

library(dplyr)
main %>%
  left_join(lookup, by = 'V1') %>%
  transmute(V1 = coalesce(V2, V1))

PANDAS - How do I replace the values in a dataframe based on a lookup table in another dataframe

You can basically do a left-merge join of the two files on the column Supplier SKU
and then keep the value of column Manufacturer SKU from old_file when the merge matches, otherwise keep the value from live_file

live_file["Manufacturer SKU"] =  pd.merge(live_file[["Supplier SKU", "Manufacturer SKU"]], 
         old_file[["Supplier SKU", "Manufacturer SKU"]],
         how="left", 
         on="Supplier SKU",
         suffixes=(None, "__right"),
         indicator="merge_flag")\
         .apply(lambda row: row["Manufacturer SKU"] 
                if row["merge_flag"] == "left_only" 
                else row["Manufacturer SKU__right"], axis=1) 

Function to replace values in data.table using a lookup table

We don't need as.name. Object on the lhs of = is not evaluated correctly. Instead, we could use a named vector in on with setNames

dt.replaceValueUsingLookup <- function(dt, col, dtLookup) {
   dt[
     dtLookup,
     on = setNames("old", col),
     (col) := new
      ]
     }

-testing

dt %>% 
    dt.replaceValueUsingLookup("chapter", dtLookup)
 
dt
#   chapter
#1:     101
#2:     102
#3:      13
#4:     105
#5:     104

Replace Values in Dataframe using a Lookup Dataframe

You could use replace with a dictionary:

import pandas as pd

df=pd.DataFrame({
                  'no1':[20,20,40,10,50],
                  'no2':[50,20,10,40,50],
                  'no3':[30,10,50,40,50]
                  })

lookup=pd.DataFrame({'label':['A','B','C','D','E'],
                  'id':[10,20,30,40,50]})

result = df.replace(dict(zip(lookup.id, lookup.label)))

print(result)

Output

  no1 no2 no3
0   B   E   C
1   B   B   A
2   D   A   E
3   A   D   D
4   E   E   E

Replace values in column of Pandas DataFrame using a Series lookup table

you can use map() function for that:

In [38]: df_normalised['name'] = df_normalised['code'].map(name)

In [39]: df_normalised
Out[39]:
   code                                          name
0     8                             Human development
1    11  Environment and natural resources management
2     1                           Economic management
3     6         Social protection and risk management
4     5                         Trade and integration
5     2                      Public sector governance
6    11  Environment and natural resources management
7     6         Social protection and risk management
8     7                   Social dev/gender/inclusion
9     7                   Social dev/gender/inclusion

Replace column values in table with values from lookup based on matches in R using data.table

We can do a join on the 'code' and 'old' from table and lookup respectively

table[lookup, code := new, on = .(code = old)]

-output

 table
   code sn
1:  CBa  1
2:  CBe  2
3:  CBa  3
4:  CBe  4
5:  OOO  5
6:  PPP  6
7:  CBa  7

replace value of a dataframe based on value from another dataframe

Use the lookup table to make a dict, and then replace the column values of the original dataframe. Assume the original dataframe is df1 and the lookup table is df2

...
dict_map = dict(zip(df2.ParamCode + "-" + df2.ParamValue, df2.ParmDesc1))

df1['IncidentType'] = ("IncidentType" +'-'+ df1.IncidentType).replace(dict_map)
df1['DangerType'] = ("DangerType" +'-'+ df1.DangerType).replace(dict_map)
...

---

Related Topics