Find a value in a column in function of another column

user_12966591 0
Viewed 85

Assuming that the value exists, how can I for example, create another column "testFinal" in the dataframe where I will have the absolute value of df["test"]- " df["test"] which is 0.2 seconds after " for example, the first value for testFinal is the absolute value of the difference between 2 and the value 0.2 seconds after -> so 8, the result is abs(2-8) = 6

My goal is to calculate "testFinal"

I don't know if its clear so here is the example

enter image description here

NB : the Timestamp is not homogeneous, so the interval between two values can be different over time

Thanks a lot

Here is the code for the dataframe

df = pd.DataFrame({'Timestamp':[11.1,11.2,11.3,11.4,11.5,11.6,11.7,11.8,11.9,12.0,12.10],
                   'test':[2,22,8,4,5,4,5,3,54,23,89],
                   'testFinal':[6,18,3,0,0,1,49,20,35,np.NaN,np.NaN]})
pandas dataframe numpy python
2 Answers

First, create a new temporary column temp obtained from converting the Timestamp column to timedelta using pd.to_timedelta, then set this temp column as dataframe index, then create a new column testFinal having the values as this new index + 0.2 seconds, then using Series.map, map the testFinal column to the values from df['test'] column, thus now the testFinal column should have values corresponding to the values in test column 0.2s later, thereafter you can subtract the values in the testFinal and test column to get the desired result:

df['temp'] = pd.to_timedelta(df['Timestamp'], unit='s')
df = df.set_index('temp')

df['testFinal'] = df.index + pd.Timedelta(seconds=0.2)
df['testFinal'] = df['testFinal'].map(df['test']).sub(df['test']).abs()

df = df.reset_index(drop=True)

# print(df)
    Timestamp  test  testFinal
0        11.1     2        6.0
1        11.2    22       18.0
2        11.3     8        3.0
3        11.4     4        0.0
4        11.5     5        0.0
5        11.6     4        1.0
6        11.7     5       49.0
7        11.8     3       20.0
8        11.9    54       35.0
9        12.0    23        NaN
10       12.1    89        NaN
user_128331660

You could use numpy as follows. I created a new column test_final to compare with the expected testFinal column. 

import numpy as np

test = df.test.values
df['test_final'] = np.abs(test - np.concatenate((test[2:], np.array([np.nan]*2)), axis=0))
print(df)

Output: 

    Timestamp  test  testFinal  test_final
0        11.1     2        6.0         6.0
1        11.2    22       18.0        18.0
2        11.3     8        3.0         3.0
3        11.4     4        0.0         0.0
4        11.5     5        0.0         0.0
5        11.6     4        1.0         1.0
6        11.7     5       49.0        49.0
7        11.8     3       20.0        20.0
8        11.9    54       35.0        35.0
9        12.0    23        NaN         NaN
10       12.1    89        NaN         NaN
user_84748940
Related