Create a dataframe column from a lookup matrix

user_11277108 0
Viewed 55

I have the following df:

idx value
0 1
1 2
2 3

I want to create a new column based on a looking up the value for a given row in conjunction with a constant value in the following matrix:

x 1 2 3
1 1 0.3 0.4
2 0.3 1 0.2
3 0.4 0.2 1

So if constant == 1 then the resulting dataframe would look like:

idx value lookup
0 1 1
1 2 0.3
2 3 0.4

Existing answers I've seen reference:

  1. pandas.lookup which is deprecated
  2. numpy.select or numpy.where which aren't really scalable if the lookup matrix gets large

I think there might be a solution around pandas.merge but I'm struggling to get my head around it. Would anyone have any suggestions?

I should probably mention that speed is really important so a vectorised solution would be preferable.

To replicate:

df = pd.DataFrame({"value": [1, 2, 3]})
df_lookup = pd.DataFrame(
    index=[1, 2, 3],
    data={1: [1, 0.3, 0.4], 2: [0.3, 1, 0.2], 3: [0.4, 0.2, 0.1]}
)
pandas lookup-tables python
2 Answers

IIUC, you can directly map the row to value column

constant = 1
df['lookup'] = df['value'].map(df_lookup.loc[constant, :])

print(df)

   value  lookup
0      1     1.0
1      2     0.3
2      3     0.4
user_103151630

If you want to make your life easy and the value column in df is non-repeating, then you could set it as the index and simply use loc.

constant = 1
df['mapped'] = df_lookup.loc[df.index, constant]

Which gives me :

   value  lookup
1      1     1.0
2      2     0.3
3      3     0.4

However, if it is repeating, this would not work. Then you could use .merge such as :

constant = 1
df = df.merge(df_lookup[constant], left_on='value', right_index=True)
user_142358260
Related