Compare values of a row with a value and return the smaller value

user_8362010 0
Viewed 46

I have a dataframe with several rows and column and I want to check for each row if the row values are higher than the value in the last column of the row. My data looks as follows:

        date      ABI.BR      NOVN.S       ROG.S      NESN.S   NOVOb.CO    HRMS.PA      TTEF.PA     OREP.PA    SASY.PA    LVMH.PA     MAX 
1 31.12.2001  7342987743 21072681660 19737660401 57324018814 3199830778 1226900000 105895000000 13740400000 6.4880e+09 1.2229e+10 163033294916
2 31.12.2002  6280730128 17748921146 20316428627 61501808862 3350848339 1242300000  92108991318 14288000000 7.4480e+09 1.2693e+10 144604417185
3 31.12.2003  6324404232 19755193920 20002601887 56367998444 3510991763 1230000000  93961037997 14029100000 8.0480e+09 1.1962e+10 122995279989
4 31.12.2004  7850834622 20947725570 19134043533 54889985325 3905732023 1331400000  87346032957 13641300000 1.5733e+10 1.2481e+10 123515812436
5 31.12.2005 12233279545 26451714210 22833829836 58587603997 4527870421 1427400000 122854000000 14532500000 2.8513e+10 1.3910e+10 171566971202
6 31.12.2006 12660668341 26602920050 26148301223 61238063838 5198638861 1514900000 126235000000 15790100000 2.9489e+10 1.5306e+10 173975809100

My aim is now to check if the values in each row of the columns 2:10 are higher than the corresponding value in column 11 (i.e. MAX). If in a row a value from a column is higher than the value in the MAX column I want to overright the actual value in the column with the value from the MAX column.

For example if I have the following data:

a b c MAX
2 3 4 3
4 5 6 6
6 7 9 8

I would like to have in the corrected data the following output

a b c MAX
2 3 **3** 3
4 5 6 6
6 7 **8** 8

Thank you very much for your help!

dataframe r
2 Answers

Using your short example and the library dplyr, you could do:

Reprex

  • Data
df <- structure(list(a = c(2L, 4L, 6L), b = c(3L, 5L, 7L), c = c(4L, 
6L, 9L), MAX = c(3L, 6L, 8L)), class = "data.frame", row.names = c(NA, 
-3L))
  • Code
library(dplyr)

df %>% transmute(across(.cols = 1:3, ~ ifelse(.x > MAX, MAX, .x)))
  • Output
#>   a b c
#> 1 2 3 3
#> 2 4 5 6
#> 3 6 7 8

Created on 2022-03-31 by the reprex package (v2.0.1)

EDIT: With your real data and following @langtang's comment to keep first and last columns, you could do:

Reprex

  • Data
structure(list(date = c("31.12.2001", "31.12.2002", "31.12.2003", 
"31.12.2004", "31.12.2005", "31.12.2006"), ABI.BR = c(7342987743, 
6280730128, 6324404232, 7850834622, 12233279545, 12660668341), 
    NOVN.S = c(21072681660, 17748921146, 19755193920, 20947725570, 
    26451714210, 26602920050), ROG.S = c(19737660401, 20316428627, 
    20002601887, 19134043533, 22833829836, 26148301223), NESN.S = c(57324018814, 
    61501808862, 56367998444, 54889985325, 58587603997, 61238063838
    ), NOVOb.CO = c(3199830778, 3350848339, 3510991763, 3905732023, 
    4527870421, 5198638861), HRMS.PA = c(1226900000L, 1242300000L, 
    1230000000L, 1331400000L, 1427400000L, 1514900000L), TTEF.PA = c(1.05895e+11, 
    92108991318, 93961037997, 87346032957, 1.22854e+11, 1.26235e+11
    ), OREP.PA = c(13740400000, 1.4288e+10, 14029100000, 13641300000, 
    14532500000, 15790100000), SASY.PA = c(6.488e+09, 7.448e+09, 
    8.048e+09, 1.5733e+10, 2.8513e+10, 2.9489e+10), LVMH.PA = c(1.2229e+10, 
    1.2693e+10, 1.1962e+10, 1.2481e+10, 1.391e+10, 1.5306e+10
    ), MAX = c(163033294916, 144604417185, 122995279989, 123515812436, 
    171566971202, 173975809100)), class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))
  • Code
library(dplyr)

df %>% mutate(across(.cols = 2:10, ~ ifelse(.x > MAX, MAX, .x)))
  • Output
#>         date      ABI.BR      NOVN.S       ROG.S      NESN.S   NOVOb.CO
#> 1 31.12.2001  7342987743 21072681660 19737660401 57324018814 3199830778
#> 2 31.12.2002  6280730128 17748921146 20316428627 61501808862 3350848339
#> 3 31.12.2003  6324404232 19755193920 20002601887 56367998444 3510991763
#> 4 31.12.2004  7850834622 20947725570 19134043533 54889985325 3905732023
#> 5 31.12.2005 12233279545 26451714210 22833829836 58587603997 4527870421
#> 6 31.12.2006 12660668341 26602920050 26148301223 61238063838 5198638861
#>      HRMS.PA      TTEF.PA     OREP.PA    SASY.PA    LVMH.PA          MAX
#> 1 1226900000 105895000000 13740400000 6.4880e+09 1.2229e+10 163033294916
#> 2 1242300000  92108991318 14288000000 7.4480e+09 1.2693e+10 144604417185
#> 3 1230000000  93961037997 14029100000 8.0480e+09 1.1962e+10 122995279989
#> 4 1331400000  87346032957 13641300000 1.5733e+10 1.2481e+10 123515812436
#> 5 1427400000 122854000000 14532500000 2.8513e+10 1.3910e+10 171566971202
#> 6 1514900000 126235000000 15790100000 2.9489e+10 1.5306e+10 173975809100

Created on 2022-03-31 by the reprex package (v2.0.1)

user_136610610 
cols = c('a', 'b', 'c')
df[cols] = lapply(df[cols], \(x) ifelse(x > df$MAX, df$MAX, x))

#   a b c MAX
# 1 2 3 3   3
# 2 4 5 6   6
# 3 6 7 8   8
user_45522950
Related