Using a Pandas Dataframe as a Lookup Table

Pandas table lookup

A one-line solution (I call your lookup table lookup):

df['Score'].apply(lambda score: lookup['Grade'][(lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)].values[0])

---

Explanation:

For a given score, here is how to find the grade:

score = -75
match = (lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)
grade = lookup['Grade'][match]

This return a series of length 1. You can get its value with, for instance:

grade.values[0]

All you need to do is apply the above to the score column. If you want a one-liner, use a lambda function:

df['Score'].apply(lambda score: lookup['Grade'][(lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)].values[0])

Otherwise the following would be more readable:

def lookup_grade(score):
    match = (lookup['Lower_Boundary'] <= score) & (lookup['Upper_Boundary'] > score)
    grade = lookup['Grade'][match]
    return grade.values[0]

df['Score'].apply(lookup_grade)

This approach would also make it easier to deal with cases when no match is found.

Quickest way to map lookup table to pandas column

Using map

df2['Colz']=df2.ColY.map(df1.set_index('Col2').Col1)
df2
Out[211]: 
    ColX  ColY Colz
0    Mon     2    B
1   Tues     3    C
2   Weds     5    A
3  Thurs     4    C
4    Fri     1    A

Using a pandas dataframe as a lookup table

I'd go with merge:

import pandas as pd

y = pd.DataFrame({'A': [1, 1, 3],
                  'B': list('aac'),
                  'C': list('ddf'),
                  'D': [4, 5, 6]})

x = pd.DataFrame([[1, 'a', 'd']],
                 columns=list('ABC'))

match = x.merge(y, on=x.columns.tolist())

match
#   A  B  C  D
#0  1  a  d  4
#1  1  a  d  5

Python Pandas: DataFrame as a Lookup Table

df1 = pd.DataFrame({
 'A': [1, 2],
 'B': ['B', 'A'],
 'FREQ_A': [1, 1],
 'FREQ_B': [1, 1],
 'Gold': [0, 1],
 'SUCCESS_A': [0.0, 0.01],
 'SUCCESS_B': [0.0, 0.01]})

df2 = pd.DataFrame({'A': [1, 2], 'B': ['A', 'B']})

result = (df2
          .merge(df1[['A', 'FREQ_A', 'SUCCESS_A']], on='A')
          .merge(df1[['B', 'FREQ_B', 'SUCCESS_B']], on='B'))
>>> result
   A  B  FREQ_A  SUCCESS_A  FREQ_B  SUCCESS_B
0  1  A       1       0.00       1       0.01
1  2  B       1       0.01       1       0.00

EDIT

For an arbitrary dataframe:

result = pd.concat(
    [df2, pd.concat([df2[[col]].merge(
                         df1[[col, 'FREQ_' + str(col), 'SUCCESS_' + str(col)]], 
                         on=col, how='left').iloc[:, 1:] 
                     for col in df2], axis=1)], 
    axis=1)

Using a Pandas DataFrame as Lookup

import pandas as pd

df_item= pd.read_csv('Item.txt')
df_price= pd.read_csv('Price.txt')

df_final=pd.merge(df_item,df_price ,on='item',how='left' )
print df_final

output

      item  inStock                description  Price
0   Apples       10              a juicy treat   1.99
1  Oranges       34           mediocre at best   6.99
2  Bananas       21  can be used as phone prop    NaN
3     Kiwi        0                  too fuzzy    NaN

Assign value to column based on lookup table using pandas

IIUC, you can perform a lookup:

df_other['new'] = working_days_df.lookup(df_other['dest1'], df_other['dest2'])

Here, working_days_df is your matrix DataFrame, while df_other is the one you'd like to lookup values for.

Pandas DataFrame as lookup table with embedded lists

Use map and loc

n = 1
df.loc[df.A.map(lambda x: n in x), 'B']

Out[209]:
0    [a, b, c]
Name: B, dtype: object

---

Related Topics