<p>尝试将两个帧转换为与另一帧兼容的格式:</p>
<p>DF1</p>
<pre><code># Get df1 into usable format
df1 = df1.set_index('id')
# Create Multi Index by splitting columns on '_'
df1.columns = df1.columns.str.split('_', expand=True)
# Stack to create long format frame
df1 = df1.stack().reset_index()
# Fix column names to match df2/output
df1.columns = ['id', 'wave', 'color']
# Set like to 1 for these since this table indicates likes
df1['like'] = 1
</code></pre>
<p><code>df1</code>:</p>
<pre><code> id wave color like
0 01 1 red 1
1 01 2 red 1
2 02 1 red 1
3 02 2 yellow 1
</code></pre>
<hr/>
<p>DF2</p>
<pre><code># Get df2 into usable format
# Set index to ID
df2 = df2.set_index('id')
# Create Multi Index by splitting columns on '_'
df2.columns = df2.columns.str.split('_', expand=True)
# Stack to create long format frame
df2 = df2.stack(level=[1, 2]).reset_index()
# Fix column names to match df1
df2.columns = ['id', 'wave', 'color', 'dislike']
# Turn "wave1" into 1, "wave2" into 2, ... etc.
df2['wave'] = df2['wave'].str.lstrip('wave')
</code></pre>
<p><code>df2</code>:</p>
<pre><code> id wave color dislike
0 01 1 blue 2
1 01 1 red 1
2 01 1 yellow 7
3 01 2 blue 3
4 01 2 red 1
5 01 2 yellow 7
6 02 1 blue 7
7 02 1 red 1
8 02 1 yellow 2
9 02 2 blue 7
10 02 2 red 2
11 02 2 yellow 1
</code></pre>
<hr/>
<p>然后<code>merge</code>将帧放在一起:</p>
<pre><code># Merge On Common Columns
df3 = df1.merge(df2, on=['id', 'wave', 'color'], how='outer')
# Fill empty values in like and dislike with 0 (only 1s in source DF1)
# (Fill dislikes in case there are likes in df1 that are not dislikes in df2)
df3[['like', 'dislike']] = df3[['like', 'dislike']].fillna(0).astype(int)
# Sort Values and fix index (to match output in question)
df3 = df3.sort_values(
['id', 'color'], ascending=[True, False]
).reset_index(drop=True)
</code></pre>
<p><code>df3</code>:</p>
<pre><code> id wave color like dislike
0 01 1 yellow 0 7
1 01 2 yellow 0 7
2 01 1 red 1 1
3 01 2 red 1 1
4 01 1 blue 0 2
5 01 2 blue 0 3
6 02 1 yellow 0 2
7 02 2 yellow 1 1
8 02 1 red 1 1
9 02 2 red 0 2
10 02 1 blue 0 7
11 02 2 blue 0 7
</code></pre>