单步Python脚本性能优化：分组取最新数据方案为何耗时过长？

之前我找过单步操作保留分组内最新日期观测数据的方案，该方案在示例数据上有效，但在包含1500万行（精简后400万行）的真实数据集上运行需数小时，而我自己的两步法仅需数秒。

两种方案代码对比 #

高效的两步法 #

df['lastRptDt'] = df.groupby(['PrimaryID', 'SecondaryID'])['ReportDate'].transform(max)
df1 = df[(df['ReportDate']==df['lastRptDt'])]

耗时的单步方案 #

df.reset_index()\
.set_index(['PrimaryID', 'SecondaryID', 'ReportDate'], drop=False)\
.loc[:,:,df.groupby(['PrimaryID', 'SecondaryID']).ReportDate.max()]\
.set_index('index')

疑问 #

1. 是否没必要刻意追求单步解决方案？
2. 为何单步方案耗时如此之久？

编辑更新：补充原始数据与期望输出 #

原始数据 #

>>> df.to_dict()
{'PrimaryID': {0: 1,
  1: 1,
  2: 1,
  3: 1,
  4: 1,
  5: 1,
  6: 1,
  7: 1,
  8: 2,
  9: 2,
  10: 2,
  11: 2,
  12: 2,
  13: 2,
  14: 2,
  15: 2},
 'SecondaryID': {0: 'A',
  1: 'A',
  2: 'A',
  3: 'A',
  4: 'B',
  5: 'B',
  6: 'B',
  7: 'B',
  8: 'X',
  9: 'X',
  10: 'X',
  11: 'X',
  12: 'Y',
  13: 'Y',
  14: 'Y',
  15: 'Y'},
 'SubAccount': {0: 123,
  1: 456,
  2: 123,
  3: 456,
  4: 789,
  5: 987,
  6: 789,
  7: 246,
  8: 234,
  9: 752,
  10: 234,
  11: 755,
  12: 731,
  13: 480,
  14: 731,
  15: 841},
 'Value': {0: 5618.48,
  1: 8206.23,
  2: 6722.05,
  3: 5500.53,
  4: 8990.75,
  5: 6294.63,
  6: 8389.6,
  7: 343.02,
  8: 4157.57,
  9: 8218.0,
  10: 6430.68,
  11: 7148.57,
  12: 5406.63,
  13: 2429.83,
  14: 6251.38,
  15: 8256.93},
 'ReportDate': {0: Timestamp('2022-01-01 00:00:00'),
  1: Timestamp('2022-01-01 00:00:00'),
  2: Timestamp('2022-07-01 00:00:00'),
  3: Timestamp('2022-07-01 00:00:00'),
  4: Timestamp('2022-02-01 00:00:00'),
  5: Timestamp('2022-02-01 00:00:00'),
  6: Timestamp('2022-03-01 00:00:00'),
  7: Timestamp('2022-03-01 00:00:00'),
  8: Timestamp('2022-02-01 00:00:00'),
  9: Timestamp('2022-02-01 00:00:00'),
  10: Timestamp('2022-03-01 00:00:00'),
  11: Timestamp('2022-03-01 00:00:00'),
  12: Timestamp('2022-05-02 00:00:00'),
  13: Timestamp('2022-05-02 00:00:00'),
  14: Timestamp('2022-06-01 00:00:00'),
  15: Timestamp('2022-06-01 00:00:00')}}

期望输出 #

>>> df1.to_dict()
{'PrimaryID': {2: 1, 3: 1, 6: 1, 7: 1, 10: 2, 11: 2, 14: 2, 15: 2},
 'SecondaryID': {2: 'A',
  3: 'A',
  6: 'B',
  7: 'B',
  10: 'X',
  11: 'X',
  14: 'Y',
  15: 'Y'},
 'SubAccount': {2: 123,
  3: 456,
  6: 789,
  7: 246,
  10: 234,
  11: 755,
  14: 731,
  15: 841},
 'Value': {2: 6722.05,
  3: 5500.53,
  6: 8389.6,
  7: 343.02,
  10: 6430.68,
  11: 7148.57,
  14: 6251.38,
  15: 8256.93},
 'ReportDate': {2: Timestamp('2022-07-01 00:00:00'),
  3: Timestamp('2022-07-01 00:00:00'),
  6: Timestamp('2022-03-01 00:00:00'),
  7: Timestamp('2022-03-01 00:00:00'),
  10: Timestamp('2022-03-01 00:00:00'),
  11: Timestamp('2022-03-01 00:00:00'),
  14: Timestamp('2022-06-01 00:00:00'),
  15: Timestamp('2022-06-01 00:00:00')},
 'lastRptDt': {2: Timestamp('2022-07-01 00:00:00'),
  3: Timestamp('2022-07-01 00:00:00'),
  6: Timestamp('2022-03-01 00:00:00'),
  7: Timestamp('2022-03-01 00:00:00'),
  10: Timestamp('2022-03-01 00:00:00'),
  11: Timestamp('2022-03-01 00:00:00'),
  14: Timestamp('2022-06-01 00:00:00'),
  15: Timestamp('2022-06-01 00:00:00')}}

内容的提问来源于stack exchange，提问作者Misha