I have the following data frame:
import pandas as pd
df_1 = pd.DataFrame(
{
"id_1": [1, 1, 2, 2],
"id_2": [11, 11, 22, 22],
"value_1": [0.1, 0.1, 0.01, 0.01],
"value_2": [0.2, 0.2, 0.02, 0.02],
"value_3": [0.3, 0.3, 0.03, 0.03],
}
)
that looks like this:
id_1 id_2 value_1 value_2 value_3
0 1 11 0.10 0.20 0.30
1 1 11 0.10 0.20 0.30
2 2 22 0.01 0.02 0.03
3 2 22 0.01 0.02 0.03
and another:
df_2 = pd.DataFrame(
{
"id_1": [1, 1, 1, 1, 2, 2, 2, 2],
"id_2": [11, 11, 11, 11, 22, 22, 22, 22],
"value_name": [
"value_1",
"value_2",
"value_3",
"value_1",
"value_1",
"value_2",
"value_3",
"value_1",
],
}
)
which looks like this:
id_1 id_2 value_name
0 1 11 value_1
1 1 11 value_2
2 1 11 value_3
3 1 11 value_1
4 2 22 value_1
5 2 22 value_2
6 2 22 value_3
7 2 22 value_1
How do I get the corresponding value (for a given id_1 and id_2) from df_1 into df_2, please? i.e. I want a final data frame should look like this:
id_1 id_2 value_name value
0 1 11 value_1 0.1
1 1 11 value_2 0.2
2 1 11 value_3 0.3
3 1 11 value_1 0.1
4 2 22 value_1 0.01
5 2 22 value_2 0.02
6 2 22 value_3 0.03
7 2 22 value_1 0.01
drop the dupe rows by id columns then melt the dataframe and merge with df_2
c = ['id_1', 'id_2']
df_2.merge(df_1.drop_duplicates(c).melt(c, var_name='value_name'), how='left')
Result
id_1 id_2 value_name value
0 1 11 value_1 0.10
1 1 11 value_2 0.20
2 1 11 value_3 0.30
3 1 11 value_1 0.10
4 2 22 value_1 0.01
5 2 22 value_2 0.02
6 2 22 value_3 0.03
7 2 22 value_1 0.01