Doing math within a column of a dataframe or numpy array

user_12027845 0
Viewed 461

I have data that I need to do simple math on but it is all in the same column. Here is what my data might look like

enter image description here

I need to do something like (A4+A5)-(A2+A3), then continue the pattern on each row. For example, my formula would be something like (A5+A6)-(A3+A4). I have been thinking about how to do this for a week almost and I cannot figure it out. What is the best way to go about this? I think my best bet would be to transpose the data and then work with it from there?

Thank you

Edit: ok I have come up with something that I think will work but I am not completely sure I am thinking of this right. Here is a code snippet. assuming the said data is in a numpy array starting with index 0, this would do the job right?

for x in range(len(list24)):
try:
    filt = ((list24[x+2]+list24[x+3])-(list24[x]+list24[x+1]))
    fraser.append(filt)
except IndexError:
    pass
pandas slice indexing numpy python
2 Answers

Sounds like convolution to me. Scipy is your best bet for fast convolution. In your case you want to multiply with [-1, -1, 1, 1] That means every element in the list is multiplied and summed with the weights [-1,-1,1,1] That is: -1 * l[x] -1 * l[x+1] +1 * l[x+2] +1 * l[x+3] For every x.

That is what convolution does. It is used in signal processing (finite response filter) and image processing (blurring / sharpening) all the time.

scipy convolution

I tested it. The result is exactly what your code does and a lot faster for large lists. On a list with 10000 elements the scipy version takes 291 µs, whereas your code needs 115 ms. That's a 400 times speed improvement. (For small lists, your code is faster).

with a list: l = [5, 6, 7, 8, 5, 6, 7, 8, 5, 6, 7, 8, 5, 6, 7, 8]

you do:

>>> from scipy.signal import convolve

>>> f = [1, 1, -1, -1]  # the filter needs to be written backwards due to convention so instead of [-1,-1,1,1] -> [1,1,-1,-1]
>>> c = convolve(l, f, mode='valid')

>>> c
array([4,  0,  -4,  0, 4,  0,  -4,  0, 4,  0,  -4,  0, 4])

vs your code:

>>> c = []
>>> for x in range(len(l)):
...    try:
...        filt = ((l[x+2]+l[x+3])-(l[x]+l[x+1]))
...        c.append(filt)
...    except IndexError:
...        pass

>>> c
[4, 0, -4, 0, 4, 0, -4, 0, 4, 0, -4, 0, 4]

Oh. and to get a list back from the array c, you do:

>>> import numpy as np
>>> c = c.tolist()
>>> c

[4, 0, -4, 0, 4, 0, -4, 0, 4, 0, -4, 0, 4]

Update: I did some timing tests on this. I also discovered that there is a numpy implementation. That one is actually faster than scipy, especially for very few samples. Scipy is faster around 40 samples. enter image description here

user_127976040

Ok this worked. I turned the values into a numpy array and did the following

fraser = []
      for x in range(len(list24)):
    try:
        filt = ((list24[x+2]+list24[x+3])-(list24[x]+list24[x+1]))
        fraser.append(filt)
    except IndexError:
        pass

The index error is because eventually it will get to a point where the calculation is out of index

user_120278450
Related