How to replace a value that is included in a list in multiple pandas columns

Question

I have the following df:

                      Q4_1                Q4_2            Q4_3        Q4_4  \
130              far cry 4                 NaN             NaN         NaN   
131       grand theft auto     Assassins Creed    call of duty  watch dogs   
132    the elder scrolls v       the witcher 3      dragon age  dark souls   
133              Uncharted        Call of duty        Deadpool     Far cry   
134  Monster Hunter: World  Kingdom Hearts III  Darksiders III         NaN   
135              Fallout 4                 NaN             NaN         NaN   

                 Q4_5         Q4_6       Q4_7 Q4_8 Q4_9 Q4_10  
130               NaN          NaN        NaN  NaN  NaN   NaN  
131               NaN          NaN        NaN  NaN  NaN   NaN  
132           fallout  assassins creed  fallout 3  NaN  NaN   NaN  
133  Grand theft auto          NaN        NaN  NaN  NaN   NaN  
134               NaN          NaN        NaN  NaN  NaN   NaN  
135               NaN          NaN        NaN  NaN  NaN   NaN

I would like to replace similar strings by a unique identity string based on a list. So this would be the desired output:

                      Q4_1                Q4_2            Q4_3        Q4_4  \
130              far cry 4                 NaN             NaN         NaN   
131       grand theft auto     Assassin's Creed    call of duty  watch dogs   
132    the elder scrolls v       the witcher 3      dragon age  dark souls   
133              Uncharted        Call of duty        Deadpool     Far cry   
134  Monster Hunter: World  Kingdom Hearts III  Darksiders III         NaN   
135              Fallout 4                 NaN             NaN         NaN   

                 Q4_5         Q4_6       Q4_7 Q4_8 Q4_9 Q4_10  
130               NaN          NaN        NaN  NaN  NaN   NaN  
131               NaN          NaN        NaN  NaN  NaN   NaN  
132           fallout  Assassin's Creed  fallout 3  NaN  NaN   NaN  
133  Grand theft auto          NaN        NaN  NaN  NaN   NaN  
134               NaN          NaN        NaN  NaN  NaN   NaN  
135               NaN          NaN        NaN  NaN  NaN   NaN

So I already got a set of values that can be found:

list_assasins_creed = ['Assasin\'s Creed', 'Assassin\'s Creed', 'Assassins Creed', 'assasins creed', 'assassin\'s creed', 'assassins creed']

This values can be found in multiple columns (Q4_1..Q4_9)

I would like to replace those values by Assassin's Creed. I tried to use loc:

df_survey_Q4.loc[df_survey_Q4[['Q4_1', 'Q4_2', 'Q4_3', 'Q4_4', 'Q4_5', 'Q4_6', 'Q4_7', 'Q4_8','Q4_9', 'Q4_10']].isin(list_assasins_creed),['Q4_1', 'Q4_2', 'Q4_3', 'Q4_4', 'Q4_5', 'Q4_6', 'Q4_7', 'Q4_8','Q4_9', 'Q4_10']] = 'Assassin\'s Creed'

But I got this error:

KeyError: "None of [Index([     ('Q', '4', '_', '1'),      ('Q', '4', '_', '2'),\n            ('Q', '4', '_', '3'),      ('Q', '4', '_', '4'),\n            ('Q', '4', '_', '5'),      ('Q', '4', '_', '6'),\n            ('Q', '4', '_', '7'),      ('Q', '4', '_', '8'),\n            ('Q', '4', '_', '9'), ('Q', '4', '_', '1', '0')],\n      dtype='object')] are in the [index]"

Any idea how can I do this?

Please provide a small set of sample data as text that we can copy and paste. Include the corresponding desired result. Check out the guide on [how to make good reproducible pandas examples](https://stackoverflow.com/a/20159305/3620003). — timgeb, Jun 13 '20 at 13:53

score 1 · Answer 1 · edited Jun 13 '20 at 14:13

1

you can write code like this,

df.replace( ['Assasin\'s Creed', 'Assassin\'s Creed', 'Assassins Creed',
             'assasins creed', 'assassin\'s creed', 'assassins creed'] , 'assasian' )

edited Jun 13 '20 at 14:13

Ch3steR

20,090
4
28
58

answered Jun 13 '20 at 14:05

NANDHA KUMAR

465
4
11

score 0 · Accepted Answer · answered Jun 13 '20 at 14:14

You can do this:

df = df.replace({word: "Assassin's Creed" for word in list_assasins_creed}, regex=True)
print(df)

or

df = df.replace(list_assasins_creed, 'Assassin\'s Creed')
print(df)

                    Q4_1              Q4_2            Q4_3        Q4_4
0              far cry 4               NaN             NaN         NaN
1       grand theft auto  Assassin's Creed    call of duty  watch dogs
2    the elder scrolls v     the witcher 3      dragon age  dark souls
3              Uncharted      Call of duty        Deadpool     Far cry
4  Monster Hunter: World  Assassin's Creed  Darksiders III         NaN
5              Fallout 4               NaN             NaN         NaN

it works! much easier and simple solution than what I thought. this also works: df.replace(list_assasins_creed, 'Assassin\'s Creed', inplace=True) — Javiss, Jun 13 '20 at 23:46

How to replace a value that is included in a list in multiple pandas columns

2 Answers2