From a list of dictionaries in python Django how to filter/find and highest valued prize per user pe-CodePudding

I want to find out per day per user what is the highest prize he has won

my_list = [{'id': 1, 'user_id': 4, 'date': datetime.datetime(2022, 9, 9), 'prize': '45.00'},
{'id': 2, 'user_id': 5, 'date': datetime.datetime(2022, 9, 5), 'prize': '85.00'},
{'id': 3, 'user_id': 5, 'date': datetime.datetime(2022, 9, 5), 'prize': '35.00'},
{'id': 4, 'user_id': 4, 'date': datetime.datetime(2022, 9, 9), 'prize': '95.00'},
{'id': 5, 'user_id': 3, 'date': datetime.datetime(2022, 9, 9), 'prize': '10.00'},
{'id': 6, 'user_id': 6, 'date': datetime.datetime(2022, 9, 5), 'prize': '15.00'}]

the result should be as below

result = [{'id': 2, 'user_id': 5, 'date': datetime.datetime(2022, 9, 5), 'prize': '85.00'},
{'id': 4, 'user_id': 4, 'date': datetime.datetime(2022, 9, 9), 'prize': '95.00'},
{'id': 5, 'user_id': 3, 'date': datetime.datetime(2022, 9, 9), 'prize': '10.00'},
{'id': 6, 'user_id': 6, 'date': datetime.datetime(2022, 9, 5), 'prize': '15.00'}]

In above Example user_id=4 have 2 prizes on date(2022, 9, 5) i.e $45 and $95. Among these 2 we have to pick the highest valued record $95

CodePudding user response：

Try this query.

from django.db.models.aggregates import Max
Model.objects.all().values('user_id','date').annotate(max_prize=Max('prize'))

CodePudding user response：

You can use groupby from itertools and pass data in each group to max with a lambda function for key parameter. But as this solution needs the data to be sorted, you can first sort the data, then group the data, and finally take the maximum. And you can take id values for these, then finally filter the records for these ids:

from itertools import groupby

[x for x in my_list
if x['id'] in (max(v, key=lambda x: float(x['prize']))['id']
               for k, v in groupby(sorted(my_list,
                                          key=lambda x: (x['user_id'], x['date'])),
                                   key=lambda x: (x['user_id'], x['date'])))]

OUTPUT

[   
    {   'date': datetime.datetime(2022, 9, 5, 0, 0),
        'id': 2,
        'prize': '85.00',
        'user_id': 5},
    {   'date': datetime.datetime(2022, 9, 9, 0, 0),
        'id': 4,
        'prize': '95.00',
        'user_id': 4},
    {   'date': datetime.datetime(2022, 9, 9, 0, 0),
        'id': 5,
        'prize': '10.00',
        'user_id': 3},
    {   'date': datetime.datetime(2022, 9, 5, 0, 0),
        'id': 6,
        'prize': '15.00',
        'user_id': 6}
]