Calculate percentage of a two columns

Question

I want to calculate percentage for a data frame which is like

day  hour  place  t1   t2
___  ____  _____  __  ___
 1    0      1     5   10
 1    0      2     6   12
 1    0      3     9   8
 1    1      1     6   12    
 1    1      2     8   15  
 1    1      3     10  18 
 1    2      1     5   1
 1    2      2     6   12
 1    2      3     9   1
 1    3      1     8   10
 1    3      2     8   2
 1    3      3     9   8
 2    0      1     5   1
 2    0      2     6   12
 2    0      3     9   8
 2    1      1     9   10
 2    1      2     6   12
 2    1      3     9   8
 2    2      1     5   10
 2    2      2     6   12
 2    2      3     9   18
 2    3      1     5   0
 2    3      2     6   2
 2    3      3     9   18

I want to calculate more two columns which will calculate the percentage of t1 and t2 by hour for example I want

day  hour  place  t1   t2    t1%     t2%
___  ____  _____  __  ___    ___     ___
 1    0      1     5   10    (5/20)  (10/30)
 1    0      2     6   12    (6/20)  (12/30)
 1    0      3     9   18    (9/20)  (18/30)
 1    1      1     6   12    (12/24) (12/45)
 1    1      2     8   15    (15/24) (15/45)
 1    1      3     10  18    (18/24) (18/45)

That means the t1 divided by the sum of t1 for the day and hour of each places.I know to take sum for whole column but I want to take sum for each hour and day. Any help is appreciated.

Haboryme · Accepted Answer · 2017-01-20T07:20:46.300

4

You could simply do:

library(tidyverse)
df%>%
  group_by(day,hour)%>%
  mutate("t1%"=t1/sum(t1),"t2%"=t2/sum(t2))

You need to use group_by() for days and hours so that only hours from a single day are grouped together.

edited Jan 20 '17 at 07:20

answered Jan 20 '17 at 07:05

Haboryme

4,611
2
18
21

I think you have a typo with `t1/sum(t2)` – akrun Jan 20 '17 at 07:15

score 2 · Answer 2 · answered Jan 20 '17 at 07:17

2

We can use the base R option of ave and group them by day and hour

df$t1perc <- ave(df$t1, df$day, df$hour, FUN = function(x) x/sum(x))
df$t2perc <- ave(df$t2, df$day, df$hour, FUN = function(x) x/sum(x))

answered Jan 20 '17 at 07:17

Ronak Shah

377,200
20
156
213

score 2 · Answer 3 · answered Jan 20 '17 at 07:18

2

Here is a base R option using ave and prop.table,

data.frame(df, sapply(df[, c("t1","t2")], function(i)
                                          ave(i, df$day, df$hour, FUN = prop.table)))

answered Jan 20 '17 at 07:18

Sotos

51,121
6
32
66

score 1 · Answer 4 · answered Jan 20 '17 at 07:14

Here is a similar option using data.table syntax. Convert the 'data.frame' to 'data.table', grouped by 'day', 'hour', specify the columns of interest in .SDcol, loop through those (lapply(..) , do the calculation, and assign (:=) it to create new columns

library(data.table)
setDT(df)[, paste0(names(df)[4:5], "_perc") := 
    lapply(.SD, function(x) x/sum(x)), .(day, hour), .SDcols = t1:t2]

Calculate percentage of a two columns

4 Answers4