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?"