0

I am summing a column of data using pandas that includes positive and negative values.

I first clean the data by removing the $ sign and parenthesis. Then format as a float.

How can I sum the whole column and subtract by the negative numbers?

Example:

    $1000
    ($200)
    $300
    $1250
    ($100)

I want the answer to be 2250 not 2550.

Thanks in advance!

Zach Cook
  • 63
  • 1
  • 8

3 Answers3

1

You want to identify the values and the signs:

# positive and negative
signs = np.where(s.str.startswith('('), -1, 1)

# extract the values
vals = s.str.extract('\$([\d\.]*)')[0].astype(int)

# calculate the sum
vals.mul(signs).sum()

# 2250
Quang Hoang
  • 146,074
  • 10
  • 56
  • 74
0

A Pandas DataFrame object has the .sum method that takes axis as a parameter

my_dataframe['name_of_column_you_want'].sum(axis = 0) # axis=0 means down (the rows)

I don't understand your example.

FilteredFrames
  • 182
  • 2
  • 8
0
import re
def clean(column_name) :
    if column_name.find('(') > 0 :
       return float(re.match(r'(\d+)').group(0)) 
    else :
       return -float(re.match(r'(\d+)').group(0)) 
my_dataframe['column_name'].apply(clean).sum()
Tserenjamts
  • 579
  • 6
  • 15
  • Although the code might solve the problem, a good answer should also explain **what** the code does and **how** it solves the question. – BDL Oct 30 '19 at 09:48