Mapping a dictionary values to a column using pandas

Asked Jun 17 '19 at 09:28

Active Jun 17 '19 at 09:42

Viewed 39 times

I have a dictionary with values like below:

d = {'3': 'w_molly', '11': 'w_snug', '13':'w_dupe'}

The actual dictionary has around 120 values and generated from a .csv file.

I have a data frame that looks like below:

KEY  TIME  VALUE
  3  20:00   6.9
  3  21:00   8.9
  3  23:00   4.9
 11  21:00   4.9
 11  23:00   9.9
 13  20:00  12.5

The data frame has millions of rows, what I want to do is replace the KEY column withe the dictionary values.

The output I'm looking for is:

KEY  TIME  VALUE
w_molly  20:00   6.9
w_molly  21:00   8.9
w_molly  23:00   4.9
w_snug   21:00   4.9
w_snug   23:00   9.9
w_dupe   20:00  12.5

I have tried the following:

df=df.replace({'KEY':d})

But I get Typeerror: cannot compare types 'ndarray(dtype=int64)' and str

Can someone please help?

edited Jun 17 '19 at 09:42

asked Jun 17 '19 at 09:28

Ninjasoup

Use `df['KEY']=df['KEY'].map(d).fillna(df['KEY'])` – jezrael Jun 17 '19 at 09:31
@jezrael Hi, thanks for the quick reply, when I try this the `df['KEY']` still displays the number instead of the 'w_' values... – Ninjasoup Jun 17 '19 at 09:38
2

Try: `df['KEY']=df['KEY'].astype(str).map(d).fillna(df['KEY'])` – Erfan Jun 17 '19 at 09:46
@Erfan that did it, thanks!! This was wrecking my head all morning. – Ninjasoup Jun 17 '19 at 09:49

0 Answers0