<p>从您的<code>DataFrames</code>,我们构建<code>df_full</code>:</p>
<pre class="lang-py prettyprint-override"><code>>>> df_full = pd.concat([pd.merge(df, df1, left_on='M', right_on='M_list_1'),
pd.merge(df, df1, left_on='M', right_on='M_list_2')]).reset_index()
>>> df_full
index M Color M_list_1 M_list_2 pink yellow blue red pink.1 yellow.1 blue.1 red.1
0 0 M1 pink M1 K1 1311 10000 104560 11560 Brand77 Brand17 Brand1 Brand59
1 1 M2 yellow M2 S2 78946 20000 204890 21120 - Brand61 Brand1 Brand1
2 2 M6 pink M6 Z6 154894 10009 10809 1009 Brand15 - Brand1 Brand77
3 3 M7 pink M7 Z7 44893 14613 146103 14783 Brand1 Brand13 - Brand1
4 4 M10 blue M10 S10 1351979 88009 889 871 - - - Brand19
5 0 M3 yellow S3 M3 23255 1000 2000 300000 Brand9 Brand9 - Brand8
6 1 M4 yellow Z4 M4 149643 789613 71113 1113 Brand96 Brand77 Brand9 Brand9
7 2 M5 blue S5 M5 148791 77778 70078 1578 Brand1 - - Brand1
8 3 M8 pink S8 M8 165496 70009 70999 799 Brand61 Brand1 Brand1 Brand1
9 4 M9 red S9 M9 5222 789746 7846 1146 - Brand1 - Brand2
</code></pre>
<p>我们首先从<code>Color</code>列获取<code>pink</code>、<code>yellow</code>、<code>blue</code>和<code>red</code>列中的值,以使用<a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.lookup.html" rel="nofollow noreferrer">^{<cd9>}</a>构建<code>Code</code>列,如下所示:</p>
<pre class="lang-py prettyprint-override"><code>df_full['Code'] = df_full.lookup(df_full.index, df_full['Color'])
</code></pre>
<p>然后我们对一个新列<code>Color1</code>执行相同的操作,它允许我们构建<code>Brand</code>并获得预期的结果:</p>
<pre class="lang-py prettyprint-override"><code>>>> df_full["Color1"] = df_full["Color"] + ".1"
>>> df_full['Brand'] = df_full.lookup(df_full.index, df_full['Color1'])
>>> df_full[['M', 'Color', 'Code', 'Brand']]
M Color Code Brand
0 M1 pink 1311 Brand77
1 M2 yellow 20000 Brand61
2 M6 pink 154894 Brand15
3 M7 pink 44893 Brand1
4 M10 blue 889 -
5 M3 yellow 1000 Brand9
6 M4 yellow 789613 Brand77
7 M5 blue 70078 -
8 M8 pink 165496 Brand61
9 M9 red 1146 Brand2
</code></pre>
<p>编辑:</p>
<p>正如@bbbbbb所评论的,<code>lookup</code>现在产生一个未来警告,因为它将被贬值。使用<code>pandas>=1.1.0</code>我们必须使用<a href="https://pandas.pydata.org/docs/reference/api/pandas.melt.html" rel="nofollow noreferrer">^{<cd14>}</a>:</p>
<pre class="lang-py prettyprint-override"><code>>>> df_full.melt(id_vars='Color', value_vars=['pink', 'yellow', 'blue', 'red'], ignore_index=False).query('Color == variable').loc[df.index, 'value']
0 1311
1 20000
2 154894
3 44893
4 889
5 1000
6 789613
7 70078
8 165496
9 1146
Name: value, dtype: int64
</code></pre>
<p>与<code>Brand</code>相同:</p>
<pre class="lang-py prettyprint-override"><code>>>> df_full["Color1"] = df_full["Color"] + ".1"
>>> df_full['Brand'] = df_full.melt(id_vars='Color1', value_vars=['pink.1', 'yellow.1', 'blue.1', 'red.1'], ignore_index=False).query('Color1 == variable').loc[df.index, 'value']
>>> df_full[['M', 'Color', 'Code', 'Brand']]
M Color Code Brand
0 M1 pink 1311 Brand77
1 M2 yellow 20000 Brand61
2 M6 pink 154894 Brand15
3 M7 pink 44893 Brand1
4 M10 blue 889 -
5 M3 yellow 1000 Brand9
6 M4 yellow 789613 Brand77
7 M5 blue 70078 -
8 M8 pink 165496 Brand61
9 M9 red 1146 Brand2
</code></pre>