In a Dataframe, modify data from one column conditioned to the value of another column

Asked

Viewed 77 times

2

I am working on an automation with reports for the results of cryptocurrency/stock buying and selling operations.

I need to convert the positive value of a cell to negative if it is bought (BUY). And when it’s a sale(SELL) transaction I don’t need to change anything. It’s already positive and will remain so.

Below follows the example of the original Dataframe:

Exemplo de um DataFrame que precisa ter na coluna Amount valores negativos quando a operação na coluna Side for de BUY

The final result for the Amount column between rows 4 and 9 should be a negative value. Therefore, I have to condition the change of the Amount column to the presence of the BUY value in the Side column.

I tried to use the Pandas . Loc function. But I stopped in the conversion operation from the original value to the negative value.

df.loc[df.Side=='BUY','Amount']="aqui preciso inserir a fórmula para multiplicar por -1"

I ran a test by placing a fixed variable and it works. So I only need the right conversion command or use another function.

Ex:

df.loc[df.Side=='BUY','Amount']='negativo'

Exemplo que a alteração funciona. Só falta o comando adequado.

python pandas conversion

  • If one of the answers below solved your problem and there was no doubt left, choose the one you liked the most and mark it as correct/accepted by clicking on the " " that is next to it, which also marks your question as solved. If you still have any questions or would like further clarification, feel free to comment.

2 answers

3

In addition to the @Augusto Vasques suggestion, you can use Oc as you previously tried:

df.loc[df['Side'] == 'BUY', 'Amount'] = -df['Amount']

Loc + isin

df.loc[df['Side'].isin(['BUY']), 'Amount'] = -df['Amount']
  • 1

    Your two suggestions worked. Even the first one is the simplest within the line of reasoning I was building. Very obvious and I missed just knowing how to write. Thank you.

  • @Heduanpinheiro, for nothing! Great hug!

2

To change values in a column where it depends on values in other columns, use the method Series.mask() replacing values where given conditions are true.

Example:
In the column Amount, df["Amount"], where the condition, df["Side"]=="BUY", is real replaces in loco, inplace=True, the value by its symmetric additive, -df["Amount"].

import pandas as pd

df = pd.DataFrame({
    "Pair":["BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "BAKEBUSD", "CAKEUSDT"],
    "Side":["SELL", "SELL", "SELL", "SELL", "BUY", "BUY", "BUY", "BUY", "BUY", "BUY", "SELL"],
    "Amount":[1.0, 2.0, 3.0, 4.0, 5.0, 6.0, 7.0, 8.0, 9.0, 10.0, 11.0]
})

print("DF Original", df, sep="\n")

df["Amount"].mask(df["Side"]=="BUY", -df["Amount"], inplace=True)

print("DF Modificado", df, sep="\n")

Upshot:

DF Original
        Pair  Side  Amount
0   BAKEBUSD  SELL     1.0
1   BAKEBUSD  SELL     2.0
2   BAKEBUSD  SELL     3.0
3   BAKEBUSD  SELL     4.0
4   BAKEBUSD   BUY     5.0
5   BAKEBUSD   BUY     6.0
6   BAKEBUSD   BUY     7.0
7   BAKEBUSD   BUY     8.0
8   BAKEBUSD   BUY     9.0
9   BAKEBUSD   BUY    10.0
10  CAKEUSDT  SELL    11.0
DF Modificado
        Pair  Side  Amount
0   BAKEBUSD  SELL     1.0
1   BAKEBUSD  SELL     2.0
2   BAKEBUSD  SELL     3.0
3   BAKEBUSD  SELL     4.0
4   BAKEBUSD   BUY    -5.0
5   BAKEBUSD   BUY    -6.0
6   BAKEBUSD   BUY    -7.0
7   BAKEBUSD   BUY    -8.0
8   BAKEBUSD   BUY    -9.0
9   BAKEBUSD   BUY   -10.0
10  CAKEUSDT  SELL    11.0

Test the example on Repl.it

  • Your suggestion worked perfectly. Thank you!

Browser other questions tagged python pandas conversion

You are not signed in. Login or sign up in order to post.