Automate coding (sum) in R

Question

First at all I would like to apologise if I did not use the correct jargon.

I have the dataset as below which contains a wide range of categories

Here some excerpt from dput (using droplevels)

structure(list(
x = c(2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 2010L, 
2010L, 2010L), *[ME: there are more years than 2010...]*
y = c(7.85986, 185.81068, 107.24097, 7094.74649, 
1.4982, 185.77319, 5090.79354, 167.58584, 4189.64609, 157.08277, 
3927.06932, 2.86732, 71.683, 4.70123, 117.53085, 2.93452, 73.36292, 
1.4982, 18.18734, 901.14744, 0.90268, 13.77532, 613.38298, 0.01845, 
0.0681, 7.19925, 3.75315, 0.14333, 136.54008, 0.04766, 0.59077, 
28.97255, 0.38608, 115.05258, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), 
x1 = structure(c(4L, 2L, 3L, 1L, 4L, 2L, 1L, 2L, 1L, 2L, 
1L, 2L, 1L, 2L, 1L, 2L, 1L, 4L, 2L, 1L, 4L, 2L, 1L, 4L, 2L, 
1L, 2L, 4L, 1L, 4L, 2L, 1L, 4L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 
2L, 1L, 2L, 1L), .Label = c("All greenhouse gases - (CO2 equivalent)", 
"CH4", "CO2", "N2O"), class = "factor"), 
x2 = structure(c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L), .Label = "Austria",         
class = "factor"), 
x4 = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 4L, 
4L, 5L, 5L, 6L, 6L, 7L, 7L, 8L, 8L, 8L, 9L, 9L, 9L, 10L, 
10L, 10L, 11L, 11L, 11L, 12L, 12L, 12L, 13L, 13L, 14L, 14L, 
15L, 15L, 16L, 16L, 17L, 17L, 18L, 18L), .Label = c("3", 
"3.1", "3.A", "3.A.1", "3.A.2", "3.A.3", "3.A.4", "3.B", 
"3.B.1", "3.B.2", "3.B.3", "3.B.4", "3.B.5", "3.C", "3.C.1", 
"3.C.2", "3.C.3", "3.C.4"), class = "factor")), class = "data.frame",     
row.names = c(NA, 
-44L))

I want to know whether the of the sum of subcategories in x4 (e.g. 3.B.1+3.B.2+...+3.B.n) equal the figure stated in the parent category (e.g. 3.B). (i.e. the in the csv stated sum) for a given year and country. I want to verify the sums.

For get the sum of the subcategories I have this

sum(df$y[df$x4 %in% c("3.A.1", "3.A.2", "3.A.3", "3.A.4") & x == 
"2010" & x2 == "Austria"])

To receive the sum of the parent category I have this

sum(df$y[df$x4 %in% c("3.A") & x == "2010" & x2 == "Austria"])

Next I would need an operation which checks whether the results of both codes are equal (True/false). However, I have more than 20 countries, 20 years, dozens of categories to check. With my newby approach I would be writing code for ages...

is there anyway to automate this? Basically, I am looking for a code which is able to do the following

1) Run for one category, go to next one 2) once done with categories change year and start again with categories 3) ... same for countries....

Any sort of help would be appreciated and even a suggestions how to use the right jargon in the title. Thanks in any case

Please do use dput - it'll make it much easier to understand and reproduce your issue. You can do `dput(droplevels(head(YourDF)))` so that excess levels don't get shown. — iod, Oct 02 '18 at 15:09
When asking for help, you should include a simple [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) with sample input and desired output that can be used to test and verify possible solutions. Pictures of data are not helpful. If your real data has "too many levels" provide a simpler sample data set that can be included in the question. Also it seems off you have `x==` and `x2==` rather than `df$x==` and `df$x2==`. Do you have the variables `x` and `x2` defined elsewhere? — MrFlick, Oct 02 '18 at 15:10
@doviod, thank you for your reply. I've added the dput results — Nordsee, Oct 02 '18 at 15:18
That's great. Note that all your y's are now zero for some reason. Also, to make it reproducible, you might want to select data that includes one parent category and all its children. — iod, Oct 02 '18 at 15:39

iod · Accepted Answer · 2018-10-02T17:00:01.093

Here's a potential solution using dplyr (might require some tweaking based on the full dataset):

require(dplyr)
# Create two columns - one that shows only the parent category number, and one that tells you if it's a parent or child; note that the regex here makes some assumptions on the format of your data.
mutate(df,parent=gsub("(.?\\..?)\\..*", "\\1", df$x4), 
  type=ifelse(parent==x4,"Parent","Child")) %>% 
# Sum the children y's by category, year and country
group_by(parent, type, x, x2) %>% 
summarize(sum(y)) %>% 
# See if the sum of the children is equal to the parent y
tidyr::spread(type,`sum(y)`) %>%
mutate(equals=isTRUE(all.equal(Child,Parent)))

Result using your (new) data:

  parent     x x2      Child Parent equals
  <chr>  <int> <fct>   <dbl>  <dbl> <lgl> 
1 3       2010 Austria   NA   7396. FALSE 
2 3.1     2010 Austria   NA   5278. FALSE 
3 3.A     2010 Austria 4357.  4357. TRUE  
4 3.B     2010 Austria  921.   921. TRUE  
5 3.C     2010 Austria    0      0  TRUE

I can see from your new data that you have two levels of parents. My solution will only work for the second level (e.g. 3.1 and its children), but can be easily tweaked to also work for the top level.

Excellent, very, very much appreciated! – Nordsee Oct 02 '18 at 18:20 — Nordsee, Oct 02 '18 at 18:20

Automate coding (sum) in R

1 Answers1