CODEWITHSUNDEEP
python-3.xpandas
deadbug
deadbug

Reputation: 444

Pandas: Create new index column by mapping existing column

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.

Upvotes: 2

Views: 1320

Answers (2)

BENY
BENY

Reputation: 323226

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

Upvotes: 2

jezrael
jezrael

Reputation: 862511

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

Upvotes: 3

Related Questions