How to match 2 data frames if there is duplicates column name?

From my previous question Is there any method to match tabular list with pivot list format? , I have found the method to get the result by using DataFrames.melt method. However, I found my data in excel have duplicates header name (the colors) which pandas will automatically add .1 and I want to check is there any other method which don’t need to have the header to be same name to match it?

the criteria to match is if the Color – pink, yellow, blue, red matches the column of the M list, then get the Code number, and Brand number

As below:

df = pd.DataFrame({'M': ['M1','M2','M3','M4','M5','M6','M7','M8','M9','M10'],
                    'Color' : ['pink','yellow','yellow','yellow','blue','pink','pink','pink','red','blue']})

df1 = pd.DataFrame({'M_list_1': ['S3','M73','M18','M12','M1','M2','M7','S5','S17','S18','S20','M78','Z4','M6','S8','M10','M99','S9'],
                    'M_list_2': ['M3','Z73','Z18','Z12','K1','S2','Z7','M5','M17','M18','M20','M78','M4','Z6','M8','S10','M99','M9'],
                    'pink': [23255,1755,7897,6666,1311,78946,44893,148791,18783,487913,79815,88888,149643,154894,165496,1351979,15648,5222],
                    'yellow': [1000,99999,78945,15543,10000,20000,14613,77778,45139,11776,78,1231,789613,10009,70009,88009,13036,789746],
                    'blue': [2000,8000,9000,6000,104560,204890,146103,70078,40009,11006,78789,13531,71113,10809,70999,889,36,7846],
                    'red': [300000,82200,90,60330,11560,21120,14783,1578,31109,91006,9719,9131,1113,1009,799,871,10116,1146],
                    'pink.1': ["Brand9","Brand9","Brand9","Brand13","Brand77","-","Brand1","Brand1"
                               ,"Brand1","Brand51","Brand71","Brand16","Brand96","Brand15","Brand61","-","Brand13","-"],
                    'yellow.1': ["Brand9","-","-","-","Brand17","Brand61","Brand13","-"
                               ,"Brand8","Brand9","Brand1","-","Brand77","-","Brand1","-","Brand11","Brand1"],
                    'blue.1': ["-","Brand13","Brand9","-","Brand1","Brand1","-","-"
                               ,"Brand1","Brand16","Brand1","-","Brand9","Brand1","Brand1","-","-","-"],
                    'red.1': ["Brand8","Brand91","Brand9","Brand13","Brand59","Brand1","Brand1","Brand1"
                               ,"Brand2","-","Brand1","-","Brand9","Brand77","Brand1","Brand19","-","Brand2"]})

My desire outcome will be like below:

     M   Color    Code     Brand
0   M1    pink    1311   Brand77
1   M2  yellow   20000   Brand61
2   M3  yellow    1000   Brand9
3   M4  yellow  789613   Brand77
4   M5    blue   70078       -
5   M6    pink  154894   Brand15
6   M7    pink   44893   Brand1
7   M8    pink  165496   Brand61
8   M9     red    1146   Brand2
9  M10    blue     889       -

Answer

This solution first use split by all columns without first M by ., so is necessary replacement NaNs in MultiIndex, then values are reshaped by DataFrame.stack and DataFrame.melt, so possible in last step use merge:

df2 = df1.set_index(['M_list_1','M_list_2'])
f = lambda x: x if pd.notna(x) else 'Code'
df2.columns = df2.columns.str.split('.', expand=True)
df2 = (df2.rename(columns = f)
          .stack(0)
          .reset_index()
          .rename(columns={'level_2':'Color','1':'Brand'})
          .melt(['Color','Code','Brand'], value_name='M')
          .drop('variable', axis=1))
print (df2)
      Color    Code    Brand    M
0      blue    2000        -   S3
1      pink   23255   Brand9   S3
2       red  300000   Brand8   S3
3    yellow    1000   Brand9   S3
4      blue    8000  Brand13  M73
..      ...     ...      ...  ...
139  yellow   13036  Brand11  M99
140    blue    7846        -   M9
141    pink    5222        -   M9
142     red    1146   Brand2   M9
143  yellow  789746   Brand1   M9

[144 rows x 4 columns]

df = df.merge(df2, how='left', on=['M','Color'])
print (df)
     M   Color    Code    Brand
0   M1    pink    1311  Brand77
1   M2  yellow   20000  Brand61
2   M3  yellow    1000   Brand9
3   M4  yellow  789613  Brand77
4   M5    blue   70078        -
5   M6    pink  154894  Brand15
6   M7    pink   44893   Brand1
7   M8    pink  165496  Brand61
8   M9     red    1146   Brand2
9  M10    blue     889        -