[SOLVED] pandas - remap value in dataframe using a dictionary with a tuple as key

pandas - remap value in dataframe using a dictionary with a tuple as key

#original dataset
data_original = {'ID':['ID_1','ID_1','ID_2','ID_3'],'FIELD_1':[4,4,2,7],'FIELD_2':[5,5,6,1]}
df_original = pd.DataFrame(data_original)

#dictionary where key exist out of a tupple. Tupple is made from a combination of a value in the ID column and the column that needs to be overwritten.
remap_dictionary = {('ID_1','FIELD_2'):9}

#outcome after remapping
data_new = {'ID':['ID_1','ID_1','ID_2','ID_3'],'FIELD_1':[4,4,2,7],'FIELD_2':[9,9,6,1]}
df_new = pd.DataFrame(data_new)

I'm trying to remap a dataframe using a dictionary that contains a tuple as key. The 1st element of the key is used for finding the relevant rows. The 2nd of the dictionary key indicates the column header for which the value needs to be replaced by the value in the dictionary.

In the example it means that for all rows where the ID value equals 'ID_1' the 'FIELD_2' value is replaced by the number 9.

Struggling with this so could use some guidance

Solution

You can use:

for k, v in remap_dictionary.items():
    df_original.loc[df_original['ID'].eq(k[0]), k[1]] = v

Output:

     ID  FIELD_1  FIELD_2
0  ID_1        4        9
1  ID_1        4        9
2  ID_2        2        6
3  ID_3        7        1

Alternatively, using reshaping:

tmp = df_original.melt('ID', ignore_index=False)
out = (
 tmp.merge(pd.Series(remap_dictionary, name='value'), how='left')
    .set_axis(tmp.index)
    .set_index(['ID', 'variable'], append=True)['value']
    .unstack().rename_axis(columns=None).reset_index('ID')
)