Working in dataframe, I want to manipulate column values based on values in another column

Question

Working in dataframe, I want to manipulate column values based on values in another column. Here is my reproducible code:

# four items
items <- c("coke", "tea", "shampoo","aspirin")

# scores for each item
score <- as.numeric(c(65,30,45,20))

# making a data frame of the two vectors created
df <- as.data.frame(cbind(items,score))

# score for coke is 65 and for tea it is 30.  I want to
# double score for tea OR coke if the score is below 50

ifelse(df$score[df$items %in% c("coke", "tea")] < 50, df$score*2, df$score)

#the above return NULL values with warning

#the statement df$score[df$items %in% c("coke", "tea")] does pull coke and tea scores

df$score[df$items %in% c("coke", "tea")]

many thanks in advance for your help

Answer 1

This should do the trick for now:

items <- c("coke", "tea", "shampoo","aspirin")

# scores for each item
score <- as.numeric(c(65,30,45,20))

Try using data.frame instead of as.data.frame. Using the latter causes the values to be converted to factors

# making a data frame of the two vectors created
df <- data.frame(items, score)

df
    items score
1    coke    65
2     tea    30
3 shampoo    45
4 aspirin    20


# score for coke is 65 and for tea it is 30.  I want to
# double score for tea OR coke if the score is below 50

df$score[df$items %in% c("coke", "tea")] = ifelse(df$score[df$items %in% c("coke", "tea")] < 50, df$score*2, df$score)

df
    items score
1    coke    65
2     tea    60
3 shampoo    45
4 aspirin    20

This method doesn't work if you end up having duplicate entries for for items though.

# New data with an added entry for item = coke and score = 15:
items <- c("coke", "tea", "shampoo","aspirin","coke")
# scores for each item
score <- c(65,30,45,20,15)

# making a data frame of the two vectors created
df <- data.frame(items, score)


# using the method from above the last entry get converted to a value of 90
# instead of 30
df$score[df$items %in% c("coke", "tea")] = ifelse(df$score[df$items %in% c("coke", "tea")] < 50, df$score*2, df$score)

df
    items score
1    coke    65
2     tea    60
3 shampoo    45
4 aspirin    20
5    coke    90

So if you have any cases where you may have duplicate entries you will have to use this method

df <- data.frame(items, score)

df$score[df$items %in% c("coke", "tea") & df$score < 50] <- 2* df$score[df$items %in% c("coke", "tea") & df$score < 50]

df
    items score
1    coke    65
2     tea    60
3 shampoo    45
4 aspirin    20
5    coke    30

Answer 2

Your problem does not need an if statement. You can just combine two logical statements.

Logical 1: df$items %in% c("coke", "tea")

Logical 2: df$score < 50

By filtering the dataframe on these two logical statements you can multiply the score. and= &, or= |.

df$score[df$items %in% c("coke", "tea") | df$score < 50] <- 2* df$score[df$items %in% c("coke", "tea") | df$score < 50]

Answer 3

items <- c("coke", "tea", "shampoo","aspirin")
score <- as.numeric(c(65,30,45,20))   

If you call data.frame() in the following way you avoid converting the score column to a factor.

df <- data.frame(items=items,score=score)

You don't need an if statement. You can simply extract the values you are interested in based on two logical statements:

df[df$score<50 & df$items %in% c("coke", "tea"), "score"] <- 2 * df[df$score<50 & df$items %in% c("coke", "tea"), "score"]

---

• df$score<50 & df$items %in% c("coke", "tea") selects the rows that match both conditions, i.e. item either coke or tea and score less than 50.

• "score" selects only the score column

• The statement on the right of <- extracts the same value(s) and multiplies them by 2.

Answer 4

The syntax for your if statement is not quite correct, it looks like you're trying to invoke it in a way similar to how it is used in MS Excel. Unfortunately, it's not doing the trick.

I would suggest you take an intro to R course (many are available for free online), such as:

https://campus.datacamp.com/courses/free-introduction-to-r/chapter-1-intro-to-basics-1?ex=1

As for your problem, here is one solution (if i am understanding your problem correctly).

item <- c("coke", "tea", "shampoo", "aspirin")
score <- as.numeric(c(65, 30, 45, 20))

df <- data.frame(item, score)

for (i in 1:length(df$item)){
  if ((df$item[i] == "coke" | df$item[i] == "tea") & df$score[i] < 50) {
    df$score[i] <- df$score[i] * 2
  }
}

View(df)

You'll note that if you now view the updated dataframe ("df"), only the score for item "tea" has been doubled, since it meets both criteria (i.e. item = coke OR tea; AND it's associated score is below 50).

Hope this helps, and good luck.