Home > OS >  Calculate dataframe profit between pairs of string events
Calculate dataframe profit between pairs of string events

Time:11-12

I have

   event   b
0  buy     4
1  nan
2  sell    5
3  buy     3
4  nan
5  nan
6  nan
7  sell    9

After each buy we have a sell at some unknown distance. I need to count how many times I had a profit.

In this case, first deal earn 1 (5-4), and second deal earn 6 (9-3).

I need to produce here 2 results total wins=2, and total lost=0

So I don't care how big is the profit/lost, only how many wins/loses

CodePudding user response:

If you drop all the nans, pivot the table, then shift the sells up so they align with the buys, you will have rows of buy/sell and can then compare.

df = df.dropna()
df_pivoted = df.pivot(columns='event',values='b')
df_pivoted
event  buy  sell
0      4.0   NaN
2      NaN   5.0
3      3.0   NaN
7      NaN   9.0

df_pivoted['sell'] = df_pivoted['sell'].shift(-1) # shift sell values up a row
df_pivoted
event  buy  sell
0      4.0   5.0
2      NaN   NaN
3      3.0   9.0
7      NaN   NaN

df_pivoted = df_pivoted.dropna()

df_pivoted['win?'] = df_pivoted['buy'].lt(df_pivoted['sell'])
df_pivoted
event  buy  sell   win?
0      4.0   5.0  True
3      3.0   9.0  True

total_wins = len(df_pivoted[df_pivoted['win?'] == True])
total_losses = len(df_pivoted[df_pivoted['win?'] == False])

CodePudding user response:

You can try this. Remove un wanted columns & subract corresponding elemnts in b ..count total postive and negative values.

filter = df["b"] != ""
df = df[filter]
df['diff']=df['b'] -df['b'].shift(1)
dfs= df.iloc[1::2] 


profit = len(dfs.loc[dfs['diff'] > 0])
loss =len(dfs.loc[dfs['diff'] <0])
print(f"total wins = {profit}")
print(f"total loss = {loss}")

output

total wins = 2
total loss = 0
  • Related