Extract the sum in JSON of a given date range?

Question

Given a JSON that looks like this:

{
    "timesheets": [
        {
            "user": {
                "username": "erik",
                "first_name": "Erik",
            },
            "project_id": 4,
            "calc_full_week": {
                "2020-06-22": 5,
                "2020-06-23": 10,
                "2020-06-24": 8,
                "2020-06-25": 13,
                "2020-06-26": null,
                "2020-06-27": null,
                "2020-06-28": null
            }
        },
        {
            "user": {
                "username": "erik",
                "first_name": "Erik",
            },
            "project_id": 4,
            "calc_full_week": {
                "2020-06-29": 15,
                "2020-06-30": 10,
                "2020-07-01": null,
                "2020-07-02": null,
                "2020-07-03": null,
                "2020-07-04": null,
                "2020-07-05": null
            }
        },
        {
            "user": {
                "username": "rawwe",
                "first_name": "joey",
            },
            "project_id": 4,
            "calc_full_week": {
                "2020-06-22": 3,
                "2020-06-23": 10.4,
                "2020-06-24": 8,
                "2020-06-25": 8,
                "2020-06-26": 8,
                "2020-06-27": 8,
                "2020-06-28": 5
            }
        }
    ]
}

How can I efficiently extract the sum of the values in calc_full_week for a given range? E.g, if I provide the range 2020-06-25 - 2020-07-03 I want to to get the sum of all values that fit that range. (13+15+10+8+8+8+5 for the JSON provided)

Not sure if I should do the calculations on the backend (django) or with javascript on the client side.

My model in django looks like this:


class Timesheet(Model):
    year = PositiveIntegerField(validators=[MaxValueValidator(2500), MinValueValidator(1900)])
    week = PositiveIntegerField()
    project = ForeignKey("projects.Project", on_delete=CASCADE)
    user = ForeignKey("accounts.User", on_delete=CASCADE)
    day_1 = DecimalField("monday", blank=True, null=True, max_digits=4, decimal_places=1)
    day_2 = DecimalField("tuesday", blank=True, null=True, max_digits=4, decimal_places=1)
    day_3 = DecimalField("wednesday", blank=True, null=True, max_digits=4, decimal_places=1)
    day_4 = DecimalField("thursday", blank=True, null=True, max_digits=4, decimal_places=1)
    day_5 = DecimalField("friday", blank=True, null=True, max_digits=4, decimal_places=1)
    day_6 = DecimalField("saturday", blank=True, null=True, max_digits=4, decimal_places=1)
    day_7 = DecimalField("sunday", blank=True, null=True, max_digits=4, decimal_places=1)

I'm serializing this and adding a calc_full_week = serializers.SerializerMethodField() value, where the SerializedMethodField looks like this:

    def get_calc_full_week(self, obj):
        current_week = obj.week
        current_year = obj.year
        # since week 1 is actually week 0
        week = current_week - 1
        # list with each date of a given week
        startdate = time.asctime(time.strptime('%d %d 1' % (current_year, week), '%Y %W %w'))
        startdate = datetime.datetime.strptime(startdate, '%a %b %d %H:%M:%S %Y')

        date_dict = {}
        days_in_week = [obj.day_1, obj.day_2, obj.day_3, obj.day_4, obj.day_5, obj.day_6, obj.day_7]
        for i in range(0, 7):
            day = startdate + datetime.timedelta(days=i)
            date_dict[day.strftime('%Y-%m-%d')] = days_in_week[i]

        return date_dict

if its for validating something or used for check points, then do it on the backend. otherwise, client side is good — Karl L, Jun 28 '20 at 08:05

score 0 · Answer 1 · answered Jun 28 '20 at 08:26

const obj = {
    "timesheets": [
        {
            "user": {
                "username": "erik",
                "first_name": "Erik",
            },
            "project_id": 4,
            "calc_full_week": {
                "2020-06-22": 5,
                "2020-06-23": 10,
                "2020-06-24": 8,
                "2020-06-25": 13,
                "2020-06-26": null,
                "2020-06-27": null,
                "2020-06-28": null
            }
        },
        {
            "user": {
                "username": "erik",
                "first_name": "Erik",
            },
            "project_id": 4,
            "calc_full_week": {
                "2020-06-29": 15,
                "2020-06-30": 10,
                "2020-07-01": null,
                "2020-07-02": null,
                "2020-07-03": null,
                "2020-07-04": null,
                "2020-07-05": null
            }
        },
        {
            "user": {
                "username": "rawwe",
                "first_name": "joey",
            },
            "project_id": 4,
            "calc_full_week": {
                "2020-06-22": 3,
                "2020-06-23": 10.4,
                "2020-06-24": 8,
                "2020-06-25": 8,
                "2020-06-26": 8,
                "2020-06-27": 8,
                "2020-06-28": 5
            }
        }
    ]
}

const sumOfValues = (firstDiaposon, secondDiaposon) => {
  const diaposon1 = +firstDiaposon.split('-').join('')
  const diaposon2 = +secondDiaposon.split('-').join('')
  let totalSum = 0
  obj.timesheets.reduce((acc, val) => {
    Object.keys(val.calc_full_week).map((date, index) => {
      const dateSplit = +date.split('-').join('')
      if (dateSplit >= diaposon1 && dateSplit <= diaposon2) {
        totalSum += val.calc_full_week[date]
      }
    })
  }, 0)
  return totalSum
}

console.log(sumOfValues('2020-06-25', '2020-07-03'))

score 0 · Answer 2 · answered Jun 28 '20 at 09:46

0

One of options if you want to do this in python:

First, you will need a list of allowed dates, like

my_date_range = ['2020-06-25', '2020-06-26', ]

Which better to be generated by separate method (given start and end date), i.e. from this SO answer.

To easier extract required sub-nodes from nested dictionary / json you can use dpath:

import json
import dpath

data = json.loads(json_string)  # parse json string to dict

# filter dict for all date values
# 2020-* - some initial key name filter, may just get all with *
# yielded=True - returns generator (for one time use) for tuples, not a dict
result = dpath.util.search(data, "timesheets/*/calc_full_week/2020-*", yielded=True)

# Now sum only required dates
# also, need to filter out possible None values
my_sum = sum(filter(None, [v for k, v in result if k in my_date_range]))

answered Jun 28 '20 at 09:46

Oleg Russkin

4,234
1
8
20

Yes, with drf `request.data` will be already a dictionary, and after serialization `serializer.data` may be an object or model instance - which means this may be different data type with different structure. On saved models - you can run a `QuerySet` to calculate values on objects in the database. – Oleg Russkin Jun 28 '20 at 11:26
`my_sum = sum(filter(None, [v for k, v in result if k in my_date_range]))` just returns 0 - does it have to do with the fact that the model instance has decimals and not integers? Edit: i just tried converting the model instance values (`Decimal 8` etc) to a string value - thats why - not sure how to solve it with model instance values though – erikvm Jun 28 '20 at 11:29
What code are you running? What is type / structure of `data`? Provided code is more dict filtering / calculations, with models one may want other approach. – Oleg Russkin Jun 28 '20 at 11:34
I updated my post with some more information about the model itself - and as an FYI - I'm using `day_1` to `day_7` instead of DateTimeField in my model because it made everything else so much easier for my specific project - I just happen to have to convert those to datefields so I can do calculations and this specific task got a little tricky. The `calc_full_week` you see in the JSON comes from that `calc_full_week = serializers.SerializerMethodField()` – erikvm Jun 28 '20 at 11:36

Extract the sum in JSON of a given date range?

2 Answers2