python - 填写 pandas df 中缺少的日期

Question

我有一个数据对应于数据库列表和差异行，以及它们的使用日期。

 DB             Dates        USAGE

 ABC            03-06-2018   IN USE
 ABC            07-06-2018   IN USE 
 XYZ            04-06-2018   IN USE
 XYZ            08-06-2018   IN USE

我想要的是让每个数据库都对应完整的日历月，而不仅仅是使用它们的日期

 DB             Dates        USAGE
 ABC            01-06-2018    NOT IN USE
 ABC            02-06-2018    NOT IN USE
 ABC            03-06-2018    IN USE
 .
 .
 ABC            07-06-2018    IN USE
 .
 .
 ABC            30-06-2018    NOT IN USE 
 XYZ            01-06-2018    NOT IN USE
 .
 .
 XYZ            30-06-2018    NOT IN USE

Answer 1

使用:

df['Dates'] = pd.to_datetime(df['Dates'], format='%d-%m-%Y')

a = df['Dates'].dt.to_period('m')
dates = pd.date_range(a.min().to_timestamp('ms'), a.max().to_timestamp('m'))

mux = pd.MultiIndex.from_product([df['DB'].unique(), dates], names=['DB','Dates'])

df = df.set_index(['DB','Dates'])['USAGE'].reindex(mux, fill_value='NOT IN USE').reset_index()
print (df.head())
    DB      Dates       USAGE
0  ABC 2018-06-01  NOT IN USE
1  ABC 2018-06-02  NOT IN USE
2  ABC 2018-06-03      IN USE
3  ABC 2018-06-04  NOT IN USE
4  ABC 2018-06-05  NOT IN USE

print (df.tail())
     DB      Dates       USAGE
55  XYZ 2018-06-26  NOT IN USE
56  XYZ 2018-06-27  NOT IN USE
57  XYZ 2018-06-28  NOT IN USE
58  XYZ 2018-06-29  NOT IN USE
59  XYZ 2018-06-30  NOT IN USE

详细信息:

print (dates)
DatetimeIndex(['2018-06-01', '2018-06-02', '2018-06-03', '2018-06-04',
               '2018-06-05', '2018-06-06', '2018-06-07', '2018-06-08',
               '2018-06-09', '2018-06-10', '2018-06-11', '2018-06-12',
               '2018-06-13', '2018-06-14', '2018-06-15', '2018-06-16',
               '2018-06-17', '2018-06-18', '2018-06-19', '2018-06-20',
               '2018-06-21', '2018-06-22', '2018-06-23', '2018-06-24',
               '2018-06-25', '2018-06-26', '2018-06-27', '2018-06-28',
               '2018-06-29', '2018-06-30'],
              dtype='datetime64[ns]', freq='D')

解释:

1. 首先转换列 to_datetime
2. 创建所有可能的日期 - 首先将列转换为 to_period , 然后到 date_range带有 to_timestamp 以及月的开始和结束
3. 然后创建MultiIndex from_product
4. 和reindex替换缺失值。