Add column that is the sum of other columns-CodePudding

Input data:

Director= c("Director A", "Director B", "Director C")
Salary = c(40000, 35000, 50000)
Listed boards = c(1, 0, 3)
Unlisted boards = c(4, 2, 6)
Other boards = c(2, 3, 3)
Number of qualifications = c(1, 2, 1)

df_directors = data.frame(Director, Salary, Listed boards, Unlisted boards, Other boards, Number of qualifications)

Now I want to create an extra colum that sums and replaces the three columns with regards to boards. So my output data should look like this:

Director         Salary         Boards         Number of qualifications
Director A       40000          7              1
Director B       35000          5              2
Director C       50000          12             1

This code somehow doesn't work.

df_directors <- df_directors %>%
  rowwise() %>%
  mutate(rBoards = sum(c_across(all_of(Listed Boards:Other Boards))))

CodePudding user response：

We could use rowSums with select:

library(dplyr)

df_directors %>% 
  transmute(Director, Salary, Boards = rowSums(select(., contains("boards"))),Number_of_qualifications)

First solution:

library(dplyr)

df_directors %>% 
  mutate(Boards = rowSums(select(., contains("boards")))) %>% 
  select(1,2,Boards,6)

    Director Salary Boards Number_of_qualifications
1 Director A  40000      7                        1
2 Director B  35000      5                        2
3 Director C  50000     12                        1

CodePudding user response：

We could use rowSums from base R

library(dplyr)
df_directors %>% 
  transmute(Director, Salary, 
   Boards = rowSums(across(ends_with('boards')), na.rm = TRUE),
        `Number of qualifications`)

-output

    Director Salary Boards Number of qualifications
1 Director A  40000      7                        1
2 Director B  35000      5                        2
3 Director C  50000     12                        1

In the OP's code, it needs backquote when there are spaces in the column names

 df_directors %>%
  rowwise() %>%
  mutate(rBoards = sum(c_across(`Listed boards`:`Other boards`))) %>%
   ungroup

-output

# A tibble: 3 × 7
  Director   Salary `Listed boards` `Unlisted boards` `Other boards` `Number of qualifications` rBoards
  <chr>       <dbl>           <dbl>             <dbl>          <dbl>                      <dbl>   <dbl>
1 Director A  40000               1                 4              2                          1       7
2 Director B  35000               0                 2              3                          2       5
3 Director C  50000               3                 6              3                          1      12

data

df_directors <- structure(list(Director = c("Director A", "Director B", "Director C"
), Salary = c(40000, 35000, 50000), `Listed boards` = c(1, 0, 
3), `Unlisted boards` = c(4, 2, 6), `Other boards` = c(2, 3, 
3), `Number of qualifications` = c(1, 2, 1)), 
class = "data.frame", row.names = c(NA, 
-3L))

CodePudding user response：

rowSums and grep for identification.

cbind(df_directors[1:2], Boards=rowSums(df_directors[grep('boards$', names(df_directors))]), df_directors[ncol(df_directors)])
#     Director Salary Boards Number.of.qualifications
# 1 Director A  40000      7                        1
# 2 Director B  35000      5                        2
# 3 Director C  50000     12                        1

Data:

df_directors <- structure(list(Director = c("Director A", "Director B", "Director C"
), Salary = c(40000, 35000, 50000), Listed.boards = c(1, 0, 3
), Unlisted.boards = c(4, 2, 6), Other.boards = c(2, 3, 3), Number.of.qualifications = c(1, 
2, 1)), class = "data.frame", row.names = c(NA, -3L))