CODEWITHSUNDEEP
pythonpandas
Shonn McNeill
Shonn McNeill

Reputation: 33

Pandas Sum Index if Column is Not NaN

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

Upvotes: 1

Views: 1362

Answers (2)

Shonn McNeill
Shonn McNeill

Reputation: 33

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.

Upvotes: 0

Stefan
Stefan

Reputation: 42905

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()

Upvotes: 1

Related Questions