Complex link between 2 dataframes

Question

I have 2 dataframes. One first is my reference

df_ref
ID  REF  VALUE
A   1    12
A   2    36
A   3    95
B   1    54
B   2    67
B   3    81
C   1    89
C   2    123
C   3    14

And the second is my restricted :

df_restrict
ID  V1  V2
A   1   2
B   3   2
C   2   1

What I want is to replace V1 and V2 in df_restrict by the values corresponding to the (ID, REF) from df_ref

df_restrict
ID  V1  V2
A   12  36
B   81  67
C   123 89

Thanks in advance

Answer 1

We can pivot df_ref to reshape, then set the index of df_restrict to ID, then transpose and replace the the values from the reshaped df_ref

r = df_ref.pivot('REF', 'ID', 'VALUE')
df_restrict.set_index('ID').T.replace(r).T

---

     V1  V2
ID         
A    12  36
B    81  67
C   123  89