<p>此解决方案首先由所有列使用<code>split</code>,而不首先由<code>.</code>使用<code>M</code>,因此需要在<code>MultiIndex</code>中替换<code>NaN</code>,然后由<a href="http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.stack.html" rel="nofollow noreferrer">^{<cd6>}</a>和<a href="http://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.melt.html" rel="nofollow noreferrer">^{<cd7>}</a>重新设置值,以便在最后一步使用<code>merge</code>:</p>
<pre><code>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]
</code></pre>
<hr/>
<pre><code>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 -
</code></pre>