Ratio of each account with its account total in R

Question

I am trying to get the ratio of each account with its respective total amount of the account.

> dput(test)
        structure(list(Account_new = c("32001932", "31011217", "31011217", 
        "22006428", "22005443", "22005443", "31011217", "32002608", "22001044", 
        "22006428", "31004020", "32002608", "32002608", "32002692", "31001730", 
        "31011597", "32002387", "32000717", "32002788", "32002789", "22001054", 
        "31002915", "22002000", "31010427", "31007795", "23001237", "32002777", 
        "22000284", "31004020", "32002062", "22000400", "31008884", "32002432", 
        "31010427", "32002608", "32002608", "32002742", "22001061", "31005764", 
        "31007410", "31011634", "31007335", "32000741", "32000741", "32000741", 
        "32001742", "32002692", "32002692", "31001725", "31001730", "32001462", 
        "32001462", "31011597", "32000756", "32000756", "32000756", "32000756", 
        "32000756", "32000756", "32000756", "32000756", "32000756", "32000246", 
        "32000256", "31008560", "32000706", "32000706", "32000706", "32000706", 
        "32002777", "32000756", "22001054", "31002915", "31002915", "22006433", 
        "22001061", "22006293", "32002707", "32002237", "32002608", "32002608", 
        "22005443", "31010427", "22002000", "22001061", "31002915", "22006428", 
        "22006428", "32002608", "32002097", "32002097", "32002097", "32002097", 
        "32002097", "32002432", "22006293", "23000062", "32002782", "22005171", 
        "22001061"), New_amt_loc_curr = c(393.82, 12558.49, 1738.87, 
        22200.19, 101.19, 142.74, 68262.44, 5357.97, 382048.48, 56877.06, 
        433.71, 43696.82, 14350.07, 369.97, 199723.81, 549.2, 52893.4, 
        1617.03, 2093.81, 7607.58, 102152.99, 88716.88, 339.85, 51401.16, 
        373.28, 14166.36, 77.22, 2478.74, 1110.22, 2520.91, 7736.87, 
        501.7, 139.97, 16555.63, 25805.93, 10620.37, 34992.76, 6267.67, 
        225.22, 228.62, 155580.49, 13749.64, 610.14, 238.31, 41.47, 1041.44, 
        78.78, 994.85, 119598.63, 310084.4, 1263.4, 536.49, 3343.21, 
        18.16, 913.63, 315.72, 5449.66, 2999.34, 121.54, 929, 403.73, 
        44.71, 48085.22, 1911.95, 104957.41, 15732.01, 4356.91, 17642.22, 
        3060.26, 6042.52, 22.69, 119372.69, 92081.72, 6213.82, 7458.01, 
        6547.72, 10092.65, 560.75, 2402.59, 22804.72, 62681.66, 105.41, 
        25664.79, 14953.4, 2145.61, 98682.25, 61021.51, 7.48, 822.44, 
        67544.51, 24179.65, 23431.98, 46609.75, 67544.51, 612.36, 4528.94, 
        25492.2, 6017.25, 15312.28, 1610.53)), row.names = c(NA, -100L
        ), class = c("tbl_df", "tbl", "data.frame"))

Below is the output which i did in excel. I would like to get the ratio for each account_new.

score 1 · Accepted Answer · answered Jul 29 '20 at 16:17

Here's a tidyverse solution.

library("dplyr")

df %>%
  group_by(Account_new) %>%
  mutate(ratio = New_amt_loc_curr / sum(New_amt_loc_curr))

   Account_new New_amt_loc_curr  ratio
   <chr>                  <dbl>  <dbl>
 1 32001932                394. 1     
 2 31011217              12558. 0.152 
 3 31011217               1739. 0.0211
 4 22006428              22200. 0.158 
 5 22005443                101. 0.290 
 6 22005443                143. 0.409 
 7 31011217              68262. 0.827 
 8 32002608               5358. 0.0288
 9 22001044             382048. 1     
10 22006428              56877. 0.406 
# ... with 90 more rows

for account number 31011217 (row number 2 and 3) the ratio should be 0.87 and 0.12. i.e 0.87=row2(amnt)/sum(row2 and row3) — Numita, Jul 29 '20 at 16:44

Ratio of each account with its account total in R

1 Answers1