CODEWITHSUNDEEP
pythonpandasdataframe
Andrey
Andrey

Reputation: 6367

Is it possible to merge two pandas dataframes based on indices and column names?

I have two dataframes:

left = pd.DataFrame(
        {
            "Col": ["D", "C", "B", "A"],
        },
        index=[0, 1, 2, 3],
    )
right = pd.DataFrame(
        {
            "A": ["A0", "A1", "A2", "A3"],
            "B": ["B0", "B1", "B2", "B3"],
            "C": ["C0", "C1", "C2", "C3"],
            "D": ["D0", "D1", "D2", "D3"],
        },
        index=[0, 1, 2, 3],
    )

Is it possible to merge them based on indices and col of the left and column names on the right ?

I need to get the following result:

result = pd.DataFrame(
        {
            "Col": ["D", "C", "B", "A"],
            "Val": ["D0", "C1", "B2", "A3"],
        },
    )

Upvotes: 3

Views: 75

Answers (3)

Andrey
Andrey

Reputation: 6367

Alternative approach (convert columns to index with melt and then merge):

left['id'] = left.index    
m = right.melt(ignore_index=False, var_name="Col", value_name="Val")
m['id'] = m.index    
result = pd.merge(left, m, on=["id", "Col"])[["Col", "Val"]]

It is faster than use of apply but slower than the accepted answer.

Upvotes: 1

Andrej Kesely
Andrej Kesely

Reputation: 195418

Another solution:

left["new"] = right.apply(lambda x: x[left.loc[x.name, "Col"]], axis=1)
print(left)

Prints:

  Col new
0   D  D0
1   C  C1
2   B  B2
3   A  A3

Upvotes: 2

BENY
BENY

Reputation: 323226

Try with

left['new'] = right.values[np.arange(len(left)), right.columns.get_indexer(left.Col)]
left
Out[129]: 
  Col new
0   D  D0
1   C  C1
2   B  B2
3   A  A3

Notice, we used to have lookup but it deprecated, ,above is one of the alternative of lookup from numpy

The reason here I am not use the index : numpy do not have index, so we need the position to pass by the correct value, most of time index same as position but it will may different from each other as well.

Upvotes: 4

Related Questions