将每个日期有多行的缺失日期添加到dataframe

Question

重新措辞的问题：

我实际上是从几个excel文件中提取数据来监控我的每日卡路里摄入量。我设法使用列表理解来生成日期

我的意见：

        Date        Time         calories   duration
# 0     22/5/2021   Morning      420        50
# 1     22/5/2021   Afternoon    380        40
# 2     24/5/2021   Morning      390        45
# 3     24/5/2021   Afternoon    400        50
# 4     26/5/2021   Morning      350        45
# 5     26/5/2021   Afternoon    280        50
# 6     27/5/2021   Morning      300        44
# 7     27/5/2021   Afternoon    430        58

输出应如下所示：

         Date       Time calories duration
0   22/5/2021    Morning      420       50
1   22/5/2021  Afternoon      380       40
2   23/5/2021    Morning      Nan      Nan
3   23/5/2021  Afternoon      Nan      Nan
4   24/5/2021    Morning      390       45
5   24/5/2021  Afternoon      400       50
6   25/5/2021    Morning      Nan      Nan
7   25/5/2021  Afternoon      Nan      Nan
8   26/5/2021    Morning      350       45
9   26/5/2021  Afternoon      280       50
10  27/5/2021    Morning      300       44
11  27/5/2021  Afternoon      430       58

Answer 1

使用.stack()和.unstack()方法的解决方案：

创建示例数据帧的代码：

import numpy as np
import pandas as pd

from io import StringIO
data = StringIO("""
     Date       Time  calories  duration
22/5/2021    Morning       420        50
22/5/2021  Afternoon       380        40
24/5/2021    Morning       390        45
24/5/2021  Afternoon       400        50
26/5/2021    Morning       350        45
26/5/2021  Afternoon       280        50
27/5/2021    Morning       300        44
27/5/2021  Afternoon       430        58
""")

df = pd.read_table(data, sep='\s+')
df
        Date        Time         calories   duration
# 0     22/5/2021   Morning      420        50
# 1     22/5/2021   Afternoon    380        40
# 2     24/5/2021   Morning      390        45
# 3     24/5/2021   Afternoon    400        50
# 4     26/5/2021   Morning      350        45
# 5     26/5/2021   Afternoon    280        50
# 6     27/5/2021   Morning      300        44
# 7     27/5/2021   Afternoon    430        58

解决方案：

# convert date column to datetime
df['Date'] = pd.to_datetime(df.Date, format="%d/%m/%Y")


(df
    .set_index(['Date', 'Time'])
    .unstack(fill_value=np.nan)
    .asfreq('D', fill_value=np.nan)
    .stack(dropna=False)
    .sort_index(ascending=[True, False])
    .reset_index()
)
#       Date        Time        calories    duration
# 0     2021-05-22  Morning     420.0       50.0
# 1     2021-05-22  Afternoon   380.0       40.0
# 2     2021-05-23  Morning     NaN         NaN
# 3     2021-05-23  Afternoon   NaN         NaN
# 4     2021-05-24  Morning     390.0       45.0
# 5     2021-05-24  Afternoon   400.0       50.0
# 6     2021-05-25  Morning     NaN         NaN
# 7     2021-05-25  Afternoon   NaN         NaN
# 8     2021-05-26  Morning     350.0       45.0
# 9     2021-05-26  Afternoon   280.0       50.0
# 10    2021-05-27  Morning     300.0       44.0
# 11    2021-05-27  Afternoon   430.0       58.0