CODEWITHSUNDEEP
rcrosstab
Agarp
Agarp

Reputation: 433

R: Regrouping rows in a table

I have the following table in R. It shows crude oil imports in Thousand Barrels. Columns are the years, and rows the month of each year:

> oil
     Year
Month   2014   2015   2016   2017
   1  288458 293297 300915 331240
   2  256340 259626 291915 281094
   3  286934 298196 310038 311840
   4  288002 281216 294659 307314
   5  291004 294570 315600 329468
   6  265109 288139 301625 307190
   7  294363 296712 326494 305336
   8  288878 305609 319990      0
   9  275435 280736 305981      0
   10 276658 274087 300671      0
   11 270260 274532 308776      0
   12 291463 302014 303563      0

This is the tables class:

> class(oil)
[1] "xtabs" "table"

I would like to to regroup it into quarters instead of months:

Quarter   2014   2015   2016   2017
       1  288458 293297 300915 331240
       2  256340 259626 291915 281094
       3  286934 298196 310038 311840
       4  288002 281216 294659 307314

Note this are not actual quarterly numbers, I am using them for illustration.

What is the best way to do this?

Upvotes: 1

Views: 222

Answers (2)

IceCreamToucan
IceCreamToucan

Reputation: 28705

With dplyr, if your data is in a data.frame you can do

oil %>% 
  group_by(quarter = ceiling(Month/3)) %>% 
  select(-Month) %>% 
  summarise_each(funs(sum))

this works with oil as

oil <- fread('Month   2014   2015   2016   2017
   1  288458 293297 300915 331240
   2  256340 259626 291915 281094
   3  286934 298196 310038 311840
   4  288002 281216 294659 307314
   5  291004 294570 315600 329468
   6  265109 288139 301625 307190
   7  294363 296712 326494 305336
   8  288878 305609 319990      0
   9  275435 280736 305981      0
   10 276658 274087 300671      0
   11 270260 274532 308776      0
   12 291463 302014 303563      0', header = T)

Upvotes: 0

LAP
LAP

Reputation: 6695

You can define the interval (1:3, 4:6, 7:9, 10:12) in a list, then lapply colSums over it to sum every three rows, and afterwards rbind the output of this with do.call.

data(iris)

mytable <- with(iris, table(Sepal.Length, Species))
mytable <- mytable[1:12,]


> mytable
            Species
Sepal.Length setosa versicolor virginica
         4.3      1          0         0
         4.4      3          0         0
         4.5      1          0         0
         4.6      4          0         0
         4.7      2          0         0
         4.8      5          0         0
         4.9      4          1         1
         5        8          2         0
         5.1      8          1         0
         5.2      3          1         0
         5.3      1          0         0
         5.4      5          1         0

mylist <- list(1:3, 4:6, 7:9, 10:12)

quartertable <- do.call(rbind, lapply(mylist, function(x) colSums(mytable[x,])))

> quartertable
     setosa versicolor virginica
[1,]      5          0         0
[2,]     11          0         0
[3,]     20          4         1
[4,]      9          2         0

For your example this would be:

mylist <- list(1:3, 4:6, 7:9, 10:12)

oil_quarters <- do.call(rbind, lapply(mylist, function(x) colSums(oil[x, ])))

Upvotes: 1

Related Questions