Skip to content Skip to sidebar Skip to footer

Is There Any Method To Match Tabular List With Pivot List Format?

I have 2 set of data, 1 table is in pivot list format and the criteria to match is if the Color - pink, yellow, blue, red matches the column of the M list, then get the Code number

Solution 1:

Use DataFrame.merge with DataFrame.melt:

df2 = (df.merge(df1.melt('M_list',
               var_name='Color', 
               value_name='Code')
         .rename(columns={'M_list':'M'}), how='left', on=['M','Color']))
print (df2)
     M   Color    Code
0   M1    pink    13111   M2  yellow   200002   M3  yellow    10003   M4  yellow  7896134   M5    blue   700785   M6    pink  1548946   M7    pink   448937   M8    pink  1654968   M9     red    11469  M10    blue     889

EDIT: You can use double melt:

df2 = (df1.melt(['M_list_1','M_list_2'], var_name='Color',  value_name='Code')
          .melt(['Color','Code'], value_name='M')
          .drop('variable', axis=1))

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

Post a Comment for "Is There Any Method To Match Tabular List With Pivot List Format?"