Iterating / Slicing over a dataframe and performing mathematical calculations

Question

the operation that I intend to perform is whenever there is a '2' in the column 3, we need to take that entry and take the column 1 value of that entry and subtract the column 1 value of the previous entry and then multiply the result by a constant integer (say 5). For example: From the image we have a '2' in column 3 at 6:00 and the value of column 1 for that entry is 0.011333 and take the previous column 1 entry which is 0.008583 and perform the following. (0.011333 - 0.008583)* 5. This I want to perform everytime when we receive a '2' in column 3 in a dataframe. Please help. I am not able to get the write code to perform the above operation.

Please provide code to replicate data, not just the image of them. — FBruzzesi, Dec 10 '19 at 08:12

score 0 · Answer 1 · answered Dec 10 '19 at 08:08

Would something like that do the job ?

dataframe = [
    [1,3,6,6,7],
    [4,3,5,6,7],
    [12,3,2,6,7],
    [2,3,7,6,7],
    [9,3,5,6,7],
    [13,3,2,6,7]
]
constant = 5
list_of_outputs = []

for row in dataframe:
    if row[2] == 2:
        try:
            output = (row[0] - prev_entry) * constant
            list_of_outputs.append(output)
        except:
            print("No previous entry!")
    prev_entry = row[0]

Amri Rasyidi · Answer 2 · 2019-12-10T08:59:11.243

0

Perhaps this question will help you

I think in SQL way, so basically you will make new column that filled with the value from the row above it.

df['column1_lagged'] = df['column 1'].shift(1)

Then you create another column that do the calculation

constant = 5
df['calculation'] = (df['column 1'] - df['column1_lagged'])*constant

After that you just slice the dataframe to your condition (column 3 with '2's)

condition = df['column 3'] == 2
df[condition]

edited Dec 10 '19 at 08:59

answered Dec 10 '19 at 08:22

Amri Rasyidi

172
1
10

Yes, I have applied the same logic in a single line of code as davidbilla mentioned. This logic works perfectly fine for me. Thankyou for your help, really appreciate it. – vaibhav desai Dec 12 '19 at 09:43

score 0 · Accepted Answer · answered Dec 10 '19 at 08:27

Hope this helps: You can use df.shift(1) to get the previous row and np.where to get the row satisfying your condition

df = pd.DataFrame([['ABC', 1, 0, 0],
                   ['DEF', 2, 0, 0],
                   ['GHI', 3, 0, 0],
                   ['JKL', 4, 0, 2],
                   ['MNO', 5, 0, 2],
                   ['PQR', 6, 0, 2],
                   ['STU', 7, 0, 0]],
                  columns=['Date & Time', 'column 1', 'column 2', 'column 3'])

df['new'] = np.where(df['column 3'] == 2, df['column 1'] - df['column 1'].shift(1) * 5, 0)

print(df)

Output:

  Date & Time  column 1  column 2  column 3   new
0         ABC         1         0         0   0.0
1         DEF         2         0         0   0.0
2         GHI         3         0         0   0.0
3         JKL         4         0         2 -11.0
4         MNO         5         0         2 -15.0
5         PQR         6         0         2 -19.0
6         STU         7         0         0   0.0

You can change your calculations as you want. In the else part you can put np.NaN or any other calculation if you want.

This logic works perfectly fine in my context. I wasn't aware of numpy's shift function. Thankyou so much for your help. Really appreciate it. — vaibhav desai, Dec 12 '19 at 09:42
Done. . I'm new to stackoverflow and so am not quite aware of the reward system. Thankyou, once again for the help — vaibhav desai, Dec 13 '19 at 11:06

Iterating / Slicing over a dataframe and performing mathematical calculations

3 Answers3