how get the sum and average of groups row wise

I want to calculate the sum and average of variables grouped by year? how can I calculate.

Thanks in advance.

bank year v1 v2 v3 v4 v5 v6
Corporation Bank 1989
Corporation Bank 1990
Corporation Bank 1991 21430.6 370.4 0.149578445 0.017283697 0.115549384
Corporation Bank 1992 26774.1 602.5 0.149578445 0.022503091 0.150443406
Corporation Bank 1993 31741.7 933.8 0.149578445 0.029418714 0.196677497
Corporation Bank 1994 46368.7 1647.4 0.149578445 0.035528277 0.237522703
Corporation Bank 1995 71008.5 2342.8 0.149578445 0.032993233 0.220574784
Corporation Bank 1996 69050.8 3260.5 0.149578445 0.047218859 0.31567957
Corporation Bank 1997 81538.5 3995.3 0.149578445 0.048998939 0.327580215
Corporation Bank 1998 112135.9 8489.6 0.149578445 0.075708136 0.506143357
Corporation Bank 1999 149831.1 1.43 9746.1 0.162473138 0.065047243 9.201811829
Corporation Bank 2000 167622.8 1.54 11447.6 0.186477106 0.068293812 8.624618038
Corporation Bank 2001 197032 1.55 13477 0.188659285 0.068400057 8.578427809
Corporation Bank 2002 236042 1.6 20462.5 0.19957018 0.086690081 8.451613766
Corporation Bank 2003 262719.8 1.88 23572.7 0.260671189 0.089725632 7.556361097
Corporation Bank 2004 291536.9 1.96 27589.5 0.27812862 0.094634676 7.387354368
Corporation Bank 2005 339238.6 1.12 30484.7 0.094825592 0.089862121 12.75881432
Corporation Bank 2006 405066.4 1.24 33716.6 0.121011739 0.083237217 10.93478392
Corporation Bank 2007 527430.6 1.17 37654.6 0.105736487 0.071392521 11.74043659
Corporation Bank 2008 666160.8 1.29 42285.2 0.131922634 0.063475966 10.25961902
Corporation Bank 2009 869224.8 1.24 48965.1 0.121011739 0.056331918 10.71244764
Corporation Bank 2010 1116923.1 1.28 57748.6 0.129740455 0.051703291 10.26436427
Corporation Bank 2011 1435422.7 1.21 66957.8 0.114465202 0.046646747 10.97841724
Corporation Bank 2012 1636441.3 1.06 79444.1 0.081732519 0.048546868 13.5631066
Corporation Bank 2013 1935917.3 0.88 93446.9 0.042453299 0.048270089 21.86567618
Corporation Bank 2014 2223467.9 0.29 99746.2 0.086295257 0.044860643 3.880406117
Corporation Bank 2015 2264796.4 0.28 104844.8 0.088477435 0.046293256 3.687869751
Corporation Bank 2016 2353802.1 -0.23 106861.4 0.19976856 0.045399484 -0.924071918
Corporation Bank 2017 2483946.6 0.23 120486.6 0.09938833 0.048506115 2.802201377
Corporation Bank 2018 2224067.2 -1.67 101990.4 0.514002321 0.045857607 -3.159795835
Corporation Bank 2019 2140778.2 -3.14 156626.9 0.83478262 0.073163535 -3.673814466
Corporation Bank 2020 2299901.4 -1.13 128710 0.396164661 0.055963269 -2.711086669
Corporation Bank 2021
D C B Bank Ltd. 1989
D C B Bank Ltd. 1990
D C B Bank Ltd. 1991
D C B Bank Ltd. 1992
D C B Bank Ltd. 1993 4672.6 638.8 0.090656652 0.136711895 1.508018351
D C B Bank Ltd. 1994 5779.9 812.2 0.090656652 0.140521462 1.550040281
D C B Bank Ltd. 1995 7704 1218 0.090656652 0.158099688 1.743939192
D C B Bank Ltd. 1996 9140.8 1737.4 0.090656652 0.190070891 2.096601703
D C B Bank Ltd. 1997 12747.9 1617.1 0.090656652 0.126852266 1.39926043
D C B Bank Ltd. 1998 18023.4 2.1 1873.1 0.357064852 0.103926007 6.172340949
D C B Bank Ltd. 1999 23596.4 1.13 2031.7 0.150260157 0.086102117 8.093310572
D C B Bank Ltd. 2000 33268.7 1.18 2263.2 0.160920193 0.068027906 7.755570527
D C B Bank Ltd. 2001 39497 0.87 2761 0.094827971 0.069904043 9.911675146
D C B Bank Ltd. 2002 41887.6 0.95 3028.9 0.111884028 0.072310182 9.137230763
D C B Bank Ltd. 2003 44071.9 0.85 2199.7 0.090563956 0.049911622 9.936752561
D C B Bank Ltd. 2004 53926.2 0.37 2855 0.011772387 0.052942725 35.92667531
D C B Bank Ltd. 2005 46594.6 -3.38 1740.5 0.811275074 0.037354114 -4.120237381
D C B Bank Ltd. 2006 37417.2 -2.01 1393 0.519190092 0.037228868 -3.799708743
D C B Bank Ltd. 2007 52783.1 0.17 3230.3 0.054412531 0.061199513 4.249012286
D C B Bank Ltd. 2008 75972.6 0.48 6257.5 0.011679691 0.082365221 48.14898109
D C B Bank Ltd. 2009 59576.3 -1.25 5384.7 0.357157548 0.090383256 -3.246793327
D C B Bank Ltd. 2010 61512.3 -1.3 5424.6 0.367817584 0.088187241 -3.294602575
D C B Bank Ltd. 2011 74250.2 0.3 5653.4 0.026696438 0.076139862 14.08951495
D C B Bank Ltd. 2012 86872.7 0.68 8064 0.054319835 0.092825479 14.2273164
D C B Bank Ltd. 2013 112907 1.06 9492.7 0.135336107 0.084075389 8.453585779
D C B Bank Ltd. 2014 129362.9 1.31 11013.6 0.188636286 0.085137238 7.395911293
D C B Bank Ltd. 2015 161504 1.37 15371.5 0.201428329 0.095177209 7.273938162
D C B Bank Ltd. 2016 191422.6 1.14 17419.4 0.152392164 0.090999704 8.077841222
D C B Bank Ltd. 2017 240705.4 0.93 19483.8 0.107620014 0.08094459 9.393648568
D C B Bank Ltd. 2018 302477.3 0.94 25574.1 0.109752021 0.084548824 9.335124899
D C B Bank Ltd. 2019 358271.7 0.99 28712.4 0.120412057 0.080141412 8.887327722
D C B Bank Ltd. 2020 385383.8 0.9 31847.7 0.101223992 0.082638917 9.707569302
D C B Bank Ltd. 2021
Dena Bank 1989
Dena Bank 1990
Dena Bank 1991
Dena Bank 1992
Dena Bank 1993 49649.6 783.8 0.149349643 0.015786633 0.105702514
Dena Bank 1994 59553.6 1385.4 0.149349643 0.023263077 0.155762524
Dena Bank 1995 71176.9 1746.7 0.149349643 0.024540265 0.164314187
Dena Bank 1996 82464.8 2765.8 0.149349643 0.033539159 0.224568053
Dena Bank 1997 97142.3 5024 0.149349643 0.051717944 0.346287695
Dena Bank 1998 122641.3 0.86 5458.3 0.047947888 0.044506214 18.86435987
Dena Bank 1999 148435.3 0.75 6966.8 0.022712158 0.046934927 35.08847232
Dena Bank 2000 168508.4 0.38 6462.3 0.062171664 0.038350017 6.728949976
Dena Bank 2001 176713.3 242.5 0.149349643 0.001372279 0.009188367
Dena Bank 2002 184478.7 0.06 1558.7 0.135584699 0.008449214 0.50484468
Dena Bank 2003 198200.6 0.6 3654.1 0.011700202 0.018436372 52.856897
Dena Bank 2004 219515.6 1.11 6426.1 0.105301822 0.029274001 10.81912905
Dena Bank 2005 240333.4 0.26 9925.9 0.089701552 0.041300543 3.3589223
Dena Bank 2006 265568.4 0.29 10655.6 0.08281908 0.04012375 3.986083275
Dena Bank 2007 314506.4 0.71 12402.8 0.013535528 0.039435763 55.36804796
Dena Bank 2008 386514.1 1.06 15665.2 0.093831035 0.04052944 11.72884259
Dena Bank 2009 484647.5 1.02 19489.2 0.084654406 0.040213145 12.52401611
Dena Bank 2010 575890.6 1.01 23930.5 0.082360248 0.041553899 12.76773595
Dena Bank 2011 708387.1 1 31120.7 0.080066091 0.043931771 13.03837565
Dena Bank 2012 873879.3 1.08 40311.7 0.09841935 0.046129597 11.44215642
Dena Bank 2013 1134404.4 0.86 47358.5 0.047947888 0.041747458 18.80682332
Dena Bank 2014 1248634.9 0.51 62437.8 0.032347618 0.050004849 17.31208923
Dena Bank 2015 1300497.9 0.22 66754.3 0.098878181 0.051329802 2.744081652
Dena Bank 2016 1335736.9 -1.02 62119.6 0.383353691 0.046505865 -2.539415057
Dena Bank 2017 1296898.2 0.67 65866.8 0.004358899 0.050787949 165.3600942
Dena Bank 2018 1210177.9 1.59 81097.8 0.215421374 0.067013123 7.691962463
Dena Bank 2019
Dena Bank 2020
Dena Bank 2021
Dhanlaxmi Bank Ltd. 1989
Dhanlaxmi Bank Ltd. 1990
Dhanlaxmi Bank Ltd. 1991
Dhanlaxmi Bank Ltd. 1992
Dhanlaxmi Bank Ltd. 1993
Dhanlaxmi Bank Ltd. 1994
Dhanlaxmi Bank Ltd. 1995 5257.4 206.6 0.017056057 0.039296991 2.30399036
Dhanlaxmi Bank Ltd. 1996 8345.6 432.1 0.017056057 0.051775786 3.03562459
Dhanlaxmi Bank Ltd. 1997 12188.6 0.64 538.3 0.119392401 0.044164219 5.730383285
Dhanlaxmi Bank Ltd. 1998 11841.5 1 633.1 0.196144659 0.05346451 5.370854938
Dhanlaxmi Bank Ltd. 1999 13808.4 664.6 0.017056057 0.048130124 2.821878685
Dhanlaxmi Bank Ltd. 2000 15940 0.71 738.8 0.134316451 0.046348808 5.631095837
Dhanlaxmi Bank Ltd. 2001 16945 0.4 722.6 0.068224229 0.042643848 6.48807402
Dhanlaxmi Bank Ltd. 2002 18885.7 0.53 1098.8 0.095940322 0.058181587 6.130702658
Dhanlaxmi Bank Ltd. 2003 21074.2 0.71 1203.8 0.134316451 0.057121979 5.711303216
Dhanlaxmi Bank Ltd. 2004 24469.1 0.71 1340.7 0.134316451 0.054791553 5.693952958
Dhanlaxmi Bank Ltd. 2005 26460.1 -0.83 1077.9 0.194012652 0.040736807 -4.068101664
Dhanlaxmi Bank Ltd. 2006 28510.1 0.33 1147.6 0.053300179 0.040252402 6.946550813
Dhanlaxmi Bank Ltd. 2007 34493.9 0.47 1278.2 0.083148279 0.037055827 6.098211934
Dhanlaxmi Bank Ltd. 2008 40338.2 0.76 1524.9 0.144976487 0.037802877 5.502981161
Dhanlaxmi Bank Ltd. 2009 56428.2 1.21 4244.8 0.240916809 0.075224799 5.334724481
Dhanlaxmi Bank Ltd. 2010 80870.1 0.35 4400.9 0.057564193 0.054419371 7.025537061
Dhanlaxmi Bank Ltd. 2011 142709.6 0.23 8446.5 0.031980107 0.059186628 9.042703577
Dhanlaxmi Bank Ltd. 2012 146806.9 -0.73 7282.5 0.17269258 0.049605979 -3.939914622
Dhanlaxmi Bank Ltd. 2013 138311.6 0.02 7660.1 0.012792043 0.055382918 5.892953768
Dhanlaxmi Bank Ltd. 2014 146904.9 -1.84 7363.3 0.409345375 0.050122903 -4.372535288
Dhanlaxmi Bank Ltd. 2015 143548.2 -1.77 7238.7 0.394421325 0.050426965 -4.359736471
Dhanlaxmi Bank Ltd. 2016 124659.1 -1.61 5079.6 0.360309211 0.040747928 -4.355292688
Dhanlaxmi Bank Ltd. 2017 123360.3 0.1 6544.9 0.004264014 0.053055156 35.89461862
Dhanlaxmi Bank Ltd. 2018 122893.6 -0.2 6092.5 0.059696201 0.049575405 -2.519835306
Dhanlaxmi Bank Ltd. 2019 118017.9 0.1 6214.5 0.004264014 0.052657266 35.80130515
Dhanlaxmi Bank Ltd. 2020 122666.1 0.55 6877.7 0.100204337 0.056068466 6.048325688
Dhanlaxmi Bank Ltd. 2021

Hi,

You can do it with by()

by(mtcars, mtcars[, "cyl"], FUN = function(d) {
  c(sum = rowSums(d), mean = rowSums(d))
})

Let's say that you have your data in variable df you may use:

by(df, df[, "year"], FUN = function(d) {
  c(sum = rowSums(d), mean = rowSums(d))
})

You may be interested in grouping by several columns:

by(df, df[, c("bank", "year")], FUN = function(d) {
  c(sum = rowSums(d), mean = rowSums(d))
})
1 Like

Is this what you mean?

library(dplyr)

sample_df %>% 
    group_by(year) %>% 
    summarise(across(starts_with("v"), .fns = list("sum" = sum, mean = mean), na.rm = TRUE))
#> # A tibble: 33 x 13
#>     year  v1_sum v1_mean v2_sum v2_mean v3_sum v3_mean v4_sum v4_mean v5_sum
#>    <int>   <dbl>   <dbl>  <dbl>   <dbl>  <dbl>   <dbl>  <dbl>   <dbl>  <dbl>
#>  1  1989      0     NaN    0     NaN        0     NaN   0     NaN     0     
#>  2  1990      0     NaN    0     NaN        0     NaN   0     NaN     0     
#>  3  1991  21431.  21431.   0     NaN      370.    370.  0.150   0.150 0.0173
#>  4  1992  26774.  26774.   0     NaN      602.    602.  0.150   0.150 0.0225
#>  5  1993  86064.  28688.   0     NaN     2356.    785.  0.390   0.130 0.182 
#>  6  1994 111702.  37234.   0     NaN     3845    1282.  0.390   0.130 0.199 
#>  7  1995 155147.  38787.   0     NaN     5514.   1379.  0.407   0.102 0.255 
#>  8  1996 169002   42250.   0     NaN     8196.   2049.  0.407   0.102 0.323 
#>  9  1997 203617.  50904.   0.64    0.64 11175.   2794.  0.509   0.127 0.272 
#> 10  1998 264642.  66161.   3.96    1.32 16454.   4114.  0.751   0.188 0.278 
#> # … with 23 more rows, and 3 more variables: v5_mean <dbl>, v6_sum <dbl>,
#> #   v6_mean <dbl>

Created on 2021-07-22 by the reprex package (v2.0.0)

Note: Next time please provide a proper REPRoducible EXample (reprex) illustrating your issue.

1 Like