CODEWITHSUNDEEP
rdataframereplacenumeric
itio
itio

Reputation: 33

Replace numeric value with group minimum

I wish to replace all numeric values 12345689 in a data.frame column (ser04 below) with the minimum of sero04 as per grouping variable cat02. I've tried to find any example online but failed miserably. Here is one attempt with a for-loop which won't run as hoped.

year <- c(1999,2000,2001,2002,2003,2004)
ser01 <- c(42,43,55,86,117,123)
ser02 <- c(67,87,93,56,217,336)
ser03 <- c(12,63,85,86,117,123)
ser04 <- c(55, 123456789, 15,123456789,187,223)
ser05 <- c(77, 48, 55,86,117,123)
ser06 <- c(63, 41, 35,88,19,98)
ser07 <- c(47, 49, 74,116,147,183)
cat01 <- c("aa", "bb", "cc", "aa", "aa","cc") 
cat02 <- c("ff", "ff", "gg", "gg", "hh","ff") 

olddf <- data.frame(year,ser01,ser02,ser03,ser04,ser05,ser06,ser07,cat01,cat02)
newdf <- NULL
newdf <- as.data.frame(newTable)

n <-  0
for(i in 1:6){
    for(j in 2:8){
        n <- n+1
        newdf[n, 1] <- as.numeric(olddf[i,j])
        if(newdf[n, 1]==123456789) newdf[n, 1]<- min(olddf[i,1:4])
    }
}

print(olddf)
print(newdf)

The original dataframe looks like

  year ser01 ser02 ser03     ser04 ser05 ser06 ser07 cat01 cat02
1 1999    42    67    12        55    77    63    47    aa    ff
2 2000    43    87    63 123456789    48    41    49    bb    ff
3 2001    55    93    85        15    55    35    74    cc    gg
4 2002    86    56    86 123456789    86    88   116    aa    gg
5 2003   117   217   117       187   117    19   147    aa    hh<
6 2004   123   336   123       223   123    98   183    cc    ff

and the resulting dataframe should look like

  year ser01 ser02 ser03     ser04 ser05 ser06 ser07 cat01 cat02
1 1999    42    67    12        55    77    63    47    aa    ff
2 2000    43    87    63        55    48    41    49    bb    ff
3 2001    55    93    85        15    55    35    74    cc    gg
4 2002    86    56    86        15    86    88   116    aa    gg
5 2003   117   217   117       187   117    19   147    aa    hh
6 2004   123   336   123       223   123    98   183    cc    ff


but it doesn't...

[EDIT: I noted that this code was from an old attempt and that it doesn't refer to the group variable]

Upvotes: 2

Views: 284

Answers (2)

akrun
akrun

Reputation: 887991

Try this:

olddf$ser04[olddf$ser04==123456789] <-with(olddf, ave(ser04, cat02, FUN=min)[ser04==123456789])

olddf$ser04
#[1]  55  55  15  15 187 223

Upvotes: 1

David Arenburg
David Arenburg

Reputation: 92310

@alexis_laz ave solution is nice, but you could also try data.table or dplyr for speed:

library(data.table)
newdf <- as.data.table(olddf)[, ser04 := ifelse(ser04 == 123456789,  min(ser04), ser04), by = cat02]


#    year ser01 ser02 ser03 ser04 ser05 ser06 ser07 cat01 cat02
# 1: 1999    42    67    12    55    77    63    47    aa    ff
# 2: 2000    43    87    63    55    48    41    49    bb    ff
# 3: 2001    55    93    85    15    55    35    74    cc    gg
# 4: 2002    86    56    86    15    86    88   116    aa    gg
# 5: 2003   117   217   117   187   117    19   147    aa    hh
# 6: 2004   123   336   123   223   123    98   183    cc    ff

Or dplyr

library(dplyr)
newdf <- 
  olddf %>%
  group_by(cat02) %>%
  mutate(ser04 = ifelse(ser04 == 123456789,  min(ser04), ser04))

Upvotes: 2

Related Questions