Need Help : group_by and summarize_each

menewtor · June 28, 2018, 1:55am

Hi All,

I'm new to R, (I've been using Excel, M language n DAX all these time but my data size is getting too huge for excel thus i switch over to R) been doing online tutorial and reading books to bring myself up to speed.

For now, I'm experiencing these errors when i try to do a sum of a new Grouping

Error in summarise_impl(.data, dots) : 
  Evaluation error: invalid 'type' (character) of argument.

I've change the necessary columns to integer by using the following code
Report2 %>% mutate_at(c(19,20,21,22,23), as.integer)

This is the code i run for my grouping

Find_Status <- select(REPORT2,"LAND", "PRODUCT" , "KEY" , 
                         "1_POPULATION" , "2_POPULATION" ,"3_POPULATION" ,"4_POPULATION") %>%
  summarize_each(funs(sum("RELEVANT_POPULATION")))

The data types for 1-4 Populations are "Chr"

Hope these information are sufficient enough

jonspring · June 28, 2018, 4:10am

Welcome to R! I started using it two years ago for much the same reason after my data got too big for Excel to handle. It sounds like you're experimenting with dplyr, which is a great tool for data wrangling. There are some great tutorials out there, including:

You might get help sooner if you can provide some clarification.

You mention grouping, but I don't see any grouping happening in any of your code. In dplyr that's typically done with a group_by(....). Summarize and its variants, like summarize_each, collapse the rows of data they see into a single summary statistic. If you use group_by first, they will summarize each group, but if there's no grouping, it would summarize the whole data frame.
What is the question you are trying to answer? It looks like you're trying to sum some columns that are Chr (character) -- that seems impossible so I presume you mean something else.
What does your data in REPORT2 look like? Could you share a sample or some fake data that uses the same kinds of data?
FAQ: What's a reproducible example (`reprex`) and how do I create one?

markdly · June 28, 2018, 4:27am

Just to add to jonspring's comment, I think summarise_each is now deprecated. The following is a snippet from the dplyr help (which I grabbed by typing ?dplyr::summarise_each in the console).

mutate_each() and summarise_each() are deprecated in favour of a more featureful family of functions: mutate_all(), mutate_at(), mutate_if(), summarise_all(), summarise_at() and summarise_if()

menewtor · June 28, 2018, 1:42pm

Thanks @Jonspring (hi5 ) for the training materials. I prob start with "Manipulate Data in R" . I did a few courses from Linkedin but doesnt seem enough when im working on my actual dataset

Sorry, I missed out my own code
@markdly, i tried "summarise_at" but i guess i'm messing things up even more

Report2_Status <- select(REPORT2,"LAND", "PRODUCT" , "KEY" , 
                         "1_POPULATION" , "2_POPULATION" ,"3_POPULATION" ,"4_POPULATION") %>%
  group_by(REPORT2$LAND, REPORT2$PRODUCT, REPORT2$KEY)%>%
  summarise_at(REPORT2,REPORT2$1_POPULATION)

I tried running this before Group_By
Report2 %>% mutate_at(c(19,20,21,22,23), as.integer)

But is still showing as 'chr' . What can i do to make those sum-able ....

Give me a day to prepare the sample data and i'll post it here. Thanks so much

menewtor · June 30, 2018, 5:38am

LAND	PRODUCT	KEY	1_POPULATION	2_POPULATION	3_POPULATION	4_POPULATION
SGP	Toy	6700	26	26	0	52
PRC	Bear	653	219.66	219.66	219.66	1318
NZ	Toy	473	1.25	1.25	0	5
AUS	Clothes	2710	3.75	3.75	0	15
PRC	Bear	431	4872.33	4872.33	4872.33	29234
PRC	Bear	89	1882.66	1882.66	1882.66	11296
RI	Bear	2505	1388.44	1388.44	0	12496
PRC	Bear	1181	4220.75	4220.75	4220.75	50649
NZ	Bear	656	75.66	75.66	75.66	227
RI	Bear	1795	28	28	0	84
RC	Shorts	1682	2023	2023	2023	2023
AUS	Toy	248	13	13	0	39
NZ	Bags	925	10.2	10.2	0	51
PRC	Bear	3391	5717.5	5717.5	5717.5	34305
J	Shorts	1185	0.5	0.5	0.5	1
NZ	Bags	1210	7	7	0	28
AUS	Sneaker	213	12.5	12.5	0	25
NZ	Bear	708	108.21	108.21	108.21	1515
AUS	Toy	173	68.75	68.75	68.75	275
NZ	Bear	950	5.38	5.38	5.38	97
J	Bags	1706	3.2	3.2	0	16
AUS	Tea	1011	13035	13035	13035	13035
AUS	Tea	712	2303.5	2303.5	2303.5	4607
NZ	Coffee	118	942	942	0	942
NZ	Shorts	562	1600	1600	1600	1600
RP	Tea	711	127.5	127.5	0	255
IND	Toy	1887	5	5	0	15
SGP	Bags	32	4.5	4.5	0	18
NZ	Bags	1133	16.33	16.33	0	49
J	Toy	409	113	113	113	113
NZ	Bags	1173	26.5	26.5	26.5	53
NZ	Tea	93	39	39	0	78
VN	Sneaker	219	122	122	0	122
PRC	Bags	7	7115.5	7115.5	7115.5	28462
J	Sneaker	1910	955	955	0	955
J	Tea	1942	428.5	428.5	0	857
J	Tea	1231	129	129	0	774
NZ	Bags	511	9.33	9.33	0	28
RC	Bear	1491	4489.33	4489.33	0	40404
NZ	Tea	315	28	28	0	28
NZ	Tea	3005	351	351	351	351
NZ	Bear	53	11.08	11.08	11.08	133
ROK	Milk	1385	1495	1495	0	1495
RC	Shorts	807	50	50	0	50
AUS	Sneaker	541	30	30	30	30
AUS	Sneaker	437	295	295	295	295
AUS	Bear	2013	914.66	914.66	914.66	2744
MAL	Bear	1425	35.4	35.4	0	177
NZ	Toy	3309	0.8	0.8	0	4
J	Tea	1505	1595.5	1595.5	0	3191
NZ	Bear	115	8.22	8.22	0	74
NZ	Bear	916	29.37	29.37	29.37	470
NZ	Bear	2468	4.42	4.42	4.42	62
AUS	Clothes	2309	2.14	2.14	0	15
AUS	Bear	780	4887.33	4887.33	4887.33	29324
AUS	Egg	102	28	28	28	28
J	Milk	943	3194	3194	0	6388
J	Toy	1473	27.5	27.5	27.5	55
AUS	Clothes	2588	2.14	2.14	0	15
RC	Butter	1796	13542.66	13542.66	13542.66	40628
NZ	Bear	111	11.06	11.06	11.06	177
AUS	Toy	1105	245.14	245.14	245.14	1716
THA	Butter	331	503.66	503.66	503.66	1511
RC	Clothes	1085	116.66	116.66	0	700
AUS	Clothes	2732	1	1	0	1
PRC	Bear	51	747.66	747.66	747.66	4486
SGP	Bear	1051	0.22	0.22	0	2
MAL	Bear	895	2	2	2	2
RC	Cat	640	132.5	132.5	0	530
AUS	Clothes	3001	1	1	0	1
NZ	Butter	4	18.5	18.5	0	74
AUS	Bags	402	849.6	849.6	849.6	4248
RI	Bear	428	198	198	0	198
J	Bear	2629	37.75	37.75	37.75	151
AUS	Clothes	1493	5	5	0	15
VN	Bear	862	642.66	642.66	0	5784
PRC	Bear	30	5729.83	5729.83	5729.83	34379
AUS	Bear	200	25	25	0	25
PRC	Bear	1644	6315.33	6315.33	6315.33	37892
NZ	Clothes	410	397	397	0	794
AUS	Butter	1661	438	438	438	438
J	Bags	486	3340.66	2087.91	2087.91	10022
THA	Dog	795	1303	1303	0	1303
MAL	Perfume	14	258	258	0	258
J	Dog	1348	6	6	6	18
RI	Bear	1032	124.72	124.72	124.72	1372
J	Dog	1993	8395	8395	8395	8395
PRC	Dog	3318	13792	13792	13792	13792
AUS	Oil	2873	4829	4829	4829	4829
J	Bear	2006	36	36	36	144
RC	Cat	1545	19.33	19.33	0	58
AUS	Bags	142	1418	1418	0	1418
J	Dog	1316	110.66	110.66	110.66	332
NZ	Bags	310	4.66	2.33	0	28
NZ	Bags	1702	34.5	34.5	0	138
NZ	Oil	215	0.8	0.8	0	4
VN	Bear	2471	32.46	32.46	0	422
NZ	Light	1336	218	218	0	218
VN	Bear	5565	59.66	59.66	0	358
SGP	Bags	1273	33	33	0	132
J	Dog	1152	1118	1118	1118	1118
NZ	Bags	1584	4.66	4.66	0	28
NZ	Bags	147	2	2	0	4
NZ	Oil	2492	30	30	0	30
RC	Perfume	39	682	682	0	682
NZ	Clothes	3596	36	36	36	72
AUS	Bear	671	460.66	427.76	427.76	2764
AUS	Bags	3058	0.5	0.5	0.5	1
AUS	Perfume	993	25916	25916	25916	25916
PRC	Bags	8	1639.16	1639.16	1639.16	9835
PRC	Bear	536	2996.5	2996.5	2996.5	17979
NZ	Butter	2086	1217.25	1217.25	0	4869
RP	Bear	223	429	429	429	5148
RC	Bear	893	41	32.45	0	82
AUS	Shorts	1936	171	171	0	342
AUS	Butter	312	20	20	0	80
NZ	Bags	1234	8.33	8.33	0	25
NZ	Bear	2654	4.42	4.42	4.42	62
MAL	Bear	1751	562.55	562.55	562.55	5063
MAL	Bear	3340	1376.55	1376.55	0	12389
MAL	Bear	1064	10.8	10.8	10.8	108
RP	Bags	1573	1	1	0	4
AUS	Clothes	28	352	352	352	352
J	Bags	455	2.6	2.6	2.6	13
RC	Radio	391	30	30	0	60
NZ	Bear	888	13.56	13.56	13.56	217

I hope this is okay ? Sorry it took me quite awhile to prepare this

cderv · June 30, 2018, 6:28am

What is the results you are trying to get so that we could give you an example ?
Can you explain what you want to calculate ?
Sum of the 4 population columns by LAND and PRODUCT ?

A few advices regarding your code:

select do not need character you can select using autocompletion and variable name.

group_by knows the scope of your data.frame. You do not need the $ syntax. group_by(LAND, PRODUCT, KEY)

As you used the pipe %>%, the first data argument of summarise_at is already provided. Don't fill it. For variable, you need to provide as character in summarise_at or use vars (see help page). Then you need to apply a summarizing function!!

Telling us with word what you can do will help us understand.

menewtor · June 30, 2018, 9:09am

This is a summary of what I'm trying to achieve in R. I hope this snapshot helps.

Is there any difference in term of performance if I use the $syntax?

I've converted the "Population" into integer by running this

before running Group_By

My questions are, what do I need to do so that:

The population can be sum
How do I run Group_By to produce the final output

Thanks for the explanation, I really need to hit the books soon to understand the above

cderv · June 30, 2018, 4:24pm

Here is an example with your data to get sum of each population column by LAND and PRODUCT. Hopes it will help you understand how you could achieve what you want on your data.

Data in tibble format

REPORT <- tibble::tribble(
  ~LAND,  ~PRODUCT,  ~KEY, ~X1_POPULATION, ~X2_POPULATION, ~X3_POPULATION, ~X4_POPULATION,
  "SGP",     "Toy", 6700L,             26,             26,              0,            52L,
  "PRC",    "Bear",  653L,         219.66,         219.66,         219.66,          1318L,
  "NZ",     "Toy",  473L,           1.25,           1.25,              0,             5L,
  "AUS", "Clothes", 2710L,           3.75,           3.75,              0,            15L,
  "PRC",    "Bear",  431L,        4872.33,        4872.33,        4872.33,         29234L,
  "PRC",    "Bear",   89L,        1882.66,        1882.66,        1882.66,         11296L,
  "RI",    "Bear", 2505L,        1388.44,        1388.44,              0,         12496L,
  "PRC",    "Bear", 1181L,        4220.75,        4220.75,        4220.75,         50649L,
  "NZ",    "Bear",  656L,          75.66,          75.66,          75.66,           227L,
  "RI",    "Bear", 1795L,             28,             28,              0,            84L,
  "RC",  "Shorts", 1682L,           2023,           2023,           2023,          2023L,
  "AUS",     "Toy",  248L,             13,             13,              0,            39L,
  "NZ",    "Bags",  925L,           10.2,           10.2,              0,            51L,
  "PRC",    "Bear", 3391L,         5717.5,         5717.5,         5717.5,         34305L,
  "J",  "Shorts", 1185L,            0.5,            0.5,            0.5,             1L,
  "NZ",    "Bags", 1210L,              7,              7,              0,            28L,
  "AUS", "Sneaker",  213L,           12.5,           12.5,              0,            25L,
  "NZ",    "Bear",  708L,         108.21,         108.21,         108.21,          1515L,
  "AUS",     "Toy",  173L,          68.75,          68.75,          68.75,           275L,
  "NZ",    "Bear",  950L,           5.38,           5.38,           5.38,            97L,
  "J",    "Bags", 1706L,            3.2,            3.2,              0,            16L,
  "AUS",     "Tea", 1011L,          13035,          13035,          13035,         13035L,
  "AUS",     "Tea",  712L,         2303.5,         2303.5,         2303.5,          4607L,
  "NZ",  "Coffee",  118L,            942,            942,              0,           942L,
  "NZ",  "Shorts",  562L,           1600,           1600,           1600,          1600L,
  "RP",     "Tea",  711L,          127.5,          127.5,              0,           255L,
  "IND",     "Toy", 1887L,              5,              5,              0,            15L,
  "SGP",    "Bags",   32L,            4.5,            4.5,              0,            18L,
  "NZ",    "Bags", 1133L,          16.33,          16.33,              0,            49L,
  "J",     "Toy",  409L,            113,            113,            113,           113L,
  "NZ",    "Bags", 1173L,           26.5,           26.5,           26.5,            53L,
  "NZ",     "Tea",   93L,             39,             39,              0,            78L,
  "VN", "Sneaker",  219L,            122,            122,              0,           122L,
  "PRC",    "Bags",    7L,         7115.5,         7115.5,         7115.5,         28462L,
  "J", "Sneaker", 1910L,            955,            955,              0,           955L,
  "J",     "Tea", 1942L,          428.5,          428.5,              0,           857L,
  "J",     "Tea", 1231L,            129,            129,              0,           774L,
  "NZ",    "Bags",  511L,           9.33,           9.33,              0,            28L,
  "RC",    "Bear", 1491L,        4489.33,        4489.33,              0,         40404L,
  "NZ",     "Tea",  315L,             28,             28,              0,            28L,
  "NZ",     "Tea", 3005L,            351,            351,            351,           351L,
  "NZ",    "Bear",   53L,          11.08,          11.08,          11.08,           133L,
  "ROK",    "Milk", 1385L,           1495,           1495,              0,          1495L,
  "RC",  "Shorts",  807L,             50,             50,              0,            50L,
  "AUS", "Sneaker",  541L,             30,             30,             30,            30L,
  "AUS", "Sneaker",  437L,            295,            295,            295,           295L,
  "AUS",    "Bear", 2013L,         914.66,         914.66,         914.66,          2744L,
  "MAL",    "Bear", 1425L,           35.4,           35.4,              0,           177L,
  "NZ",     "Toy", 3309L,            0.8,            0.8,              0,             4L,
  "J",     "Tea", 1505L,         1595.5,         1595.5,              0,          3191L,
  "NZ",    "Bear",  115L,           8.22,           8.22,              0,            74L,
  "NZ",    "Bear",  916L,          29.37,          29.37,          29.37,           470L,
  "NZ",    "Bear", 2468L,           4.42,           4.42,           4.42,            62L,
  "AUS", "Clothes", 2309L,           2.14,           2.14,              0,            15L,
  "AUS",    "Bear",  780L,        4887.33,        4887.33,        4887.33,         29324L,
  "AUS",     "Egg",  102L,             28,             28,             28,            28L,
  "J",    "Milk",  943L,           3194,           3194,              0,          6388L,
  "J",     "Toy", 1473L,           27.5,           27.5,           27.5,            55L,
  "AUS", "Clothes", 2588L,           2.14,           2.14,              0,            15L,
  "RC",  "Butter", 1796L,       13542.66,       13542.66,       13542.66,         40628L,
  "NZ",    "Bear",  111L,          11.06,          11.06,          11.06,           177L,
  "AUS",     "Toy", 1105L,         245.14,         245.14,         245.14,          1716L,
  "THA",  "Butter",  331L,         503.66,         503.66,         503.66,          1511L,
  "RC", "Clothes", 1085L,         116.66,         116.66,              0,           700L,
  "AUS", "Clothes", 2732L,              1,              1,              0,             1L,
  "PRC",    "Bear",   51L,         747.66,         747.66,         747.66,          4486L,
  "SGP",    "Bear", 1051L,           0.22,           0.22,              0,             2L,
  "MAL",    "Bear",  895L,              2,              2,              2,             2L,
  "RC",     "Cat",  640L,          132.5,          132.5,              0,           530L,
  "AUS", "Clothes", 3001L,              1,              1,              0,             1L,
  "NZ",  "Butter",    4L,           18.5,           18.5,              0,            74L,
  "AUS",    "Bags",  402L,          849.6,          849.6,          849.6,          4248L,
  "RI",    "Bear",  428L,            198,            198,              0,           198L,
  "J",    "Bear", 2629L,          37.75,          37.75,          37.75,           151L,
  "AUS", "Clothes", 1493L,              5,              5,              0,            15L,
  "VN",    "Bear",  862L,         642.66,         642.66,              0,          5784L,
  "PRC",    "Bear",   30L,        5729.83,        5729.83,        5729.83,         34379L,
  "AUS",    "Bear",  200L,             25,             25,              0,            25L,
  "PRC",    "Bear", 1644L,        6315.33,        6315.33,        6315.33,         37892L,
  "NZ", "Clothes",  410L,            397,            397,              0,           794L,
  "AUS",  "Butter", 1661L,            438,            438,            438,           438L,
  "J",    "Bags",  486L,        3340.66,        2087.91,        2087.91,         10022L,
  "THA",     "Dog",  795L,           1303,           1303,              0,          1303L,
  "MAL", "Perfume",   14L,            258,            258,              0,           258L,
  "J",     "Dog", 1348L,              6,              6,              6,            18L,
  "RI",    "Bear", 1032L,         124.72,         124.72,         124.72,          1372L,
  "J",     "Dog", 1993L,           8395,           8395,           8395,          8395L,
  "PRC",     "Dog", 3318L,          13792,          13792,          13792,         13792L,
  "AUS",     "Oil", 2873L,           4829,           4829,           4829,          4829L,
  "J",    "Bear", 2006L,             36,             36,             36,           144L,
  "RC",     "Cat", 1545L,          19.33,          19.33,              0,            58L,
  "AUS",    "Bags",  142L,           1418,           1418,              0,          1418L,
  "J",     "Dog", 1316L,         110.66,         110.66,         110.66,           332L,
  "NZ",    "Bags",  310L,           4.66,           2.33,              0,            28L,
  "NZ",    "Bags", 1702L,           34.5,           34.5,              0,           138L,
  "NZ",     "Oil",  215L,            0.8,            0.8,              0,             4L,
  "VN",    "Bear", 2471L,          32.46,          32.46,              0,           422L,
  "NZ",   "Light", 1336L,            218,            218,              0,           218L,
  "VN",    "Bear", 5565L,          59.66,          59.66,              0,           358L,
  "SGP",    "Bags", 1273L,             33,             33,              0,           132L,
  "J",     "Dog", 1152L,           1118,           1118,           1118,          1118L,
  "NZ",    "Bags", 1584L,           4.66,           4.66,              0,            28L,
  "NZ",    "Bags",  147L,              2,              2,              0,             4L,
  "NZ",     "Oil", 2492L,             30,             30,              0,            30L,
  "RC", "Perfume",   39L,            682,            682,              0,           682L,
  "NZ", "Clothes", 3596L,             36,             36,             36,            72L,
  "AUS",    "Bear",  671L,         460.66,         427.76,         427.76,          2764L,
  "AUS",    "Bags", 3058L,            0.5,            0.5,            0.5,             1L,
  "AUS", "Perfume",  993L,          25916,          25916,          25916,         25916L,
  "PRC",    "Bags",    8L,        1639.16,        1639.16,        1639.16,          9835L,
  "PRC",    "Bear",  536L,         2996.5,         2996.5,         2996.5,         17979L,
  "NZ",  "Butter", 2086L,        1217.25,        1217.25,              0,          4869L,
  "RP",    "Bear",  223L,            429,            429,            429,          5148L,
  "RC",    "Bear",  893L,             41,          32.45,              0,            82L,
  "AUS",  "Shorts", 1936L,            171,            171,              0,           342L,
  "AUS",  "Butter",  312L,             20,             20,              0,            80L,
  "NZ",    "Bags", 1234L,           8.33,           8.33,              0,            25L,
  "NZ",    "Bear", 2654L,           4.42,           4.42,           4.42,            62L,
  "MAL",    "Bear", 1751L,         562.55,         562.55,         562.55,          5063L,
  "MAL",    "Bear", 3340L,        1376.55,        1376.55,              0,         12389L,
  "MAL",    "Bear", 1064L,           10.8,           10.8,           10.8,           108L,
  "RP",    "Bags", 1573L,              1,              1,              0,             4L,
  "AUS", "Clothes",   28L,            352,            352,            352,           352L,
  "J",    "Bags",  455L,            2.6,            2.6,            2.6,            13L,
  "RC",   "Radio",  391L,             30,             30,              0,            60L,
  "NZ",    "Bear",  888L,          13.56,          13.56,          13.56,           217L
)

library(tidyverse)
REPORT %>%
  group_by(LAND, PRODUCT) %>%
  summarise_at(vars(contains("POPULATION")), sum)
#> # A tibble: 54 x 6
#> # Groups:   LAND [?]
#>    LAND  PRODUCT X1_POPULATION X2_POPULATION X3_POPULATION X4_POPULATION
#>    <chr> <chr>           <dbl>         <dbl>         <dbl>         <int>
#>  1 AUS   Bags            2268.         2268.          850.          5667
#>  2 AUS   Bear            6288.         6255.         6230.         34857
#>  3 AUS   Butter           458           458           438            518
#>  4 AUS   Clothes          367.          367.          352            414
#>  5 AUS   Egg               28            28            28             28
#>  6 AUS   Oil             4829          4829          4829           4829
#>  7 AUS   Perfume        25916         25916         25916          25916
#>  8 AUS   Shorts           171           171             0            342
#>  9 AUS   Sneaker          338.          338.          325            350
#> 10 AUS   Tea            15338.        15338.        15338.         17642
#> # ... with 44 more rows

Created on 2018-06-30 by the reprex package (v0.2.0).

menewtor · July 2, 2018, 8:20am

Error in summarise_impl(.data, dots) : 
  Evaluation error: invalid 'type' (character) of argument.

I tried the about and I run into this error

I check the data type for "POPULATION" and those are showing as 'chr'. Must i convert the data type for "POPULATION" ?

Many Thanks

FlorianGD · July 2, 2018, 11:19am

Are you doing this in the same pipeline? I mean, are you doing something like

Report2 %>% mutate_at(c(19,20,21,22,23), as.integer)

Report2 %>%
  group_by(LAND, PRODUCT) %>%
  summarise_at(vars(contains("POPULATION")), sum)

Or

Report2 %>%
  mutate_at(19:23, as.integer) %>%
  group_by(LAND, PRODUCT) %>%
  summarise_at(vars(contains("POPULATION")), sum)

In the first one, Report2 is not modified in place, you will just print the result of the mutate. So, if you use it next, the Report2 object will still have character columns. In the second one, you pass the mutated object to the group_by and so it should work.

Another option would be to save the mutated object and reuse it afterwards:

Report2 <- Report2 %>%
  mutate(19:23, as.integer)

Report2 %>%
  group_by(LAND, PRODUCT) %>%
  summarise_at(vars(contains("POPULATION")), sum)

Note that in @cderv answer, the tibble is already with the POPULATION columns as integer, that's why he doesn't need to mutate it before grouping and summarising.

menewtor · July 3, 2018, 8:31am

@FlorianGD,

Thanks

I'm using this but i'm getting an error;

Error in mutate_impl(.data, dots) :
Evaluation error: object 'NA' not found.

I assume i need to convert the "NA" into 0 before running the "Summarise_at" , right ?
If so, let me try it out myself and see if that going to work

jcblum · July 11, 2018, 10:57am

It's probably a better idea to pass the appropriate na.rm parameter to sum(), e.g.,

Report2 %>% 
  mutate_at(19:23, as.integer) %>% 
  group_by(LAND, PRODUCT) %>% 
  summarise_at(vars(contains("POPULATION")), sum, na.rm = TRUE)

Without na.rm = TRUE, any NA values will make your entire sum NA:

sum(c(1, 2, NA))
#> [1] NA

sum(c(1, 2, NA), na.rm = TRUE)
#> [1] 3

menewtor · July 13, 2018, 12:14am

Thanks @jcblum,
I'll try your method after this post

Been busy with other stuff but here what happened, I keep running into this error;

Error in mutate_impl(.data, dots) :
Evaluation error: object 'NA' not found.

I went back to check the data set and there is no Blank or NA values, only 0.

Does R treat 0 as NA when sum or just 0 ?

jcblum · July 17, 2018, 10:36pm

Sorry I missed this update!

Can you post the code you ran that gave this error? The error implies that R thinks you're referencing a non-existent object named NA, not an NA value in a data frame, so I think there must be some sort of typo somewhere...

0 is treated as 0 when summing.

menewtor · July 30, 2018, 7:45am

@jcblum,
Sorry for the late reply... I was busy with the reporting thus i dont have time to check back in.

I run the new line

sum, na.rm = TRUE)

it still show the error

Error in mutate_impl(.data, dots) : 
  Evaluation error: object 'NA' not found.

But now seem like the numbers are adding up but let me check the no. first and i'll post the update here

Sorry for the late reply

menewtor · August 20, 2018, 8:19am

Quick Update. I think is better if i start reading the book again.

Somehow the dataframe just don't group based on what i want and the numbers are still showing as Characters

Is there any good link to start afresh ?

jcblum · August 20, 2018, 10:37pm

I’m afraid I’m not quite sure what you mean. If you’re still trying to solve the same problem, the best thing you can do is post all of the code you are running. Excerpts of code really aren’t very helpful, since errors are usually due to the interaction of different parts.

Have you seen our guidelines for posting code-related questions here? FAQ: Tips for writing R-related questions

Also, since you said you had an Excel background, it occurred to me that this book might help you. It is currently available as a free preview in advance of publication:

It covers the general kind of thing you’re trying to do in some detail.

menewtor · August 20, 2018, 11:33pm

Thanks @jcblum,

Yeah, is the same problem but i just cant seem to do the grouping and convert some of the characters into integers.
Since i cant do both, i guess is best i start from scratch again and thanks for the link

As for the code, yeah, those are pretty much it since those are the beginning.
Not too sure if the original file structure have some issue or etc but my colleague manage to do it using the same file but she didnt use Tidyverse.

I can post the original code again later in the day