python – Inquiry. Number of crossings per year


Good day to all. There is a database with columns id, date, value. id – id of a person, can be repeated. date – the year of the entry. value – for example, these are numbers 0 or 3.

With each entry in the database, a value is assigned to a person, which can change (make a transition) later (next year). The value can increase but not decrease.

The essence of the task. Build a table with columns: date, count. Where date is the year, and count is a field that shows the number of transitions from any group to group 3.

In what direction to think? The task is not tied to any DB, you just need the very algorithm of how to do it. If it helps, then I'm working on pandas. I tried the option with the grouping by date and number of records, which have a value less than 3. And the second group of which has a value of 3. After subtracting the value and it turned out some kind of nonsense …


If you mean an algorithm without reference to a database, then I would create a trigger that would change the count value every time a person's value changed.

Scroll to Top