Convert positive values based on a filter condition in a DataFrame to negative values

Question

user_id           date         type        transaction_part_sum     transaction_part_count
1000616411022604  2011-12-20   debit                51.58                       1
1000616411022604  2013-06-10   debit                25.52                       1
1000616411022604  2013-11-07   credit               533.29                      1
1000616411022604  2013-12-26   debit                3.82                        1
1000616411022604  2013-12-31   credit               259.68                      1
1000616411022604  2014-01-02   debit                79.10                       1
1000616411022604  2014-02-25   debit                9.99                        1
1000616411022604  2014-03-26   debit                3.42                        1
1000616411022604  2014-04-02   debit                71.90                       1

In a pandas DataFrame as shown above, I want to change the debit row of the "transaction_part_sum" to negative value.

I did this

grouped.loc[grouped['type'] == 'debit', 'transaction_part_sum'] = -1 * grouped['transaction_part_sum']

but when printing grouped. The values in the debit row don't get populated. If I multiply with any other positive number I get the values populated. How can I change the debit row to negative value?

output: 
user_id           date         type         transaction_part_sum      transaction_part_count
1000616411022604  2011-12-20   debit                                            1
1000616411022604  2013-06-10   debit                                            1
1000616411022604  2013-11-07   credit               533.29                      1
1000616411022604  2013-12-26   debit                                            1
1000616411022604  2013-12-31   credit               259.68                      1
1000616411022604  2014-01-02   debit                                            1
1000616411022604  2014-02-25   debit                                            1
1000616411022604  2014-03-26   debit                                            1
1000616411022604  2014-04-02   debit                                            1

jezrael · Answer 1 · 2020-09-30T08:02:55.070

1

Your solution for me working, also should be simplify with mutiple by constant by *= statement:

EDIT: There is dtype for column amount object, it means obviously strings, so first is necessary convert to numeric:

dataframe = pd.DataFrame(dataList, columns=['user_id', 'account_id','amount','randString','date','type','string'])
dataframe['amount'] = dataframe['amount'].astype(float)

grouped = dataframe.groupby(['user_id','date','type']).agg({'amount':['sum','count']}).sort_values(by='date')
grouped.loc[grouped['type'] == 'debit', 'transaction_part_sum'] *= -1
print (grouped)
            user_id        date    type  transaction_part_sum  \
0  1000616411022604  2011-12-20   debit                -51.58   
1  1000616411022604  2013-06-10   debit                -25.52   
2  1000616411022604  2013-11-07  credit                533.29   
3  1000616411022604  2013-12-26   debit                 -3.82   
4  1000616411022604  2013-12-31  credit                259.68   
5  1000616411022604  2014-01-02   debit                -79.10   
6  1000616411022604  2014-02-25   debit                 -9.99   
7  1000616411022604  2014-03-26   debit                 -3.42   
8  1000616411022604  2014-04-02   debit                -71.90   

   transaction_part_count  
0                       1  
1                       1  
2                       1  
3                       1  
4                       1  
5                       1  
6                       1  
7                       1  
8                       1

edited Sep 30 '20 at 08:02

answered Sep 30 '20 at 07:15

jezrael

822,522
95
1,334
1,252

This still does not populate the debit row for me. It's empty like the output I have pasted above. @jezrael – pvr Sep 30 '20 at 07:18
Yes, this is how I want my output to look like but unfortunately the output on my system does not populate negative values. – pvr Sep 30 '20 at 07:22
@pvr - What is `print (grouped.info())` ? – jezrael Sep 30 '20 at 07:23
@pvr - also not working if use data from my answer? – jezrael Sep 30 '20 at 07:23
`grouped.info() output:` ` # Column Non-Null Count Dtype --- ------ -------------- ----- 0 user_id 6 non-null object 1 date 6 non-null object 2 type 6 non-null object 3 transaction_part_sum 6 non-null object 4 transaction_part_count 6 non-null int64` @jezrael – pvr Sep 30 '20 at 07:30
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/222292/discussion-between-pvr-and-jezrael). – pvr Sep 30 '20 at 07:34
1

Thanks @jezrael for an excellent solution. It helped me to understand more about the *strange* expression: `math operator (+, -, *, /) =` – Nemo Sep 21 '22 at 14:33

score 0 · Answer 2 · answered Sep 30 '20 at 07:19

0

You can do a simple command to multiply it by -1.

import pandas as pd
df = pd.DataFrame({'trans':[10,20,30,40]})
print (df)
df['trans'] *= -1
print (df)

This will print the results as follows:

You can do the same even for a condition.

df.loc[df['trans'] == 20,'trans'] *= -1
print (df)

This will result in -20 but all others say as is.

answered Sep 30 '20 at 07:19

Joe Ferndz

8,417
2
13
33

2

same like my answer. – jezrael Sep 30 '20 at 07:19
1

just saw it now. Sorry. was too late to post i guess – Joe Ferndz Sep 30 '20 at 07:20

score 0 · Answer 3 · answered Sep 30 '20 at 07:35

0

You can try numpy.where

grouped['transaction_part_sum'] = np.where(grouped.type == 'debit', -1 * grouped.transaction_part_sum, grouped.transaction_part_sum)

answered Sep 30 '20 at 07:35

Rajesh Bhat

980
6
8

Convert positive values based on a filter condition in a DataFrame to negative values

3 Answers3