Python Forum
Groupby in pandas with conditional - add and subtract
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Groupby in pandas with conditional - add and subtract
#1
I have a dataframe with 4 columns "Identificação Única", "Nome", "Rubrica" and "Valor" and I would like to groupby the column "Identificação Única" e "Nome", and sum the column Valor, except when Rubrica is 240 or 245. Also, I want to minus the value in column Valor always Rubrica is 352 and add others(that is, different from "240","245" and "352")) and its result will be kept in the new column. And, I want to show the value in this column that is higher than 10000. The code is below: This way, the code add correctly all of rubricas except 240 and 245. But, I can not subtract the rubrica when it is 352. A picture of the dataframe is bellow:https://i.stack.imgur.com/WoWB9.jpg

The code that I have tried is this:

f0219_grouped = f0219[~f0219['Código Rubrica'].isin(['240','245'])].groupby(['Identificação Única', 'Nome'])['Valor'].sum()>10000 - f0219_grouped['Valor'] where f0219_grouped['Rubrica']==352`
And, when I run the code, I got the following error Message: `File "", line 1

f0219_grouped = f0219[~f0219['Código Rubrica'].isin(['240','245'])].groupby(['Identificação Única', 'Nome'])['Valor'].sum()>10000 - f0219_grouped['Valor'] where f0219_grouped['Rubrica']==352
                                                                                                                                                               ^
SyntaxError: invalid syntax`
python pandas
Reply
#2
i guess you missed a column while doing groupby.. Apart from that you can make the below minor adjustments

f0219_grouped = f0219[~f0219['Rubrica'].isin(['240','245'])].groupby(['Identificação Única', 'Nome','Rubrica'])['Valor'].sum().reset_index()
f0219_grouped['Valor']=(f0219_grouped['Valor'] - (f0219_grouped['Valor'].where(f0219_grouped['Rubrica']==352)).fillna(0))
f0219_grouped[f0219_grouped["Valor"]>=10000]
Reply
#3
Hi, thank you, but is not working. It is not adding all the values where Rubrica is not 240 or 245, and I need that all values belong to these two codes(245 and 240) is added.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Assigning conditional values in Pandas Scott 3 725 Dec-19-2023, 03:10 AM
Last Post: Larz60+
  xlswriter(pandas) and conditional format paul18fr 1 1,141 Aug-28-2022, 07:56 AM
Last Post: paul18fr
  Remove extra count columns created by pandas groupby spyf8 1 2,686 Feb-10-2021, 09:19 AM
Last Post: Naheed
  How to subtract columns with dates? jpy 3 2,220 Dec-29-2020, 12:11 AM
Last Post: jpy
  Combine groupby() and shift() in pandas rama27 0 4,153 Nov-17-2020, 09:49 PM
Last Post: rama27
  Pandas: summing columns conditional on the column labels ddd2332 0 2,078 Sep-10-2020, 05:58 PM
Last Post: ddd2332
  Fastest way to subtract elements of datasets of HDF5 file? Robotguy 3 2,591 Aug-01-2020, 11:48 PM
Last Post: scidam
  Pandas + Groupby + Filter unique values JosepMaria 1 2,842 Jun-15-2020, 08:15 AM
Last Post: JosepMaria
  Python pandas merge with or conditional Lafayette 0 2,149 May-07-2020, 07:34 PM
Last Post: Lafayette
  python pandas: diff between 2 dates in a groupby bluedragon 0 3,251 Mar-25-2020, 04:18 PM
Last Post: bluedragon

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020