Pandas Sum Index if Column is Not NaN

Question

I have a Pandas DataFrame of data where time in seconds is the index and the other columns are acceleration values. I would like the total time the acceleration value is greater than 60 g's. The issue is the acceleration may be above 60 g's for a while, but will drop below then return again. I have run the code below to turn the acceleration values not greater than or equal to 60 to "NaN". Is there a way to check each row to see if it is not equal to "NaN" and if so add the index (time) until NaN is reached again record the total time then start over again.

shot1_gt60 = shot1_df.where(shot1_df['CH7 [g] RMS']>=60)
shot1_gt60.head()

Time [s]    CH7 [g] RMS     
-0.250000   NaN
-0.249995   65
-0.249990   67
-0.249985   90
-0.249980   NaN

you could do a for loop with df.iterrows() – Kirell Jan 21 '16 at 22:52 — Kirell, Jan 21 '16 at 22:52
I did try that without much luck. – Shonn McNeill Jan 25 '16 at 00:00 — Shonn McNeill, Jan 25 '16 at 00:00

Stefan · Answer 1 · 2016-01-22T22:29:30.593

1

IIUC and you want the sum of the index values where the acceleration is greater than or equal to 69, you should be able to simply:

shot1_df[shot1_df['CH7 [g] RMS']>=60].index.to_series().sum()

edited Jan 22 '16 at 22:29

answered Jan 21 '16 at 23:05

Stefan

41,759
13
76
81

That gives me the total of all the time above 60 (69). I need the total between each set of NaN. The CH7 [g] RMS data has multiple changes from numbers to NaN and I need the total from each change from NaN to numbers. – Shonn McNeill Jan 24 '16 at 23:58

score 0 · Answer 2 · edited May 23 '17 at 11:59

This was my solution though not with my data. I got the solution from here.

# Some data
d = {'one' : [-.2, .2, -.1, .5, .6, .7, -.7, .4, -.9, .3]}

# convert to a dataframe
df = pd.DataFrame(d)

# check if the 'one' column is >=0 and put the results in a new column '>=0'
df['>=0'] = df[df>=0]

# create a new column called 'block' and if the next row in the column '>=0' is null then create
# then go up by one.
df['block'] = (df['>=0'].shift(1).isnull()).astype(int).cumsum()

# drop all the NaN values in the data
df.dropna(inplace=True)

# group by the block and sum the index.
x = df.reset_index().groupby(['block'])['index'].sum()
df, x

(   one  >=0  block
 1  0.2  0.2      2
 3  0.5  0.5      3
 4  0.6  0.6      3
 5  0.7  0.7      3
 7  0.4  0.4      4
 9  0.3  0.3      5, block
 2     1
 3    12
 4     7
 5     9
 Name: index, dtype: int64)

So it totals the index when the value in 'one' is >=0.

Pandas Sum Index if Column is Not NaN

2 Answers2