Django ORM fill 0 for missing date

user_3719167 0
Viewed 247

I'm using Django 2.2.

I want to generate the analytics of the number of records by each day between the stand and end date. The query used is

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')}
)

Which produces output as

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

I want to fill the missing dates with 0, so that the output should be

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

How I can achieve this using either ORM or python (ie., pandas, etc.)?

pandas django-orm django
1 Answers

Use DataFrame.reindex by date range created by date_range with DatetimeIndex by 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

Or use left join by another DataFrame create from range with replace misisng values to 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)
user_29010020
Related