Replacing row strings based on condition in column values

Question

I have multiple datasets with the following format

head(averagetable)
   Group.1     Moving   Feeding  Standing
1 cluster1 0.05632530 0.1722892 0.7503012
2 cluster2 0.09220779 0.2644481 0.6118506
3 cluster3 0.04863636 0.1268182 0.7993182

I'm quite new to R and but my assignment is simple:

1) I would like to replace the name cluster# in Group.1 by Standing in the row with the highest value in column Standing.

2) The name Moving/Feeding to the second highest value of column Standing

3) The name Feeding/Moving to the thirsd highest value of column Standing.

Hence the output:

print(averagetable)
   Group.1       Moving     Feeding   Standing
1 Moving/Feeding 0.05632530 0.1722892 0.7503012
2 Feeding/Moving 0.09220779 0.2644481 0.6118506
3 Standing       0.04863636 0.1268182 0.7993182

Hope this was clear enough. Note that replacing the strings using order() doesn't suit my needs as I have multiple dataframes and values might be different. I'm guessing ifelse() is the function to use but I'm guessing a for-loop needs to be implemented an I'm unsure on how to do that.

Are all your data frames only 3x3?

divibisan
– divibisan

2019-03-22 15:52:21 +00:00
Commented Mar 22, 2019 at 15:52 — divibisan
– divibisan, Commented Mar 22, 2019 at 15:52

Ronak Shah · Accepted Answer · 2019-03-22 15:53:12Z

1

You could just order Standing and replace the Group.1 values

averagetables$Group.1[order(averagetables$Standing)] <- 
                      c("Feeding/Moving", "Moving/Feeding", "Standing")

averagetables
#         Group.1     Moving   Feeding  Standing
#1 Moving/Feeding 0.05632530 0.1722892 0.7503012
#2 Feeding/Moving 0.09220779 0.2644481 0.6118506
#3       Standing 0.04863636 0.1268182 0.7993182

If there are many rows and you want to change Group.1 values only in top 3 values of Standing we can use tail to subset

inds <- tail(order(averagetables$Standing), 3)
averagetables$Group.1[inds] <- c("Feeding/Moving", "Moving/Feeding", "Standing")

This would change values only in Group.1 for 1st highest, 2nd highest and 3rd highest values of Standing.

data

averagetables <- structure(list(Group.1 = c("cluster1", "cluster2", 
"cluster3"
), Moving = c(0.0563253, 0.09220779, 0.04863636), Feeding = 
c(0.1722892, 
0.2644481, 0.1268182), Standing = c(0.7503012, 0.6118506, 0.7993182
)), row.names = c("1", "2", "3"), class = "data.frame")

edited Mar 22, 2019 at 15:53

answered Mar 22, 2019 at 15:40

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

juancda4 Over a year ago

Thank you for your reply. I have multiple dataframes looking similar to this one. Values for the Standing column vary so I need a script with some sort of hierachical classification of the values.

Ronak Shah Over a year ago

@juancda4 The later part of my answer using tail should work for any dataframe. It will get index of top 3 values of Standing and use those indexes to change Group.1 value of the dataframe. Can you check that?

juancda4 Over a year ago

Right. Thank you!

Collectives™ on Stack Overflow

Replacing row strings based on condition in column values

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related