How to add a total column in last row in R dataframe having value with %

Question

I have below-mentioned dataframe in R:

country    product          profit_perc
USA        100              15.2%
Canada     10               10.25%
Russia     50               19.72%
Japan      30               25.23%

Required dataframe:

country    product          profit_perc
USA        100              15.2%
Canada     10               10.25%
Russia     50               19.72%
Japan      30               25.23%
Total      190              70.4%

How is your dataframe storing "15.2%"? Is it storing 15.2 and then using a format function to provide the "%"? — John Garland, Apr 22 '20 at 15:18
@JohnGarland: No, I'm fetching these details from MySQL table which has value in this format. — user9211845, Apr 22 '20 at 15:22

desval · Answer 1 · 2020-04-22T15:30:18.560

1

library(data.table)
d <- read.table(text = "
country    product          profit_perc
USA        100              15.2
Canada     10               10.25
Russia     50               19.72
Japan      30               25.23", header=T
)

d <- setDT(d)
vars <- c("product", "profit_perc")

d <- rbindlist(list(
  d,
  d[,  lapply(.SD, sum), .SDcols=vars ][,country := "Tot"]
), use.names = T)

   country product profit_perc
1:     USA     100       15.20
2:  Canada      10       10.25
3:  Russia      50       19.72
4:   Japan      30       25.23
5:     Tot     190       70.40

You can also pass the index of the columns directly:

d <- rbindlist(list(
  d,
  d[,  lapply(.SD, sum), .SDcols=c(2:3) ][,country := "Tot"]
), use.names = T)

edited Apr 22 '20 at 15:30

answered Apr 22 '20 at 15:15

desval

2,345
2
16
23

When I'm passing the columns like `Vars<-c(2:6)` it is not working. – user9211845 Apr 22 '20 at 15:25
Getting error `Error in FUN(X[[i]], ...) : invalid 'type' (character) of argument`. – user9211845 Apr 22 '20 at 15:25
Why 2:6?, I only see 2 columns – desval Apr 22 '20 at 15:29
for the example point of view, I have mentioned only two columns. Though, I have manually entered the column name but still getting the same error. – user9211845 Apr 22 '20 at 15:31
it very difficult to help if you dont show what you have tried. Note that in my code I used vars and not Vars. It can be either a vector of names, or a vector in column indices. – desval Apr 22 '20 at 15:41

How to add a total column in last row in R dataframe having value with %

1 Answers1