Changing pandas dataframe values based on dictionary

Question

Is there any way to replace value using dictionary or mapping? I have dataframe like this:

Q14r63: Audi                 Q14r2: BMW                 Q14r1: VW 

Selected                     Not Selected               Not Selected
Not Selected                 Selected                   Selected 
Selected                     Selected                   Not Selected

and i have another dataframe which provides codes for the Brands. This df ofcourse can be changed into dictionary also.

Brand           Code
Audi             63
BMW              2
VW               1

Is there any way to get output where "selected" values in main df can be be changes with car brand?

Desired Output

Q14r63: Audi               Q14r2: BMW                 Q14r1: VW 

Audi                         NaN                       NaN 
NaN                          BMW                       VW 
Audi                         BMW                       NaN

yatu · Accepted Answer · 2020-03-13 13:00:19Z

2

Here's one approach using np.where:

import numpy as np
df[:] = np.where(df.eq('Selected'), df.columns.str.split(': ').str[1], np.nan)

print(df)

 Q14r63: Audi Q14r2: BMW Q14r1: VW
0        Audi       NaN      NaN
1         NaN       BMW       VW
2        Audi       BMW      NaN

edited Mar 13, 2020 at 13:00

answered Mar 13, 2020 at 12:58

yatu

88.6k12 gold badges93 silver badges148 bronze badges

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

3 Comments

s_khan92 Over a year ago

I want to use the second dataframe because i have quite big string of column... Is there any way ?

yatu Over a year ago

If the structure of the column names is as above, using the second dataframe just complicates this task? @s_khan92

s_khan92 Over a year ago

Thats the problem.. I made exemplary column.. This is how my column look like

Q14r63: Audi - Sometimes it is hard to remember all. Which of the following car brands do you know of  even if only by name? Please also mark brands you mentioned already in the previous question.

jezrael · Accepted Answer · 2020-03-13 13:25:44Z

1

First extract by column df1['Brand'] columns with regex and then set values by mask with DataFrame.mask and missing values by DataFrame.where:

v = df.columns.str.extract('(' + '|'.join(df1['Brand']) + ')', expand=False)
print (v)
Index(['Audi', 'BMW', 'VW'], dtype='object')

m = df.eq('Selected')
print (m)
   Q14r63: Audi  Q14r2: BMW  Q14r1: VW
0          True       False      False
1         False        True       True
2          True        True      False

df = df.mask(m, v[None, :]).where(m)
print (df)
  Q14r63: Audi Q14r2: BMW Q14r1: VW
0         Audi        NaN       NaN
1          NaN        BMW        VW
2         Audi        BMW       NaN

Alternative solution with numpy.where and DataFrame constructor:

df = pd.DataFrame(np.where(m, v, np.nan), index=df.index, columns=df.columns)
print (df)
  Q14r63: Audi Q14r2: BMW Q14r1: VW
0         Audi        NaN       NaN
1          NaN        BMW        VW
2         Audi        BMW       NaN

edited Mar 13, 2020 at 13:25

answered Mar 13, 2020 at 13:00

jezrael

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

8 Comments

s_khan92 Over a year ago

Thanks.. I tried and it didnt changed anything and also no error :(

jezrael Over a year ago

@s_khan92 - There are not some whitespaces in Brand column? What return print (v) ?

jezrael Over a year ago

@s_khan92 - What is your pandas version?

s_khan92 Over a year ago

V is working fine. But m is giving True False values and its only checking if string selected is there

jezrael Over a year ago

@s_khan92 - So what is print (df.stack().unique()) ? There are values Selected ? Or different?

|

Vishnudev Krishnadas · Accepted Answer · 2020-03-13 13:28:20Z

1

Try

brand_map = dict(zip(df2.Code, df2.Brand))
{63: 'Audi', 2: 'BMW', 1: 'VW'}

mapped_values = df.columns.str.extract('Q14r(\d+)')[0].astype(int).map(brand_map)
df[:] = np.where(df == 'Selected', mapped_values, np.nan)

  Q14r63: Audi Q14r2: BMW Q14r1: VW
0         Audi        NaN       NaN
1          NaN        BMW        VW
2         Audi        BMW       NaN

answered Mar 13, 2020 at 13:28

Vishnudev Krishnadas

11k2 gold badges29 silver badges58 bronze badges

Comments

Pragya · Accepted Answer · 2020-03-13 13:29:10Z

1

df1 =

Q14r63: Audi                 Q14r2: BMW                 Q14r1: VW 

Selected                     Not Selected               Not Selected
Not Selected                 Selected                   Selected 
Selected                     Selected                   Not Selected

df2 =

Brand           Code
Audi             63
BMW              2
VW               1

You can do something like this,

for column in df1.columns: 
    brand = [x for x in df2['Brand'].values if x in column][0]
    df1[column] = df1[column].replace({'Selected': brand, 'Not Selected': 'NaN'})

answered Mar 13, 2020 at 13:29

Pragya

1114 bronze badges

Collectives™ on Stack Overflow

Changing pandas dataframe values based on dictionary

4 Answers 4

3 Comments

8 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related