Comparing rows in dataset based on a specific column to find min/max

user_6557968 0
Viewed 134

So I have a dataset that contains history of a specific tag from a start to end date. I am trying to compare rows based on the a date column, if they're similar by month, day and year, I'll add those to a temporary list by the value of the next column and then once I have those items by similar date, I'll take that list and find the min/max values subtract them, then add the result to another list and empty the temp_list to start all over again.

For the sake of time and simplicity, I am just presenting a example of 2D List. Here's my example data

dataset = [[1,5],[1,6],[1,10],[1,23],[2,4],[2,8],[2,12],[3,10],[3,20],[3,40],[4,50],[4,500]]

Where the first column will act as dates and second value.

The issues I am having is :

  1. I cant seem to compare every row based on its first column which would take the value in the second column and include it in the temp list to perform min/max operations?
  2. Based on the above 2D List I would expect to get [18,8,30,450] but the result is [5,4,10]
dataset = [[1,5],[1,6],[1,10],[1,23],[2,4],[2,8],[2,12],[3,10],[3,30],[3,40],[4,2],[4,5]]
temp_list = []
daily_total = []
for i in range(len(dataset)-1):
    if dataset[i][0] == dataset[i+1][0]:
        temp_list.append(dataset[i][1])
    else:
        max_ = max(temp_list)
        min_ = min(temp_list)
        total = max_ - min_
        daily_total.append(total)
        temp_list = []
            
print([x for x in daily_total])
dataset python-3.x list python
2 Answers

Try:

tmp = {}
for d, v in dataset:
    tmp.setdefault(d, []).append(v)

out = [max(v) - min(v) for v in tmp.values()]
print(out)

Prints:

[18, 8, 30, 450]
user_100359850

Here is a solution using pandas:

import pandas as pd

dataset = [
    [1, 5],
    [1, 6],
    [1, 10],
    [1, 23],
    [2, 4],
    [2, 8],
    [2, 12],
    [3, 10],
    [3, 20],
    [3, 40],
    [4, 50],
    [4, 500],
]

df = pd.DataFrame(dataset)
df.columns = ["date", "value"]
df = df.groupby("date").agg(min_value=("value", "min"), max_value=("value", "max"))
df["res"] = df["max_value"] - df["min_value"]
df["res"].to_list()

Output:

[18, 8, 30, 450]
user_107430470
Related