Python按多列分组调整重复time_of_day值为连续序列的实现方法

解决方法 #

需求核心逻辑为：同pid、同date分组下，按datestamp升序排序后，直接给每组记录生成从1开始的连续序号替换原有重复的time_of_day即可，无需复杂的条件判断。

完整可运行代码 #

import pandas as pd
from pandas import Timestamp
import datetime

# 加载示例数据
df_dict = {'pid': {0: '21',
  1: '21',
  2: '21',
  3: '21',
  4: '21',
  5: '21',
  200: '26',
  201: '26',
  202: '26',
  203: '26',
  204: '26'},
 'datestamp': {0: Timestamp('2019-12-07 21:33:11'),
  1: Timestamp('2019-12-08 13:32:16'),
  2: Timestamp('2019-12-08 13:33:41'),
  3: Timestamp('2019-12-08 19:54:22'),
  4: Timestamp('2019-12-08 19:55:24'),
  5: Timestamp('2019-12-09 12:11:24'),
  200: Timestamp('2020-02-12 20:16:33'),
  201: Timestamp('2020-02-13 08:37:21'),
  202: Timestamp('2020-02-13 13:24:20'),
  203: Timestamp('2020-02-13 17:05:27'),
  204: Timestamp('2020-02-13 20:02:12')},
 'date': {0: datetime.date(2019, 12, 7),
  1: datetime.date(2019, 12, 8),
  2: datetime.date(2019, 12, 8),
  3: datetime.date(2019, 12, 8),
  4: datetime.date(2019, 12, 8),
  5: datetime.date(2019, 12, 9),
  200: datetime.date(2020, 2, 12),
  201: datetime.date(2020, 2, 13),
  202: datetime.date(2020, 2, 13),
  203: datetime.date(2020, 2, 13),
  204: datetime.date(2020, 2, 13)},
 'time_of_day': {0: 4,
  1: 2,
  2: 2,
  3: 4,
  4: 4,
  5: 2,
  200: 4,
  201: 1,
  202: 2,
  203: 3,
  204: 4},
 'depressed': {0: 3,
  1: 3,
  2: 4,
  3: 4,
  4: 4,
  5: 4,
  200: 3,
  201: 3,
  202: 1,
  203: 2,
  204: 2},
 'prev_night_sleep': {0: 0.0,
  1: 0.0,
  2: 0.0,
  3: 0.0,
  4: 0.0,
  5: 11.35,
  200: 7.166666666666667,
  201: 10.18333333333333,
  202: 10.18333333333333,
  203: 10.18333333333333,
  204: 10.18333333333333}}

data = pd.DataFrame.from_dict(df_dict)

# 核心处理逻辑
# 1. 先按pid、date、datestamp升序排序，保证同组内记录按提交时间先后排列
data = data.sort_values(by=['pid', 'date', 'datestamp'], ignore_index=True)
# 2. 按pid、date分组，组内生成从1开始的连续序号替换原有time_of_day
data['time_of_day'] = data.groupby(['pid', 'date']).cumcount() + 1

# 你也可以用尝试的assign链式写法实现：
# data = data.sort_values(by=['pid', 'date', 'datestamp'], ignore_index=True)\
#            .assign(time_of_day = lambda x: x.groupby(['pid', 'date']).cumcount() + 1)

结果验证 #

处理后pid=21、date=2019-12-08的4条记录的time_of_day会依次变为1、2、3、4，完全符合你给出的示例调整要求；原本没有重复的分组（比如pid=26、date=2020-02-13）的time_of_day仍保持1-4的原有顺序，不会产生影响。

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