How to replace value in whole dataframe based on dictionary when part of string match?

Question

Hi I have 2 dataframes where i have to use 1 dataframe to replace the value in other. I can normally create the dictionary to replace values in whole dataframe but I have a bit different value in other dataframe so i need condition where i can tell if the part of the string is matched then it should map the dictionary. The first dataframe is like this:

The second dataframe is like this:

        id        cars1                     cars2

        1     $ {hQOpelText.r1.val}        BMW
        2     $ {hQOpelText.r2.val}        $ {hQOpelText.r2.val}
        3     $ {hQOpelText.r3.val}        $ {hQOpelText.r5.val}
        4     $ {hQOpelText.r4.val}        Audi
        5     $ {hQOpelText.r5.val}        Audi

And i want resulted df like this:

        id        cars1                     cars2

        1     Opel Adam                   BMW
        2     Opel Astra Estate           Opel Astra Estate
        3     Opel Astra Hatchback        Opel Grandland x 
        4     Opel Astra Saloon           Audi
        5     Opel Grandland x            Audi

Does this answer your question? Remap values in pandas column with a dict — sushanth
– sushanth, Commented Jun 8, 2020 at 8:38
No because i can't map directly the values as i have different values. If i create the dictionary then it will have different values as compared to second data-frame. — s_khan92
– s_khan92, Commented Jun 8, 2020 at 8:39
You can transform $ {hQOpelText.r1.val} to hQOpelTextr1 if they are going to be consistent, then convert to dict. — sushanth
– sushanth, Commented Jun 8, 2020 at 8:42

jezrael · Accepted Answer · 2020-06-08 08:49:15Z

1

Idea is replace . to empty strings, then extract values by keys of dictionary, map and replace original if no match:

c = df.select_dtypes(object).columns
func = lambda x: (x.str.replace('.', '', regex=False)
                  .str.extract(f'({"|".join(d.keys())})', expand=False)
                  .map(d)
                  .fillna(x))
df[c] = df[c].apply(func)

print (df)
   id                 cars1              cars2
0   1             Opel Adam                BMW
1   2     Opel Astra Estate  Opel Astra Estate
2   3  Opel Astra Hatchback    Opel Grandand X
3   4     Opel Astra Saloon               Audi
4   5       Opel Grandand X               Audi

edited Jun 8, 2020 at 8:49

answered Jun 8, 2020 at 8:44

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

s_khan92 Over a year ago

Thanks for the answer as always :) Can you differentiate between dataframes as you are using everywhere df so i am not sure where you are using what?

jezrael Over a year ago

@s_khan92 - not sure if understand, but seems only way is necessary use df1, df2, df3 instead df only if processing different DataFrames and need distingush them.

Shubham Sharma · Accepted Answer · 2020-06-08 09:33:05Z

1

We can first change all the column values of type $ {hQOpelText.r*.val} in df2 to adhere to the convention of the values used in Variable column in df1 i.e hQOpelTextr*, then we can replace those values from the corresponding values from df1:

cols = df2.select_dtypes(object).columns
df2[cols] = df2[cols].transform(
    lambda s: (
        s.str.replace(r'\$\s*\{([^\.]+).*?([^\.]+).*?\}', r'\g<1>\g<2>')
        .replace(df1.set_index('Variable')['AUS'])
    )
)

# print(df2)
   id                 cars1              cars2
0   1              OpehAdam                BMW
1   2     Opel Astra Estate  Opel Astra Estate
2   3  Opel Astra Hatchback   Opel Grandland X
3   4     Opel Astra Saloon               Audi
4   5      Opel Grandland X               Audi

edited Jun 8, 2020 at 9:33

answered Jun 8, 2020 at 9:27

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to replace value in whole dataframe based on dictionary when part of string match?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related