Bottom Page

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
Quote
#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]
Quote
#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.
Quote

Top Page

Possibly Related Threads...
Thread Author Replies Views Last Post
  Subtract rows (like r[1]-r[2] and r[3]-r[3]) and no pandas pradeepkumarbe 1 344 Dec-18-2018, 01:16 PM
Last Post: ichabod801
  Pandas Dataframe conditional actions tom1983 1 396 Dec-06-2018, 11:47 PM
Last Post: MAZambelli4353
  ' List[ Conditional statement ] ' What is this code supposed to do. Arindam 1 349 Nov-30-2018, 04:51 PM
Last Post: ichabod801
  Pandas segmenting groupby average brocq_18 0 458 Jul-11-2018, 10:54 AM
Last Post: brocq_18
  Conditional If Statement: If value contains string then set another column equal to s Jack_Sparrow 2 875 Jun-15-2018, 03:33 PM
Last Post: snippsat
  groupby question smw10c 2 1,186 Mar-29-2017, 11:17 PM
Last Post: smw10c
  pandas/index.pyx in pandas.index.IndexEngine.get_loc - KeyError jacobs.smith 2 3,327 Nov-17-2016, 04:07 PM
Last Post: nilamo

Forum Jump:


Users browsing this thread: 1 Guest(s)