VLOOKUP and match together in pandas?

Question

How can I pass this excel formula to pandas?

=IF(J3="",7,IFERROR(VLOOKUP(I3,Abrang!$A$1:$LT$5956,MATCH(Relatorio!L3,Abrang!$A$3:$LT$3,0),1),0))

How do a merge with this data frames

 data_1 = {'COD(COLUMN_I)': [763807643,45968455,56565435,5833250],
 'Data(COLUMN_J)':["16/11/2021","19/11/2021","19/11/2021","09/11/2021"],
 'Type(COLUMN_L)': ["Type 1", "Type 2","Type 3","Type 4"]}
 
 data_2 = {'COD(COLUMN_I)': [763807643,45968455,56565435,5833250],
          'Type_1':["4","21","9","8"],
          'Type_2': ["5", "45","3","8"],
          'Type_3': ["12", "43","54","6"],
          'Type_4': ["7", "5","2","1"]
          }
 df_1 = pd.DataFrame(data=data_1)
 Abrang = pd.DataFrame(data=data_2)

To get this result?

There once was a `lookup` function. Now you can just do a `for` loop with `loc`. — Quang Hoang, Nov 24 '21 at 15:25

score 1 · Answer 1 · answered Nov 24 '21 at 15:16

1

Use melt to reformat your dataframe Abrang then use merge to lookup the right rows:

df_2 = Abrang.melt('COD(COLUMN_I)', var_name='Type(COLUMN_L)', value_name='Result')
df_2['Type(COLUMN_L)'] = df_2['Type(COLUMN_L)'].str.replace('_', ' ')

out = df_1.merge(df_2, on=['COD(COLUMN_I)', 'Type(COLUMN_L)'], how='left')

Output:

>>> out
   COD(COLUMN_I) Data(COLUMN_J) Type(COLUMN_L) Result
0      763807643     16/11/2021         Type 1      4
1       45968455     19/11/2021         Type 2     45
2       56565435     19/11/2021         Type 3     54
3        5833250     09/11/2021         Type 4      1

Note: the code could be simpler if the Type column/value was the same between the two dataframes: 'Type 1' and 'Type_1', etc.

answered Nov 24 '21 at 15:16

Corralien

109,409
8
28
52

FWIW, [this is](https://github.com/pandas-dev/pandas/pull/35224#discussion_r564046976) what I think about melt and merge as a replacement for `lookup` function :p – Quang Hoang Nov 24 '21 at 15:29
I know `lookup` is deprecated but do you have an alternative? – Corralien Nov 24 '21 at 15:33
np indexing like [this](https://github.com/pandas-dev/pandas/issues/39171#issuecomment-773374707), there's also [`pd.get_indexer`](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Index.get_indexer.html) or [`pd.factorize`](https://github.com/pandas-dev/pandas/issues/39171) as replacements of `np.unique`. – Quang Hoang Nov 24 '21 at 15:40

sammywemmy · Answer 2 · 2021-11-24T20:02:07.577

The docs has an example that you can adapt; however, you should reshape the last column to match Abrang:

df_1 = df_1.assign(Result = df_1.iloc[:, -1].str.split().str.join('_'))

idx, cols = pd.factorize(df_1.Result)

df_1 = df_1.assign(Result = Abrang
                            .reindex(cols, axis=1)
                            .to_numpy()[np.arange(len(df_1)), idx]


   COD(COLUMN_I) Data(COLUMN_J) Type(COLUMN_L) Result
0      763807643     16/11/2021         Type 1      4
1       45968455     19/11/2021         Type 2     45
2       56565435     19/11/2021         Type 3     54
3        5833250     09/11/2021         Type 4      1

See here for more ways to handle lookup

Another option is with a pivot, but is considerably longer:

A, B = df_1.iloc[:, 0], df_1.iloc[:, -1]
B.index = A
Abrang['Result'] = Abrang.iloc[:, 0].map(B)
cols = [*zip(Abrang.columns[1:], B)]
result = (Abrang
          .pivot(Abrang.columns[0], 'Result')
          .loc[:, cols]
          .ffill(axis=1)
          .iloc[:, -1]
          )

df_1.assign(Result = df_1.iloc[:, 0].map(result))

   COD(COLUMN_I) Data(COLUMN_J) Type(COLUMN_L) Result
0      763807643     16/11/2021         Type 1      4
1       45968455     19/11/2021         Type 2     45
2       56565435     19/11/2021         Type 3     54
3        5833250     09/11/2021         Type 4      1

VLOOKUP and match together in pandas?

2 Answers2