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?
CodePudding user response:
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.
CodePudding user response:
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