1

Is there any method in python pandas to slice price range a DataFrame, not only time range, like groupby('D')? I need price range from this DataFrame, when price range is 5, and I want to other columns start/end/min/max of prices.

Time,Price,Volume
2022-04-10 00:01:00,100,5
2022-04-10 00:02:00,102,5
2022-04-10 00:03:00,104,5
2022-04-10 00:04:00,102,5
2022-04-10 00:05:00,105,5
2022-04-10 00:06:00,105,5
2022-04-10 00:07:00,106,5
2022-04-10 00:08:00,104,5
2022-04-10 00:09:00,103,5
2022-04-10 00:10:00,106,5
2022-04-10 00:11:00,108,5
2022-04-10 00:12:00,107,5
2022-04-10 00:13:00,105,5
2022-04-10 00:14:00,108,5
2022-04-10 00:15:00,104,5
2022-04-10 00:16:00,103,5

I'd like to get this:

Time,Start_price,End_price,Min_price,Max_price,Sum_volume
2022-04-10 00:01:00,100,105,100,105,25
2022-04-10 00:06:00,105,108,103,108,30
2022-04-10 00:12:00,107,103,103,108,25
bandito
  • 23
  • 4
  • Can you explain more `I need price range from this DataFrame, when price range is 5` ? – jezrael Apr 26 '22 at 05:20
  • In dataframe above, price column start from 100. The range is 5, so I'd like to get 100-105 range. The first five rows is the range as you see the second dataframe with min/max/ and start the first element and end of last element. ---» 2022-04-10 00:01:00,100,105,100,105,25 – bandito Apr 26 '22 at 05:27
  • So need aggregate `100-105`, then `106-110`, `111-115` ? Or need aggregate by first 5 rows, then next 5, next 5 to end of DataFrame? – jezrael Apr 26 '22 at 05:29
  • Also is possible explain how start and end second groups, third group? Because in data in question seems first group has 5 rows, second 6 rows and third 5 rows. – jezrael Apr 26 '22 at 05:32
  • Need aggregate 100-105, and second range is 105-108, because the lowest price is 103 then 103-108 is a 5 range, and last is 107-103, because the range is 108-103=5 – bandito Apr 26 '22 at 05:52
  • Unfortuantely not understand. It means each 5 rows is dynamically count? in second group `106-110` isl lowest `103`, so second group is changed from `106-110` to `103-108`? – jezrael Apr 26 '22 at 05:58
  • No, I want to get ranges from price column based on range (5) amplitude(min«-»max move). Your **output** is perfect! But not slice on time, slice on price range – bandito Apr 26 '22 at 06:02

1 Answers1

0

Try this for making groupby col:

df['Price'] = df['Price'].astype(int)
r = df['Price'].iloc[0]
out = []
g = 0
for price in df['Price']:
    if abs(price-r)>=5:
        out.append(g)
        g+=1
        r = price
    else:
        if price < r:
            r = price
        out.append(g)

Output:

[0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2]

The code iterates on the Price column and creates groups based on the absolute difference of the observed max and mins.

keramat
  • 4,328
  • 6
  • 25
  • 38