<p>让我们使用伪“顺序”列:</p>
<pre><code>df_del = df.loc[(df['Status'] == 'Delivered')].copy()
df_open = df.loc[(df['Status'] == 'Opened')].copy()
df_del['order'] = df_del.groupby(['Customer_ID']).cumcount()
df_open['order'] = df_open.groupby(['Customer_ID']).cumcount()
d = df_del.rename(columns={'Date': 'Date Delivered'})
o = df_open.rename(columns={'Date': 'last action date', 'Status': 'Open Status'})
w = d.merge(o, on=['Customer_ID','Subject','order'], how='left')
w['last action date'] = w['last action date'].fillna(w['Date Delivered'])
W
</code></pre>
<p>输出:</p>
<pre class="lang-none prettyprint-override"><code> Date Delivered Customer_ID Subject Status order last action date Open Status
0 1-1-2020 123 How can I Help? Delivered 0 1-1-2020 Opened
1 1-2-2021 100 New Offer Delivered 0 1-2-2021 NaN
2 1-2-2021 100 New Offer Delivered 1 1-2-2021 NaN
3 1-4-2021 144 Last chance, buy now! Delivered 0 2-4-2021 Opened
4 1-4-2021 144 Last chance, buy now! Delivered 1 1-4-2021 NaN
</code></pre>