Error when using If parole

Question

I am trying to analyze the data from the following table. This is a Trades record just to make it clear.

DATAFRAME:

def lista_preco():
    
    
    #ainda falta resolver os trades depois de 19h
    
    lista_dias = df['DATE'].unique().tolist()
    lista = df['pair'].unique().tolist()
    lista_buy = []
    lista_sell = []
    
    
    for dias in lista_dias:
        for tickers in lista:
            if (df['pair']== tickers and df['type'] == 'sell'):

                mask1 = (df['pair']== tickers and df['type'] == 'sell')

                df2 = df[mask1]
                df2['price_vol'] = df2['price'] * df2['vol']
                preco = df2['price_vol'].sum(axis = 0) / df2['vol'].sum(axis = 0)
                lista_sell.append([dias,tickers,preco,"LONG"])

            if (df['pair']== tickers and df['type'] == 'buy'):

                mask2 = df['pair']== tickers and df['type'] == 'buy'

                df2 = df[mask2]
                df2['price_vol'] = df2['price'] * df2['vol']
                preco = df2['price_vol'].sum(axis = 0) / df2['vol'].sum(axis = 0)
                lista_buy.append([dias, tickers, preco, "SHORT"])

    return lista_buy, lista_sell

ERROR:

ValueError                                Traceback (most recent call last)
<ipython-input-16-d301c71c9e88> in <module>
----> 1 lista_preco()

<ipython-input-15-8ac3ff545e1d> in lista_preco()
     12     for dias in lista_dias:
     13         for tickers in lista:
---> 14             if (df['pair']== tickers and df['type'] == 'sell'):
     15 
     16                 mask1 = (df['pair']== tickers and df['type'] == 'sell')

~\anaconda3\lib\site-packages\pandas\core\generic.py in __nonzero__(self)
   1477     def __nonzero__(self):
   1478         raise ValueError(
-> 1479             f"The truth value of a {type(self).__name__} is ambiguous. "
   1480             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."
   1481         )

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

Answer 1

I think you’re complicating more than is really necessary.

Having:

import pandas as pd
from io import StringIO

csv = StringIO("""DATE;time;pair;type;ordertype;price;cost;fee;vol;margin;misc
30/12/2020;15:11:05;ABEV3;buy;market;10;1000;12;300;0;0
30/12/2020;15:11:05;ABEV3;sell;market;15;1000;12;300;0;0
30/12/2020;15:10:08;MRV3;buy;limit;20;15500;9;200;0;0
31/12/2020;15:09:11;PETR4;sell;limit;30;4516;8;100;0;0
30/12/2020;13:46:53;ABEV3;buy;market;40;45415;10;500;0;0
31/12/2020;13:20:07;TAEE11;buy;limit;10;5465;13;600;0;0
31/12/2020;13:20:07;TAEE11;sell;limit;10;5465;13;600;0;0
""")

df = pd.read_csv(csv, sep=";")

Add the column total_price:

df['total_price'] = df['price'] * df['vol']

df:

         DATE      time    pair  type ordertype  price   cost  fee  vol  margin  misc  total_price
0  30/12/2020  15:11:05   ABEV3   buy    market     10   1000   12  300       0     0         3000
1  30/12/2020  15:11:05   ABEV3  sell    market     15   1000   12  300       0     0         4500
2  30/12/2020  15:10:08    MRV3   buy     limit     20  15500    9  200       0     0         4000
3  31/12/2020  15:09:11   PETR4  sell     limit     30   4516    8  100       0     0         3000
4  30/12/2020  13:46:53   ABEV3   buy    market     40  45415   10  500       0     0        20000
5  31/12/2020  13:20:07  TAEE11   buy     limit     10   5465   13  600       0     0         6000
6  31/12/2020  13:20:07  TAEE11  sell     limit     10   5465   13  600       0     0         6000

We can start by grouping and calculate the average price per asset per type and day, assuming that 'active' is the pair, and make a sum() of total_price and vol and average.

df_tmp = df.groupby(['pair','type', 'DATE'], as_index=False).agg({'total_price': 'sum', 'vol': 'sum'})
df_tmp['mean_price'] = df_tmp['total_price'] / df_tmp['vol']
df_tmp.drop(['vol', 'total_price'], axis=1, inplace=True) # drop das colunas visto que ja nao precisamos

df_tmp:

     pair  type        DATE  mean_price
0   ABEV3   buy  30/12/2020       28.75
1   ABEV3  sell  30/12/2020       15.00
2    MRV3   buy  30/12/2020       20.00
3   PETR4  sell  31/12/2020       30.00
4  TAEE11   buy  31/12/2020       10.00
5  TAEE11  sell  31/12/2020       10.00

As for the second theme commented we can change the values of the type column, since we will not use them for this problem, with "SHORT" or "LONG" depending on the requested condition:

df_tmp['type'] = df_tmp['type'].apply(lambda t: "SHORT" if t == 'sell' else 'LONG')

df_tmp:

     pair   type        DATE  mean_price
0   ABEV3   LONG  30/12/2020       28.75
1   ABEV3  SHORT  30/12/2020       15.00
2    MRV3   LONG  30/12/2020       20.00
3   PETR4  SHORT  31/12/2020       30.00
4  TAEE11   LONG  31/12/2020       10.00
5  TAEE11  SHORT  31/12/2020       10.00

And finally, convert them to the requested list:

lista = df_tmp.values.tolist()

list:

[['ABEV3', 'LONG', '30/12/2020', 28.75], ['ABEV3', 'SHORT', '30/12/2020', 15.0], ['MRV3', 'LONG', '30/12/2020', 20.0], ['PETR4', 'SHORT', '31/12/2020', 30.0], ['TAEE11', 'LONG', '31/12/2020', 10.0], ['TAEE11', 'SHORT', '31/12/2020', 10.0]]