Django ORM填写失踪日期0

Question

我在用Django 2.2。

我想要生成的分析记录的数量，每一天之间的立场和结束日期。所使用的查询是

start_date = '2021-9-1'
end_date = '2021-9-30'

query = Tracking.objects.filter(
  scan_time__date__gte=start_date,
  scan_time__date__lte=end_date
)

query.annotate(
  scanned_date=TruncDate('scan_time')
).order_by(
  'scanned_date'
).values('scanned_date').annotate(
  **{'total': Count('created')}
)

它产生的输出

[{'scanned_date': datetime.date(2021, 9, 24), 'total': 5}, {'scanned_date': datetime.date(2021, 9, 26), 'total': 3}]

我想用填充缺少的日期，这样输出应该是

2021-9-1: 0
2021-9-2: 0
...
2021-9-24: 5
2021-9-25: 0
2021-9-26: 3
...
2021-9-30: 0

如何使用ORM或python (如熊猫等)实现这一目标？

Answer 1

用DataFrame.reindex按日期范围使用date_range创建的日期范围与DatetimeIndex由DataFrame.set_index创建的日期范围

data = [{'scanned_date': datetime.date(2021, 9, 24), 'total': 5},
        {'scanned_date': datetime.date(2021, 9, 26), 'total': 3}]

start_date = '2021-9-1'
end_date = '2021-9-30'

r = pd.date_range(start_date, end_date, name='scanned_date')
#if necessary convert to dates from datetimes
#r = pd.date_range(start_date, end_date, name='scanned_date').date
df = pd.DataFrame(data).set_index('scanned_date').reindex(r, fill_value=0).reset_index()

print (df)
   scanned_date  total
0    2021-09-01      0
1    2021-09-02      0
2    2021-09-03      0
3    2021-09-04      0
4    2021-09-05      0
5    2021-09-06      0
6    2021-09-07      0
7    2021-09-08      0
8    2021-09-09      0
9    2021-09-10      0
10   2021-09-11      0
11   2021-09-12      0
12   2021-09-13      0
13   2021-09-14      0
14   2021-09-15      0
15   2021-09-16      0
16   2021-09-17      0
17   2021-09-18      0
18   2021-09-19      0
19   2021-09-20      0
20   2021-09-21      0
21   2021-09-22      0
22   2021-09-23      0
23   2021-09-24      5
24   2021-09-25      0
25   2021-09-26      3
26   2021-09-27      0
27   2021-09-28      0
28   2021-09-29      0
29   2021-09-30      0

或由另一个DataFrame使用左联接，使用替换misisng值从范围创建到0

r = pd.date_range(start_date, end_date, name='scanned_date').date

df = pd.DataFrame({'scanned_date':r}).merge(pd.DataFrame(data), how='left', on='scanned_date').fillna(0)