如果原始数据中没有缺失值的解决方案 - 首先用原始值替换具有一个元素组的行,然后向前填充缺失值:
m = ~df.duplicated(['ID','Date']) & ~df.duplicated(['ID','Date'], keep=False)
df1 = df.groupby(['ID','Date']).shift(-1).mask(m, df).ffill()
df = pd.concat([df, df1.add_suffix('.1')], axis=1)
print (df)
ID Date Time Lat Lon Time.1 Lat.1 Lon.1
0 A 07/16/2019 08:00 29.39291 -98.50925 09:00 29.39923 -98.51256
1 A 07/16/2019 09:00 29.39923 -98.51256 10:00 29.40147 -98.51123
2 A 07/16/2019 10:00 29.40147 -98.51123 10:00 29.40147 -98.51123
3 A 07/18/2019 08:30 29.38752 -98.52372 09:30 29.39291 -98.50925
4 A 07/18/2019 09:30 29.39291 -98.50925 09:30 29.39291 -98.50925
5 B 07/16/2019 08:00 29.39537 -98.50402 08:00 29.39537 -98.50402
6 B 07/18/2019 11:00 29.39343 -98.49707 12:00 29.39291 -98.50925
7 B 07/18/2019 12:00 29.39291 -98.50925 12:00 29.39291 -98.50925
8 B 07/19/2019 10:00 29.39556 -98.53148 10:00 29.39556 -98.53148
如果不需要自定义函数则加倍groupby
是必要的,因为每组都需要前向填充:
df1 = df.groupby(['ID','Date']).shift(-1).groupby([df['ID'],df['Date']]).ffill().fillna(df)
df = pd.concat([df, df1.add_suffix('.1')], axis=1)
print (df)
ID Date Time Lat Lon Time.1 Lat.1 Lon.1
0 A 07/16/2019 08:00 29.39291 -98.50925 09:00 29.39923 -98.51256
1 A 07/16/2019 09:00 29.39923 -98.51256 10:00 29.40147 -98.51123
2 A 07/16/2019 10:00 29.40147 -98.51123 10:00 29.40147 -98.51123
3 A 07/18/2019 08:30 29.38752 -98.52372 09:30 29.39291 -98.50925
4 A 07/18/2019 09:30 29.39291 -98.50925 09:30 29.39291 -98.50925
5 B 07/16/2019 08:00 29.39537 -98.50402 08:00 29.39537 -98.50402
6 B 07/18/2019 11:00 29.39343 -98.49707 12:00 29.39291 -98.50925
7 B 07/18/2019 12:00 29.39291 -98.50925 12:00 29.39291 -98.50925
8 B 07/19/2019 10:00 29.39556 -98.53148 10:00 29.39556 -98.53148
使用 lambda 函数应该是这样的解决方案:
c = ['Time','Lat','Lon']
df1 = df.groupby(['ID','Date'])[c].apply(lambda x: x.shift(-1).ffill()).fillna(df)
df = pd.concat([df, df1.add_suffix('.1')], axis=1)
print (df)
ID Date Time Lat Lon Time.1 Lat.1 Lon.1
0 A 07/16/2019 08:00 29.39291 -98.50925 09:00 29.39923 -98.51256
1 A 07/16/2019 09:00 29.39923 -98.51256 10:00 29.40147 -98.51123
2 A 07/16/2019 10:00 29.40147 -98.51123 10:00 29.40147 -98.51123
3 A 07/18/2019 08:30 29.38752 -98.52372 09:30 29.39291 -98.50925
4 A 07/18/2019 09:30 29.39291 -98.50925 09:30 29.39291 -98.50925
5 B 07/16/2019 08:00 29.39537 -98.50402 08:00 29.39537 -98.50402
6 B 07/18/2019 11:00 29.39343 -98.49707 12:00 29.39291 -98.50925
7 B 07/18/2019 12:00 29.39291 -98.50925 12:00 29.39291 -98.50925
8 B 07/19/2019 10:00 29.39556 -98.53148 10:00 29.39556 -98.53148