Pandas dict application over several columns, handling non matches values

Viewed 83

I have a dataframe like:

ìmport pandas as pd
df = pd.DataFrame({
       "age" : [20,22,20,23],
       "name" : ["A","B","C","A"],
       "addres" : ["add1","add2","add3","add4"],
       "job" :  ["C","E","C","D"],
       "score" : [0.44,0.43,0.25,0.36]
     })
categors = ["name","addres","job"]
df:

       age name addres job  score
    0   20    A   add1   C   0.44
    1   22    B   add2   E   0.43
    2   20    C   add3   C   0.25
    3   23    A   add4   D   0.36

and I have a dict like this:

mapping_dict = {
    "name" : {"A":0, "B": 1},
    "addres" : {"add1": 0, "add2": 1, "add3":2},
    "job" : {"A":0, "B":1, "C": 2, "D":3}
    }

I would likt to apply this dictionary to their match column, so I can do this:

df[categors].replace(mapping_dict,inplace=True)

or

df[categors] = df[categors].replace(mapping_dict)

it's the same because they return the same problem:

      name addres job
    0    0      0   2
    1    1      1   E
    2    C      2   2
    3    0   add4   3

The problem is that non matched values (like add4 in column addres or C in column name or E in column job) are not handled to be replaced with any argument of .replace function. I need those values to be mapped to -1

So, to achieve this, we can make a loop:

for column in categors:
    df[column] = df[column].map(mapping_dict[column])

df
   age  name  addres  job  score
0   20   0.0     0.0  2.0   0.44
1   22   1.0     1.0  NaN   0.43
2   20   NaN     2.0  2.0   0.25
3   23   0.0     NaN  3.0   0.36 

and solve NaN with .fillna(-1) or better one

 for column in categors:
     l = lambda x: mapping_dict[column].get(x,-1)
     df[column] = df[column].apply(l)
 df 
        age  name  addres  job  score
    0   20     0       0    2   0.44
    1   22     1       1   -1   0.43
    2   20    -1       2    2   0.25
    3   23     0      -1    3   0.36

So, I know how to do the stuff, this has been proved.

My problems are:

    1. I don't think that pandas was made to loop over columns, but vectorized functions applications over columns.
    1. My real dataframe is large enought to need a vectorized function, and mapping_dict is large enough too.

So, If I could use apply with axis=1 and get the column name column_name somehow like pandas.Series.column_name, I could do something like :

df[columns].apply(lambda x: mapping_dict[x.column_name].get(x,-1) , axis=1)

By now, I think that an inherited class who owns from pandas.dataframe all its properties and adds x.column_name is the "Cannon to kill a mosquito" solution.

So do you know any fast, one line solution for this?

1 Answers

Add a "catch-all" to the end of the dict for each column. This was anything not matched becomes -1:

mapping_dict = {
    "name" : {"A":0, "B": 1, ".*":-1},
    "addres" : {"add1": 0, "add2": 1, "add3":2, ".*":-1},
    "job" : {"A":0, "B":1, "C": 2, "D":3, ".*":-1}
}
    

Then just include the regex parameter in your replace.

df.replace(mapping_dict, inplace=True, regex=True)

   age  name  addres  job  score
0   20     0       0    2   0.44
1   22     1       1   -1   0.43
2   20    -1       2    2   0.25
3   23     0      -1    3   0.36
Related