Previously, I have used the adorn_totals function from the janitor package to add row and columns totals to a data.frame. The content of the data.frame was count, so that was fine:
RegionSize
size KUALA LUMPUR OTHERS PENANG SELANGOR DARUL EHSAN TOTAL
tiny 20 3 1 0 24
small 164 120 20 90 394
medium 100 69 0 0 169
big 0 293 106 0 399
TOTAL 284 485 127 90 986
Now I am working on a data.frame, where each cell is a proportion. I need to have the total row and also column, but the adorn_totals cannot be used in that case, because the order of operations return wrong values.
So I have three separate tables:
Summary
size KUALA LUMPUR OTHERS PENANG SELANGOR DARUL EHSAN
1 tiny 0.000 0.000 0.000 0.000
2 small 435.344 245.598 333.317 272.342
3 medium 187.874 9656.649 0.000 0.000
4 big 0.000 116.861 138.366 0.000
RegionTotals
experience region1
1: 156.523 PENANG
2: 272.342 SELANGOR DARUL EHSAN
3: 343.998 KUALA LUMPUR
4: 296.601 OTHERS
SizeTotals
experience size
1: 0.000 tiny
2: 348.692 small
3: 136.207 big
4: 223.415 medium
overallTotal
experience
1: 276.613
How can i achieve the following result, considering that the regions and sizes may change? For example sometimes i will not have the region "OTHERS" or the size "BIG" depending on the data that i get. How can I achieve this:
Summary
size KUALA LUMPUR OTHERS PENANG SELANGOR DARUL EHSAN TOTAL
1 tiny 0.000 0.000 0.000 0.000 0.000
2 small 435.344 245.598 333.317 272.342 348.692
3 medium 187.874 9656.649 0.000 0.000 223.415
4 big 0.000 116.861 138.366 0.000 136.207
5. TOTAL 343.998 296.601 156.523 272.342 276.613
You can use rbind to add a row to your data frame, e.g.:
total <- c(size="TOTAL", apply(x[,-1], FUN=sum, MAR=2)
rbind(x, total)
In the same way, you can use cbind to add a column. The only tricky part is that you must omit the non-numeric columns from the summation: that's why I use the index -1, which removes the first column.