How to merge columns by different criteria

Question

I have 2 df's with common columns and values

df1:

Metal Group        Trophic Level   Concentration       Author      Title
 Cadmium              Algae             0.01           Chang       Combined effects of Cadmium
 Cadmium              Algae             0.015          Robinson    Cadmium in the Algae
 Cadmium Chloride     Algae             0.01           Jones       Chloride effects in Algae
 .
 .
 .

df2:

 Metal Group       Trophic Level   Concentration 
 Cadmium              Algae             0.01        
 Cadmium              Algae             0.015        
 Cadmium Chloride     Algae             0.01
 .
 .
 .

I want to merge Author and Title column of df1 to their respective value in df2. The problem is: The column value that can be used to identify the corresponding author and title is sometimes different in each row.

For example, in the first row you can identify the author and title with the metal group and concentration at the same time.

In the second row you can identify the author by it's concentration.

I could also refer to several of these rows but I don't know how to do it.

I only know the pd.merge command

score 1 · Accepted Answer · answered Aug 29 '20 at 06:02

Merge needs to be performed on a column of unique identifiers or a list of columns, which can provide a unique identifier.
In the case of the example data, all three columns must be used.
- ['Metal Group', 'Trophic Level', 'Concentration']

import pandas as pd

d1 = {'Metal Group': ['Cadmium', 'Cadmium', 'Cadmium Chloride'], 'Trophic Level': ['Algae', 'Algae', 'Algae'], 'Concentration': [0.01, 0.015, 0.01], 'Author': ['Chang', 'Robinson', 'Jones'], 'Title': ['Combined effects of Cadmium','Cadmium in the Algae', 'Chloride effects in Algae']}
df1 = pd.DataFrame(d1)

d2 = {'Metal Group': ['Cadmium', 'Cadmium', 'Cadmium Chloride'], 'Trophic Level': ['Algae', 'Algae', 'Algae'], 'Concentration': [0.01, 0.015, 0.01]}
df2 = pd.DataFrame(d2)

# merge
dfm = pd.merge(df2, df1, on=['Metal Group', 'Trophic Level', 'Concentration'])

# display(dfm)
      Metal Group Trophic Level  Concentration    Author                        Title
          Cadmium         Algae          0.010     Chang  Combined effects of Cadmium
          Cadmium         Algae          0.015  Robinson         Cadmium in the Algae
 Cadmium Chloride         Algae          0.010     Jones    Chloride effects in Algae

How to merge columns by different criteria

1 Answers1