Mutate multiple columns in a dataframe

by

Update (as of the 18th of March, 2019)

There has been a change. We have been using funs() in .funs (funs(name = f(.)). But this is changed (dplyr 0.8.0 above). Instead of funs, now we use list (list(name = ~f(.))). See the following new examples.

bankdata %>%
mutate_at(.funs = list(toAsset = ~./totass), .vars = vars(cash:loans))

bankdata %>%
mutate_at(.funs = list(toAsset = ~./totass), .vars = c("cash", "bond", "loans"))

bankdata %>%
mutate_at(.funs = list(toAsset = ~./totass), .vars = 5:7)

Update (as of the 2nd of December, 2017)

Since I answered this question, I have realized that some SO users have been checking this answer. The dplyr package has changed since then. Therefore, I leave the following update. I hope this will help some R users to learn how to use mutate_at().

mutate_each() is now deprecated. You want to use mutate_at(), instead. You can specify which columns you want to apply your function in .vars. One way is to use vars(). Another is to use a character vector containing column names, which you want to apply your custom function in .fun. The other is to specify columns with numbers (e.g., 5:7 in this case). Note that, if you use a column for group_by(), you need to change the numbers of column positions. Have a look of this question.

bankdata %>%
mutate_at(.funs = funs(toAsset = ./totass), .vars = vars(cash:loans))

bankdata %>%
mutate_at(.funs = funs(toAsset = ./totass), .vars = c("cash", "bond", "loans"))

bankdata %>%
mutate_at(.funs = funs(toAsset = ./totass), .vars = 5:7)

#  bankname bankid year   totass   cash   bond loans cash_toAsset bond_toAsset loans_toAsset
#1   Bank A      1 1881   244789   7250  20218 29513   0.02961734  0.082593581    0.12056506
#2   Bank B      2 1881   195755  10243 185151  2800   0.05232561  0.945830247    0.01430359
#3   Bank C      3 1881   107736  13357 177612    NA   0.12397899  1.648585431            NA
#4   Bank D      4 1881   170600  35000  20000  5000   0.20515826  0.117233294    0.02930832
#5   Bank E      5 1881 32000000 351266 314012    NA   0.01097706  0.009812875            NA

I purposely gave toAsset to the custom function in .fun since this will help me to arrange new column names. Previously, I used rename(). But I think it is much easier to clean up column names with gsub() in the present approach. If the above result is saved as out, you want to run the following code in order to remove _ in the column names.

names(out) <- gsub(names(out), pattern = "_", replacement = "")

Original answer

I think you can save some typing in this way with dplyr. The downside is you overwrite cash, bond, and loans.

bankdata %>%
    group_by(bankname) %>%
    mutate_each(funs(whatever = ./totass), cash:loans)

#  bankname bankid year   totass       cash        bond      loans
#1   Bank A      1 1881   244789 0.02961734 0.082593581 0.12056506
#2   Bank B      2 1881   195755 0.05232561 0.945830247 0.01430359
#3   Bank C      3 1881   107736 0.12397899 1.648585431         NA
#4   Bank D      4 1881   170600 0.20515826 0.117233294 0.02930832
#5   Bank E      5 1881 32000000 0.01097706 0.009812875         NA

If you prefer your expected outcome, I think some typing is necessary. The renaming part seems to be something you gotta do.

bankdata %>%
    group_by(bankname) %>%
    summarise_each(funs(whatever = ./totass), cash:loans) %>%
    rename(cashtoAsset = cash, bondtoAsset = bond, loanstoAsset = loans) -> ana;
    ana %>%
    merge(bankdata,., by = "bankname")

#  bankname bankid year   totass   cash   bond loans cashtoAsset bondtoAsset loanstoAsset
#1   Bank A      1 1881   244789   7250  20218 29513  0.02961734 0.082593581   0.12056506
#2   Bank B      2 1881   195755  10243 185151  2800  0.05232561 0.945830247   0.01430359
#3   Bank C      3 1881   107736  13357 177612    NA  0.12397899 1.648585431           NA
#4   Bank D      4 1881   170600  35000  20000  5000  0.20515826 0.117233294   0.02930832
#5   Bank E      5 1881 32000000 351266 314012    NA  0.01097706 0.009812875           NA

More Related Contents:

  1. Why does summarize or mutate not work with group_by when I load `plyr` after `dplyr`?
  2. How to create a lag variable within each group?
  3. Why are my dplyr group_by & summarize not working properly? (name-collision with plyr)
  4. Filter data frame by character column name (in dplyr)
  5. How to number/label data-table by group-number from group_by?
  6. Remove duplicated rows using dplyr
  7. dplyr summarise: Equivalent of “.drop=FALSE” to keep groups with zero length in output
  8. dplyr: “Error in n(): function should not be called directly”
  9. dplyr: inner_join with a partial string match
  10. Can dplyr summarise over several variables without listing each one? [duplicate]
  11. Change value of variable with dplyr
  12. sample rows of subgroups from dataframe with dplyr
  13. Assigning Dates to Fiscal Year
  14. filter function in dplyr errors: object ‘name’ not found
  15. Use rle to group by runs when using dplyr
  16. Number of significant digits in dplyr summarise
  17. Calculate group mean while excluding current observation using dplyr
  18. Emulate split() with dplyr group_by: return a list of data frames
  19. Sparklyr: how to center a Spark table based on column?
  20. R: x ‘probs’ outside [0,1]
  21. dplyr::mutate to add multiple values
  22. Tilde Dot in R (~.)
  23. Changing factor levels with dplyr mutate
  24. Conditionally Count in dplyr
  25. How to filter data without losing NA rows using dplyr
  26. How to replace empty string with NA in R dataframe?
  27. Adding prefix or suffix to most data.frame variable names in piped R workflow
  28. Add margin row totals in dplyr chain
  29. Why does as_tibble() round floats to the nearest integer?
  30. How to pass column name as argument to function for dplyr verbs?

Leave a Comment