Summing values in R based on column value with dplyr

  • A+
Category:Languages

I have a data set that has the following information:

Subject    Value1    Value2    Value3      UniqueNumber 001        1         0         1           3 002        0         1         1           2 003        1         1         1           1 

If the value of UniqueNumber > 0, I would like to sum the values with dplyr for each subject from rows 1 through UniqueNumber and calculate the mean. So for Subject 001, sum = 2 and mean = .67.

total = 0; average = 0; for(i in 1:length(Data$Subject)){    for(j in 1:ncols(Data)){    if(Data$UniqueNumber[i] > 0){     total[i] = sum(Data[i,1:j])     average[i] = mean(Data[i,1:j])    } } 

Edit: I am only looking to sum through the number of columns listed in the 'UniqueNumber' column. So this is looping through every row and stopping at column listed in 'UniqueNumber'. Example: Row 2 with Subject 002 should sum up the values in columns 'Value1' and 'Value2', while Row 3 with Subject 003 should only sum the value in column 'Value1'.

 


Check this solution:

df %>%   gather(key, val, Value1:Value3) %>%   group_by(Subject) %>%   mutate(     Sum = sum(val[c(1:(UniqueNumber[1]))]),     Mean = mean(val[c(1:(UniqueNumber[1]))]),   ) %>%   spread(key, val) 

Output:

 Subject UniqueNumber   Sum  Mean Value1 Value2 Value3   <chr>          <int> <dbl> <dbl>  <dbl>  <dbl>  <dbl> 1 001                3     2 0.667      1      0      1 2 002                2     1 0.5        0      1      1 3 003                1     1 1          1      1      1 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: