pythonpandasdataframelookuppandas-merge

How to map values into multiple columns of a dataframe

Given df1

id1 id2
1 3
4 2
2 1

and df2

id3 id4
1 a
2 b
3 c
4 d

I want the results of df1 to be as follows:

id1 id2
a c
d b
b a

How can I accomplish this?

Solution

  • The easiest solution is to df2 into a lookup Series and map its values to every column in df1.

    # lookup mapping from id3 to id4
lookup = df2.set_index('id3')["id4"]
# map id4 to df1 via id3 in a loop
for c in df1:
    df1[c] = df1[c].map(lookup)

    You can also apply the call to map, instead of an explicit loop:

    lookup = df2.set_index('id3')["id4"]
df1 = df1.apply(lambda col: col.map(lookup))

    Another option is to stack the columns in df1 to make a single column dataframe, merge it with df2 and reshape it back into its original shape using pivot:

    df1 = (
    df1
    .stack()
    .reset_index(name="id3")
    .merge(df2)
    .pivot(index="level_0", columns="level_1", values="id4")
    .rename_axis(index=None, columns=None)
)

    All options give the following result:

    enter image description here