Calculate percent from row total in R-CodePudding

I have this table:

group	May 1990	Jun 1990	Jul 1990
1	581	552	465
2	193	184	176
3	207	177	165
Total	981	913	806

I want to calculate percent on row level from the row total.

group	May 1990	Jun 1990	Jul 1990
1	0.59	0.60	0.58
2	0.19	0.21	0.22
3	0.21	0.19	0.20
Total	1	1	1

I got this far for now, but is not what I want.

df <- data.frame(group=c('1','2','3','Total'),may_1990=c(581,193,207,981),jun_1990=c(552,184,177,913),jul_1990=c(465,176,165,806))

total <- df %>% slice_tail(n = 1)
z <- df %>% rowwise() %>% mutate(across(where(is.numeric), ~ .x/total[-1]))

CodePudding user response：

With across:

library(dplyr)
df %>% 
  mutate(across(where(is.numeric), ~ .x / .x[group == "Total"]))

  group  may_1990  jun_1990  jul_1990
1     1 0.5922528 0.6046002 0.5769231
2     2 0.1967380 0.2015334 0.2183623
3     3 0.2110092 0.1938664 0.2047146
4 Total 1.0000000 1.0000000 1.0000000

With the nature of your data, this could also work if you prefer base R:

df[-1] <- sapply(df[-1], proportions) * 2

CodePudding user response：

I think the easy way to achieve this kind of table is to use table() function:

df <- data.frame(group=c('1','2','3','Total'),may_1990=c(581,193,207,981),jun_1990=c(552,184,177,913),jul_1990=c(465,176,165,806))

# Compute proportions for the central data
prop = proportions(as.matrix(df[-4,-1]), 2)

# Add total at the column level (margin = 1)
prop = addmargins(prop, 1)

# Create the final table
df_end = data.frame(
              group=c('1','2','3','Total'),
              prop
       
)

You obtain this:

    group  may_1990  jun_1990  jul_1990
1       1 0.5922528 0.6046002 0.5769231
2       2 0.1967380 0.2015334 0.2183623
3       3 0.2110092 0.1938664 0.2047146
Sum Total 1.0000000 1.0000000 1.0000000

CodePudding user response：

We may use

df[-1] <-  df[-1]/df[df$group == "Total", -1][col(df[-1])]

-output

> df
  group  may_1990  jun_1990  jul_1990
1     1 0.5922528 0.6046002 0.5769231
2     2 0.1967380 0.2015334 0.2183623
3     3 0.2110092 0.1938664 0.2047146
4 Total 1.0000000 1.0000000 1.0000000