<p>在合并之前,我们可以使用<a href="https://pyjanitor.readthedocs.io/notebooks/Pivoting%20Data%20from%20Wide%20to%20Long.html" rel="nofollow noreferrer">pivot_longer</a>from <a href="https://pyjanitor.readthedocs.io/index.html" rel="nofollow noreferrer">pyjanitor</a>来重塑各个数据帧:</p>
<pre><code>left = (df1.pivot_longer('id',
names_to=('.value', 'num'),
names_pattern=r".+_(.+)_(\d$)")
.rename(columns={"wave":"color",
"num":"wave"})
.assign(like = 1)
)
left
id wave color like
0 01 1 red 1
1 02 1 red 1
2 01 2 red 1
3 02 2 yellow 1
right = (df2.pivot_longer('id',
names_to=(".value", "dislike", "color"),
names_pattern = r".+_(.+)(\d)_(.+)",
sort_by_appearance=True)
.rename(columns = {"dislike":"wave", "wave":"dislike"})
)
right
id wave color dislike
0 01 1 yellow 7
1 01 1 red 1
2 01 1 blue 2
3 01 2 yellow 7
4 01 2 red 1
5 01 2 blue 3
6 02 1 yellow 2
7 02 1 red 1
8 02 1 blue 7
9 02 2 yellow 1
10 02 2 red 2
11 02 2 blue 7
right.merge(left, how = 'outer').fillna(0)
id wave color dislike like
0 01 1 yellow 7 0.0
1 01 1 red 1 1.0
2 01 1 blue 2 0.0
3 01 2 yellow 7 0.0
4 01 2 red 1 1.0
5 01 2 blue 3 0.0
6 02 1 yellow 2 0.0
7 02 1 red 1 1.0
8 02 1 blue 7 0.0
9 02 2 yellow 1 1.0
10 02 2 red 2 0.0
11 02 2 blue 7 0.0
</code></pre>