Mapping and Updating dataframe contents to a partial dictionary

user_7978112 0
Viewed 68

Problem

TypeA and TypeB columns of the following dataframe df:

   Mix             TypeA  TypeB
0  701        12,101,102    9.0
1  702           7,11,16    7.0
2  703  12,102,NaN,11,16    7.0
3  704            5,3,16    NaN
4  705   101,102,10,3,16    6.0

...needs to be mapped to the dictionary:

code_dict = {12: 'a', 102: 'b', 7: 'c', 11: 'd', 16: 'e', 5: 'f'}

Desired Result

   Mix             TypeA  TypeB
0  701           a,101,b    9
1  702             c,d,e    c
2  703       a,b,NaN,d,e    c
3  704             f,3,e    NaN
4  705      101,b,10,3,e    6

Efforts made

Attempt # 1

Thought df could be split, expanded and mapped code_dict to df using a simple command like:

pd.concat([df.TypeA.str.split(',', expand=True), df.TypeB]).apply(lambda x: code_dict[x], 1)

...but it gives the TypeError: 'Series' objects are mutable, thus they cannot be hashed

Attempt # 2

Made a function f:

def f(x):
    try:
        result = code_dict[x]
    except KeyError:
        result = x
    return result

...and used it as ...

dfa = df.TypeA.str.split(',', expand=True).\
        apply(pd.to_numeric, errors='coerce').applymap(f)

pd.concat([df.iloc[:, :2], dfa, df.TypeB.map(f)], axis=1)

...which is close to the desired result, though not exactly that.

Queries

  1. Is there a vectorized way to replace / update the expanded df without using map and applymap on function f ?
  2. To get to the desired result, I tried manipulate dfa using recs = [list(i.values()) for i in dfa.to_dict('records')]. This gives a list of lists as follows:
[['a', 101, 'b', nan, nan], 
['c', 'd', 'e', nan, nan], 
['a', 'b', nan, 'd', 'e'], 
['f', 3, 'e', nan, nan], 
[101, 'b', 10.0, 3.0, 'e']]

... and I am not able to clear the nan in it.

Is there a way to achieve the desired result?

pandas python
1 Answers

I suggest use dict.get(key, key) method here - it working similar like d[key] but if no match original value is returned (second argument).

In column TypeA are strings, so for match are converted keys of dictionary to strings with split and join back, for column TypeB is used original dictionary, because numeric (if strings too, use d):

code_dict = {12: 'a', 102: 'b', 7: 'c', 11: 'd', 16: 'e', 5: 'f'}
d = {str(k): v for k, v in code_dict.items()}

df.TypeA = df.TypeA.apply(lambda x: ','.join(d.get(y, y) for y in x.split(',')))
df.TypeB = df.TypeB.apply(lambda x: code_dict.get(x, x))
print (df)
   Mix         TypeA TypeB
0  701       a,101,b     9
1  702         c,d,e     c
2  703   a,b,NaN,d,e     c
3  704         f,3,e   NaN
4  705  101,b,10,3,e     6

Is there a vectorized way to replace / update the expanded df without using map and applymap on function f ?

In my opinion not, because here is replace problematic, because need processing values between ,.

To get to the desired result, I tried manipulate dfa using recs = [list(i.values()) for i in dfa.to_dict('records')].

There is multiple solution, you can check this, e.g.:

recs =[value[~np.isnan(value)] for value in recs]
user_29010020
Related