Pandas用重复的索引值填充组内缺失的日期和值

Question

我试图按用户组填充缺少的日期，但是我的一个索引列有一个重复的日期，所以我试图使用唯一日期并重新索引，然后我得到的长度不匹配错误。怎么了我是否可以按天频率重新采样而不会出现重复错误。在

import pandas as pandas

x = pandas.DataFrame({'user': ['a','a','b','b','a'], 'dt': ['2016-01-01','2016-01-02', '2016-01-05','2016-01-06','2016-01-06'], 'val': [1,33,2,1,2]})
udates=x['dt'].unique()
x['dt'] = pandas.to_datetime(x['dt'])
dates = x.set_index(udates).resample('D').asfreq().index
users=x['user'].unique()
idx = pandas.MultiIndex.from_product((dates, users), names=['dt', 'user'])
x.set_index(['dt', 'user']).reindex(idx, fill_value=0).reset_index()
print(x)

期望输出

^{pr2}$

Answer 1

这里有一种方法，将每个user重新编制索引，使日期范围从最小日期到最长日期：

# setup your dataframe as you had it before:
x = pandas.DataFrame({'user': ['a','a','b','b','a'], 'dt': ['2016-01-01','2016-01-02', '2016-01-05','2016-01-06','2016-01-06'], 'val': [1,33,2,1,2]})
udates=x['dt'].unique()
x['dt'] = pandas.to_datetime(x['dt'])

# fill with new dates:
filled_df = (x.set_index('dt')
             .groupby('user')
             .apply(lambda d: d.reindex(pd.date_range(min(x.dt),
                                                      max(x.dt),
                                                      freq='D')))
             .drop('user', axis=1)
             .reset_index('user')
             .fillna(0))


>>> filled_df
           user   val
2016-01-01    a   1.0
2016-01-02    a  33.0
2016-01-03    a   0.0
2016-01-04    a   0.0
2016-01-05    a   0.0
2016-01-06    a   2.0
2016-01-01    b   0.0
2016-01-02    b   0.0
2016-01-03    b   0.0
2016-01-04    b   0.0
2016-01-05    b   2.0
2016-01-06    b   1.0

Answer 2

另一种方式不如@sacul优雅…但速度几乎一样。在

import pandas as pd
x = pd.DataFrame({'user': ['a','a','b','b','a'],
                  'dt': ['2016-01-01','2016-01-02',
                         '2016-01-05','2016-01-06','2016-01-06'],
                  'val': [1,33,2,1,2]})

users = pd.unique(x.user)
x.dt = pd.to_datetime(x.dt)
dates = pd.date_range(min(x.dt), max(x.dt))
x.set_index('dt', inplace=True)

df = pd.DataFrame(index=dates)
for u in users:
    df[u] = x[x.user==u].val

df = df.unstack().reset_index()
df.rename(columns={'level_0': 'user',
                    'level_1': 'dt',
                    0: 'val'}, inplace=True)
df.val.fillna(0, inplace=True)
df.val = df.val.astype(int)
df = df[['dt', 'user', 'val']]

数据框：

^{pr2}$