Look Up in Python DataFrames?

Question

I have a dataframe df1:

Month

1
3
March
April
2
4
5

I have another dataframe df2:

Month  Name

1       January
2       February
3       March
4       April
5       May

If I want to replace the integer values of df1 with the corresponding name from df2, what kind of lookup function can I use?

I want to end up with this as my df1:

    Month

January
March
March
April
February
May

why not simply use `df1['Month'] = df2['Name']` if they are ordered as shown in example? — harvpan, Feb 01 '19 at 16:08

score 5 · Answer 1 · answered Feb 01 '19 at 16:15

5

replace it

df1.replace(dict(zip(df2.Month.astype(str),df2.Name)))
Out[76]: 
      Month
0   January
1     March
2     March
3     April
4  February
5     April
6       May

answered Feb 01 '19 at 16:15

BENY

317,841
20
164
234

I've tried it ( df1=df1.replace(dict(zip(df2.Month.astype(str),df2.Name)))) but it isn't working. When I print df1, I get the orignal values. – Lax Mandis Feb 01 '19 at 17:14

score 3 · Accepted Answer · answered Feb 01 '19 at 16:21

You can use pd.Series.map and then fillna. Just be careful to map either strings to strings or, as here, numeric to numeric:

month_name = df2.set_index('Month')['Name']

df1['Month'] = pd.to_numeric(df1['Month'], errors='coerce').map(month_name)\
                 .fillna(df1['Month'])

print(df1)

      Month
0   January
1     March
2     March
3     April
4  February
5     April
6       May

You can also use pd.Series.replace, but this is often inefficient.

score 1 · Answer 3 · answered Feb 01 '19 at 16:12

One alternative is to use map with a function:

def repl(x, lookup=dict(zip(df2.Month.astype(str), df2.Name))):
    return lookup.get(x, x)

df['Month'] = df['Month'].map(repl)
print(df)

Output

      Month
0   January
1  February
2     March
3     April
4       May

score 1 · Answer 4 · answered Feb 01 '19 at 16:31

Use map with a series, just need to make sure your dtypes match:

mapper = df2.set_index(df2['Month'].astype(str))['Name']
df1['Month'].map(mapper).fillna(df1['Month'])

Output:

0     January
1       March
2       March
3       April
4    February
5       April
6         May
Name: Month, dtype: object

Look Up in Python DataFrames?

4 Answers4