<p>您必须使用:</p>
<ul>
<li><code>str.split</code>将<code>COlA</code>和<code>ColB</code>中的字符串转换为列表</li>
<li><code>explode</code>将这些列表转换为行</li>
<li><code>get_dummies</code>以生成包含0和1值的指示符列</li>
<li><code>group-by.sum</code>对每个记录的行进行分组\u编号,ColB对</li>
</ul>
<p>困难的部分是,您希望使用排序值,而不仅仅是Ai列中的1。因此,我将使用<code>enumerate</code>获得排序值,并使用它乘以<code>get_dummies</code>将提供的值</p>
<p>可能代码:</p>
<pre><code># explode the "lists" and keep the ordering value for COlA
result = df.assign(COlA=df['COlA'].str.split(',').apply(
lambda x: list(enumerate(x, 1)))).assign(ColB=df['ColB'].str.split(' ')
).explode('COlA').explode('ColB')
# separate the ordering value from the value itself in different columns
result['Coeff'] = result['COlA'].apply(lambda x: x[0])
result['COlA'] = result['COlA'].apply(lambda x: x[1])
</code></pre>
<p>它给出:</p>
<pre><code> Record_No COlA ColB Coeff
0 1 A1 B1 1
0 1 A1 B2 1
0 1 A2 B1 2
0 1 A2 B2 2
0 1 A3 B1 3
0 1 A3 B2 3
1 2 A4 B3 1
1 2 A5 B3 2
2 3 A1 None 1
2 3 A4 None 2
2 3 A2 None 3
</code></pre>
<p>让我们继续:</p>
<pre><code># build the indicator values and replace None with Blank in ColB
result = pd.get_dummies(result.fillna('Blank'), columns=['COlA'])
# multiply the indicator value by the ordering value
result.iloc[:, 3:] = np.transpose(np.transpose(result.iloc[:, 3:].values
) * result.Coeff.values)
</code></pre>
<p>我们有:</p>
<pre><code> Record_No ColB Coeff COlA_A1 COlA_A2 COlA_A3 COlA_A4 COlA_A5
0 1 B1 1 1 0 0 0 0
0 1 B2 1 1 0 0 0 0
0 1 B1 2 0 2 0 0 0
0 1 B2 2 0 2 0 0 0
0 1 B1 3 0 0 3 0 0
0 1 B2 3 0 0 3 0 0
1 2 B3 1 0 0 0 1 0
1 2 B3 2 0 0 0 0 2
2 3 Blank 1 1 0 0 0 0
2 3 Blank 2 0 0 0 2 0
2 3 Blank 3 0 3 0 0 0
</code></pre>
<p>只需重命名列、分组值并删除现在无用的Coeff column</p>
<pre><code>result = result.drop(columns='Coeff').rename(
columns=lambda x: x.replace('COlA_', '')).groupby(
['Record_No', 'ColB']).sum().reset_index()
</code></pre>
<p>我们得到了期望值:</p>
<pre><code> Record_No ColB A1 A2 A3 A4 A5
0 1 B1 1 2 3 0 0
1 1 B2 1 2 3 0 0
2 2 B3 0 0 0 1 2
3 3 Blank 1 3 0 2 0
</code></pre>
<p>如果你需要新的号码。列,它现在是微不足道的:</p>
<pre><code>result = result.rename_axis('NewNo.').reset_index()
</code></pre>
<p>我们终于做到了:</p>
<pre><code> NewNo. Record_No ColB A1 A2 A3 A4 A5
0 0 1 B1 1 2 3 0 0
1 1 1 B2 1 2 3 0 0
2 2 2 B3 0 0 0 1 2
3 3 3 Blank 1 3 0 2 0
</code></pre>