Add and subtract according to a criterion in another column

Question

I have a f0519_grouped dataframe like the one in the image. " Unique Identification" is the code that identifies the employee of the others; "Code Item" identifies the payment; I also have the name of the employee and "Value" identifies the value, which can add or discount. Whenever the Heading Code is 352, there is a discount. If it is any other value, it must be added up. In the example of the figure, I need to add the codes that are not 352, ie 1058, 1080 or 1387 and subtract when the code of the heading is equal to 352. And the result I must compare is greater than 39000. In this case, I must make 3546222 + 1564.47 + 11820.84 - 9554.11 = 39293.32, which is the result I hope to obtain. And then compare if it is greater than 39000. I’m using Pandas and the code below, but I don’t know why it doesn’t add up or discount according to the rubrics. Follow the code. Thank you.

f0519_grouped['Valor']=(f0519_grouped['Valor'] - (f0519_grouped['Valor'].where(f0519_grouped['Código Rubrica']==352)).fillna(0))
f0519_grouped[f0519_grouped["Valor"]>39000] 

Answer 1

If I understand you, you can do it by adding up the whole column Valor and subtract by twice the sum where Código Rubrica is equal to 352.

sub352 = f0519_grouped.loc[f0519_grouped['Código Rubrica'] == 352, 'Valor'].sum()

f0519_grouped['Valor'].sum() - (sub352 * 2) > 39000
#saida:
True

Another way would be to create a Bolean selection array and add the column Valor:

mask = df['Código Rubrica'] == 352
f0519_grouped.loc[~mask, 'Valor'].sum() - f0519_grouped.loc[mask, 'Valor'].sum() > 39000
#saida:
True

Edit

How will it be necessary to check for each Unique Identification in DF, I would convert the number in the column to negative Valor where the item is equal to 352, thereafter with groupby would repeat the sum of the values.

df2 = df.copy()

mask = df2['Código Rubrica'] == 352
df2.loc[mask, 'Valor'] = df2.loc[mask, 'Valor'].abs().mul(-1)

df2 = df2.groupby('Identificação Única')['Valor'].sum().reset_index()
df2['Maior que 39000'] = df2['Valor'] > 39000