2

I have a column 

col1
----
a
b
c
a
c
b

I have a mapping like this :

KI13232:a
MK25436:b
SL365487:c

That means, create a new column with name ID and for all occurrences of a in the col1 the ID column value should be KI13232, same applies to b and c values as well.

The result should look like : 

ID        col1
----      -----
KI13232    a
MK25436    b
SL365487   c
KI13232    a
SL365487   c
MK25436    b

I have a larger dataset to implement this. What is the optimal method in Pandas to achieve this.

deadbug
  • 434
  • 5
  • 19
  • 1
    What is the point of mapping values to keys? Shouldn't the values be the keys in the first place? Also, how are you going to manage keys with duplicate values? – cs95 Feb 05 '18 at 20:06
  • I didn't wrote it as dict. It is another table with these mappings. `a`,`b` and `c` could be keys. I just need new column with correct mapping. Hope it make sense. – deadbug Feb 05 '18 at 20:16

2 Answers2

3

Use map with dict comprehension for swap keys with values of dict:

d = {'KI13232':'a','MK25436':'b','SL365487':'c'}

df['ID'] = df['col1'].map({v:k for  k, v in d.items()})

Or similar:

df['ID'] = df['col1'].map(dict(zip(d.values(),d.keys())))

print (df)
  col1        ID
0    a   KI13232
1    b   MK25436
2    c  SL365487
3    a   KI13232
4    c  SL365487
5    b   MK25436

If order of column is important add insert:

df.insert(0, 'ID', df['col1'].map({v:k for  k, v in d.items()}))
print (df)
         ID col1
0   KI13232    a
1   MK25436    b
2  SL365487    c
3   KI13232    a
4  SL365487    c
5   MK25436    b

And for index:

df.index = df['col1'].map({v:k for  k, v in d.items()}).rename('ID')
print (df)
         col1
ID           
KI13232     a
MK25436     b
SL365487    c
KI13232     a
SL365487    c
MK25436     b
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Yes, it depends of dict. – jezrael Feb 05 '18 at 20:04
  • 1
    it [annoys me](https://stackoverflow.com/questions/48493129/injective-two-way-mappings) that a proper injective construct doesn't exist. i think it would be commonly used if available! – jpp Feb 05 '18 at 20:06
2

Borrow Jez's dict 

df['New']=df.col1.replace(d.values(),d.keys())
df
Out[921]: 
  col1       New
0    a   KI13232
1    b   MK25436
2    c  SL365487
3    a   KI13232
4    c  SL365487
5    b   MK25436
BENY
  • 317,841
  • 20
  • 164
  • 234