我有以下数据帧(示例(：
import pandas as pd
data = [['A', '2022-09-01', 2], ['A', '2022-09-02', 1], ['A', '2022-09-04', 3], ['A', '2022-09-06', 2],
['A', '2022-09-07', 1], ['A', '2022-09-07', 2], ['A', '2022-09-08', 4], ['A', '2022-09-09', 2],
['B', '2022-09-01', 2], ['B', '2022-09-03', 4], ['B', '2022-09-04', 2], ['B', '2022-09-05', 2],
['B', '2022-09-07', 1], ['B', '2022-09-08', 3], ['B', '2022-09-10', 2]]
df = pd.DataFrame(data = data, columns = ['group', 'date', 'value'])
df['date'] = pd.to_datetime(df['date'])
df['diff_days'] = (df['date']-df['date'].groupby(df['group']).transform('first')).dt.days
group date value diff_days
0 A 2022-09-01 2 0
1 A 2022-09-02 1 1
2 A 2022-09-04 3 3
3 A 2022-09-06 2 5
4 A 2022-09-07 1 6
5 A 2022-09-07 2 6
6 A 2022-09-08 4 7
7 A 2022-09-09 2 8
8 B 2022-09-01 2 0
9 B 2022-09-03 4 2
10 B 2022-09-04 2 3
11 B 2022-09-05 2 4
12 B 2022-09-07 1 6
13 B 2022-09-08 3 7
14 B 2022-09-10 2 9
我想用每组上一个日期的值来填写缺失的日期。我可以使用这个答案中的代码，但问题是每个组可能有重复的条目(日期(。返回以下错误：
df['date'] = pd.to_datetime(df['date'])
df = df.set_index(
['date', 'group']
).unstack(
fill_value=-999
).asfreq(
'D', fill_value=-999
).stack().sort_index(level=1).reset_index()
df.replace(-999, np.nan).ffill()
ValueError: Index contains duplicate entries, cannot reshape
我想要的输出应该是这样的：
data = [['A', '2022-09-01', 2, 0], ['A', '2022-09-02', 1, 1], ['A', '2022-09-03', 1, 1], ['A', '2022-09-04', 3, 3],
['A', '2022-09-05', 3, 3], ['A', '2022-09-06', 2, 5], ['A', '2022-09-07', 1, 6], ['A', '2022-09-07', 2, 6],
['A', '2022-09-08', 4, 7], ['A', '2022-09-09', 2, 8],
['B', '2022-09-01', 2, 0], ['B', '2022-09-02', 2, 0], ['B', '2022-09-03', 4, 2], ['B', '2022-09-04', 2, 3],
['B', '2022-09-05', 2, 4], ['B', '2022-09-06', 2, 4], ['B', '2022-09-07', 1, 6], ['B', '2022-09-08', 3, 7],
['B', '2022-09-09', 3, 7], ['B', '2022-09-10', 2, 9]]
df_desired = pd.DataFrame(data = data, columns = ['group', 'date', 'value', ' diff_days'])
group date value diff_days
0 A 2022-09-01 2 0
1 A 2022-09-02 1 1
2 A 2022-09-03 1 1
3 A 2022-09-04 3 3
4 A 2022-09-05 3 3
5 A 2022-09-06 2 5
6 A 2022-09-07 1 6
7 A 2022-09-07 2 6
8 A 2022-09-08 4 7
9 A 2022-09-09 2 8
10 B 2022-09-01 2 0
11 B 2022-09-02 2 0
12 B 2022-09-03 4 2
13 B 2022-09-04 2 3
14 B 2022-09-05 2 4
15 B 2022-09-06 2 4
16 B 2022-09-07 1 6
17 B 2022-09-08 3 7
18 B 2022-09-09 3 7
19 B 2022-09-10 2 9
解释了一些日期：
- 对于组A；2022-09-03"；以及"；2022-09-05"；缺少。正如您所看到的，这些值是从上一个日期获得的
- 对于组B；2022-09-02"2022-09-06"；以及"；2022-09-09"；缺少。同样，对于这些，它们从上一行获取值
所以我想知道是否有人知道如何填写这些缺失的日期，即使使用Pandas每个组可能有重复的条目？
解决方案
c = ['group', 'date']
m = df[c].duplicated(keep='last')
s = df[~m].set_index('date').groupby('group').resample('D').ffill()
out = pd.concat([df[m], s.droplevel(0).reset_index()]).sort_values(c)
这是如何工作的
- 识别每个
group和date的重复行 - 删除重复数据并使用前向填充
resample数据帧 Concat将重复的行与重新采样的行相加以获得结果
结果
group date value diff_days
0 A 2022-09-01 2 0
1 A 2022-09-02 1 1
2 A 2022-09-03 1 1
3 A 2022-09-04 3 3
4 A 2022-09-05 3 3
5 A 2022-09-06 2 5
4 A 2022-09-07 1 6
6 A 2022-09-07 2 6
7 A 2022-09-08 4 7
8 A 2022-09-09 2 8
9 B 2022-09-01 2 0
10 B 2022-09-02 2 0
11 B 2022-09-03 4 2
12 B 2022-09-04 2 3
13 B 2022-09-05 2 4
14 B 2022-09-06 2 4
15 B 2022-09-07 1 6
16 B 2022-09-08 3 7
17 B 2022-09-09 3 7
18 B 2022-09-10 2 9
您可以使用helper列对日期进行重复数据消除：
(df.assign(n=df.groupby(['group', 'date']).cumcount())
.pivot(index=['date', 'n'], columns='group')
.ffill()
.stack().reset_index()
.sort_values(by=['group', 'date'], ignore_index=True)
[df.columns]
)
输出：
group date value diff_days
0 A 2022-09-01 2.0 0.0
1 A 2022-09-02 1.0 1.0
2 A 2022-09-03 1.0 1.0
3 A 2022-09-04 3.0 3.0
4 A 2022-09-05 3.0 3.0
5 A 2022-09-06 2.0 5.0
6 A 2022-09-07 1.0 6.0
7 A 2022-09-07 2.0 6.0
8 A 2022-09-08 4.0 7.0
9 A 2022-09-09 2.0 8.0
10 A 2022-09-10 2.0 8.0
11 B 2022-09-01 2.0 0.0
12 B 2022-09-02 2.0 0.0
13 B 2022-09-03 4.0 2.0
14 B 2022-09-04 2.0 3.0
15 B 2022-09-05 2.0 4.0
16 B 2022-09-06 2.0 4.0
17 B 2022-09-07 1.0 6.0
18 B 2022-09-07 1.0 6.0
19 B 2022-09-08 3.0 7.0
20 B 2022-09-09 3.0 7.0
21 B 2022-09-10 2.0 9.0