Sum the values of big amount columns based on one column

Question

I have the dataframe below and I would like to sum the values of all my columns which actually are much more than 4, per Category. So I would like a loop or something to run something like:

library(dplyr)
Ind<-Abstract_50 %>% group_by(Category) %>%
  summarize(Abstract_50$`2018-03` = sum(Abstract_50$`2018-03`))

Abstract_50<-structure(list(Category = c("Gaming", "Comedy", "Education", 
"Entertainment", "People & Blogs", "Howto & Style", "Gaming", 
"Entertainment", "Comedy", "Education", "Science & Technology", 
"People & Blogs", "Gaming", "Howto & Style", "Entertainment", 
"Entertainment", "Entertainment", "Gaming", "Entertainment", 
"Gaming", "Entertainment", "Entertainment", "Film & Animation", 
"Entertainment", "Education", "Gaming", "Gaming", "People & Blogs", 
"Entertainment", "Comedy", "Gaming", "People & Blogs", "Entertainment", 
"Comedy", "Entertainment", "Sports", "Gaming", "Gaming", "Film & Animation", 
"Entertainment", "Entertainment", "Entertainment", "Music", "Gaming", 
"Film & Animation", "People & Blogs", "Entertainment", "Science & Technology", 
"Entertainment", "Gaming"), `2018-03` = c(NA, NA, 101163664, 
222435612, 2566, 539836128, 170830842, 43657714, NA, 589789888, 
18453451, 22327, 275987966, 3398517, 115637850, NA, 3358627, 
104063416, 904737659, NA, 138065088, NA, 178509153, 147605809, 
252376836, 47241577, 145748966, NA, 112800, NA, 92281120, NA, 
884076, NA, 147558050, 160818771, 209932596, 1269924, 66883227, 
NA, NA, NA, 45831420, 10426490, NA, NA, 67836946, 8847841, 2614794, 
51696616), `2018-04` = c(NA, NA, 138215454, 217690060, 2486, 
634144976, 140351664, 40516483, NA, 755403217, 23270005, -178648, 
326352497, 3315085, 77217671, NA, 2824360, 105855275, 708273455, NA, 209272453, NA, 165972373, 195448116, 262205166, 71156940, 
64229826, NA, 789991, NA, 107928087, NA, 587108, NA, 135852933, 
208830991, 198161237, 2462926, 77881362, NA, NA, NA, 38904148, 
21032898, NA, NA, 59783457, 16303243, 7311107, 57273819), `2018-05` = c(NA, 
NA, 151697886, 254479194, 2374, 712486430, 196327850, 75886076, 
NA, 959619148, 29213361, 164395, 330922933, 3112488, 86213061, 
NA, 3601193, 103629125, 708728068, NA, 250967293, NA, 151501861, 
48698231, 249038679, 73774699, 164568363, NA, 943891, NA, 130376287, 
NA, 5322697, NA, 164697288, 189706047, 213415764, 7806720, 78552229, 
NA, NA, NA, 73012836, 45648322, NA, NA, 68432350, 18830495, 15834552, 
88757967), `2018-06` = c(NA, NA, 142169001, 297190999, 2057, 
850709260, 191765278, 70501496, NA, 1454871272, 28011897, 465378, 
370654981, 3211888, 53592160, NA, 4667279, 193768594, 821707471, 
NA, 599954205, NA, 189548519, 371602705, 229237800, 68325636, 
193613716, NA, 1858138, NA, 94145274, NA, 8718655, NA, 255038043, 
254772059, 265200729, 9386440, 51753755, NA, NA, NA, 84645886, 
266089027, NA, NA, 167742900, 11844037, 33770300, 81240819)), row.names = c(NA, 
-50L), class = c("tbl_df", "tbl", "data.frame"))

The question or problem is not clear. And a description of what you would like for output can be helpful. But I'm guessing that your code returns an error, and the reason is the data frame name bit in `summarise`. This works instead: ```Ind<-Abstract_50 %>% group_by(Category) %>% summarize(`2018-03` = sum(`2018-03`))```. — sashahafner, Mar 17 '22 at 14:12
I see now. The issue was generating a sum for *all* columns. — sashahafner, Mar 17 '22 at 14:22

AndrewGB · Accepted Answer · 2022-03-17T16:18:04.987

You can use across to calculate the sum across all columns, except for the grouping column.

library(tidyverse)

Abstract_50 %>% 
  group_by(Category) %>% 
  summarise(across(everything(), sum, na.rm = TRUE))

Output

   Category              `2018-03`  `2018-04`  `2018-05`  `2018-06`
   <chr>                     <dbl>      <dbl>      <dbl>      <dbl>
 1 Comedy                        0          0          0          0
 2 Education             943330388 1155823837 1360355713 1826278073
 3 Entertainment        1794505025 1655567194 1683803894 2686344351
 4 Film & Animation      245392380  243853735  230054090  241302274
 5 Gaming               1109479513 1094805169 1355228030 1734190494
 6 Howto & Style         543234645  637460061  715598918  853921148
 7 Music                  45831420   38904148   73012836   84645886
 8 People & Blogs            24893    -176162     166769     467435
 9 Science & Technology   27301292   39573248   48043856   39855934
10 Sports                160818771  208830991  189706047  254772059

yopu probable need `na.rm = TRUE`, else you'll get a lot of NA.. `summarise(across(everything(), sum, na.rm = TRUE))` — Wimpel, Mar 17 '22 at 14:25

Sum the values of big amount columns based on one column

1 Answers1