Recalculate columns values based on data from another column-CodePudding

I have a sample of data that consists of time spent for some tasks in each month for several people. There is also a column "department". I need to take the value of column department and devide its value equally for each person

Date    department  name1   name2   name3   name4   name5   name6   name7
2022/05 117.89     85.37    30.30   108.86  14.67   23.90   77.62   23.30
2022/06 69.25      44.00    85.00   113.20  16.28   20.40   56.97   77.85
2022/07 73.55      37.36    85.00   113.20  16.28   35.68   74.86   43.34
2022/08 30.89      41.34    36.34   184.90  20.70   58.79   30.34   72.38

And I need to get

Date              name1              name2              name3   etc.
2022/05.          85.37 (117.89/7)  30.30 (117.89/7)    108.86 (117.89/7)

I am trying to use function lapply but seems that i am doing smth wrong )

mydata6 <- lapply(mydata4[3:15], function(x)  {x   x[1]/13} )

CodePudding user response：

Using the input shown reproducibly in the Note at the end, cbind the Date column (column 1) to the name... columns (all except the first 2) plus the department column divided by 7.

cbind(DF[1], DF[-(1:2)]   DF$department/7)
##      Date     name1    name2    name3    name4    name5    name6    name7
## 1 2022/05 102.21143 47.14143 125.7014 31.51143 40.74143 94.46143 40.14143
## 2 2022/06  53.89286 94.89286 123.0929 26.17286 30.29286 66.86286 87.74286
## 3 2022/07  47.86714 95.50714 123.7071 26.78714 46.18714 85.36714 53.84714
## 4 2022/08  45.75286 40.75286 189.3129 25.11286 63.20286 34.75286 76.79286

Either of these variations would also work.

replace(DF, -(1:2), DF[-(1:2)]   DF$department/7)[-2]

replace(DF[-2], -1, DF[-(1:2)]   DF$department/7)

Double check the first 3 name values in the first row:

c(85.37 (117.89/7), 30.30 (117.89/7), 108.86 (117.89/7))
## [1] 102.21143  47.14143 125.70143

Note

DF <-
structure(list(Date = c("2022/05", "2022/06", "2022/07", "2022/08"
), department = c(117.89, 69.25, 73.55, 30.89), name1 = c(85.37, 
44, 37.36, 41.34), name2 = c(30.3, 85, 85, 36.34), name3 = c(108.86, 
113.2, 113.2, 184.9), name4 = c(14.67, 16.28, 16.28, 20.7), name5 = c(23.9, 
20.4, 35.68, 58.79), name6 = c(77.62, 56.97, 74.86, 30.34), name7 = c(23.3, 
77.85, 43.34, 72.38)), class = "data.frame", row.names = c(NA, 
-4L))

CodePudding user response：

In the tidyverse, this is a job for across().

library(tidyverse)

d %>% 
  mutate(
    across(
      starts_with("name"), 
      function(x) x   department/7
    )
  )
     Date department     name1    name2    name3    name4    name5    name6    name7
1 2022/05     117.89 102.21143 47.14143 125.7014 31.51143 40.74143 94.46143 40.14143
2 2022/06      69.25  53.89286 94.89286 123.0929 26.17286 30.29286 66.86286 87.74286
3 2022/07      73.55  47.86714 95.50714 123.7071 26.78714 46.18714 85.36714 53.84714
4 2022/08      30.89  45.75286 40.75286 189.3129 25.11286 63.20286 34.75286 76.79286