-2

Trying to add a cumulative column to a pandas df.

Have tried this code but get a NaNs:

df['Total_Coins_Bought'] = df.query("side == 'buy'")['amount'].cumsum()
df['Total_Coins_Sold'] = -df.query("side == 'sell'")['amount'].cumsum()

df['Total_Coins'] = df['Total_Coins_Bought']-df['Total_Coins_Sold']

Only looking to build the Total_Coins column; the 2 fields Total_Coins_Bought & Total_Coins_Sold are me just trying to build this step by step

enter image description here

Mayank Porwal
  • 33,470
  • 8
  • 37
  • 58
Technaut
  • 47
  • 1
  • 7
  • 1
    Please share a sample input dataframe based on expected output. – Mayank Porwal Jan 18 '21 at 14:55
  • I suggest reading [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples), then editing your question. – Mr. T Jan 18 '21 at 15:03

1 Answers1

0
df["sign_amount"] = df["amount"] * df["side"].map({"buy": +1, "sell": -1})
df["total_coins"] = df["sign_amount"].cumsum()

Basically, I add a sign_amount column, which is identical to the amount column, but multiplied by +1 if side is "buy", and multiplied by -1 if side is "sell". The cumsum of sign_amount column should be your desired outcome.

Enrico Gandini
  • 855
  • 5
  • 29
  • I agree that the original question was not reproducible, but I think I managed to guess the structure of the DataFrame from the picture. – Enrico Gandini Jan 18 '21 at 15:17