0

I feel really stupid now, this should be easy.

I got good help here how-to-keep-the-index-of-my-pandas-dataframe-after-normalazation-json

I need to get the min/max value in the column 'price' only where the value in the column 'type' is buy/sell. Ultimately I want to get back the 'id' also for that specific order.

So first of I need the price value and second I need to get back the value of 'id' corresponding.

You can find the dataframe that I'm working with in the link.

What I can do is find the min/max value of the whole column 'price' like so :

x = df['price'].max() # = max price

and I can sort out all the "buy" type like so:

d = df[['type', 'price']].value_counts(ascending=True).loc['buy']

but I still can't do both at the same time.

duhh
  • 53
  • 1
  • 5

3 Answers3

0

you have to use the .loc method in the dataframe in order to filter the type.

import pandas as pd

data = {"type":["buy","other","sell","buy"], "price":[15,222,11,25]}
df = pd.DataFrame(data)


buy_and_sell = df.loc[df['type'].isin(["sell","buy"])]
min_value = buy_and_sell['price'].min()
max_value = buy_and_sell['price'].max()

min_rows = buy_and_sell.loc[buy_and_sell['price']==min_value]
max_rows = buy_and_sell.loc[buy_and_sell['price']==max_value]

min_rows and max_rows can contain multiple rows because is posible that the same min price is repeated.

To extract the index just use .index.

fabran99
  • 1
  • 2
  • I don't really understand the code you show here. i used this hbid = df.loc[df.type == 'buy'][['price', 'txid']] but with [0] it gives me the lowest price for buy order but with [1] on the end it gives me the lowest value of txid instead of the txid of the lowest price buy order. – duhh Nov 18 '20 at 20:28
  • The index you are looking for is another column? in that case you could just simply filter the dataframe by the type column as i do here buy_and_sell = df.loc[df['type'].isin(["sell","buy"])] then sort the result by the price and get the first and last values with .iloc[[0, -1]] – fabran99 Nov 18 '20 at 22:13
  • I' m still not sure how your line isin works. buy_and_sell not specified ;) I now first found the highest buy, then found the txid for that price, then i had to remove the index and then I had to remove a whitespace before my string. no idea how it came there def get_highest_sell_txid(): hs = df.loc[df.type == 'sell', :].max()['price'] hsid = df.loc[df.price == hs, :] xd = hsid['txid'] return xd.to_string(index=False) – duhh Nov 19 '20 at 17:54
  • isin is a function that takes a list as a parameter, it filters the dataframe by only leaving the rows where type is equal to sell or equal to buy. You said that you need to get only the rows with the value buy or sell. – fabran99 Nov 20 '20 at 02:00
0
hbid = df.loc[df.type == 'buy'].min()[['price', 'txid']]

gives me the lowest value of price and the lowest value of txid and not the id that belongs to the order with lowest price . . any help or tips would be greatly appreciated !

0 OMG4EA-Z2WUP-AQJ2XU None ... buy 0.00200000 XBTEUR @ limit 14600.0
1 OBTJMX-WTQSU-DNEOES None ... buy 0.00100000 XBTEUR @ limit 14700.0
2 OAULXQ-3B5WJ-LMLSUC None ... buy 0.00100000 XBTEUR @ limit 14800.0

[3 rows x 23 columns] highest buy order = 14800.0 here the id and price . . txid =

price 14600.0 txid OAULXQ-3B5WJ-LMLSUC

duhh
  • 53
  • 1
  • 5
0

I' m still not sure how your line isin works. buy_and_sell not specified ;)

How I did it --> I now first found the highest buy, then found the 'txid' for that price, then I had to remove the index from the returned series. And finally I had to remove a whitespace before my string. no idea how it came there

def get_highest_sell_txid():
hs = df.loc[df.type == 'sell', :].max()['price'] 
hsid = df.loc[df.price == hs, :] 
xd = hsid['txid'] 
return xd.to_string(index=False)




 xd = get_highest_sell_txid()
    sd = xd.strip()
    cancel_order = 'python -m krakenapi CancelOrder txid=' + sd  #
    subprocess.run(cancel_order)
duhh
  • 53
  • 1
  • 5