Calculate row sums conditional on column with 1 and 0's in a data frame in R , want the sum between consecutive 1's between 0's

sum specific columns in r
sum subset of columns in r
r sum columns by row
sum specific rows in r
sum multiple columns in r
conditional sum in r
sum columns in r
row sum in r

Input data frame reproducible example:

onoff = c(0,1,1,1,0,0,1,1,1,0)
amount = c(0,0.5,0,0.6,0,0,0.5,0.6,0.7,0)

mockdata = data.frame(onoff, amount)

It looks like this.

I want to sum the sequence of rows where onoff is 1 between 0's. So in this example, I'd have two sets of sums, the first between rows 2 and 4, and the second between rows 7 and 9. So I would output two numbers, 1.1 and 1.8.

Not sure how to do this, any help would be appreciated!

We can use rleid from data.table to create a grouping column and then use that to get the sum

library(dplyr)
library(data.table)
mockdata %>% 
   group_by(grp = rleid(onoff)) %>% 
   mutate(Sum = sum(amount) * onoff)
# A tibble: 10 x 4
# Groups:   grp [5]
#   onoff amount   grp   Sum
#   <dbl>  <dbl> <int> <dbl>
# 1     0    0       1   0  
# 2     1    0.5     2   1.1
# 3     1    0       2   1.1
# 4     1    0.6     2   1.1
# 5     0    0       3   0  
# 6     0    0       3   0  
# 7     1    0.5     4   1.8
# 8     1    0.6     4   1.8
# 9     1    0.7     4   1.8
#10     0    0       5   0  

If we need a summarised output

mockdata %>% 
  group_by(grp = rleid(onoff)) %>% 
  filter(onoff == 1) %>% 
  summarise(amount = sum(amount))

Sum data in one column based on condition in another column , So, the first one would be the sum of rows 2-11 in the average column (since there is a 0 in row 12 in the m_off_ground column) and the� It takes the top left cell in the sum_range argument as the beginning cell (C2 in our Excel SUMIF example), and then includes as many columns and rows as contained in the range argument (in our case, it's 1 column x 7 rows, i.e. C2:C8). How to use SUMIF in Excel - formula examples

aggregate(mockdata$amount, list(grp = c(1, cumsum(diff(mockdata$onoff) == 1))), sum)

      grp   x
1       1 1.1
2       2 1.8

rowsum: Give Column Sums of a Matrix or Data Frame, Based on a, Compute column sums across rows of a numeric matrix-like object for each level of a a vector or factor giving the grouping, with one element per row of x . Calculate row sums conditional on column with 1 and 0's in a data frame in R , want the sum between consecutive 1's between 0's - Stack Overflow. Input data frame reproducible example:onoff = c(0,1,1,1,0,0,1,1,1,0)amount = c(0,0.5,0,0.6,0,0,0.5,0.6,0.7,0)mockdata = data.frame(onoff, amount)It looks like this.

Using base R, we can use rle to create groups and tapply to calculate summarised data

with(rle(mockdata$onoff != 1), tapply(mockdata$amount, 
           rep(cumsum(values), lengths), sum))

#  1   2   3 
#1.1 1.8 0.0 

Or another approach which I think is closer to expected output is to filter the rows with onoff = 1

mockdata$row <- seq_len(nrow(mockdata))
temp <- subset(mockdata, onoff == 1)
tapply(temp$amount, cumsum(c(TRUE, diff(temp$row) > 1)), sum)

#  1   2 
#1.1 1.8 

colSums: Form Row and Column Sums and Means, Form row and column sums and means for numeric arrays (or data frames). For row* , the sum or mean is over dimensions dims+1, ; for col* it is over of the output is set to 0 ( *Sums ) or NaN ( *Means ), consistent with sum and mean . ( Add Noise) to Numbers kappa: Compute or Estimate the Condition Number of a� D= [0 2 1 1 1 0 0 0 4 0 2 ] The idea is, when successive B's = 1, I add the corresponding values from A and store in C. Similarly if successive B's = -1, I add the values up and store in C. If more than 1 value in A is added, I fill the gaps in C with 0's.

How do I sum values in a column that match a given condition using , And you want to sum the rows of Y where Z is 2 and X is 2 ,then we may use the following: 1.groupby print(np.where(df['a']==1, df['b'],0).sum()). So, my idea was calculate GRP data based on category value in filter. I could do this through if statement in measure, but I have 40 different columns and if statement would be just too long to code. So I wanted to change if statement with reference table, in which I would pair category with name of the column for the calculation.

pandas.DataFrame.sum — pandas 1.1.0 documentation, Return the sum of the values for the requested axis. This is equivalent to the method numpy.sum . Parameters. axis{index (0), columns (1)}. Axis for the function� {0;1;0;0;0;0;0;1;0;0;0;1;0;0;0;1;0} Now the rest of the operation is simple to understand. Formula will find the product of values and then calculate the sum to give us the value. Bonus Tip #1 – SUM amounts that were paid on August 06, 2013

count_if function, These functions calculate count/sum/average/etc. on values that meet a criterion that you *_row_if works on each row and *_col_if works on each column. 2 # count_row_if count_row_if(regex("^a"), sheet1) # c(1,0,0,1) sheet1 %row_in%� How could I sum the figures in column 2 by taking only one amount per each value in column 1? By example, for value 3, I should have an average of the amounts displayed in column 2 and add it to the average amount for value 2 and so on.

Comments
  • if onoff=0 then we can assume amount is also 0?