Cumulative values in R per group-CodePudding

So, I'm having a headache finding a way to program this in R. Since it is very easy to do Excel, I'm hoping this is just my n00b lack of knowledge. Checking the example table I'm presenting: my objetive is to create the last column (Balance).

Each TRA (101 and 102) has a number of IDAs (the order of all entries in that TRA, from 1 to last).

Balance in the 1st IDA is the total sum of the Principal. For each next IDA, the Balance value is reduce by the total amount of its Principal, until the last Balance being simply equal to the last TDA.

In order words, the Balance value of one row is the sum of the Principal value in that same row plus the Balance value of the next IBA row, until the last one of each TRA.

So, for instance: For TRA 101, we got fow rows (IDA from 1 to 4). The Balance value of the 1st row is (-4.799.471 -14.398.412 = -19.197.882), the Principal of 1st row plus Balance of the 2nd. For last IDA of each TRA (4 in 101, 9 in 102), I just need the value of the principal.

We tried this option, but it isn't working when we have different Principal values through the TRA.

df %<>%
  group_by(TRA)%>%
  arrange(desc(IDA))%>%
  mutate(saldo = cumsum(Principal))%>%
  ungroup()%>%
  arrange(TRA)

Can someone point the best approach for me, please?

ROW TRA IDA IDB     Principal   Balance
1   101 1   1011    -4,799,471  -19,197,882
2   101 2   1012    -4,799,471  -14,398,412
3   101 3   1013    -4,799,471  -9,598,941
4   101 4   1014    -4,799,471  -4,799,471
5   102 1   1021    -5,248,583  -47,237,250
6   102 2   1022    -5,248,583  -41,988,667
7   102 3   1023    -5,248,583  -36,740,084
8   102 4   1024    -5,248,583  -31,491,500
9   102 5   1025    -5,248,583  -26,242,917
10  102 6   1026    -5,248,583  -20,994,334
11  102 7   1027    -5,248,583  -15,745,750
12  102 8   1028    -5,248,583  -10,497,167
13  102 9   1029    -5,248,584  -5,248,584

CodePudding user response：

If your posted data is the data frame you're working with you need to convert your Principal column to numeric, e.g.

df %>% 
  group_by(TRA) %>% 
  arrange(desc(IDA)) %>% 
  mutate(saldo = cumsum(gsub(",", "", Principal))) %>% 
  ungroup() %>% 
  arrange(TRA)
# A tibble: 13 × 7
     ROW   TRA   IDA   IDB Principal  Balance         saldo
   <int> <int> <int> <int> <chr>      <chr>           <dbl>
 1     4   101     4  1014 -4,799,471 -4,799,471   -4799471
 2     3   101     3  1013 -4,799,471 -9,598,941   -9598942
 3     2   101     2  1012 -4,799,471 -14,398,412 -14398413
 4     1   101     1  1011 -4,799,471 -19,197,882 -19197884
 5    13   102     9  1029 -5,248,584 -5,248,584   -5248584
 6    12   102     8  1028 -5,248,583 -10,497,167 -10497167
 7    11   102     7  1027 -5,248,583 -15,745,750 -15745750
 8    10   102     6  1026 -5,248,583 -20,994,334 -20994333
 9     9   102     5  1025 -5,248,583 -26,242,917 -26242916
10     8   102     4  1024 -5,248,583 -31,491,500 -31491499
11     7   102     3  1023 -5,248,583 -36,740,084 -36740082
12     6   102     2  1022 -5,248,583 -41,988,667 -41988665
13     5   102     1  1021 -5,248,583 -47,237,250 -47237248

CodePudding user response：

It works fine, no?

df <- read_table(
  "ROW TRA IDA IDB     Principal   Balance
1   101 1   1011    -4,799,471  -19,197,882
2   101 2   1012    -4,799,471  -14,398,412
3   101 3   1013    -4,799,471  -9,598,941
4   101 4   1014    -4,799,471  -4,799,471
5   102 1   1021    -5,248,583  -47,237,250
6   102 2   1022    -5,248,583  -41,988,667
7   102 3   1023    -5,248,583  -36,740,084
8   102 4   1024    -5,248,583  -31,491,500
9   102 5   1025    -5,248,583  -26,242,917
10  102 6   1026    -5,248,583  -20,994,334
11  102 7   1027    -5,248,583  -15,745,750
12  102 8   1028    -5,248,583  -10,497,167
13  102 9   1029    -5,248,584  -5,248,584"
)


df %>%
  group_by(TRA) %>%
  arrange(TRA, desc(IDA)) %>%
  mutate(saldo = cumsum(Principal)) %>% 
  ungroup()

# A tibble: 13 × 7
     ROW   TRA   IDA   IDB Principal   Balance     saldo
   <dbl> <dbl> <dbl> <dbl>     <dbl>     <dbl>     <dbl>
 1     4   101     4  1014  -4799471  -4799471  -4799471
 2     3   101     3  1013  -4799471  -9598941  -9598942
 3     2   101     2  1012  -4799471 -14398412 -14398413
 4     1   101     1  1011  -4799471 -19197882 -19197884
 5    13   102     9  1029  -5248584  -5248584  -5248584
 6    12   102     8  1028  -5248583 -10497167 -10497167
 7    11   102     7  1027  -5248583 -15745750 -15745750
 8    10   102     6  1026  -5248583 -20994334 -20994333
 9     9   102     5  1025  -5248583 -26242917 -26242916
10     8   102     4  1024  -5248583 -31491500 -31491499
11     7   102     3  1023  -5248583 -36740084 -36740082
12     6   102     2  1022  -5248583 -41988667 -41988665
13     5   102     1  1021  -5248583 -47237250 -47237248