Looking up values in two pandas data frames and create new columns

Question

I have two data frames in my problem.

df1

ID      Value
 1      A
 2      B
 3      C

df2:

ID      F_ID     S_ID
1       2        3
2       3        1
3       1        2

I want to create a column next to each ID column that will store the values looked up from df1. The output should look like this :

ID  ID_Value     F_ID  F_ID_Value   S_ID  S_ID_Value
1     A             2   B              3       C
2     B             3   C              1       A
3     C             1   A              2       B

Basically looking up from df1 and creating a new column to store these values.

Answer 1

you can use map on each column of df2 with the value of df1.

s = df1.set_index('ID')['Value']
for col in df2.columns:
    df2[f'{col}_value'] = df2[col].map(s)
print (df2)
   ID  F_ID  S_ID ID_value F_ID_value S_ID_value
0   1     2     3        A          B          C
1   2     3     1        B          C          A
2   3     1     2        C          A          B

or with apply and concat

df_ = pd.concat([df2, df2.apply(lambda x: x.map(s)).add_prefix('_value')], axis=1)
df_ = df_.reindex(sorted(df_.columns), axis=1)