我有两个日期列“StartDate”和“EndDate”。我想查找从2019年12月起这两个日期之间的每个月的天数,并向前忽略2019年之前的任何几个月进行计算。每行的StartDate和EndDate可以跨越2年,其中月份重叠,并且Date列也可以为空。

样本数据:

df = {'Id': ['1','2','3','4','5','6','7', '8'],
      'Item': ['A','B','C','D','E','F','G', 'H'],
        'StartDate': ['2019-12-10', '2019-12-01', '2019-10-01', '2020-01-01', '2019-03-01','2019-03-01','2019-10-01', ''],
        'EndDate': ['2020-02-21' ,'2020-01-01','2020-08-31','2020-01-30','2019-12-31','2019-12-31','2020-08-31', '']
        }
df = pd.DataFrame(df,columns= ['Id', 'Item','StartDate','EndDate'])

预期O/P:

下面的解决方案部分起作用。
df['StartDate'] = pd.to_datetime(df['StartDate'])
df['EndDate'] = pd.to_datetime(df['EndDate'])

def days_of_month(x):
    s = pd.date_range(*x, freq='D').to_series()
    return s.resample('M').count().rename(lambda x: x.month)

df1 = df[['StartDate', 'EndDate']].apply(days_of_month, axis=1).fillna(0)

df_final = df[['StartDate', 'EndDate']].join([df['StartDate'].dt.year.rename('Year'), df1])

最佳答案

尝试这个:

df.join(df.dropna(axis=0,how='any')
         .apply(lambda x: pd.date_range(x['StartDate'],x['EndDate'], freq='D')
         .to_frame().resample('M').count().loc['2019-12-01':].unstack(), axis=1)[0].fillna(0))

输出:
 Id Item  StartDate    EndDate  2019-12-31 00:00:00  2020-01-31 00:00:00  2020-02-29 00:00:00  2020-03-31 00:00:00  2020-04-30 00:00:00  2020-05-31 00:00:00  2020-06-30 00:00:00  2020-07-31 00:00:00  2020-08-31 00:00:00
0  1    A 2019-12-10 2020-02-21                 22.0                 31.0                 21.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
1  2    B 2019-12-01 2020-01-01                 31.0                  1.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
2  3    C 2019-10-01 2020-08-31                 31.0                 31.0                 29.0                 31.0                 30.0                 31.0                 30.0                 31.0                 31.0
3  4    D 2020-01-01 2020-01-30                  0.0                 30.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
4  5    E 2019-03-01 2019-12-31                 31.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
5  6    F 2019-03-01 2019-12-31                 31.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0                  0.0
6  7    G 2019-10-01 2020-08-31                 31.0                 31.0                 29.0                 31.0                 30.0                 31.0                 30.0                 31.0                 31.0
7  8    H        NaT        NaT                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN                  NaN

关于python - python如何查找从2019年12月开始的每个月的天数,并在两个日期列之间转发,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/59274898/

10-16 00:57