0

maybe one of you could help me with this R problem: I want to create a new column in a dataframe that sums up a column, but only the entries up to the current rowI further want to include a condition, where only rows are summed that contain certain text in another column (ie. df$Type == "A") Here is an executable example

#Create example dataframe
df <- data.frame(Date=c(1513900800, 1515024000, 1515196800, 1511481600, 1515456000, 1515456000, 1515456000, 1516233600, 1516233600, 1516492800),
                 Quantity=c(1, 2, -2, 1, 5, 2, -1, 5, 10, 15),
                 Type=c('A', 'A', 'A', 'B','C','A','B','C','C','C'))


#         Date Quantity Type
#1  1513900800        1    A
#2  1515024000        2    A
#3  1515196800       -2    A
#4  1511481600        1    B
#5  1515456000        5    C
#6  1515456000        2    A
#7  1515456000       -1    B
#8  1516233600        5    C
#9  1516233600       10    C
#10 1516492800       15    C

# What I want is to sum all rows where df$Type equals the specific row's df$Type up to the specific row's number
# In other words, I want a new column that sums up df$Quantity for each row above specific to df$Type
# The df i expect after the function would look like this df_expected:
df_expected <- data.frame(Date=c(1513900800, 1515024000, 1515196800, 1511481600, 1515456000, 1515456000, 1515456000, 1516233600, 1516233600, 1516492800),
                 Quantity=c(1, 2, -2, 1, 5, 2, -1, 5, 10, 15),
                 Type=c('A', 'A', 'A', 'B','C','A','B','C','C','C'),
                 Qty.held=c(1, 3, 1, 1, 5, 3, 0, 10, 20, 35))

#         Date Quantity Type Qty.held
#1  1513900800        1    A        1
#2  1515024000        2    A        3
#3  1515196800       -2    A        1
#4  1511481600        1    B        1
#5  1515456000        5    C        5
#6  1515456000        2    A        3
#7  1515456000       -1    B        0
#8  1516233600        5    C       10
#9  1516233600       10    C       20
#10 1516492800       15    C       35

I am really new to R, but have lots of experience in excel. First time poster. Thanks!

ChiChi88
  • 1

1 Answers1

1

First, you group by type and then you can call cumulative sum function on it

library(dplyr)

df |> 
  group_by(Type) |> 
  mutate(Qty.held = cumsum(Quantity))