Is it possible to calculate the max/min value of 2 or more colums if the value in another column is equal to a specific value in Python pandas?

Question

I'm importing this csv file with pandas:

Date     Type   Price1  Price2  Price3  LowestBuy   HighestSell
01-02-19 BUY    0.1201  0.1202  0.1205      
01-02-19 SELL   0.1198  0.1199  0.1202

Now I want to add the minimum of the colums Price1, Price2, Price3 to the LowestBuy column if the type (column 2) of that row is equal to BUY. When the type is SELL I want to add the maximum of the colums Price1, Price2, Price3 to the HighestSell column. Is this possible?

This is my code so far:

import pandas as pd

path = "orderbookData/NEOBTC2019-02-02.csv"

df = pd.read_csv(path, na_values=0)

for row in df:
    if(df["type"] == "BUY"):
        df["lowestBuy"] = df[["Price1", "Price2", "Price3"]].min(axis=1)
    if(df["type"] == "SELL"):
        df["highestSell"] = df[["Price1", "Price2", "Price3"]].max(axis=1)

print(df)

When I run this code I get this error message:

Exception has occurred: ValueError
The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().
  File "D:\test.py", line 10, in <module>
    if(df["type"] == "BUY"):

This has been covered extensively somewhere else [link](https://stackoverflow.com/questions/36921951/truth-value-of-a-series-is-ambiguous-use-a-empty-a-bool-a-item-a-any-o) — Jacopo Repossi, Feb 03 '19 at 19:18

score 0 · Accepted Answer · answered Feb 03 '19 at 19:20

0

Use DataFrame.loc

df.loc[df['Type'] == 'BUY', 'LowestBuy'] = df[['Price1','Price2','Price3']].min(1)
df.loc[df['Type'] == 'SELL', 'HighestSell'] = df[['Price1','Price2','Price3']].max(1)

    Date        Type    Price1  Price2  Price3  LowestBuy   HighestSell
0   01-02-19    BUY     0.1201  0.1202  0.1205  0.1201      NaN
1   01-02-19    SELL    0.1198  0.1199  0.1202  NaN         0.1202

answered Feb 03 '19 at 19:20

Vaishali

37,545
5
58
86

Thanks! If I now want to divide LowestBuy by HighestSell and add this to a new column, how do I do that? – Kobe Janssens Feb 03 '19 at 19:36
Wouldn't the value be True only in LowestBuy or HighestSell for each row? – Vaishali Feb 03 '19 at 19:37
Yes, want to divide in each pair of BUY an SELL LowestBuy by HighestSell, and append it every time to the SELL row. – Kobe Janssens Feb 03 '19 at 19:45
1

If your data is in the same order as above, simple division would suffice. df['LowestBuy'].shift(1)/df['HighestSell'] – Vaishali Feb 03 '19 at 20:01

Is it possible to calculate the max/min value of 2 or more colums if the value in another column is equal to a specific value in Python pandas?

1 Answers1